Заховайте перший стовпець
10. Поновіть перший стовпець на екрані.
11. Вилучіть другий запис з таблиці.
12. Доповніть таблицю ще двома записами.
Записи додаються в кінці таблиці.
13. Увімкніть панель інструментів Таблиця, якщо вона вимкнена.
Вигляд => Панелі інструментів => Таблиця.
14. Упорядкуйте введені записи за зростанням значень деякого поля.
Виокремте поле, клацнувши на назві, наприклад Прізвище, і
натисніть на стандартній панелі на кнопку Впорядкування (Сортування) за зростанням. Зауважимо, що в деяких російськомовних версіях програми впорядкування текстових даних відбувається неправильно.
15. Упорядкуйте записи за спаданням значень поля Мат, а потім — за зростанням значень поля Номер.
16. Модифікуйте структуру таблиці.
Перейдіть у режим конструктора структури. Для цього натисніть на кнопку Вигляд або виконайте команду Вигляд => Конструктор. Змініть назву першого поля — напишіть її великими буквами: НОМЕР. Зауважимо, що в dBase-системах зміна назви поля веде до втрати даних, а в цій програмі — ні.
17. Закрийте свою таблицю, зберігаючи дані на диску.
18. У тій же базі даних створіть другу таблицю Телефони, яка міститиме номери телефонів тих же студентів.
Структура таблиці має складатися з таких полів: числового (тут не лічильника) поля НОМЕР і текстових полів Прізвище, Ім'я, Місто, Телефон, Типтел (зі значеннями: домашній, мобільний, батьків).
Оскільки перші два поля ті ж самі, що і в таблиці Оцінки, то спочатку в головному вікні БД зробіть копію таблиці Оцінки (Ctrl+C) і вставте її (Ctrl+V). Дайте копії назву Телефони та виберіть з меню параметр структура і дані. ОК. Вилучіть у конструкторі зайві поля з оцінками і створіть нові. Формат поля Телефон задайте такий: (###) ###-##—##. У конструкторі для типу поля Типтел увімкніть майстра підстановки і задайте режим Буде введено фіксований набір значень, натискайте на кнопку Далі та введіть один стовпець з трьох значень: домашній, мобільний, батьків. Перейдіть у режим таблиці і заповніть таблицю даними, яких бракує. Під час введення даних стежте за відповідністю між номерами і прізвищами студентів у двох таблицях. Дані у таблицю введіть так, щоб записів, що стосуються одного студента, була різна кількість: 1, 2 або 3, залежно від кількості телефонів. Для цього вставте додаткові записи для студентів, які мають декілька телефонів. Отже, значення даних в полях НОМЕР і Прізвище тут повторюватимуться. Поле Типтел заповнюйте даними, користуючись створеним списком. Упорядкуйте таблицю за зростанням значень у полі НОМЕР або Прізвище. Закрийте таблицю зі збереженням на диску.
19. Створіть третю таблицю з назвою Адреси, яка міститиме адреси тих же осіб.
Структура таблиці повинна складатися з п'яти полів: числового (тут може бути лічильник) НОМЕР, текстових Місто і Вулиця, числових Будинок і Квартира. Заповніть таблицю даними. Якщо студент живе в особняку, то поле Квартира може бути незаповнене. У цьому випадку вважається, що воно має значення Null.
20. Закрийте базу даних зі збереженням на диску, а також на дискеті. Закінчіть роботу. Здайте звіти.
Контрольні запитання
1. Що таке база даних?
2. Яке призначення програми MS Access?
3. З яких об'єктів складається файл бази даних?
4. Що таке запис?
5. Наведіть приклади баз даних.
6. Які є способи створення таблиці в базі даних?
7. Що таке структура таблиці бази даних?
8. Які є властивості полів?
9. З яких структурних елементів складається таблиця бази даних?
10. Які об'єкти може містити файл бази даних?
11. Що таке база даних в MS Access?
12. Яке призначення баз даних?
13. Що таке поле?
14. Наведіть приклад запису.
15. Які є типи полів?
16. Як створити структуру бази даних?
17. Що означає модифікувати структуру бази даних?
18. Як ввести дані у базу даних?
19. Які закладки (кнопки) має головне вікно бази даних?
20. Як сховати чи показати стовпці в таблиці?
21. Що таке умова на значення поля?
22. Як вставити нове поле в структуру?
23. Для чого призначене повідомлення про помилку введення?
24. Як упорядкувати записи в таблиці?
25. Яке призначення конструктора таблиці?
26. Як вилучити поле зі структури таблиці?
27. Як ввести в таблицю новий запис?
28. Які види сортування вам відомі?
29. Як змінити назву поля в таблиці?
30. Як збільшити шрифт під час введення даних у БД?
31. Наведіть приклади значень числового і грошового полів.
32. Яким способом задають тип поля? Які є типи полів?
33. Яка відмінність між назвою файлу БД і назвою таблиці в цій БД?
34. Що таке майстер підстановки?
35. Яка відмінність між записом і полем?
Практичні роботи № 17
Тема: MS Access. Робота з фільтрами і запитами.
Мета: Уміти конструювати фільтри і запити для відшукання інформації у базі даних. Знати призначення таких запитів: на вибірку, параметричних, на внесення змін у БД, на доповнення, вилучення, копіювання, створення, обчислення.
План роботи № 4
1. Команда Знайти.
2. Робота з фільтрами.
3. Запит на вибірку з простою числовою умовою.
4. Запит на вибірку з умовою по текстовому полю.
5. Запит з параметром.
6. Запит на створення нової таблиці з копіюванням у неї даних.
7. Запит на доповнення таблиці.
8. Запити на створення таблиць з обчислювальним полем.
9. Запити на відшукання повторень і вилучення записів.
10. Запит на копіювання частини таблиці.
11. Створення таблиці «Адреси».
Завдання
Для таблиці Оцінки, створеної раніше, чи таблиці, заданої викладачем, сконструювати різні фільтри і запити згідно з планом роботи. Після виконання кожного фільтра чи запиту занотовувати у звіт номери записів, які задовольняють критерій пошуку.
Теоретичні відомості
Команда Знайти.
Бази даних можуть містити тисячі і десятки тисяч записів.
Типову задачу опрацювання даних формулюють так: серед записів відшукати в БД ті, які задовольняють деякий критерій (умову), і вивести їх на екран, папір чи створити з них іншу таблицю.
Для відшукання потрібних даних використовують команду Знайти, а також фільтри і запити.
Команда Редагувати => Знайти дає змогу відшукати записи у великій таблиці за деякою нескладною умовою пошуку у всій базі чи в конкретному полі.
2. Фільтри.
Фільтри застосовують у випадку нескладних умов пошуку і виведення даних на екран чи папір. На відміну від команди Знайти тут зайві записи не висвітлюються. Є три типи фільтрів: фільтр за вибраним, фільтр за формою і розширений фільтр. Доступ до команд роботи з фільтрами можна отримати одним із трьох способів:
1. виконати команду головного меню Запис => ФІЛЬТР;
2. виконати команду з контекстного меню;
3. скористатися кнопками команд на панелі інструментів:
- Фільтр за вибраним 
;
- Змінити фільтр (фільтр за формою) 
;
- Розширений фільтр;
- Застосувати/Скасувати фільтр 
.
Як користуватися кнопками — це ви дізнаєтеся, виконуючи роботу. Під час експериментів застосовуйте команду-кнопку Скасувати фільтр, щоб повернути початкове відображення таблиці.
3. Побудова виразів для пошуку даних.
Для відшукання даних згідно з деяким критерієм відбору (умови відбору) користувач будує логічні вирази, які бувають простими або складеними.
Прості вирази (умови) — це числа, тексти, вирази з необов'язковою операцією порівняння. Вони описують критерій шукання даних за зразком у заданому полі. Значенням виразу може бути «так» або «ні». Розглянемо приклади простих умов:
а) =5 або 5, >3, <=5, <>3 або !3 (не 3) — для числових полів;
б) 4/21/85 або 4.21.85 — для полів з датами в американському стандарті; 21/4/85 або 21.4.85 — у європейському стандарті; < date() — усі дати до вчора, оскільки функція date() має значення сьогоднішньої дати;
в) "Артист" або Артист — для текстових полів з прізвищами; Like[А-Г] — для пошуку слів, які починаються на А, Б, В, Г; Like[!А-Г] — слів, які починаються на Д, Е, ...Я;
Like "Ар*" або Ар* — слів, які починаються на «Ар»; Like *ко — слів, які закінчуються на «ко»; Like 69#### — шестизначних номерів телефонів, які починаються на 69.
У шаблонах операції порівняння Like символ * означає деяку кількість будь-яких символів, символ ? — один будь-який символ, # — одну цифру, ! — заперечення.
Складені вирази — це вирази, побудовані з простих за допомогою логічних операцій not (не), and (і), or (або). Наприклад, not 2; between 3 and 5 (між 3 і 5); 3 or 4 or 5; Таня or Іра; between date()-30 and date() — дати за минулі 30 днів від сьогоднішньої, between 4/21/99 and 5/21/99 — між двома датами.
У конструкторах фільтрів і запитів умови вводять у рядок умов. Умови, які стосуються різних полів і мають сполучник (логічну операцію) "і", записують в одному рядку конструктора умов. Умови, які стосуються одного поля і мають сполучник "або", розташовують одну під одною.
Запити.
Запит — це засіб відшукання записів, різних маніпуляцій з даними, зокрема, перетворення таблиць і створення на їхній основі нових таблиць. На відміну від фільтра запит є структурним елементом бази даних, що має назву, яка поміщається на закладку Запити головного вікна БД.
Розрізняють декілька типів запитів. Найпростішим є звичайний запит (інша назва запит на вибірку), який відображає на екрані вибрані з БД записи. Ці запити не змінюють таблиці БД.
Для створення нової таблиці, що міститиме вибрані з деякої таблиці записи, внесення змін у таблиці (доповнення, оновлення, вилучення чи архівування записів, створення обчислювального поля тощо) використовують запити на перетворення (на внесення змін, на виконання дії з таблицею, action queries).
Запит створюють вручну в режимі конструктора або за допомогою майстра запиту на базі деякої таблиці чи декількох таблиць, яку/які додають до запиту.
Щоб створити запит вручну, треба виконати таку послідовність команд, стартуючи з головного вікна бази даних:
Вікно БД => Запити => Створити => Конструктор => OK => Додають таблицю, наприклад, Оцінки => Закривають вікно Додати таблицю.
Отримують вікно (бланк) конструктора запитів, яке потрібно заповнити (рис. 1). Конструювання запиту складається з декількох етапів.
1. Рядок Поле міститиме назви полів, які користувач хоче відобразити у запиті. Щоб заповнити цей рядок, у вікні таблиці Оцінки вибирають усі чи потрібні поля і перетягують їх у рядок Поле на бланку. Інший спосіб: рядок Поле заповнюють, клацнувши в ньому і вибравши назву кожного поля із запропонованого списку.
2. Задають, якщо потрібно, режим упорядкування записів (методом вибору режиму зі списку) і режим відображення полів на екрані.
3. Вводять умови пошуку в рядок умов.
4. Щоб запустити запит на виконання, виконують команди Запит Запуск чи натискають на кнопку запуску, на якій нарисовано знак оклику (І).
Рис. 1. Вікно конструктора запиту.
5. Для редагування запиту треба повернутися до конструктора.
6. У разі потреби змінюють тип запиту командою з меню Запит => Вибирають потрібний тип із запропонованого списку.
7. Коли запит готовий, закривають конструктор зі збереженням запиту з деякою назвою у файлі БД.
Загальна схема роботи із запитами на внесення змін така:
1. виконують звичайний запит на вибірку записів;
2. змінюють тип запиту, наприклад, на створення нової таблиці тощо;
3. запускають запит на виконання — буде створена нова таблиця з відібраними записами.
Окрім вищезазначених, є й інші типи запитів: перехресний, результат роботи якого нагадує зведену електронну таблицю; на виявлення записів, що повторюються; на виявлення записів, що не мають підпорядкованих записів у деякій іншій таблиці (наприклад, такий запит, застосований до таблиць Оцінки і Телефони, дає змогу виявити студентів, які не мають телефонів).
Створення запитів за допомогою конструктора називають технологією QBE (Query By Example). Створивши QBE-запит, можна ознайомитися з його SQL-кодом, натиснувши на кнопку з написом SQL. Мову SQL розглянемо в наступній роботі.
Словник
| Редагувати | Правка | Edit |
| Знайти/Поміняти | Найти/Заменить | Find/Replace |
| Зразок | Образец | Find What |
| Фільтр за вибраним | Фильтр по выделенному | Filter by Selection |
| Застосувати / Скасувати | Применить/Удалить | Apply/Remove |
| Змінити фільтр | Изменить фильтр | Filter by Form |
| Розширений фільтр | Расширенный фильтр | Advanced Filter |
| Запит на вибірку | Запрос на выборку | Select Query |
| Додавання таблиці | Добавление таблицы | Show Table |
| Додати/Закрити | Добавить/Закрыть | Add/Close |
| Умова, критерій | Условие отбора | Criteria |
| Вивести на екран | Вывод на экран | Show |
| Створення таблиці | Создание таблицы | Make New Table |
| Доповнення | Добавление | Append |
| Записи, що повторюються | Повторяющиеся записи | Dublicate Records |
Хід роботи
1. Запустіть програму MS Access і відкрийте БД, що містить таблицю Оцінки, чи створіть подібну таблицю.
2. Розгляньте таблицю і від корегуйте її.
Якщо в таблиці немає відмінників, змініть дані так, щоб вони були, а також щоб були учні/студенти, які вчаться лише на "4" і "5", і щоб були такі, котрі мають "2" з математики. У таблиці повинно бути не менше десяти записів.
3. Поекспериментуйте з командою Знайти, щоб відшукати записи, де прізвища студентів починаються на букву М.
Клацніть у полі Прізвище і виконайте команду Редагувати => Знайти => У поле Зразок введіть М* і увімкніть перемикач Шукати тільки в поточному полі => Знайти => Знайти далі.
4. Знайдіть записи студентів, які мають "5" з математики.
5. Усі студенти виправили "2" з математики на "3". Внесіть зміни в БД, використовуючи команду Редагувати => Поміняти.
6. Виведіть на екран (відфільтруйте) записи про студентів, які мають з математики оцінку "5".
Виберіть потрібне поле (клацніть мишею у будь-якій клітинці з оцінкою "5" з математики будь-якого студента) і натисніть на кнопку Фільтр за вибраним . Перепишіть номери записів у звіт. Відновіть вигляд таблиці кнопкою Вилучити фільтр .
7. Відфільтруйте записи про студентів, котрі не мають "З" з математики.
Виберіть поле з оцінкою "З" з математики і виконайте команду з меню Записи => Фільтр => Заперечити вибране. Вилучіть фільтр.
8. Виведіть записи про студентів, які мають "4" з математики і фізики.
Тут умова складніша, тому виконайте команду Записи => Фільтр => Змінити фільтр. Задайте оцінки ("4" і "4") в полях умов для математики і фізики. Застосуйте фільтр: Записи => Застосувати фільтр. Перепишіть номери записів у звіт. Вилучіть фільтр.
9. Застосуйте розширений фільтр, щоб вивести записи про студентів, які вчаться на "4" або "5" з математики або інформатики.
Записи => Фільтр => Розширений фільтр => У поля фільтрування Мат і Інф введіть умову 4 or 5 або введіть 4 та 5 у двох рядках => Застосувати фільтр => Перепишіть номери шуканих записів => Вилучити фільтр.
10. Виведіть записи про відмінників.
11. Створіть і виконайте Запит1 на вибірку записів про студентів, які мають "5" з інформатики.
Вікно БД => Запити => Створити Конструктор => ОК => Додайте таблицю Оцінки => Закрийте вікно Додати таблицю. Заповніть бланк запиту, виконайте його і перепишіть номери знайдених записів. Закрийте вікно запиту зі збереженням структури запиту з назвою Запиті.
12. Модифікуйте Запит1, щоб вибрати записи про студентів, які вчаться на "4" або "5" з математики і мови.
Вікно бази даних => Запити => Виберіть Запит1 => Конструктор. Оцінки "4" і "5" введіть одну під одною (у двох рядках) в умовах для полів математики і мови. Запустіть запит. Перепишіть номери записів. Закрийте запит без збереження.
13. Створіть і виконайте новий Запит2 на вибірку лише номерів і прізвищ студентів, у яких сьогодні день іменин.
Запит1 за основу не брати. Створіть новий запит. Виберіть якесь ім'я і введіть його у поле Ім'я бланка запиту як умову пошуку (наприклад, Юрій, якщо сьогодні 6 травня, чи інше). Вимкніть режим виведення на екран оцінок, якщо їхні поля занесені в бланк запиту. Виконайте і збережіть Запит2.
14. Створіть Запит3 з параметром для відшукання записів про студентів, які мають деяку, задану параметром, оцінку з математики.
Значення параметра користувач вводитиме в окремому діалоговому вікні після запуску запиту. Виконайте цей пункт методом модифікації Запиту1. У полі Мат задайте у квадратних дужках такий текст: [Введіть оцінку]. Ліквідуйте числову умову в іншому полі (Інф).
15. Запустіть Запит3 двічі, щоб визначити, хто має з математики "5", а хто - "2".
Прізвища й оцінки занотуйте у звіт.
16. Модифікуйте Запит3 на випадок двох параметрів, наприклад, для полів Мат та Інф, та поекспериментуйте з ним.
17. Закрийте Запит3, зберігаючи його.
18. Доповніть структуру таблиці Оцінки полем Дата і введіть дати народження студентів, наприклад 25.1.83 тощо.
19. Сконструюйте запит, щоб з'ясувати, хто зі студентів може бути скерований на стажування за кордоном, якщо умова стажування така: оцінки "4" і "5" з усіх предметів і дата народження від 1.1.87 до 31.12.89.
20. Запустіть програму MS Access і відкрийте БД з оцінками.
21. Сконструюйте і виконайте Запит4 на створення нової таблиці Відмінники, куди треба з таблиці Оцінки скопіювати записи про всіх відмінників за алфавітом.
Відкрийте конструктор звичайних запитів (можна на базі Запиту1) і задайте умову вибірки відмінників. Змініть тип запиту на запит Створення таблиці. Введіть назву нової таблиці Відмінники і запустіть запит. Закрийте конструктор запиту, зберігаючи його, і переконайтеся, що на закладці Таблиці є нова таблиця. Відкрийте цю таблицю і переконайтеся, що в ній є записи про відмінників. . .
22. Доповніть таблицю Відмінники записами про студентів, які вчаться на "4" або "5" з усіх предметів.
Відкрийте конструктор Запиту4, змініть умови вибірки, змініть тип запиту на Доповнення і запустіть його на виконання. Перегляньте таблицю Відмінники. Скільки в ній є записів? Чи є записи, що повторюються? Закрийте запит без збереження.
23. Створіть на базі таблиці Оцінки нову таблицю з назвою Рейтинг і з обчислювальним полем Сума — сумою оцінок студентів з усіх предметів.
Відкрийте Запит1 у режимі конструктора або створіть новий запит. Зліквідуйте старі умови. У рядку Поля після поля Мова чи Дата у порожню клітинку введіть вираз для створення нового поля так: Сума: [Мат]+[Інф]+ [Фіз]+ [Літ]+[Мова].
Виконайте запит.
25. Упорядкуйте список студентів за рейтингом.
Поверніться в конструктор запиту. Увімкніть режим упорядкування за спаданням для поля Сума. Виконайте запит. Перепишіть номери студентів за спаданням рейтингу в звіт.
26. Збережіть одержані результати в таблиці Рейтинг.
Конструктор запиту => Змініть тип запиту на тип Створення таблиці Дайте таблиці назву "Рейтинг" => ОК => Запуск запиту => Закрийте запит зі збереженням як Запит5.
27. Відкрийте і перегляньте таблицю Рейтинг.
28. Виконайте запит на відшукання в таблиці Відмінники записів, що повторюються.
Вікно бази даних => Закладка Запити (але нічого не вибирати) => Створити (кнопка) => Виберіть режим Записи, що повторюються => ОК => Виберіть таблицю Відмінники => Далі => Задайте поля, де можна виявити небажані повторення, наприклад, Номер, перевівши цю назву в поле пошуку повторень => Готово. Перепишіть номери записів, що повторюються, у звіт. Закрийте запит як Запит6.
29. Вилучіть з таблиці Відмінники записи, що повторюються, за допомогою запиту на вилучення і доповнення.
Розв'язування задачі потребує виконання такого алгоритму: 1) скопіюйте таблицю Відмінники у буфер обміну і вставте її (лише структуру) як таблицю Відмінники2; 2) зробіть у структурі поле Номер ключовим; 3) на базі старої таблиці створіть запит на доповнення нової таблиці всіма записами; 4) виконайте цей запит, незважаючи на конфліктну ситуацію — одержете записи без повторень. Розглянемо ще один спосіб. Відкрийте Запит1 у режимі конструктора. Доповніть запит таблицею Відмінники. Вилучіть з конструктора таблицю Оцінки, клацнувши в ній і застосувавши команду Вилучити. В умові пошуку по полю Номер зазначте один під одним номери записів, які треба вилучити. Запустіть на виконання запит на вилучення. Увага! Будуть вилучені всі копії записів, що повторюються. Тепер потрібно доповнити цю таблицю втраченими записами. Відшукайте їх за допомогою простого запиту в іншій таблиці, де вони не повторюються, за записаними у звіт номерами.
30. Збережіть таблицю Відмінники чи Відмінники2 як ново-створену без записів, що повторюються.
31. Створіть нову таблицю Оцінки2, відобразивши у ній три перші поля з таблиці Оцінки тих студентів, які мають хоча би одну двійку (чи трійку, якщо двійок немає) з будь-якого предмета.
Створіть новий Запит7, у якому скасуйте режим виведення на екран усіх полів, окрім перших трьох, а умову (=2) зазначте в різних рядках у полях оцінок. Змініть тип запиту на Створення НОВОЇ таблиці. Таблицю назвіть 0цінки2. Запустіть запит.
32. Відкрийте таблицю Оцінки2 і перегляньте її.
33. Сконструюйте Запит8 для виявлення записів у таблиці Оцінки, які не мають підпорядкованих записів у таблиці Оцінки2, тобто з'ясуйте, хто зі студентів не має заборгованостей.
На закладці Запити натисніть на кнопку Створити і виберіть останній тип запиту "Записи без підпорядкованих" => ОК. Виконайте вказівки майстра. Виберіть таблицю Оцінки як основну => Далі, а Оцінки2 — як підпорядковану => Далі. У наступному вікні налагодьте зв'язок між полями НОМЕР в обох таблицях. Між таблицями буде налагоджено зв'язок у цьому випадку один до одного => Далі. Занесіть усі поля таблиці Оцінки для відображення в запиті => Далі. Змініть назву запиту на Запит8 => Готово.
34. Закрийте базу даних. Закінчіть роботу. Здайте звіти.
Контрольні запитання
1. Яке призначення баз даних?
2. Як у базі даних відшукати потрібні записи?
3. Яке призначення фільтрів?
4. Для чого призначена команда Застосувати фільтр?
5. Які є типи запитів?
6. Як створити запит?
7. Як виконати пошук по текстовому полю?
8. Чи можна з відфільтрованих записів створити нову таблицю?
9. Що таке запит?
10. Яка відмінність між використанням фільтра і запиту?
11. Яке призначення команди Скасувати (Відмінити) фільтр?
12. Що таке простий запит на вибірку?
13. Яке призначення конструктора запитів?
14. Як створити нову таблицю за допомогою запитів?
15. Як побудувати умову зі сполучником "або" у запиті?
16. Що таке запит на доповнення?
17. Які є способи створення запитів?
18. Як додати таблицю у вікно конструктора запитів?
19. Як перемістити поля з таблиці у бланк запиту?
20. Як упорядкувати записи, використовуючи запит?
21. Як змінити тип запиту?
22. Як побудувати умову зі сполучником "і" у запиті?
23. Що таке запит з параметром?
24. Як модифікувати запит?
25. Як скопіювати частину структури таблиці в нову таблицю?
26. Як створити таблицю з обчислювальним полем?
27. Як відшукати і вилучити з таблиці записи, що повторюються?
28. Опишіть умову для відшукання записів про студентів, які народилися від 1 квітня до 30 травня.
29. Опишіть умову відшукання записів про студентів, номери телефонів яких починаються на 69.
30. Опишіть умову вибірки записів про студентів, які живуть на вулиці Науковій.
31. Опишіть умову вибірки записів про студентів, прізвища яких починаються на букви "С" або "П".
32. Опишіть умову вибірки записів про студентів, які живуть на вулиці Науковій або Садовій.
33. Опишіть умову вибірки записів про студентів, які мають з усіх предметів "4" або "5".
34. Як визначити середню оцінку групи з математики?
35. Як визначити середні оцінки групи з усіх предметів?