Як вже зазначалося вище, сучасні програми роботи з базами даних повинні будується на повсюдне використання мови. Це поширюється і на запити додавання і зміни даних.
Запити на додавання
Припустимо, необхідно створити запит, який додає в таблицю «Товар» відомості про товар, введені користувачем. Структура таблиці «Товар» нам відома - вона складається з полів «НаіменованіеТовара» і «ЕдініцаІзмеренія» (значення поля «КодТовара» генерується автоматично).
Для того, щоб створити запит на додавання запису в таблицю «Товари», необхідно виконати наступну послідовність дій:
1. Виконати команду Створення запиту за допомогою конструктора.
2. Перейти в режим введення тексту запиту на мові SQL, виконавши команду Вид ® Режим SQL.
3. Набрати текст SQL-запиту, як показано на малюнку:
4. Виконати команду Файл ® Зберегти для збереження запиту і ввести ім'я запиту «ДобавленіеТовара», натиснути «ОК» і закрити вікно конструктора. Після цього в списку запитів з'явиться новий запит - «ДобавленіеТовара»:
Для того, щоб побачити результат запиту «Додавання товару» необхідно виконати наступну послідовність дій:
1. Виконати запит «Додавання товару»: зробіть на ньому подвійне клацання мишею.
У вікні відповісти «Так». дозволивши тим самим запуск запиту на зміну даних.
Оскільки запит є параметричним, то система спочатку запросить значення параметра «НаіменованіеТовара».
2. Ввести в якості найменування товару текст «Горілка Золотий рай 0,5л» як показано на малюнку:
4. Ввести в якості одиниці вимірювання текст «бут», як показано на малюнку:
6. Підтвердити додавання запису в наступному діалозі
8. Виконати команду Сервіс ® Видалити фільтр для зняття фільтра і поновлення набору даних. отримаємо:
Як видно з малюнка, в результаті виконання запиту до таблиці «Товар» була додана нова запис.
Додавання даних в таблицю на основі запиту було детально розглянуто вище в частині «Створення запиту на збереження даних запиту в тимчасовій таблиці».
Запити на зміну
Запити на зміну використовуються для зміни однієї і більше записів таблиці. Розглянемо приклад зі зміною запису.
Для виконання цієї операції за допомогою запиту SQL необхідно виконати наступні дії:
1. Виконати команду «Створення запиту за допомогою конструктора».
2. Перейти в режим введення тексту на мові SQL, виконавши команду Вид ® Режим SQL.
3. Ввести текст SQL-запиту, як показано на наступному малюнку:
Виконаємо створений запит командою Запит ® Запуск. Відкриємо таблицю «Товар»:
Аналогічним чином можна змінити за допомогою запиту будь-яке інше поле будь-якої таблиці.
Припустимо, необхідно виключити будь-який товар з подальшого використання в запитах або формах введення даних і звітів.
Тут можливі два випадки:
1) якщо по даному товару не проводилося ніяких операцій, то потрібно відкрити таблицю «Товар» і видалити запис з найменуванням даного товару;
2) набагато частіше зустрічається ситуація, коли по товару було багато операцій (закупівля, продаж, повернення, списання, переміщення та ін.), І інформація про ці операції зберігається в базі даних. Тоді, якщо в базі даних правильно була створена схема даних, в якій були правильно визначені зв'язку та механізм підтримки цілісності бази даних, видалити цей запис не вийде, тому що якщо видалити цей запис, потрібно видаляти і всі операції, пов'язані з даним товаром. Якщо видалити операції, то база даних перестане відображати реальний стан бізнес-процесів: будуть неправильно розраховуватися залишки, підсумки, формуватися невірні звіти.
Вихід полягає в додаванні в таблицю спеціального поля, яке буде являти собою позначку про видалення. Фактично, запис про товар з довідника віддалятися не буде. Користувачі, що запитують список товарів, будуть звертатися не до таблиці, а до запиту, в якому використовуються записи довідника вже відібрані.
Розглянемо другий випадок на конкретному прикладі.
Припустимо, необхідно створити запит, позначати певний запис в таблиці «Товари» як віддалену. При цьому, запис про цей товар більше не повинна використовуватися в інших запитах.
1. Додати в таблицю «Товар» поле «ПометкаНаУдаленіе» як показано на наступному малюнку:
2. Створити запит на встановлення позначки на видалення в таблиці «Товар».
2.1. Виконати команду Створення запиту за допомогою конструктора.
2.2. Виконати команду Вид ® Режим SQL для переходу до введення тексту запиту на мові SQL.
2.3. Ввести текст SQL-запиту, як це показано на малюнку:
2.4. Зберегти запит, виконавши команду Файл ® Зберегти і
задавши йому ім'я «ПометкаТовараНаУдаленіе».
Простежимо роботу цього запиту.
1. Виконаємо команду Запит ® Запуск для запуску запиту на
виконання.
2. Відповімо Так в діалозі підтвердження виконання запиту на
зміна записів.
3. Введемо найменування товару «Телевізор», що позначається як віддалений.
Тепер, для того, щоб працювати з актуальним списком товарів
створимо запит, який формує цей список. Для цього:
1. Виконаємо команду Створення запиту за допомогою конструктора.
2. Додамо таблицю «Товари» у вікно конструктора.
3. Перенести всі поля в область результатів запиту.
4. У графу ПометкаНаУдаленіе, в рядок «Умова відбору» введемо текст «= Брехня». У рядку висновок на екран знімемо прапорець «Висновок на екран». як це показано на наступному малюнку:
5. Виконаємо команду Файл ® Зберегти і введемо ім'я запиту «СпісокТоваровАктуальний».
Виконавши цей запит (Вид ® Режим таблиці), можна побачити, що помічених на видалення (за допомогою запиту «ПометкаТовараНаУдаленіе») товарів немає в списку. Це видно на наступному малюнку:
Тепер запит «СпісокТоваровАктуальний» потрібно використовувати у всіх запитах замість прямого звернення до таблиці «Товари».
Досить часто зустрічається завдання, коли потрібно змінити відразу кілька записів таблиці. Наприклад, збільшити ціни на 25% або змінити групу товарів у кількох товарів. Розглянемо подібний приклад.
Припустимо, необхідно створити запит, який би змінював ціни на товари в таблиці «Товар» на певний відсоток. Наприклад, необхідно збільшити всі ціни на 25%.
Вихідна таблиця «Товар» представлена на наступному малюнку:
Створимо SQL-запит, що змінює ціни на певний відсоток. Для цього необхідно.
1. Виконати команду Створення запиту за допомогою конструктора.
2. Виконати команду Вид ® Режим SQL.
3. Ввести текст запиту на мові SQL як це показано на малюнку:
4. Виконати команду Файл ® Зберегти і ввести ім'я для нового запиту «ІзмененіеЦенНаПроцент».
Простежимо, як працює цей запит. Вище дана таблиця з вихідними цінами на товари. Виконаємо запит «ІзмененіеЦенНаПроцент». Для цього необхідно:
1. Виконати команду Запит ® Запуск.
2. Ввести в якості параметра «Відсоток» число 25. Це означає, що ціни будуть збільшені на 25%.
3. Відповісти Так на запит підтвердження зміни записів.
Результат роботи запиту представлений на наступному малюнку:
Як видно з таблиці, ціни на товари дійсно були збільшені на 25%. Якщо вказати параметр «Відсоток» з мінусом, то ціни будуть зменшені на вказане число відсотків.
Порядок виконання роботи
1. Вивчити теоретичний матеріал, розбираючи всі представлені
в ньому приклади розв'язання типових задач.
3. Створити таблиці, зв'язку між ними, відповідно до варіанту завдання.
4. Заповнити таблиці даними, наведеними в варіанті завдання.
5. На основі завдання визначити структуру результуючого відносини (склад полів таблиці, одержуваної в результаті виконання
запиту).
6. Визначити джерела даних для створення результуючого відносини.
7. Визначити послідовність дій, необхідних для отримання результуючого відносини - послідовність операцій з вилучення даних з таблиць-джерел і їх подальшій обробці.
9. Перевірити правильність рішення задачі шляхом запуску запитів і перевірки результатів їх виконання.
1. Які види запитів можна створювати в Access?
2. Які способи створення запитів Вами відомі?
3. Що таке майстер? Опишіть процес побудови простого запиту за допомогою майстра.
4. Що таке детальний запит? Опишіть процес побудови детального запиту по кроках за допомогою майстра на конкретному прикладі.
6. Поняття підсумкового запиту. Опишіть процес створення підсумкового запиту за допомогою майстра на конкретному прикладі.
7. Як запустити запит на виконання?
8. Що таке конструктор? Опишіть елементи інтерфейсу конструктора.
9. Опишіть процес створення простого запиту в конструкторі на прикладі. Чим процес побудови запиту в конструкторі відрізняється від побудови запиту за допомогою майстра.
10. Як встановити сортування в запиті?
11. Як встановити умову відбору записів в запиті?
12. Як додати таблицю або запит у вікно конструктора?
13. Опишіть по кроках процес побудови детального запиту на конкретному прикладі.
14. Як встановити зв'язок між таблицями запиту? Що таке параметри об'єднання?
15. Чому при побудові запитів важливо, щоб на етапі створення таблиць бази даних були правильно визначені зв'язки між ними.
16. Що таке параметри запиту? Як встановити параметри запиту в режимі конструктора? Як можна використовувати параметри запиту?
17. Опишіть по кроках на конкретному прикладі процес побудови підсумкового запиту за допомогою конструктора.
18. Як встановити угруповання значень в запиті? Опишіть на конкретному прикладі.
19. Опишіть процес створення запиту на додавання записів в таблицю на конкретному прикладі. Чому дані складних запитів необхідно зберігати у тимчасових таблицях?
20. Опишіть на конкретному прикладі процес створення запиту на видалення записів.
21. Опишіть процес створення структури таблиці за допомогою запиту SQL.
22. Що таке обчислюване поле? Як створити обчислюване поле в запиті?
23. Опишіть по кроках технологію створення запиту на об'єднання даних з декількох таблиць. Наведіть приклад.
4. Новалис C. Access 97. Керівництво по макромова і VBA.
Зберегти результати запиту в тимчасовій таблиці.
6. Отримати підсумки по товарах (по полях Кількість і Сума)
з угрупованням по товарах і постачальникам.
Зберегти результати запиту в окремій таблиці.
6. Отримати підсумки по товарах (по полях «Кількість» і «Сума»)
з угрупованням по клієнтам і товарам.
Зберегти результати запиту в окремій таблиці.
5. Отримати підсумки по товарах (по полях «Кількість» і «Сума») з угрупованням по покупцям і товарам.
Вихідні дані представлені в вигляді наступних таблиць:
Виручка від реалізації товарів
Виручка від реалізації послуг
Доходи з цінних паперів
Доходи дочірніх компаній
Надходження коштів від засновників
За допомогою конструктора або майстра створити такі запити:
1. Отримати список контрагентів, відсортоване по полю «НаіменованіеКонтрагента». Результати зберегти за допомогою запиту в окремій таблиці.
7. Створити запити для додавання записів шляхом введення параметрів для всіх таблиць.
8. Створити запит для зміни значення поля «Сума» в таблиці «ПоступленіеДенежнихСредств».
За допомогою конструктора або майстра створити такі запити:
1. Отримати список контрагентів, відсортоване по полю
«НаіменованіеКонтрагента». Результати зберегти за допомогою запиту в окремій таблиці.
7. Створити запити для додавання записів шляхом введення параметрів для всіх таблиць.
8. Створити запит для зміни значення поля «Сума» в таблиці «РасходДенежнихСредств».
Результати зберегти в окремій таблиці за допомогою запиту на додавання записів.
2. На підставі запиту, створеного в п.1, розрахувати підсумки (суми за графами «ПоступленіеСумма» і «РасходСумма») надходження і витрачання коштів по контрагентам.
4. Розрахувати залишок коштів (Прихід-Витрата) на певну
дату, встановлену користувачем в параметрі запиту.