Практична робота 12 на тему MS Access та Робота з фільтрами і запитами
« Назад
Практична робота № 12 Тема MS Access. Робота з фільтрами і запитамиМета. Уміти конструювати фільтри і запити для відшукання інформації у базі даних. Знати різницю між запитами: на вибірку, параметричними, на внесення змін у БД, на доповнення, вилучення, копіювання, створення, обчислення. Запити Конструювання однотабличного запиту на вибіркуРозглянемо процес конструювання однотабличного запиту на вибірку на прикладі одержання інформації з таблиці ПРЕДМЕТ бази даних «Навчальний процес». Використання логічних операцій в умові відборуНехай треба вибрати предмети, по яких загальне число навчання не більше 100, і є лекції, а також вибрати предмети, по яких загальне число годин більше 150 і число семестрів навчання не більше двох. Результат повинен містити найменування предмета (НП), загальне число годин по предмету (ГОДИНИ), кількість лекційних годин (ЛЕК) і число семестрів (ЧС). Для створення запиту в режимі конструктора виберемо вкладку Создать на панелі швидкого доступу і натиснемо кнопку Конструктор запросов. Після натискання кнопки з'являється вікно запиту на вибірку в режимі конструктора Запрос1 (рис. 7.1) і діалогове вікно Добавление таблицы. У діалоговому вікні виберемо таблицю ПРЕДМЕТ і натиснемо кнопку Добавить. Обрана таблиця буде відображена в області схеми даних запиту. Закриємо вікно Добавление таблицы. У вікні конструктора (рис. 7.1) перетягнемо зі списку полів таблиці ПРЕДМЕТ поля НП, ГОДИНИ, ЛЕК і ЧС у стовпці бланка запиту у рядок Поле. Сформульовані в завданні умови вимагають формування наступного логічного виразу: (ГОДИНИ<=100 AND ЛЕК<>0) OR (ГОДИНИ>150 AND ЧС<3) Тут ЛЕК<>0 (число лекцій не дорівнює нулю), відповідає заданому в завданні умові вибрати предмети, у яких є лекції. Умови з перших дужок запишемо у відповідних полях ГОДИНИ і ЛЕК першої строки Условия отбора. Між умовами в різних полях одного рядка виконується логічна операція . Умови із других дужок запишемо у відповідних полях ГОДИНИ і ЧС другого рядка Условия отбора. Між умовами, записаними в різних рядках, виконується логічна операція . Виконаємо запит, нажавши на панелі конструктора запитів кнопку Выполнить. На екрані з'явиться вікно запиту в режимі таблиці із записами з таблиці ПРЕДМЕТ, які відповідають заданим умовам відбору. Збережемо запит, натиснувши кнопку Сохранить на вкладці Файл і задавши потрібне ім'я запиту. Закриємо поточний запит натиснувши кнопку вікна запиту Закрыть. Збережений запит можна виконати, виділивши запит у вікні Все объекты Access, натисканням правої кнопки миші і вибравши кнопку Открыть. Використання в умові відбору виразів з іменами полівНехай необхідно перевірити правильність завдання загальних годин у таблиці ПРЕДМЕТ. При запиті повинні відбиратися тільки ті записи, у яких значення у полі ГОДИНИ не дорівнює значенню, отриманому при додаванні значень полів ПР і ЛЕК. Така умова записується у бланку запиту в стовпці ГОДИНИ і у полях [ПР] і [ЛЕК], як показано в бланку запиту на рис. 7.2. Конструювання багатотабличного запиту на вибіркуЗапит на основі декількох взаємозалежних таблицьНехай необхідно одержати інформацію про оцінки отриманих студентами по всіх предметах. Результат повинен містити прізвище студента, найменування зданих предметів і оцінки. Для створення запиту на панелі швидкого доступу виберемо вкладку Создание і натиснемо кнопку Конструктор запросов. Формування схеми даних запитуУ вікні Додавання таблиці виберемо таблиці:
Закриємо вікно Добавление таблицы. У вікні конструктора запитів (рис. 7.3) представлена схема даних запиту, що містить обрані таблиці. Між таблицями автоматично встановлені необхідні зв'язки:
Підготовка бланка запитуОскільки в запиті використовується кілька таблиць, у бланку запиту зручно бачити ім'я таблиці поряд з ім'ям поля. Для відображення імен таблиць у бланку запиту (рис. 7.4) натиснемо кнопку Имена таблиц на панелі інструментів конструктора або натиснемо відповідну кнопку в контекстному меню. Перетягнемо за допомогою миші поля, що включають у результат виконання запиту, у строку бланка запиту Поле:
Введення значень в умови відбору записівНехай необхідно одержати інформацію про успішність конкретних студентів: Боярскої Н.П. і Макової. Задамо у рядку Условие отбора їх прізвища. Запишемо прізвища студентів у різних рядках бланка запиту, оскільки необхідно вибрати записи зі значенням у поле ПІБ- Боярська або Макова. Оскільки ініціали студентки Макової невідомі, її прізвище задамо з використанням символу шаблона «*». Прізвище з ініціалами має крапки, тому його треба брати в лапки. Після введення прізвища із символом шаблону система сама вставляє оператор Like, що визначає пошук за зразком. Заповнений бланк запиту представлений на рис. 7.4. Виконаємо запит, натиснувши на панелі конструктора запитів кнопку Выполнить. Зауваження Запис про заданого студента з'являться у результуючій таблиці запиту тільки в тому випадку, якщо запис про цього студента є в таблиці СТУДЕНТ, а в таблиці НАВЧАННЯ є записи, пов'язані із записом про студента. Формування записів результату при виконанні запитуПо заданому прізвищу студента - Боярська Н.П.- у таблиці СТУДЕНТ відшукується запис. За значенням ключа зв'язку НГ+НС здійснюється вибірка підлеглих записів з таблиці УСПІШНІСТЬ із оцінками даного студента по різних предметах (у поле ОЦІНКА). Для кожного із цих записів за значенням ключа зв'язку КП вибирається один запис з найменуванням предмета (НП) з таблиці ПРЕДМЕТ. Таким чином, таблиця з результатом запиту буде містити по одному запису про кожен предмет, зданому студентом. Аналогічно формуються записи для другого заданого в запиті студента - Макової. Введення параметрів у запитУ попередньому прикладі для завдання прізвища конкретного студента необхідно було коректувати бланк запиту. Щоб уникнути цього, доцільно використати в запиті параметри. При цьому Access перед виконанням запиту через діалогове вікно буде запитувати у користувача конкретні значення параметрів і уведе їх в умови відбору. Нехай необхідно одержати інформацію про оцінку студента по заданому предмету. В умову відбору поля ПІБ замість конкретного прізвища введемо назву параметра, по якому буде запитуватися прізвище при виконанні запиту. Назву параметра введемо як текст у квадратні дужки: [Прізвище і ініціали студента] Цей текст Access сприймає як ім'я параметра (рис. 7.5). В умову відбору поля НП уведемо другий параметр запиту: [Найменування предмету] При виконанні запиту Access виведе діалогові вікна, представлені на рис. 7.6, у які користувач зможе ввести потрібні значення параметрів. Використання імен полів різних таблиць в умові відборуНехай необхідно вибрати записи з таблиці НАВЧАННЯ, у яких ГОДИНИ практичних занять по інформатиці не відповідають рівномірному розподілу по семестрах всіх годин практики. Для рішення цього завдання необхідно використати таблиці:
Для відбору записів про практичні заняття по інформатиці з таблиці НАВЧАННЯ треба у рядку Условие отбора для поля НП (ТАБЛИЦЯ ПРЕДМЕТ) задати значення «Інформатика», а для поля ВИД3 (таблиці НАВЧАННЯ) задати значення «пр» (практичне заняття). При рівномірному розподілі практики по семестрах загальне число годин практичних занять з предмету (ПР) повинно дорівнювати добутку годин практики (ГОДИНИ) з таблиці НАВЧАННЯ на число семестрів (ЧС) з таблиці ПРЕДМЕТ. Для рішення треба включити результати тільки тих записів, для яких число годин не відповідають цьому добутку. Для цього запишемо в Условие отбора поля ПР (таблиці ПРЕДМЕТ) вираз: [НАВЧАННЯ] ! [ГОДИНИ]*[ЧС] Запит на вибірку з умовами відбору записів наведений на рис. 7.7., а результати виконання запиту наведені на рис. 7.8. Створення полів, що обчислюються, у запитахПоле, що обчислюється, включене у запит, дозволяє одержати нове поле з результатами обчислення, відображуваними тільки в таблиці запиту, і не створює полів у вихідних таблицях бази даних. Розглянемо технологію створення запиту з обчислюємим полем на прикладі таблиці ПРЕДМЕТ. Нехай необхідно знайти записи про предмети, у яких загальне число годин по предмету не збігається із сумою годин лекцій і практики. Для рішення цього завдання розрахуємо різницю між загальним числом годин по предмету (поле ГОДИНИ) і сумою годин лекцій (поле ЛЕК) і практики (поле ПР). У відповідь включимо тільки ті записи, для яких ця різниця не дорівнює нулю. Створимо запит на вибірку для таблиці ПРЕДМЕТ. Перетягнемо в бланк запиту з полями НП, ПР, ЛЕК, ГОДИНИ (рис.7.9). Створення обчисленого поляУ порожній клітинці рядка Поле запишемо вираз: [ГОДИНИ] - [ПР] - [ЛЕК] Для відбору записів з ненульовим значенням різниці у рядку Условие отбора введемо <>0 (не дорівнює 0). Після введення виразу система формує ім'я обчислюємого поля (за замовчуванням - «Выражение 1». Це ім'я вставиться перед виразом. Для зміни імені встановимо курсор миші і натиснемо праву кнопку миші. У контекстному меню виберемо Свойства поля, а в рядок Подпись уведемо нове ім'я поля - «ГОДИНИ не рівні ПР+ЛЕК». Ім'я поля може бути виправлене також безпосередньо у бланку запиту. Використання Построитель выраженийДля формування складного виразу, доцільно використати Построитель выражений. Він дозволяє вибрати необхідні імена полів з таблиць, запитів, форм, знаки операцій, функції. Викличемо Построитель выражений, вибравши Построить у контекстному меню (курсор миші повинен бути встановлений на рядку Поле, якеобчислюється). У лівій частині вікна Построитель выражений (рис. 7.10) виберемо таблицю ПРЕДМЕТ. Праворуч відобразиться список її полів. Послідовно виберемо потрібні поля, додаючи їх подвійним клацанням миші, знаки операцій вводяться з клавіатури. При цьому у верхній частині вікна сформується вираз. Збережемо запит під ім'ям «Різниця годин по предмету». Збережений запит можна виконати, виділивши запит у вікні Все объекты Access, натисканням правої кнопки миші і вибравши кнопку Открыть. Побудований запит може бути використаний для перевірки правильності заповнення поля ГОДИНИ в таблиці ПРЕДМЕТ. Використання групових операцій у запитахПризначення групових операційГрупові операції дозволяють виділити групи записів з однаковими значеннями в зазначених полях і використати для цих груп одну із статистичних функцій. В Access передбачено дев'ять статистичних функцій:
Результат запиту з використанням групових операцій містить по одному запису для кожної групи. У запит включаються поля, по яких провадиться угруповання, і поля, для яких виконуються групові функції. Порядок створення запиту з використанням групових операційДля створення запиту з використанням групових операцій формується запит на вибірку. У бланк запиту включаються поля, по яких треба зробити групування, і поля, по яких треба зробити статистичні обчислення. Виконаєте команду Создать/Конструктор запросов і на панелі інструментів конструктора запитів натисніть кнопку Итоги. Для групових обчислень по деякому полю потрібно замінити в ньому слово Группировка на потрібну статистичну функцію. Вибрати потрібну функцію можна через розгорнутий список. Конструювання однотабличного запиту із груповою операцією Розглянемо технологію конструювання однотабличного запиту із груповою операцією на прикладі таблиці СТУДЕНТ. Запит з функцією CountВизначимо фактичне число студентів у групі. Створимо запит на вибірку з таблиці СТУДЕНТ. Зі списку таблиці СТУДЕНТ перетягнемо у бланк запиту поле НГ (номер групи). У такий спосіб ми вкажемо, що по цьому полю повинно провадитися групування. Перетягнемо у бланк запиту поле НС, по якому буде обчислюватися функція Count для підрахунку числа студентів у групі. Натиснемо кнопку Итоги. Замінимо слово " Группировка " у стовпці НС на функцію Count. Для цього викличемо список і виберемо цю функцію. Бланк запиту прийме вид, показаний на рис. 7.11. Результат запиту показаний на рис. 7.12. Підпис поля "Count_HC" можна замінити на "Фактичне число студентів". Для введення цього підпису у бланку запиту встановимо на поле НС курсор миші і натиснемо праву кнопку. У контекстному меню виберемо команду Свойства . У Окне свойств наберемо у рядку Подпись "Фактична кількість студентів". Таблиця результату після виконання показана на рис. 7.13. Збережемо запит на вибірку під ім'ям "Число студентів у групах". Запит з функцією AvgПідрахуємо середній прохідний бал у групі. Сформуємо запит на вибірку для таблиці СТУДЕНТ із функцією Avg для поля ПБАЛ (прохідний бал студента). У бланку запиту заповнимо поля, як показано на рис. 7.14. Для обмеження точності результату двома знаками виберемо у Окне свойств у рядку Формат поля значення Фиксированный. Результат виконання запиту представлений на рис. 7.15. Збережемо цей запит під ім'ям "Середній прохідний бал групи" Запит з декількома груповими функціямиВиконаємо розрахунок числа студентів і середнього прохідного бала в групі в одному запиті. Це можливо, тому що групи записів в обох випадках формуються однаково (рис. 7.16). Збережемо цей запит під ім'ям "Число студентів і середній ПБАЛ групи". Завдання умов відбору в запитах із груповими операціямиУ запит із груповими операціями можна включати поля для завдання умов відбору записів з таблиць. Підрахуємо число студентів у кожній із груп з прохідним балом більше 4,5. Для цього в запит Число студентів і середній ПБАЛ групи вдруге включимо поле ПБАЛ і у рядку Групповые операции замінимо значення Группировка на значення Условие, вибравши його зі списку. Після цього введемо в рядок Условие отбора ">4,5" (рис.7.17). Зверніть увагу, що середній бал у цьому запиті також обчислюється тільки для студентів з прохідним балом, що перевершує 4,5. Умова відбору, задана у полі, по якому проводиться угруповання, або в полі, де записана функція угруповання, дозволяє відібрати тільки потрібні групи записів, наприклад, групи студентів із заданим номером або із заданим середнім прохідним балом. Запитання до захисту практичної роботи:
З повагою ІЦ “KURSOVIKS”! |