Тема 8. Створення запитів

1 Типи запитів

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

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

Для цієї мети в Access 2010(2013) включений самостійний об'єкт − "Запити" (Запрос). Запити створюються за допомогою "Майстра запитів" (Мастера запросов), Конструктора запитів (Конструктора запросов) і мови запитів SQL (Structured Query Language − структурний мову запитів). Кожне з перерахованих засобів має певну специфіку.

До основних типів запитів відносяться:

Запит на вибірку (Запрос на выборку − Select query). Ці запити дозволяють витягувати інформацію з таблиць, проводити обчислення з показниками, створювати перехресні посилання. У запитах на вибірку, змінювати дані в таблицях можна.
Запит на зміну (Запрос на изменение − Action query). Запити такого типу дають можливість коригувати інформацію, яка міститься в таблицях. Запити на зміну діляться на чотири категорії:
- запит на створення таблиці (запрос на создание таблицы − Make-table) − дозволяє створити нову таблицю на основі даних, що містяться в одній або кількох таблицях;
- запит на видалення (запрос на удаление − Delete) − видаляє всі записи з однієї або декількох таблиць на основі критеріїв, що задаються користувачем;
- запит на приєднання (запрос на присоединение − Append) − додає цілі записи або тільки зазначені поля в таблиці;
- запит на оновлення (запрос на обновление − Update) − змінює дані в існуючих таблицях на підставі інформації у вікні "Конструктора".
SQL-запит. Ці запити створюються за допомогою мови запитів у вигляді окремих інструкцій (операторів), що дозволяє вирішувати складні завдання при роботі з таблицями. SQL-запити часто створюються для зміни структури таблиці, для внесення змін до запису, для автоматичного перейменування полів, для під'єднання до зовнішніх баз даних.

Для створення запитів до БД MS Access 2010(2013) необхідно на вкладці "Створення" (Создание) вибрати режим "Майстер запитів" (Мастер запросов) або "Конструктор запитів" (Конструктор запросов), використовуючи піктограми (рис. 1) (режим SQL -запит суміщений з режимом "Конструктор запитів").

Рис. 1. Піктограми для вибору режиму створення запитів

2 Створення простого запиту на вибірку в режимі конструктора

Існує постановка задачі: необхідно сформувати список співробітників підприємства із зазначенням їх посади, основного окладу та надбавки за стаж роботи.

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

Клацаємо по піктограмі "Конструктор запитів" (Конструктор запросов). В результаті відкривається порожнє вікно Конструктор запитів і діалогове вікно "Додавання таблиці" (Добавление таблицы) (рис. 1).

Рис. 1. Діалогове вікно для додавання необхідних таблиць на поле запитів

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

Для виконання поставленого завдання, знадобляться таблиці "Особисті дані", "Надбавки за стаж" і "Номенклатура посад".

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

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

На малюнку 2 показаний список, що розкриває доступні поля з усіх таблиць. Можна побачити, що в списку знаходиться ім'я таблиці та ім'я поля. Після того, як буде обрано необхідний рядок, в бланку запиту в першому рядку з'явиться ім'я поля, а в другому − ім'я таблиці.

На бланку запиту в рядку під найменуванням "Виведення на екран" (Вывод на экран) встановлено елемент , в окремих випадках можна зняти цю позначку, тоді дані з цього поля будуть оброблятися в запиті, але на екран виводитися не будуть.

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

Встановимо коректний зв'язок між таблицями без ключового поля. Слід зауважити, що таблиця "Номенклатура посад" має зв'язок із файлом в Excel, з цієї причини ключове поле в таблиці не створюється.

Зв'язок таблиці "Особисті дані" з таблицею "Номенклатура посад" не показує відношення "Один до багатьох"». Для того, щоб запит працював коректно − змінюємо зв'язок.

Виділяємо лінію зв'язку між таблицями і клікаємо правою кнопкою миші по цій лінії. У контекстному меню вибераємо рядок з командою "Параметри об'єднання" (Параметры объединения).

Ставимо позначку в рядку вікна "Параметри об'єднання" (Параметры объединения) (рис. 3) і натискаємо на кнопку "ОК".

Рис. 3. Діалогове вікно "Параметри об'єднання"

Слід звернути увагу, що у вікні зв'язок між таблицями отримано зображення у вигляді лінії зі стрілкою на кінці.

Присвоюємо ім'я запиту, натисніть у правому верхньому куті , у діалоговому вікні, що з'явилося(рис. 4), натискаємо на кнопку "Так" (Да), після чого у наступному діалоговому вікні надаємо ім'я запиту (наприклад, Оклади та надбавки).

Рис. 4. Пропозиція системи по збереженню запиту

В області об'єктів бази даних в розділі "Запити" (Запросы) з'явиться новий запис .

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

Рис. 5. Результати виконання запиту на вибірку

Таблиця з результатами по сформованому запиту на вибірку даних містить записи всіх співробітників організації (рис. 5). Але для користувача незручно переглядати дані, якщо вони ніяк не впорядковані і це слід мати на увазі при формуванні запиту.

З цією метою, рекомендуємо в бланку запиту (рис. 2) використовувати рядок з найменуванням "Сортування" (Сортировка).

(Наприклад, можна вибудувати список співробітників по надбавках за стаж від мінімальної величини до максимального значення).

Для цього в стовпці з полем Надбавка розкриваємо список за допомогою символа , після чого обираємо рядок з командою "за зростанням" (по возростанию).

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

(Наприклад, користувача цікавлять тільки ті, чиї прізвища починаються на Г та отримують надбавку за стаж).

Таким чином, при використанні фільтра, слід виконати наступні дії:

Розкрити в полі Прізвища список символім.
Вибрати рядок "Текстові фільтри" (Текстовые фильтры), і розкрити його.
Вибрати рядок з найменуванням "Починається з..." (Начинается с...), натиснути на кнопку "ОК".
У діалогове вікно "Настроюваний фільтр" (Настраиваемый фильтр) ввести г (рис. 6).

Рис. 6. Приклад використання текстового фільтра в таблиці запиту

Для того, щоб відновити таблицю запиту, слід натиснути на символ "Видалити фільтр" (Удалить фильтр) або в контекстному меню таблиці запиту в стовбці Пізвище обрати .

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

Під параметричним запитом слід розуміти − відбір значень даних з таблиць по заданому параметру.

Фактично, вводиться умова на пошук інформації в однотипних рядках таблиць.

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

Для формування запиту, необхідно продумати найменування таблиць в яких знаходяться дані, які нас цікавлять, після чого запускаємо "Конструктор запитів" (Конструктор запросов).

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

Рис. 7. Приклад створення запиту з параметрами

Розмістіть таблиці на полі запиту.

На бланк запиту перенесіть поля з таблиць (Прізвище, Телефон, Фото, Найменування відділу, Посада).

У рядку "Умови відбору" (Условия отбора) запишіть в квадратних дужках текст повідомлення [Уведіть посаду], яке з'являтиметься перед користувачем.

У рядку "Або" (Или) запишіть параметр у подвійних лапках "Завідувач відділом".

У рядку "Сортування" (Сортировка) розкрийте список і виберіть »по зростанню».

Збережіть запит під ім'ям "Телефони та посади", після чого запустіть цей запит на виконання. Система повинна видати діалогове вікно з запитанням (рис. 8 ), в яке введіть, наприклад − Бухгалтер, і натисніть на кнопку "ОК".

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

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

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

Після виконання запиту відкрийте цей же запит в режимі Конструктор, і подивіться, що в рядку з найменуванням "Або" (Или) бланка запиту нічого немає. Система самостійно формувала вираз і записала його в рядок "Умова відбору" (Условие отбора).

Спробуйте мишкою розширити в бланку запиту стовпець з найменуванням "Посада", після чого у рядку з'явиться повний логічний вираз, який показано на малюнку 10.

Рис. 10. Логічний вираз Or (Або) для заданих параметрів відбору даних

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

При введенні жорсткого параметра у вигляді тексту, наприклад, можна вказати посаду, тоді текстовий параметр полягає в подвійні лапки ( "..." ).

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

При використанні діалогового вікна, ознакою його відкриття є відкрита і закрита квадратні дужки ( [...] ), параметр, що вводиться в діалогове вікно може бути і текстом і числом.

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

4 Формування запиту за неповним значенням поля

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

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

У цьому випадку доцільно скласти пошуковий запис, що використовує стандартні функції "Like" − Подібно і "Between" − Між.

Для вирішення поставленого завдання слід виконати наступні дії:

Створюємо запит у режимі "Конструктора", використовуючи як джерело таблицю "Особисті дані".

Помістити в бланк запиту поля "Прізвище", "Посада", "Рік надходження", "Найменування відділу" (можна також використовувати й інші поля).

У рядок "Умови відбору" (Условия отбора) для поля "Прізвище" ввести умову: Like "*ко*". Зірочка ліворуч і праворуч замінює невідомий фрагмент тексту (можна поставити зірочку тільки з одного боку).

У рядок "Умови відбору" (Условия отбора) для поля "Рік надходження" ввести умову: Between 2007 And 2013 (рис. 8).

Рис. 8. Заповнення бланка запиту для здійснення пошуку за неповним значенням поля

Збережіть запит, наприклад під ім'ям "Наближений пошук", а потім перевірте, як він працює.

5 Обробка множинних даних

Створивши у таблиці "Особисті дані" поле "Доплати" з множинним значенням даних, було досягнуто компактність їх зберігання, але постає питання, як працювати з такими даними?

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

Використовуючи можливості Access 2010 (2013), ця процедура не викликає труднощів. Для початку необхідно продумати, в яких вихідних таблицях можна знайти необхідні значення даних, а потім приступити до формування запиту.

Створюємо новий запит у режимі "Конструктора". Для цього переносимо на поле конструктора таблиці відповідні поля з таблиць (рис. 8).

Рис. 8. Підготовка запиту для роботи з множинним значенням даних

У бланку запиту необхідно додати новий рядок з найменуванням "Групова операція" (Групповая операция). Для цього можна натиснути правою кнопкою миші по бланку запиту, а потім вибрати рядок або на стрічці активізувати елемент (на вкладці Робота з запитами (Работа с запросами)). З цією метою розкриваємо список вибору функцій при роботі з груповими даними в стовпці для таблиці "Доплати" (рис. 9), та вибераємо функцію Sum.

Рис. 9. Список для вибору функцій при роботі з груповими даними

Зберігаємо запит, наприклад під ім'ям "Прізвище та оклад за посадою" та запускаємо запит на виконання. Результат виконання запиту показаний на малюнку 10.

Рис. 10. Результати роботи запиту з обробкою множинних дани

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

6 Технологія використання Будівника виразів для виконання обчислень

Знайомство з надбудовою Будівник виразів

Створення вирази для проведення обчислень у таблиці

6.1 Знайомство з налаштуванням Будівник виразів

Виконання обчислень над даними, які знаходяться в різних полях таблиці або в різних таблицях і запитах доводиться дуже часто. Ефективним засобом для складання формул за певними алгоритмами, є налаштування в Access 2010 (2013) − Будівник виразів (Построитель выражений), який підключається в режимі Конструктора за допомогою піктограми .

У діалоговому вікні Будівник виразів (Построитель выражений) містяться два розділи (рис. 11):

розділ у верхній частині вікна містить поле, в якому створюється вираз;
розділ в нижній частині вікна призначений для створення елементів виразів і вставки їх в поле виразу.

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

Даний розділ поділено на три вертикальних поля:

у лівому полі Елементи виразів (Элементы выражений) виводяться теки, що містять:
- об'єкти бази даних − ;
- вбудовані і визначені користувачем функції − ;
- константи − ;
- оператори − ;
- вирази − .
середнє поле Категорії виразів (Категории выражений) служить для вибору елемента або типу елементів з теки, заданої в лівому полі;
у правому полі Значення виразів (Значения выражений) виводиться список значень для елементів, заданих в лівому і середньому полях.

Наприклад, на малюнку 11 в конструкторі виразів показано, як відображається інформація в полях Будівника виразів.

Рис. 11. Загальний вигляд будівника виразів

6.2 Створення вирази для проведення обчислень у таблиці

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

Для створення такого запиту, будуть потрібні відомості з таблиці "Особисті дані". У даний момент нас буде цікавити технологія використання Будівника (Построителя), тому виконаємо послідовно наступні дії:

Запускаємо створення нового запиту в режимі Конструктор і на полі конструктора поміщаємо таблицю "Особисті дані".
У бланк запиту переносимо послідовно наступні поля: "Прізвище", "Рік надходження", "Посада".
У порожньому полі клацаємо мишкою, і на стрічці клацніть по піктограмі , після чого відкриється вікно Будівник виразів (Построитель выражений).
У верхньому полі Будівника вводимо вираз: Набрав стаж:2014− , в полі "Елементи виразів" (Элементы выражений) розкриваємо об'єкт "Таблиці" і клацаємо по значку , в полі "Категорії виразів" (Категории выражений) двічі клацаємо по − , після чого, у верхньому полі Будівника буде сформована запис − (рис. 12).
Натискаємо на кнопку ОК.

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

У бланку запиту повинен відображатися, створений вираз (рис. 13), після перевірки виразу слід зберегти запит, наприклад під ім'ям "Фактичний стаж роботи".

Рис. 13. Бланк запиту для обчислення фактичного стажу роботи співробітника

Отримавши результати виконання запиту (рис. 14), можна ввести логічні змінні на предмет аналізу, хто з працівників повинен отримати наступного року іншу надбавку, у зв'язку з переходом в іншу категорію.

Слід нагадати, що градації надбавок знаходяться в таблиці "Надбавка за стаж".

Для зручності проведення візуального аналізу, результати, які відображаються в полі "Набрав стаж" слід відсортувати за зростанням.

Рис. 14. Результати обчислень фактично відпрацьованих років співробітниками

6.3 Проведення складних обчислень

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

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

Створюємо новий запит в Конструкторі і як джерело даних вибераємо таблицю із запиту "Прізвище та оклад за посадою" (рис. 15).

Рис. 15. Базова таблиця для формування відомості на видачу заробітної плати співробітникам

Перенесіть в бланк запиту необхідні поля для проведення розрахунків заробітної плати (рис. 16).

Рис. 16. Бланк запиту з переліком полів

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

Рис. 17. Вираз (формула) для обчислення належної суми заробітної плати співробітнику за місяць

Формула складається з чотирьох доданків, на малюнку 17 кожний доданок виділено в окремий рядок.

Сумарний коефіцієнт доплати множиться на ставку співробітника, потім складається зі ставкою, після чого додається надбавка за стаж роботи. Так як значення виразу вибиралися з вікна Категорій Будівника виразів (Построитель віражений), то у вікно переносилися повні значення (назва таблиць, звідки вони отримані, назва таблиці, з якої переносилися і найменування поля).

Слід звернути увагу, що найменування таблиць і полів у таблицях укладені в квадратні дужки, а символ "!" (знак оклику) − позначає об'єднання, вибраних параметрів. У тому випадку, якщо складати таку формулу вручну, досить вказати тільки найменування полів, з бланка запиту при його складанні, тоді запис формули буде компактніше і зрозуміліше.

Ось, що варто було б записати вручну у вираз вікна Будівника виразів (Построитель віражений):

Усього: [Sum-коефіцієнт доплати] * [Оклад] + [Оклад] + [Надбавка]

Спробуйте створити такий вираз у ручному режимі і подивіться на отриманий результат.

Насправді, нам необхідно підготувати відомість для виплати заробітної плати за місяць, тому потрібно в запит ввести ще одне поле "Видати на руки". На рисунку 18 показано вираз:

"Видати на руки: 0,87 * [Усього ]",

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

Рис. 18. Створення поля в запиті і встановлення його властивостей

При виплаті заробітної плати зручно оперувати з цифрами, які відображаються, як тип "Грошовий" (Денежный), тому слід встановити у вікні властивостей формат створеного поля.

Для виконання цієї операції в бланку запиту клацаємо правою кнопкою миші по полю "Видати на руки", після чого у меню вибераємо рядок з командою .

У вікні властивостей (рис. 18) розкрийте список в рядку "Формат поля" і встановіть "Грошовий" (Денежный). Після чого можна зберегти запит і перевірити, як він працює. Результат виконання запиту представлений на рисунку 19.

Рис. 19. Таблиця з даними по заробітній платі співробітників

Сайт:	Навчально-інформаційний портал ВП НУБіП України "Ніжинський агротехнічний інститут"
Курс:	Система управління базами даних
Книга:	Тема 8. Створення запитів
Надруковано:	Гість
Дата:	вівторок 30 квітень 2024 10:55