ПРАКТИЧНА РОБОТА

ТЕМА: MS Access. Створення запитів за допомогою майстра та в режимі конструктора

 

МЕТА: сформувати поняття фільтр, автофільтр, запит; розглянути методи пошуку, сортування та фільтрування даних у БД; пояснити принципи створення запитів; формувати вміння сортувати дані за критерієм, використовувати фільтр та автофільтр, створювати запити різних типів; формувати вміння чітко й лаконічно висловлювати думки; виховувати уважність, дисциплінованість під час роботи за ПК.

Обладнання: ПК, робочий зошит, тестові завдання

В результаті виконання практичної роботи студент повинен

знати:

•      методи пошуку даних;
•      призначення фільтрів;
•      призначення та основні типи запитів;
•      правила складання простих і складних умов;

вміти:

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

Тривалість 2 години

Питання для підготовки до практичної роботи

1. Що таке сортування? Як виконати сортування за даними одного із полів?
2. Опишіть послідовність виконання пошуку даних в таблиці?
3. Що таке фільтрування?
4. Які є типи фільтрів?
5. Для чого призначені запити?
6. Які є типи запитів?
7. Які є способи створення запитів?
8. Опишіть структуру вікна запиту в режимі конструктора. Поясніть призначення його об’єктів.
9. Як створити запит з обчислювальним полем?

 

Короткі теоретичні відомості

Один з найбільш ефективних засобів організації даних є сортування. Сортування впорядковує дані за змістом визначених полів. Інформацію можна сортувати за алфавітом, числовими значеннями, датами та іншими параметрами. Записи сортуються або в порядку зростання (А-Я та 0-9), або у порядку спадання (Я-А та 9-0).

При сортуванні таблиці за одним полем, треба виділити це поле та виконати команду Сортировка по возрастанию або Сортировка по убыванию (контекстного меню стовпчика) або кнопки на панелі інструментів .

Сортування даних таблиці за декількома полями використовується в тому випадку, коли в одному полі дані часто повторюються, тому при сортуванні по другому полю, відповідні дані будуть сортуватися не порушуючи слідування записів при першому сортуванні в межах повторень. При сортуванні даних таблиці за декількома полями треба задати порядок сортування полів. Поле, яке стоятиме першим, буде сортуватися першим, далі, не порушуючи отриманого порядку будуть сортуватися дані другого поля. Тому перед сортуванням поля треба розмістити в таблиці поруч в правильному порядку для сортування (перемістити стовпчики). Далі, виділити ці поля та виконати команду Сортировка по возрастанию або Сортировка по убыванию.

Бази даних можуть містити тисячі і десятки тисяч записів.

Типову задачу опрацювання даних формулюють так: серед записів відшукати в БД ті, які задовольняють деякий критерій (умову) і вивести їх на екран, папір чи створити з них іншу таблицю.

Команда Правка  Найти дає змогу відшукати записи у великій таблиці за деякою нескладною умовою пошуку у всій базі чи в конкретному полі.

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

• Фильтр по выделенному –  цей фільтр можна встановити, виділивши частину поля (одну або декілька комірку), які повинні відповідати значенню результуючого набору. Access відфільтровує записи за зразком.
• Исключить выделенное – працює як попередня команда, тільки навпаки.
• Звичайний фільтр . Команди Изменить фильтр і Применить фильтр використовують разом. За першою командою змінюються умови фільтрації записів, а за другою - здійснюється власне фільтрація записів таблиці. Після виконання цієї команди з’явиться вікно, в якому буде вихідна таблиця, але з єдиним записом, причому пустим. Потрібно в комірки ввести шукані значення або вирази. Якщо необхідно декілька виразів, то клацнути на вкладку ИЛИ внизу вікна і ввести шукане значення в іншу комірку. Виконати команду Применить фильтр.
• Розширений фильтр . На панелі вибираємо меню Фильтр  Розширений фильтр. Додаємо у бланк фільтру критерії для інших полів нашої таблиці, а також задаємо для них принципи сортування та вводимо потрібні вирази напроти комірки умови відбору. Натискаємо кнопку Применить фильтр. Ваш розширений фільтр збережеться для подальшого використання та відобразиться у діалоговому вікні під час подальшого вибору команди Розширений фильтр. Ви маєте змогу видалити його, змінити або доповнити.

Виконання команди Удалить фильтр дозволяє відновити вміст всієї таблиці.

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

Залежно від призначення запитів їх поділяють на:

• запити на вибірку даних – запити, з використанням яких на основі існуючих таблиць створюється таблиця з даними, які відповідають певним умовам;
• перехресні запити – запити, у яких на першому етапі здійснюється узагальнення даних (знаходиться сума, середнє, максимальне значення тощо), а на другому – групування цих даних за двома наборами даних, один з яких визначає заголовки стовпців таблиці, а другий – заголовки рядків;
• запити на внесення змін – запити, використовуючи які користувач може змінювати значення у полях певних записів, створювати нові записи або видаляти існуючи записи тощо;
• запити з параметрами – запити, після запуску на виконання яких користувач має ввести значення певних параметрів, за якими буде здійснено опрацювання даних.

Створення запиту на вибірку. Для бази даних Країни світу створимо запит, який би містив дані про площу країн:

1. У вікні бази даних у списку Объекты вибрати Запросы і на панелі інструментів вікна бази даних натиснути кнопку .
2. В діалоговому вікні Новый запрос вибрати Конструктор і натиснути Ok.
3. У діалоговому вікні Добавление таблицы вибрати таблиці, на основі яких буде створено запит (наприклад, Країни) і натиснути кнопку , а потім натиснути кнопку .
4. Додати поля в рядок Поле в бланку запиту (рис. 52) і, якщо необхідно, указати умови і порядок сортування.

рис. 52 Створення запиту в режимі Конструктор

Для перегляду результатів запиту на панелі інструментів потрібно натиснути кнопку Запуск.

Під час створення запитів на вибірку можна застосовувати шаблони для вибірки потрібних записів. Символи, які можна застосовувати для складання умов запиту, подано у таблиці.

Символи для застосування у шаблоні	Зразок шаблону	Результат, що може бути виведений у запиті
? – знак питання. У шаблоні заміняє будь-який один символ	?адя	Бадя Вадя Надя Фадя
* - зірочка. У шаблоні заміняє будь-яку кількість символів	П*вич	Петрович Павлович Пилькевич Павич
# - решітка. У шаблоні заміняє будь-яку цифру	# - ий	1-ий 3-ий 9-ий
[] – квадратні дужки. У шаблоні заміняють один з наведених символів	Коров[аи]	Корова Корови
! – знак оклику. У шаблоні виключає символи, що позначені після нього	Коров[!аи]	Корову [але не корова, а також не корови]

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

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

Умовні вирази	Результат роботи умови у запиті
«Київ»	Записи, що мають значення Київ
<> «Київ»	Записи поля, що не мають значення Київ
=>22.10.91	Записи поля, що мають значення дати пізніші ніж 22.10.91
<=22.10.91	Записи поля, що мають значення дати до 22.10.91
> «К»	Записи поля, що мають значення, які починаються з літер К-Я
< «К»	Записи поля, що мають значення, які починаються з літер А-К
Null	Записи поля порожні
Is Not Null	Записи поля мають не порожні значення
Like «Р*»	Записи поля, що мають значення, які починаються з літери Р
<=500	Записи поля, що мають значення, менші або дорівнюють 500
500	Записи поля, що мають значення, що дорівнює 500
Between 01/01/08 and 31/07/08	Записи поля, що мають значення дат від 1 січня до 31 липня 2008
>10 and < 20	Записи поля, що мають діапазон значень від 10 до 20
20 or 40	Записи поля, що мають значення 20 або 40
Date()	Записи поля, що мають значення, що дорівнює поточній системній даті

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

Для створення обчислювальних виразів використовують спеціальний інструмент програми Построитель выражений. Для того, щоб викликати на екран Построитель выражений, слід у режимі конструктора запиту встановити курсор у поле, для якого потрібно побудувати вираз, у рядку Поле: з контекстного меню обрати команду  Построить (рис. 53).

рис. 53 Виклик побудовника виразів

На екрані з’явиться вікно Построитель выражений (рис. 54). Це вікно складається з кількох частин:

Поле виразу (1). У верхній частині вікна розташована ділянка, у якій створюється вираз.

Кнопки операторів (2). У середній частині вікна розташовані кнопки із часто використовуваними операторами. При натискання на одну із кнопок Построитель выражений вставить відповідний оператор у поточну позицію поля виразу.

Елементи виразу (3). У нижній частині вікна розташовані три поля. У лівому полі виводяться папки, що містять таблиці, запити, форми, об’єкти бази даних, вбудовані й створені користувачем функції, сталі, оператори й загальні вирази. У задаються конкретні елементи або типи елементів для папки, що була обрана в лівому полі. У правому полі подається список значень для елементів, заданих у лівому і середньому полях.

рис. 54 Вікно Построитель выражений

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

Створивши вираз, у конструкторі запита можна надати ім’я новому стовпчику. Для цього замість слова Выражение 1 слід ввести іншу назву (рис. 55).

рис. 55 Зміна назви стовпчика

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

Щоб до запиту додати параметр вибірки слід на етапі створення запиту у режимі конструктора в рядок Условие отбора замість конкретного значення ввести певний ключовий вислів, який вмістити у квадратні дужки (рис. 56).

рис. 56 Створення запиту з параметром

Тоді те, що вміщене всередині квадратних дужок, програма буде сприймати як ім’я параметра. Воно виводитиметься в вікні діалогу перед виконанням такого запиту (рис. 57).

рис. 57

 

Завдання для практичного виконання

Завдання на 4-6 балів

1. Запустіть програму для опрацювання бази даних MS Access

Пуск  Все программы  Microsoft Office  MS Access

2. З власної папки відкрийте файл ПЗ №9_12_Прізвище
3. Відкрийте таблицю Країни та виконайте сортування даних:
   • Відсортуйте записи за даними поля Кількість населення за спаданням. Визначте п’ять країн з найбільшою кількістю населення, результат занесіть у таблицю.

Номер	Назва країни	Кількість населення

• Відсортуйте записи за даними поля Державний устрій і визначте скільки країн мають державний устрій – монархія і скільки – конституційна монархія, результат занесіть у таблицю.

Державний устрій	Кількість країн
Монархія
Конституційна монархія

4. Виконайте фільтрування даних таблиці Країни.
   • виберіть держави з Державним устроєм Військова диктатура. Для результату фільтрування створити скриншот і зберегти у власній папці під назвою Завдання 11.1
   • відмініть усі фільтри. Виберіть держави з усіх частин світу крім Азії. Для результату фільтрування створити скриншот і зберегти у власній папці під назвою Завдання 11.2
5. Створити запит на вибірку. Запит повинен містити поля: Назва, Частина світу, Площа.

Див. теоретичні відомості ст. 81-82

6. Збережіть запит під назвою Площі країн.
7. Продемонструйте результат викладачу.

Завдання на 7-8 балів

8. Виконайте сортування даних таблиці Країни за даними полів Кількість населення, Частина світу. Визначте країни, що мають найбільшу кількість населення в певній частині світу. Результат занесіть у таблицю

Частина світу	Назва країни	Кількість населення
Австралія і Океанія
Азія
Америка
Африка
Європа

9. Виконайте фільтрування даних таблиці Країни. Виберіть усі держави, де кількість населення > 20000. Для результату фільтрування створити скриншот і зберегти у власній папці під назвою Завдання 11.3

Фильтр  Розширений фильтр

10. Створити запит з полями: Назва, Частина світу, Площа, Кількість населення. За даними полів Кількість населення і Площа визначити густоту населення для кожної з країн.

Див. теоретичні відомості ст. 83-84

11. Збережіть запит під назвою Густота населення.
12. Створити запит з параметром. Запит повинен містити поля: Назва, Частина світу, Площа. Відбір повинен відбуватися по полю Частина світу.

Див. теоретичні відомості ст. 84-85

13. Збережіть запит під назвою Запит по частинам світу.
14. Продемонструйте результат викладачу.

Завдання на 9-10 балів

15. Створити запит, який би виводив на екран всі держави за такими умовами: Дата створення після 1990 року, Державний устрій – Монархія. Запит повинен містити поля: Назва, Дата створення, Державний устрій.
16. Збережіть запит під назвою Держави створені після 1990 року
17. Створити запит, який би виводив на екран всі держави за такими умовами: Дата створення від 01.01.1930 по 01.01.1970. Запит повинен містити поля: Назва, Дата створення, Державна валюта, Короткий опис.
18. Збережіть запит під назвою Держави створені з 01.01.1930 по 01.01.1970
19. Створити запит, який би виводив на екран всі держави, назва яких починається на літеру А. Запит повинен містити поля: Назва, Кількість населення, Площа.
20. Збережіть запит під назвою Держави на літеру А
21. Продемонструйте результат викладачу.

Завдання на 11-12 балів

22. Створити запит, в якому для кожної держави обчислити кількість років з дня заснування. Запит повинен містити поля: Частина світу, Назва, Кількість населення, Площа, Державний устрій, Державна валюта, Столиця, Кількість років з дня заснування.
23. Збережіть запит під назвою Кількість років з дня заснування
24. Внесіть зміни до запиту Кількість років з дня заснування Відбір держав повинен відбуватися по введеній формі державного устрою. Передбачити введення форми державного правління повністю або лише декількох перших літер.