Лабораторна робота № 5 запити на зміну даних

Тема: запити на зміну даних в MS Access.

Мета: навчитися створювати запити модифікації даних в Access за допомогою бланка QBE.

Запити на зміну даних

При необхідності зробити однотипні дії над декількома рядками в таблиці звичайно застосовують запити-дії. Запити-вибірки роблять вибірку даних з бази за деяким критерієм, а запити-дії дозволяють маніпулювати даними з вибірки: редагувати, видаляти й додавати поля й записи.

Спочатку зручно створити запит-вибірку, що фільтрує дані, над якими необхідно зробити дію. Запит запускається, перевіряється вихідна таблиця, і якщо запит правильно відбирає дані, які повинні модифікуватися, то він перетворюється в запит-дію. Ця послідовність дій застосовується й при роботі з дизайнером запитів, і при використанні безпосередньо команд SQL.

Порядок виконання лабораторної роботи

1. Створення запитів створення таблиць

Створимо запит-дію, що формує таблицю "Дані в деканат" про студентів, що здали заборгованість протягом сесії. За основу візьмемо запит "Підсумок", створений у попередній лабораторній роботі. Викличемо його в режимі конструктора. Скинемо всі умови відбору записів і всі групові функції. Групування залишимо по номерах студентів і прізвищ, а в групи будуть входити тільки записи, у яких дата здачі заборгованості попадає в часовий діапазон, що задаєтся користувачем.

Для поля "Дата здачі" у рядку "Групова операція" установимо значення "Умова", а в рядку "Умова відбору" введемо вираз

[Введіть дату закінчення сесії]

Візьмемо створений параметричний запит-вибірку, що виводить інформацію про боржників за певний період. Для перетворення його в запит на створення таблиці виконаємо команду "Створення таблиці" у меню "Запит". У вікні, що з'явилося, уведемо ім'я створюваної таблиці. Збережемо отриманий запит під ім'ям "Підсумки у деканат".

Запустимо запит на виконання командою "Запуск" меню "Запит". Access запросить параметри для умови відбору, після чого (проаналізувавши всі записи на відповідність цій умові) видасть число записів, на яких буде сформована генеруюча таблиця. Натиснемо кнопку "Ок". Для перегляду створеної таблиці активізуємо вікно бази даних і, вибравши корінець "Таблиці", переглянемо таблицю "Дані в деканат".

Виконання запиту зі створенням таблиці приводить до знищення раніше виведених результатів у вихідній таблиці. Можна для кожного нового запуску такого запиту задавати нові імена вихідних таблиць. Або ж використати додавання даних до вже існуючої таблиці. Для цього замість команди "Створення таблиці" у меню "Запит" виконується команда "Додавання" цього ж меню. У вікні, що з'явилося, необхідно ввести тільки ім'я таблиці для дописування підсумкових даних.

2. Створення запиту на додавання даних в існуючу таблицю

Відкриємо базу даних STUD і в ній – таблицю "Нові результати". Відкриємо вікно нового запиту в режимі конструктора й виконаємо команду "Додавання" у меню "Запит". Введемо ім'я таблиці "Нові результати". У діалоговому вікні "Властивості запиту" уведемо ім'я таблиці, у яку будемо додавати дані – "Результати". Якщо дані для копіювання перебувають в іншій базі даних, то слід установити перемикач у положення "В іншій базі даних" і ввести внизу шлях до бази джерела. Натиснемо кнопку "Ок".

З'явиться бланк запиту на додавання. Нам необхідно скопіювати всі поля, тому зі списку назв полів переносимо символ "*". Якщо назви полів або їхніх властивостей різні, то необхідно в бланку QBE описати всі відповідності між полями вихідної таблиці й таблиці приймача. Якщо якесь поле є в таблиці приймача, але не визначено відповідність цьому полю в джерелі, то в ньому ставиться значення за замовчанням.

Access дозволяє задавати умову фільтрації даних для вставки в таблицю. Для цього в стовпцях, по яких буде йти відбір, у рядку "Умова відбору" вводиться коректна умова для вибірки даних. Уведемо для поля "Дата здачі" параметричне обмеження по даті, починаючи з якого буде вироблятися вибірка на додавання

[Введіть дату]

Зберігаємо отриманий запит-дію під ім'ям "Додаткові результати". Запуск запиту на виконання виконаємо командою "Виконати" меню "Запит", або клацнувши кнопку "Виконати" на панелі інструментів. Уведемо дату у відповідь на запит діалогового вікна й натиснемо "Ок". З'явиться вікно, що інформує про число записів, які будуть скопійовані. Натиснемо кнопку "Ок". Відкривши таблицю "Нові результати" можна побачити записи, додані до неї запитом-дією.

3. Створення запиту на відновлення груп записів

Запит на відновлення легко виходить із запиту-вибірки простим перемиканням запиту з режиму "Вибірка" меню "Запит" у режим "Відновлення". Необхідно тільки ввести формули перерахування змінюваних значень у рядок "Відновлення", що з'явився. Виконання такого запиту приведе до змін у таблиці.

Перерахуємо зарплату викладачам з обліком параметрично заданого коефіцієнта інфляції щодо попереднього місяця. Створюємо запит-вибірку по таблиці "Викладачі", куди включаємо поля "Прізвище", "Номер_П", "Зарплата". Потім у меню "Запит" виконаємо команду "Відновлення". У блоці QBE з'явиться рядок "Відновлення", куди вносяться формули зміни необхідних полів. Введемо у поле "Зарплата" формулу

зарплата * [Коеф. інфляції]

Можна задати умову відбору викладачів таким чином, щоб вводити коефіцієнт було не потрібно, або щоб була можливість змінити коефіцієнт на інший. Вибір записів для зміни виробляється в рядку "Умова вибору". Можливості умовного відбору були розглянуті в попередній лабораторній роботі.

Збережемо запит під ім'ям "Перерахування зарплати" і запустимо його на виконання. Після введення параметра коефіціента з'явиться інформаційне повідомлення про число змінюваних записів. Для підтвердження змін натиснемо кнопку "Ок" (рис.10).

В ідновлення декількох полів здійснюється подібним чином.

Рисунок 10 – Інформаційне повідомлення про число змінних записів

Особливістю такого запиту є можливість використання перехресної ініціалізації полів. Так при наявності полів "Поле_1" і "Поле_2" у таблиці можна "Полю_1" привласнити значення "Поле_2" * 2 і одночасно "Полю_2" привласнити "Поле_1" * 3.

Access копіює змінюваний запис, обчислення змін робить над вихідним записом, а результати цих обчислень записуються на місце вихідного запису й уже цей запис переноситься в таблицю.

4. Створення запитів на видалення даних з таблиці

Дії по видаленню груп записів аналогічні діям по відновленню, але тут виконується команда "Видалити" меню "Запит". Вводиться умова в бланк запиту, якому повинні задовольняти групи записів, призначені для видалення. Після цього запит перемикається в режим "Видалення" і виконується команда "Виконати" меню "Запит".

Зміст звіту

У звіті треба навести тему, мету лабораторної роботи, коротенький опис своєї предметної області, описи очікуваних цілей застосування усіх запитів-дій (запиту створення таблиці, запитів на додавання, відновлення та видалення даних), конструктори та результати виконання усіх запитів, висновки.