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

Головна / Оптимізація роботи

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

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

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

Бланк запиту наведено на рис.1. Як видно, в бланку запиту створюється нове поле, що обчислюється, Ціна зі знижкою, в комірку Поле якого вводиться наступна формула: Ціна зі знижкою: [ЦінаПродажі]*(1-[Знижка у відсотках]/100).

Мал. 1. Бланк запиту з одним параметром

Під час виконання запиту Access відкриває діалогове вікно Введіть значення параметра, куди необхідно запровадити необхідну обчислень величину (рис.2).

Мал. 2. Діалогове вікно Введіть значення параметра

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

Мал. 3. Результати виконання запиту

Параметри також можна використовувати для введення критерію відбору записів. Такий підхід є доцільним, якщо заздалегідь передбачається, що запит запускатиметься неодноразово, але одну або кілька умов доведеться змінювати. Наприклад, у запиті до таблиці Клієнти, Що відображає список всіх клієнтів із зазначенням їх даних, можна вимагати найменування фірми клієнта. Тоді при кожному запуску запиту назва фірми, введена користувачем діалогове вікно Введіть значення параметра, буде перетворено на умову відбору, й у результуючу таблицю програма Access включить лише записи, які відповідають цьому критерію. На рис.4 наведено бланк та діалогове вікно введення такого запиту, в якому для поля Фірмав рядку Умова відборузамість конкретного значення вказано підказку для введення параметра – [ Введіть назву фірми].

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

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

Покрокова інструкція

id="a1">

Якщо у вас виникла потреба вирішити завдання, в якому критерії запитів заздалегідь не відомі або можуть змінюватися, слід скористатися запитом із параметром у Access.

Щоб ввести параметр замість певного значення, слід ввести в розділ «Умова відбору» ім'я або фразу, укладену в квадратні дужки. Тільки після цього, Access розглядатиме інформацію та виводитиме її як коментар до параметрів. Для використання кількох змінюваних критеріїв слід придумати їм унікальні імена.

Для прикладу створіть запит, який показує список викладачів, які працюють на певній кафедрі. Саме цей критерій змінюється, тому в рядку «Умови відбору» необхідно ввести значення =[Введіть назву кафедри].
Збережіть отриманий фільтр під назвою «Вибірка викладачам на кафедрі».
Тепер після запуску ви побачите діалогове віконце, в якому буде потрібно ввести необхідну назву, після чого з'явиться список викладачів, які числяться на цій кафедрі.

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

Для створення запиту на вибірку в режимі Конструктордля поля, яке передбачається використовувати як параметр, ввести в комірку рядка Умова відборувираз із текстом запрошення, укладеним у квадратні дужки [Текст запрошення].

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

Like“*” & [Текст питання] & “*”, у цьому випадку можна буде вказувати неповну назву.

приклад 10.Необхідно отримати інформацію про продукції конкретному відділі. Побудуємо наступний запит (рис. 25):

Мал. 25. Конструктор запиту з параметрами

Тепер, при запуску запиту користувачеві задаватиметься питання (рис. 26):

Мал. 26. Робота запиту з параметром

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

Вправи

1. Створити запит Поставки за період, відібрати відомості про те, що коли і в якій кількості поставлялося до супермаркету за вказаний інтервал часу (тобто при відкритті запиту вказуються початкова та кінцева дати періоду).

Указання. Для поля Дата постачанняз таблиці Постачання в рядку Умова відборузадати вираз:

Between[Введіть початкову дату] And[Введіть кінцеву дату].

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

3. Що за останній місяць постачалося до конкретного (зазначеного користувачем) відділу?

4. Створити запит Відділи на поверсі, який дозволить за вказаним номером поверху отримати перелік відділів, що знаходяться на ньому.

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

6. Створити запит Постачальник та його товари, який за вказаним постачальником видає список товарів, що їм поставляються.

7. Створити запит, який визначає того, хто раніше за всіх був прийнятий на роботу в конкретно зазначений відділ.

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

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

1. Дайте визначення умови Запит із параметром.

2. Як побудувати умову Запит із параметром?

3. Яка умова необхідно сформулювати для введення неповного текстового параметра?

Тема 7 Функції у запитах

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

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

Приклади основних функцій у Access:

Day([Назва таблиці].[Назва поля]) – повертає значення дня місяця в діапазоні від 1 до 31;

Month(Дата) – повертає значення місяця року в діапазоні від 1 до 12;

Year(Дата) – повертає значення року в діапазоні від 100 до 9999.

Приклад 11.Створіть запит, який дозволяє отримати повну інформацію про поставлену продукцію за вказаним номером місяця від 1 до 12 (рис. 27).

Мал. 27. Функції у запитах

приклад 12.Визначте стаж роботи кожного працівника.

Для цього скористаємося будівельником виразів (рис. 28).

Функція DateDiffдозволяє одержати проміжок між датами. Причому щоб проміжок був наданий у днях, роках або інших одиницях часу, необхідно вказати формат відповіді: «yyyy» – проміжок у роках; "m" - різниця в місяцях; "d" - у днях; "w" - тижнях.

Бланк запиту виглядатиме, як показано на рис. 29.

При побудові запитів, у яких зіставляються дані за періодами, часто доводиться посилатися початку місяця, кварталу тощо (табл. 7).

Мал. 28. Використання функції DateDiff

Мал. 29. Бланк запиту із заданою функцією

Т а б ли ц а 7

Корисні функції для обробки дат

Записи без підлеглих

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

У вікні БД вибрати об'єкт Запити, натиснути кнопку Створити;

У діалоговому вікні, що відкрилося Записи без підлеглих;

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

Вправи

У БД Супермаркет реалізувати такі запити:

1. Створити запит Місяць поставки, в якому з повного ДатиПостачаннябуде виділено порядковий номер місяця (на підставі таблиць Товари і Постачання ). Категорія функцій Дата час, функція Month.

Номер місяця

Номер місяця: Month([Постачання]![Дата Поставки]).

2. Створити запит Рік постачання (аналогічно до попереднього завдання). Категорія функцій Дата час, функція Year.

3. У запиті Розрахунок акцизу визначити розмір акцизу (20 %), якщо товар підакцизний, в іншому випадку встановити значення 0 (на підставі таблиці Товари ). Категорія функцій Управління, функція IIf.

У к а з а н і е. Додати нове поле Розмір акцизу, в якому задайте вираз:

Розмір акцизу: IIf(Товари! Акциз = Істина; Товари! Ціна * 0,2; 0).

4. У запиті Постачання у вихідні дні, на підставі таблиць Товари і Постачання , відобразити інформацію про поставки, які були зроблені в суботу або неділю. Категорія функцій Дата час, функція WeekDay.

У к а з а н і е. Додати нове поле День тижня, В якому задати вираз:

День тижня: Weekday([Постачання]![Дата Поставки]; 2).

В рядку Умовавідбору задати умову 6 Or 7.

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

У к а з а н і е. Додати нове поле Назва місяця, В якому задати вираз:

Назва місяця: MonthName(Month([Постачання]![Дата Поставки])).

В рядку Умова відборузадати умову параметра:

Like"*" & [Ввести назву місяця] & "*".

6. Створити запит Вік співробітників (на підставі таблиці Співробітники ). Категорія функцій Дата час, функція DateDiff.

7. Скільки місяців минуло з першої поставки?

8. У списку відділів супермаркету створити поле, в якому є уточнення щодо наявності в ньому підакцизних товарів (тобто у стовпці написано текст: «підакцизні товари Є» або «підакцизних товарів НІ»).

У к а з а н і е. Додати у запиті нове поле з умовою Підакцизні товари:

IIf(Sum([Товари]![Акциз]=Істина)<>0; «Є»; «НІ»).

Включити групові операції у рядку Групова операціявибрати Вираз.

9. Організувати запит Зіставлення даних за 2006 та 2007, в якому будуть виведені сумарні витрати за всіма поставками 2006 та 2007 років окремо у два рядки.

10. Організувати запит Зіставлення даних за двома будь-якими місяцями, в якому будуть виведені сумарні витрати за двома будь-якими назвами місяців, які вводяться з клавіатури у вигляді тексту (наприклад, січ., лют. тощо).

11. Організувати запит Постачання поточного кварталу (з використанням функцій DateDiff, Now() для розрахунку різниці між датами, вираженою в місяцях "m", та інших умов).

12. Створити запит, який визначає, які товари, занесені до таблиці Товари , ніколи не постачалися.

13. Створити запит, який визначає, хто із зареєстрованих постачальників не зробив жодного постачання.

14. Створити запит, який визначає, чи є у БД назви відділів, у яких ніхто зі співробітників не нараховується.

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

1. Які вбудовані функції ви знаєте?

2. Якими способами можна внести функцію в Конструкторзапиту?

3. Дайте характеристику способу створення запитів запису без підлеглих.

4. За допомогою якої функції можна визначити різницю між датами?

5. Як визначити у запиті поточну дату?

Тема 8 Перехресні запити

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

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

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

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

Мал. 1. Бланк запиту з одним параметром

Мал. 2. Діалогове вікно Введіть значення параметра

Мал. 3. Результати виконання запиту

Запит у Access є об'єктом, який зберігається у файлі бази даних і може багаторазово повторюватися. Усі запити, які ми демонстрували досі, містили конкретні значення дат, назв, імен тощо. буд. Якщо потрібно повторити такий запит коїться з іншими значеннями за умов відбору, його потрібно відкрити як Конструктора, змінити умову і виконати. Щоб не робити багаторазово цих операцій, можна створити запит із параметрами. Під час виконання такого запиту видається діалогове вікно Введіть значення параметра(Enter Parameter Value), в якому користувач може ввести конкретне значення, а потім отримати потрібний результат.

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

1. Відкрийте цей запит у режимі Конструктора.

2. Щоб визначити параметр запиту, введіть у рядок Умова відбору(Criteria) для стовпця "Назва" (CompanyName) замість конкретного значення слово або фразу і покладіть їх у квадратні дужки, наприклад [Постачальник:]. Ця фраза видаватиметься у вигляді запрошення у діалоговому вікні під час виконання запиту.

3. Якщо ви хочете, щоб Access перевіряла дані, які вводяться як параметр запиту, потрібно вказати тип даних для цього параметра. Зазвичай у цьому не потрібно під час роботи з текстовими полями, т.к. за умовчанням параметру надається тип даних Текстовий(Text). Якщо ж дані в полі запиту є датами або числами, рекомендується тип даних для параметра визначати. Для цього клацніть правою кнопкою миші на вільному полі у верхній частині запиту та виберіть з контекстного меню команду Параметри(Parameters) або виконайте команду меню Запит, Параметри(Query, Parameters). З'являється діалогове вікно Параметри запиту(Query Parameters), наведене на рис. 4.31.

Мал. 4.31.Діалогове вікно Параметри запиту

4. У стовпець Параметр(Parameter) потрібно ввести назву параметра точно так, як він визначений у бланку запиту (найлегше це зробити шляхом копіювання через буфер обміну), тільки можна не вводити квадратні дужки. У стовпці Тип даних(Data Type) виберіть зі списку, що розкривається, необхідний тип даних. Натисніть кнопку ОК.

5. Натисніть кнопку Запуск(Run) на панелі інструментів, щоб виконати запит. Під час виконання запиту з'являється діалогове вікно Введіть значення параметра(Input Parameter Value) (рис. 4.32), до якого потрібно ввести значення, наприклад Tokyo Traders. Результат виконання запиту подано на рис. 4.33. До нього потрапляють лише товари, які постачаються даним постачальником.

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

Мал. 4.32.Діалогове вікно Введіть значення параметра

Мал. 4.33.Результат виконання запиту з параметром

Порада

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

Покрокова інструкція

Вибір редакції