Побудова запитів користувача в середовищі СКБД Access

Мета: Навчитися створювати вибірку даних з декількох таблиць бази даних. Відображати результати запитів у вигляді форми та звіту.

Завдання:

1. Створити декілька простих запитів, за допомогою конструктора запитів, з однієї та декількох таблиць.

2. Створити підсумковий запит за допомогою майстра простих запитів.

3. Створити декілька параметричних запитів.

4. Створити запит з використанням складної довільної умови.

5. Створити перехресний запит.

6. Всі сформовані запити: продивитись на мові SQL, продивитися у формі таблиці, створити форму для виведення запиту на екран, створити звіт для друку документу.

Теоретичні відомості та приклад створення

бази даних у СКБД MS Access

3.1. Загальні поняття про запит в MS Access

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

По суті, запит-вибірка - це питання, яке задається MS Access про інформацію, що зберігається в БД. MS Access виконує обробку запиту і зберігає записи, що задовольняють запиту, у тимчасовій таблиці, яка називається динамічним набором записів (даних). Динамічні набори записів не зберігаються в БД, а формуються кожен раз при виконанні запиту. При зміні даних, що містяться у динамічному наборі записів, MS Access внесе відповідні зміни в дані тих таблиць, з яких вони були вибрані. У динамічні набори записів можна також вносити нові записи, які будуть додаватись у відповідні таблиці.

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

У MS Access існує ще один тип запитів - підпорядковані запити. Результати виконання підпорядкованих запитів використовуються як фільтри для вибору записів в інших типах запитів.

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

Для полегшення створення запитів у MS Access використовується таблиця (шаблон) QBE (Query By Example) вікна конструктора запитів. Всі типи запитів, крім підпорядкованих, можуть бути створені за допомогою QBE.

Для створення запиту необхідно заповнити клітини таблиці QBE, Критерії та опції, що вводяться у цю таблицю визначають вибір та впорядкування записів, що є результатом запиту. MS Access транслює таблицю QBE у вирази SQL (Structured Query Language). SQL - є стандартною мовою запитів, яка використовується у багатьох СКБД. У MS Access існує режим створення запитів безпосередньо на мові SQL.

3.2. Створення запитів-вибірок за допомогою конструктора

Для створення запиту у вікні бази даних переходимо на вкладку Запросы, натискаємо кнопку Создать, у вікні Новый запрос вибираємо Конструктор та натискаємо кнопку Ok. На екран виводиться вікно шаблону QBE.

Рис. 3.1. Вікно шаблону QBE

У вікно конструктора запитів додаємо необхідні таблиці та запити. Для додавання таблиці/запиту використовуємо пункти меню Запрос/Добавить таблицу … або натискаємо кнопку . Вибрані таблиці повинні бути зв'язані по одному або декількох полях.

У рядок Поле шаблону QBE переміщуємо за допомогою "мишки" необхідні для формування динамічного набору записів поля із списків таблиць/запитів.

Рядок Сортировка містить значення по возрастанию/по убыванию і використовується для впорядкування даних динамічного набору записів по деякому полю відповідного стовпця таблиці QBE. При упорядкуванні даних по декількох полях спочатку виконується впорядкування по полю, яке розміщене ліворуч.

Рядок Вывод на экран містить для кожного поля прапорець, який дає змогу вивести/сховати деяке поле запиту.

Рядок Условие отбора використовується для формування фільтра для відповідного поля. Для побудови складних виразів у рядку Условие отбора, як правило, використовується засіб Построитель выражений, який можна викликати кнопкою Построить

Таблиця QBE залежно від типу запиту може містити інші рядки.

Розрахункові поля створюються за допомогою засобу Построитель выражений при цьому курсор необхідно розмістити у рядку Поле вільного стовпця шаблону QBE.

У базах даних Microsoft Access для побудови фільтрів і пошуку значень полів, записів у діалогових вікнах Пошук і Заміна чи у командах і виразах допускається використання ряду символів приведених у додатку 3.

Для створення умов відбору і розрахункового поля використовуються підстановлювальні знаки (див. додаток 3) як прототипи для інших символів при вказівці прикладу пошуку в наступних випадках:

· відома тільки частина значення;

· потрібно знайти значення, що починаються з конкретної літери або відповідні певному шаблону.

Для перегляду результатів у режимі конструктора із списку режимів перегляду кнопки Вид обирається елемент Режим таблицы.

Для повернення до режиму конструктора обирається елемент Конструктор

Для відображення або створення запиту на мові SQL із списку режимів перегляду кнопки Вид обирається елемент Режим SQL.

Для редагування створеного запиту у вікні бази даних на вкладці Запросы вибираємо потрібний запит та натискаємо кнопку Конструктор.

3.3. Побудова параметричного запиту

Створимо у режимі конструктора запит з параметром "Забезпечення кафедри викладачами".

Запит будується у режимі конструктора на основі трьох таблиць "Кафедра", "Викладач" та "Дисципліна". Для поля "НазваКафедри" у рядку Условие отбора введено значення параметру.

Рис. 3.2. Запит "Забезпечення кафедри викладачами", відкритий у режимі конструктора

При виконанні запиту на екран виводиться вікно для введення назви кафедри, за допомогою якого формується відповідний фільтр для виведення даних.

3.4. Створення підсумкового запиту за допомогою майстра

Для створення запиту у вікні бази даних переходимо на вкладку Запросы, натискаємо кнопку Создать, у вікні Новый запрос вибираємо Простой запрос та натискаємо кнопку Ok. Послідовно виконуючи кроки, запропоновані майстром запитів, створюємо відповідний підсумковий запит.

Створимо у режимі майстра підсумковий запит "Середній бал" для формування середнього балу кожного студента.

В даному запиті виконується групування даних за прізвищем та номером залікової книжки студента та обчислюється загальна середня успішності навчання кожного студента.

У вікні Создание простых запросов з таблиці "Студент" обираємо поля "ПІП_Студента" та "НомерЗаліковоїКнижки", з таблиці "Успішність" поле "Іспит", далі обираємо тип запиту – итоговый та натискаємо кнопку Итоги… з переліку функцій обираємо функцію Avg(), потім визначаємо ім'я запиту.

Створений запит відкриваємо у режимі конструктора для редагування.

Рис. 3.3. Запит "Середній бал", відкритий у режимі перегляду

Для виведення запитів на екран створюємо відповідні форми як в лабораторній 2 створювали для таблиць.

3.5. Створення звітів

Звіти – це об'єкти MS Access, які використовуються для виводу на друк даних таблиць або динамічних наборів (результатів запиту). При формуванні звітів надається можливість групування даних та виконання обчислень.

Звіт може містити декілька розділів, за допомогою яких виконується формування звіту:

Заголовок відображається на початку звіту;

Верхній колонтитул містить інформацію, яка відображається на початку кожної сторінки;

Заголовок групи містить інформацію, яка виводиться перед першим записом групи. Як правило, в області заголовка групи розміщають назви полів, зв'язаних таблиць чи запитів;

Область даних, використовується для відображення даних зв'язаних таблиць чи запитів;

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

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

Нижній колонтитул відображається внизу кожної сторінки.

Звіт можна створити за допомогою майстра звіту або у режимі конструктора звітів. Вікно конструктора звітів має таку саму структуру, як і вікно конструктора форм.

Для створення звіту у вікні бази даних переходимо на вкладку Отчеты, натискаємо кнопку Создать. У вікні Новый отчет обираємо необхідний режим для створення звіту, натискаємо кнопку Ok.

Питання для самоперевірки

1. Що розуміють під запитом до бази даних?

2. Яка конструкція простого запиту на мові SQL?

3. Яка конструкція запиту за умовою на мові SQL?

4. Яка конструкція підсумкового запиту на мові SQL?

5. Які види виведення результатів запиту забезпечує СКБД Access?