Лабораторна робота №5 Формування запитів

Скачати

ТЕОРЕТИЧНІ ВІДОМОСТІ

1. Створення простого запиту на вибірку

Запити є об’єкти бази даних, основне призначення яких – відображення або зміна даних. Існує декілька типів запитів. Запит на вибірку використовується для відображення даних або для створення нових таблиць, запит на зміну служить для зміни даних в джерелі даних і так далі. Запит на вибірку отримує дані з таблиць і інших запитів і відображає в режимі таблиці. Для створення простого запиту на вибірку перейдіть на сторінку «стрічки» «Створення».

У розділі «Інші» можна вибрати для створення запиту режим Майстра або режим конструктора.

Виберіть таблицю, що містить дані для запиту і натисніть кнопку «Майстер запитів».

На першій сторінці Майстра виберіть тип запиту «Простий запит» і натисніть кнопку «ОК». На другій сторінці можна вибрати або змінити джерело даних, яке може виступати як таблицею бази даних, так і створеним раніше запитом. У групі «Доступні поля» треба вибрати поля, які будуть включені в список полів запиту. Додавання полів в список і видалення із списку виконується за допомогою кнопок із стрілками. Вибравши потрібні поля, натисніть кнопку «Далі».

Задайте для запиту ім’я або залиште ім’я, призначене йому Майстром. Для завершення створення запиту і перегляду результатів його виконання натисніть кнопку «Готово».

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

Для перегляду запиту в режимі SQL натисніть відповідну кнопку в рядку стану Microsoft Office Access.

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

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

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

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

Для створення запиту в режимі конструктора на основі зв’язаних таблиць натисніть кнопку «Новий об’єкт: запит» на сторінці «стрічки» «Створення». У вікні «Додавання таблиці» виділимо таблиці «Витрати», «Співробітники» і «Статті витрат», утримуючи натиснутою клавішу Ctrl. Потім натисніть кнопку «Додати». Після додавання всіх необхідних таблиць натисніть кнопку «Закрити».

У верхній частині робочої області конструктора представлені включені в запит таблиці і зв’язки між ними.

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

У списку, що відкрився, представлені всі поля всіх таблиць запиту. Виберіть поле «Дата» з таблиці «Витрати».

Таким же чином виберемо наступні поля – поле «ФІО» з таблиці «Співробітники», поле «Сума» з таблиці «Витрати» і поле «Стаття витрат» з таблиці «Статті витрат». Якщо якесь поле було вибране помилково, виділити відповідний стовпець таблиці і видалити його, натиснувши на кнопку «Видалити стовпці» на сторінці «стрічки» «Конструктор». Потім можна буде повторити вибір поля. По будь-якому з вибраних полів можна задати умову відбору. Наприклад, можна включити в запит тільки ті записи таблиці «Витрати», для яких значення поля «Сума» буде більше 20. Натисніть кнопку «Виконати» для повернення в режим таблиці і перегляду результатів виконання запиту.

Отримана в результаті виконання запиту вибірка включає зв’язані дані зі всіх трьох таблиць, причому у вибірці немає жодного запису, для якого значення поля «Сума» менше або рівно двадцяти.

3. Підрахунок даних за допомогою запиту

У запитах Microsoft Office Access 2007 можна використовувати статистичні функції для виконання різних розрахунків за наявними даними. Використовувати статистичні функції можна, як мінімум, двома способами. Один з них – додавання в таблицю з результатами запиту рядка підсумків, як для звичайної таблиці. На сторінці «стрічки» «Головна» розкрийте розділ «Записи»

і натисніть кнопку «Підсумки».

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

Аналогічним чином встановимо для поля «Дата» варіант «Максимальне значення».

Тепер в останньому рядку таблиці відображається найпізніша з дат і загальна сума витрат по всіх записах вибірки. Використання рядка «Підсумок» дозволяє проводити підрахунок даних по вибірці, не змінюючи структуру запиту. Відключити рядок «Підсумки» треба так: розкрийте розділ «Записи». Натисніть кнопку «Підсумки» і перемкніться в режим конструктора.

Другий спосіб підрахунку – використання підсумкових запитів. У підсумковому запиті обчислюються проміжні підсумки по групах записів, тобто спочатку згрупуються записи, що мають однакові значення в заданих полях, а потім обчислюється необхідна статистика за даними кожної групи. Таким чином, для кожного поля, включеного у вибірку, необхідно задати або ознаку групування за даними цього поля, або статистичну функцію для обробки його значень. Натисніть кнопку «Підсумки» на сторінці «стрічки» «Конструктор». У таблиці опису полів з’явиться новий рядок – «Групова операція». За замовчуванням для всіх полів в цьому рядку встановлена ознака групування. Клацніть в рядку «Групова операція» поля «Сума» відкрийте список допустимих групових операцій для поля «Сума» і виберіть функцію підсумовування значень.

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

Опція «Вивід на екран» для даного поля буде відключена автоматично.

Натисніть кнопку «Виконати» для переглядання результатів виконання запиту.

ЗАВДАННЯ РОБОТИ

Створення запитів

1. Скопіювати базу даних із попередньої лабораторної роботи (Lab4).
2. Створити простий запит на вибірку Клієнти, що відображає усі записи таблиць Клієнти і Реалізація за полями Код клієнта, НазвФірми, Телефон, №НаклВитратп, Дата, Відмітка про оплату
3. Створити простий запит Рух товару, що відображає всі данні таблиці Рух товару, додати розрахункове поле для визначення загальної вартості товару (кількість*ціна). Відображати тільки ті товари, загальна вартість яких >50000
4. Створити запит із параметрами Оплачена реалізація, що виводить записи про оплачену реалізацію за певний період з усіма полями таблиці Реалізація, дати початку і кінця періоду повинні вводитися під час виконання запиту у вигляді параметру

Оформлення звіту

1. У звіті повинні бути зазначені:

• Номер лабораторної роботи
• Назва лабораторної роботи
• Завдання до лабораторної роботи
• До кожного пункту завдання повинна бути зроблена копія екрану
• Докладно описано хід виконання лабораторної роботи, додані копії екранів
• Зроблено висновок до лабораторної роботи
• Звіт оформити у вигляді презентації.

2. Слайди презентації роздрукувати.

ХІД РОБОТИ

Створення простого запиту на вибірку Клієнти

1. у вікні бази даних перейти до стрічки Створення, обрати  Мастер запросов
2. у діалоговому вікні Новый запрос вибрати майстра Простой запрос
3. натиснути кнопку <ОК>;
4. вказати ім’я таблиці або запиту, на якому має базуватися створюваний запит, а потім вибрати поля, з яких відбираються дані. Спочатку слід вказати на таблицю Клієнти і вибрати з неї поля Код клієнта, Назва Фірми, Телефон
5. якщо необхідно, вказати додаткові таблиці або запити, а потім вибрати з них поля. Повторювати ці дії доти, доки не будуть відібрані всі необхідні поля. Скажімо, у нашому прикладі слід вказати додатково на таблицю Реалізація і відібрати поля №НаклВитрат, Дата, Відмітка про оплату
6. якщо серед вибраних до запиту полів є числові, для них можна розрахувати підсумки за функціями SUM(), AVG(), MAX(), MIN() й іншими, а також підрахувати загальну кількість записів, які запит виведе на екран. Доступ до цих дій можна отримати, натиснувши в діалоговому вікні кнопку Итоги. Якщо числових полів у запиті немає, таке діалогове вікно в Мастере запросив не з’явиться;
7. в останньому діалоговому вікні користувачеві пропонується надати ім’я запиту, у нашому прикладі – Klienti, і вибір: виконати запит чи переглянути його структуру в режимі Конструктора запросов

 

 

Створення запиту Клієнти із розрахунковим полем

1. Створити простий запит Рух товару
2. у вікні відкритої БД перейти до стрічки Створення, обрати запит Рух товарів, натиснути кнопку Конструктор. З’явиться вікно, у верхній частині якого відображена структура таблиці Рух товарів. Нижня частина – бланк запиту – містить опис запиту в табличній формі. Кожний стовпчик у ньому відповідає одному полю. Рядки Поле та Имя таблицы мають списки, що випадають, за допомогою яких і визначають потрібні для запиту поля;
3. для створення розрахункового поля Вартість слід:

• встановити курсор у бланку запиту після поля Ціна, вставити порожній стовпчик через пункти меню Вставка\Столбцы та натиснути піктограму Построитель выражений на панелі інструментів;
• у полі Построителя выражений набрати розрахунковий вираз: =[Ціна]*[ Кількість], для цього:

–          вибрати в переліку полів, вибраних до запиту, поле Ціна та натиснути кнопку Вставить

–          натиснути кнопку знака множення у вікні Построителя выражений

–          – вибрати в переліку полів запиту Кількість та натиснути кнопку Вставить

–          натиснути <ОК>.

• У бланку запиту замість слова Выражение!, запропонованого програмою для підпису розрахункового поля, ввести Вартість;
• наприкінці сформувати умову відбору окремих записів, заповнивши рядок Условия отбора для стовпчика Вартість умовою > 50 000.

1. Для перегляду створеного запиту достатньо переключити режим його перегляду за допомогою кнопки зі списком Вид на панелі інструментів Конструктора запросов.

 

Створення запиту Оплачена реалізація з параметрами

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

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

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

Наприклад, слід створити запит з параметрами, що виводить записи про оплачену реалізацію за певний період з усіма полями таблиці Реалізація бази даних Облік товарів з прикладу. Дати початку і кінця періоду повинні вводитися під час виконання запиту у вигляді параметру.

Послідовність дій:

• створити запит у режимі Мастера запросов, включивши всі поля таблиці Реалізація. Дати запиту назву Oplaty_realizacii (Оплачена реалізація);
• відкрити запит у режимі Конструктора запросов. У рядку Условие отбора для поля Відмітка про оплату ввести умову True (Истина), тобто дати завдання програмі виводити запити з накинутим прапорцем у полі;
• у рядку Условие отбора для поля Дата, у якому відображаються дати, ввести запрошення такого вигляду

BETWEEN [Введіть дату початку періоду:] AND [Введіть дату кінця періоду:]

 щоб визначити межі діапазону значень, об’єднані операторами  BETWEEN(укр. – «між») і AND (укр. – «і»);

• переглянути запит у режимі таблиці.

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

У найпростішому запиті з одним параметром для обраного для параметру поля слід ввести у рядку Условие отбора вираз з текстом запрошення у квадратних дужках. Наприклад, у запиті з параметром, який має вивести записи таблиці Рух товарів із записами для товарів, ціна яких менше визначеного рівня, у полі Ціна у рядку Условие отбора вводиться вираз:

[Введіть рівень цін у гривнях:].

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

[Введіть кількість партії товарів:].

Корисним є використання параметрів із символами підстановки. Для кожного поля, яке буде застосовуватися як параметр, слід вводити до рядка Условие отбора вираз з текстом запрошення у квадратних дужках. Щоб запитати у користувача один чи декілька знаків для пошуку записів, що починаються з цих знаків або містять їх, слід створити запит з параметрами, який використовує оператор LIKE, і знак підстановки «зірочка» – *.

Наприклад, наступний вираз виконує пошук клієнтів у таблиці Клієнти, назви яких починаються з введеної користувачем у вікні запрошення літери:

LIKE [Введіть першу літеру назви клієнта: ] AND «*»