Теоретична довідка та рекомендації по ходу виконання завдань. Примітка – рекомендації є лише дорадчим матеріалом

Примітка – рекомендації є лише дорадчим матеріалом. Досягнення поставленої завданням мети з використанням інших прийомів, не зазначених в даних рекомендаціях не може вважатися помилкою.

Запит – це об’єкт бази даних, що дозволяє відбирати дані з однієї або кількох таблиць за умовами. Крім того, використовуючи запит можна створювати обчислювальні поля для кожного запису або для групи записів. Це робиться в запитах “на виборку”. Запити на оновлення даних дозволяють вносити зміни одразу в декілька записів, що задовольняють вказаним умовам. Запити – найважливіший об’єкт роботи з даними.

При роботі над створенням запиту необхідно вірно записувати умови відбору даних в запитах. Умови записуємо в рядку “Условие отбора:”. Для числових полів умови мають вигляд, наприклад, <100 , або >40 And <60. Тобто записуємо знак відношення та числове значення. Можна також використовувати логічні функції And , Or, Not.

Функція Beetween нижня_границяAnd верхня_границя

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

Наприклад, Beetwen 40 And 60 попадання значення поля від 40 до 60.

Умови для текстових полів записуються з використанням функції

Like “Образник пошуку з символами шаблону”

Символ шаблону * замінює довільну кількість символів в даній позиції, символ ? замінює один який-небудь символ в позиції, символ # вказує, що в даній позиції повинна стояти цифра.

Наприклад, умова Like “CA-#######” .

дозволить відібрати записи, в яких записані номери паспортів серії СА.

Виконання завдання №1

1.Порядок створення запиту, який відбирає з таблиці КНИГИ ті примірники, ціна котрих більше 20 гривень та прізвище автора починається або з літери “Б”, або з літери “В”.

- В вікні бази даних перейдіть на вкладку Запити. Натисніть кнопку Створити. Оберіть з меню Конструктор. Увікні діалогу Додавання таблиці на вкладці Таблиціоберіть таблицю, з якої потрібно відібрати дані. Для нашого випадку це -КНИГИ.Натисніть кнопку Добавити. Закрийте вікно Додавання таблиці. Тепер потрібно обрати ті поля, що необхідно включити до запиту. Оберемо поля:Автор, Назва, Рік, Ціна.Для того, щоб помістити ці поля до бланка запита, потрібно провести подвійний клік на імені поля у таблиці. Є і інші можливості: перетягнути назву поля з таблиці до бланка запита або обрати необхідні поля у списку при назві поля у бланку запиту.

- Далі необхідно поставити умови, по яких буде вестися відбір. Для цього у рядку бланка запита Умова відбору для Цінаставимо умову >20. Для поля Авторставимо умову “Б*” Or “В*”, де * означає усі символи після першої Б ( для одиничного символу необхідно використовувати “?”). Функцію Like буде додано автоматично при переході (активації) іншої клітинки таблиці конструктора запиту.

- Для перегляду результату роботи ще не збереженого запиту з віконного меню головного вікна Access перейдіть режим таблиці (Вид à Режим таблиці), при виявленні помилок ви зможете повернутись в режим конструктора провівши операції Вид à Конструктор.

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

- Під час закривання вікна запиту збережіть його під іменем Запит1_1

2.Порядок створення підсумкового запиту:

- Для створення підсумкового запиту на панелі інструментів обираємо кнопку Групові операції ( ). У бланку запиту з'явиться рядок Групові операції. Після клацання у цьому рядку з'являється список можливих обчислень:

SUM- обчислення суми значень для групи;

AVG - середнє значення поля для даних з групи записів;

MIN - мінімальне значення по даних з групи записів;

MAX - максимальне значення по даних з групи записів;

COUNT- кількість записів, в яких є значення, з групи;

STDEV - стандартне відхилення;

VAR - дисперсія;

FIRST - значення в першому запису групи;

LAST - значення в останньому запису групи.

- Для поля запиту Відділення обираємо Групування, тобто за значенням цього поля утворюємо групу, а для другого – COUNT( підрахунок кількості входжень відповідного запису до групи).

- Так як в вікні запиту призначення поля ПІП змінилося (воно відображатиме кількість читачів, а не їх прізвища), змінимо в вікні запиту властивості цього поля. Для цього (при активній клітинці поля ПІП) проводимо операції: меню Вид à Властивості,в вікні Властивості поля на вкладці Загальні в поле Підпис введемо Кількість читачів.

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

- При виявленні помилок проведіть роботи по їх усуненню, при вірній роботі запиту – закрийте його вікно із збереженням запиту під іменем Запит1_2

3.Порядок створення перехресного запиту:

- В вікні бази даних перейдіть на вкладку Запити. Натисніть кнопку Створити. Оберіть з меню Перехресний запит.

- Перший крок – виберіть таблицю КНИГИ à Дальше.

- Другий крок–як поле, значення якого буде використане в якості заголовку стрічок виберіть Рік(виділити в полі Доступні поля, натиснути кнопку > , при цьому виділене повинне відобразитись в полі Вибрані поля), à Дальше.

- Третій крок - як поле, значення якого буде використане в якості заголовку стовпчиків задайте Видавець à Дальше.

- Четвертий крок – задайте порядок обчислень – в списку Поля виділіть Ціна, в списку Функції виділіть Сума, дозвольте ви числення підсумкового значення для кожної стрічки відмітивши відповідну опцію, àДальше.

- Задайте ім’я запиту (Запит1_3), прогляньте отримані результати в режим таблиці. При вірній побудові запиту ви отримаєте результат, зображений на малюнку.

Виконання завдання №2

1.В таблицях баз даних (на відміну від електронних таблиць) ніколи не зберігають дані, які можуть бути обчислені по даним з таблиць. В більшості випадків тому, що це потребує значних витрат пам’яті та значно уповільнює пошук необхідних даних та обробку даних в таблицях. Всі необхідні обчислення виконують в запитах, створюючи поля, що обчислюються. Для створення таких полів в вільному стовпчику конструктора запитів записують нове ім’я поля, знак “:”, а потів вираз, що використовує імена інших полів та обчислює необхідне значення. Імена полів записуються у квадратних дужках.

При запису виразів можна використовувати вбудовані функції Access. Імена цих функцій на відміну від Excel не перекладені на російську, вони пишуться англійською, але за змістом багато з низ співпадає з функціями Excel. При виконанні завдання 2.1 ми скористаємось двома із цих функцій:

1) Iif(умова; вираз1; вираз2) – обчислює вираз1, якщо умова виконується, та вираз2, якщо умова не виконується.

2) DateDiff(“d”; дата1; дата2) - обчислює різницю між двома датами в днях. Якщо перший аргумент “m” – різниця дат обчислюється в місяцях, якщо “y” - в роках. “d” – днях .

Для того . щоб виконати завдання 2.1 проведіть черговість операцій:

- В вікні бази даних перейдіть на вкладку Запити. Натисніть кнопку Створити. Оберіть з меню Конструктор. Увікні діалогу Додавання таблиці з вкладки Таблицідобавте в вікно конструктора дві таблиці – ВИДАНО та КНИГИ. Закрийте вікно Додавання таблиці.

- З таблиць в вікно запиту оберіть поля ЧитКв№, Дата видачі, Дата повернення, Інв№, Автор, Назва, Ціна.

- У вільну колонку (зразу ж за колонкою Ціна) в таблиці конструктора запиту в клітинку стрічки Полевведіть такий текст:
Пеня: IIf([Дата повернення]<Date();(DateDiff("d";[Дата повернення];Date())*(0,01*[Ціна]));0)
(Якщо значення в полі Дата повернення <за поточну дату, тов поле Пеня помістити значення виразу {(різниця в днях між датою повернення та поточною датою)*(ставка пені за 1 день * ціну книги)}, якщо ні – то вивести 0).
Для створення такого виразу можна використати Майстра побудови виразів, викликавши його кнопкою з панелі інструментів.

- Після введення виразу перевірте його роботу перейшовши в режим відображення таблиці. Якщо ви отримали вірний результат роботи запиту, закрийте його вікно з збереженням під назвою Запит2_1.
Вірний результат роботи запиту показано на малюнку:

2.Для виконана цього завдання:

- в вікні конструктора оберіть таблиці КНИГИ і читачі та запит Запит 2_1. Список1. До бланку запиту включіть поля ПІП з таблиці ЧИТАЧІ, Інв№таЦіназ таблиці КНИГИ, та Пеня із запиту Запит2_1.

- Для створення підсумкового запиту на панелі інструментів обираємо піктограму (Групові операції). У бланку запиту після цього з'являється рядок Групові операції. В цьому рядку задаємо : для ПІП - Групування, для Ціна – SUM( підрахунок суми), для поля Інв№ - Count (підрахунок кількості записів) та для Пеня - SUM( підрахунок суми).

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

для поля Інв№ - Кількість;

для поля Ціна –Загальна вартість книг;

для поляПеня –Загальна сума пені.

- Перевірте роботу запиту перейшовши в режим таблиці, якщо помилок в роботі запиту не виявлено, збережіть його під іменем Запит2_2. Вигляд таблиці вірно побудованого запиту представлено на малюнку.

3.Існує два різних типу об’єднання двох таблиць – внутрішнє та зовнішнє. Якщо об’єднання внутрішнє, то до одного запису запиту будуть об’єднані записи, в яких співпадають значення в полях зв’язку. Якщо який-небудь запис в одній таблиці не має відповідного запису в інший таблиці, він не буде включений до запиту.

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

Для створення запитів типу «Записи без підлеглих» необхідно змінювати тип об’єднання таблиць на зовнішній.

Для виконана поставленого завдання:

- в вікні конструктора оберіть таблиці КНИГИ і ВИДАНО, з яких включимо до запиту поля Інв№, Шифр, Автор, Назва з таблиці КНИГИ та Інв№ із таблиці ВИДАНО;

- змінимо параметри об’єднання на Зовнішнє, для чого проведіть подвійний клік на лінії зв'язку між таблицями. При вірному проведенні операції виникне вікно діалогу Параметри об’єднання (при невірному розташуванні вказівника над лінією зв’язку виникне вікно Властивості запиту)У вікні діалогу Параметри об’єднання оберіть другий тип об’єднання – “об'єднання ВСІХ записів з “КНІГИ” і лише тих записів з “ВИДАНО”, в яких зв'язані поля співпадають.” і натисніть на ОК.

- В результаті проведеної роботи створено зовнішнє об’єднання, до якого включені всі книжки з таблиці КНИГИ. Для тих книжок , що не мають записів з таблиці ВИДАНО встановлюється значення ‘Null’, тому для поля Інв№ із таблиці ВИДАНО встановимо такі параметри: в клітинку Умова відборувведемо – Is Null та знімемо позначку біля опції Вивід на екран.

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

Виконання завдання №3

1.Завдання 3.1 вимагає створення запиту, який модифікує таблицю КНИГИ. Тому перед його виконанням, щоб зберегти цілісність бази даних при допущенні в створенні запиту помилок, створіть резервну копію цієї таблиці копію, для чого в вікні бази даних перейдіть на вкладку Таблиці, виділіть необхідну таблицю, проведіть операції: меню Правка à Копіювати;меню Правка àВставити,в вікні діалогу задайте ім’я копії як К_КНИГИ.

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

- В режимі Конструктор створимо запит, в який включимо поля Рікта Ціна з таблиці КНИГИ.Надамо можливість вводити рік видання як параметр. Для цього у прямокутних дужках введемо [Рік1] у рядку Умови відбору. Під час виконання запиту це ім'я з'явиться у діалоговому вікні для вводу року видання. Перейшовши в режим таблиці (меню Вид à Режим таблиці) перевіримо, чи обираються необхідні записи. На малюнку представлено таблиця, яка генерується запитом при введенні 1999року видання.
якщо ви отримали подібну таблицю – перейдіть до виконання наступних кроків, якщо ні – проаналізуйте помилку і після її виправлення продовжте роботу.

- Далі перебудуємо цей запит на запит на оновлення. Для цього знову ввійдемо в режим конструктора - меню Вид àКонструктор,для запитуувімкнемо режим оновлення, для чого меню Запит àОновлення. Для поля Ціна в рядок Оновленнявведемо вираз [Ціна]*([націнка]+100)/100 для перерахування вартості книжок. Зверніть увагу, що цей вираз використовує ще один параметр [націнка], що надає можливість вводити розмір націнкиу відсотках. Вигляд вікна конструктора матиме при цьому наступний вигляд:

- Закрийте вікно конструктора з збереженням запиту під іменем Запит3_1. Перевірте роботу запиту відкривши його з вкладки Запити вікна бази даних. При вірній побудові запиту на екран послідовно будуть виведені запити на введення рівня націнки (вводити лише цифрове значення, без знаку %), року видання , для якого проводиться переоцінка (вводити повний цифровий запис, без скорочень та тексту типу р. чи рік). Після введення даних на екрані з'являється вікно діалогу, що повідомляє про кількість оновлених записів. Після підтвердження таблиця оновлюється, після чого доступу до початкових даних вже не має.

2.Побудова запиту на створення таблиці.

- Перед тим як зробити запит на зміну даних, створимо запит на вибірку, щоб переконатися, що робота йде з необхідними даними. В режимі конструктора до запиту включимо таблиці ЧИТАЧІ та запит Запит2_2, котрий для кожного читача підраховує суму пені. Оберіть поля ЧитКв№, ПІП, Відділення, Телефон- з таблиці ЧИТАЧІ, Count-Інв№, Sum-Ціна, Sum-Пеня з запиту Запит2_2. В області відображення джерел даних (верхня частина конструктора) створіть зв'язок між таблицею та запитом за полю ПІП (перетягнути відповідне поле з таблиці на відповідне поле запиту, після чого в вікні відобразиться лінія зв’язку). Для поля Sum-Пеня задайте умову >0 Ця умова надасть можливість обирати тих читачів, у яких пеня більше 0.

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

- При одержанні вірного проміжного результату приступіть до перебудови запиту на створення таблиці, для чого ввійдіть в режим конструктора, після чого в меню Запитвикористайте команду Створення таблиці. У вікні діалогу наберіть слушне ім'я для нової таблиці (наприклад, “Заборгованість на 22 листопада”). Закрийте вікно конструктора з збереженням створеного запиту під іменем Запит3_2.

- Перевірте роботу запиту запустивши його з вкладки Запити вікна бази даних. Після запуску запиту на екрані з'явиться вікно діалогу з повідомленням про кількість записів, що будуть вставлені до таблиці. Для підтвердження створення таблиці натисніть кнопку ДА. На вкладці Таблиці вікна бази даних перевірте наявність таблиці з цим ім'ям та перегляньте її зміст.

3.Для повного захисту від помилок перед створенням запиту зробіть резервну копію таблиці ВИДАНО, присвоївши копії ім’я К_ВИДАНО.

- Перед тим як зробити запит на зміну даних, створимо запит на вибірку, щоб переконатися, що робота йде з необхідними даними. В режимі конструктора до запиту включимо таблицю ВИДАНО: поля Інв№ та ЧитКв№. Для них задамо умови відбору ввівши в відповідні клітинки стрічки Умови відбору параметри [Введіть Інв№ книги], [Введіть номер читацького квитка]. Коли ви будете виконувати цей запит з’являться послідовно два вікна діалогу для введення необхідної інформації.

- Перейшовши в режим відображення таблиці переконайтеся, що запит обирає необхідну інформацію.

- Для перебудови запиту на вилучення поверніться в режим конструктора, в віконному меню Запит виберіть команду Видалення, після чого в таблиці конструктора виникне рядок під назвою Видалення. В ньому за замовчуванням стоятиме параметр Умова, залиште цей параметр без змін, закрийте вікно конструктора з збереженням створеного запиту під іменем Запит3_3.

- З вікна бази даних відкрийте таблицю ВИДАНО і запам’ятайте один чи кілька записів (а саме - Інв№ книги та ЧитКв№., якому ця книга видана) , ці відомості вам буде необхідно ввести в процесі діалогу роботи з запитом. (приклад – книга Інв№ 1 видана ЧитКв№.3)

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

Контрольні запитання.

1. Чим відрізняється запит від таблиць і форм?

2. Що являє собою запит?

3. Для чого використовують запити?

4. Чи можна використовувати запити як джерела інформації для форм і звітів?

5. Які типи запитів ви знаєте?

6. Що можна використовувати як джерело даних при створенні запиту?

7. Які способи створення запитів ви знаєте?

8. Яка черговість операцій при створенні запиту за допомогою конструктора?

9. При роботі в режимі конструктор як побачити результати роботи ще не збереженого запиту?

10. Як задати властивості полю запита?

11. Чи існує різниця в функціях, які використовують в Excel та Access?