Використання запитів для роботи з даними

Типи запитів

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

Призначення запитів:

•  вибір записів, що відповідають певним критеріям відбору без попереднього відкриття конкретної таблиці або форми;

•  вибір таблиць, що містять потрібні записи, з можливістю подальшого додавання інших таблиць;

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

•  створення нової таблиці на основі даних, отриманих з існуючих таблиць;

•  оновлення, додавання і видалення записів таблицях;

•  виконання обчислень над значеннями полів. У Access розрізняють наступних типів запитів:

•  запит на вибірку даних (Select query) — дозволяє витягувати дані з однієї або декількох таблиць згідно із заданим критерієм і відображувати їх в новій таблиці;

•  перехресний запит (Crosstab query) — підсумовує в електронній таблиці дані з однієї або декількох таблиць. Вони використовуються для аналізу даних, створення діаграм;

•  запит з параметрами (Parameter query) — дозволяє витягувати дані з однієї або декількох таблиць згодне одному або декільком параметрам. Наприклад, для поля, в якому відображуються дати, можна ввести запрошення наступного вигляду «Введіть початкову дату:» і «Введіть кінцеву дату», аби задати кордони діапазону значень;

•  запит на зміну (Action query) — створюють нові таблиці із запитів. Вони дозволяють включити нові записи або видалити старі, внести до них зміни за допомогою виразів, вбудованих в запит;

•  запит SQL (SQL query) — заснований на інструкціях SQL (Structured Query Language — мова структурованих запитів). Мова SQL є стандартом для більшості СУБД. У форматі SQL у базі даних зберігаються всі запити.

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

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

Мал. 24.1 Вікно бази даних після . вибору значка Запити на панелі Об'єкти

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

Мал. 24.2 Вікно для введення початкової дати відбору дані

Запит можна відкрити в режимі таблиці (мал. 24.3) або в режимі конструктора (мал. 24.8).

Мал. 24.3 Відображення запиту на вибірку в режимі таблиці

Створення запиту

Запит можна створити за допомогою майстра або в режимі конструктора. Як завжди, найбільш простий спосіб побудови запиту передбачає використання майстра. Режим конструктора (Query Design) дозволяє задавати не лише умови вибору даних, але і порядок сортування. Запити зберігаються як окремі об'єкти і відображуються у вікні бази даних.

Створення простого запиту за допомогою майстра

Для відкриття вікна майстра запиту у вікні бази даних на панелі Об'єкти (Objects) виберіть значок Запити (Queries) і двічі клацніть значок Створення запиту за допомогою майстра (Create Query By Using Wizard) (див. мал. 24.1). Інший спосіб відображення майстра запиту описаний нижчим в розділі «Створення багатотабличних запитів».

У вікні Створення простих запитів (Simple Query Wizard) у списку, що розкривається Таблиці і запити (Table/Query) виберіть таблиці і запити, поля яких використовуватимуться в запиті (мал. 24.4). У списку Доступні поля (Available Fields) двічі клацніть імена використовуваних в запиті полів. Ці поля перемістяться в список Вибрані поля (Selected Fields) . Натискуйте кнопку Далі (Next) .

Мал. 24.4 Вікно майстра створення простих запитів

При створенні запиту по декількох таблицях в другому вікні положенням перемикача вам треба вибрати детальний (виведення кожного поля кожного запису) (Detail (shows every field of every record)) або підсумковий (Summary ) запит (мал. 24.5). Підсумкові запити містять поля, по яких групуються дані, і числові поля, по яких визначають, наприклад, сумарне, середнє, максимальне або мінімальне значення. У підсумковому запиті можна зробити підрахунок кількості записів в групі (Count records in). Для додавання обчислень в результати запиту натискуйте кнопку Підсумки (Summary Options) і виберіть потрібні поля або встановите прапорець Підрахунок числа записів в Підсумки (Count records in Quarterly Orders by Product).

Мал. 24.5 Вибір типа створюваного звіту

У наступному вікні майстра вам буде запропоновано дати ім'я запиту і положенням перемикача визначити подальші дії: Відкрити запит для перегляду даних (Open The Query To View Information) або Змінити макет запиту (Modify the query design). Після того, як ви натискуватимете кнопку Готово (Finish) відкриється запит в режимі таблиці.

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

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

У діалоговому вікні Новий запит (New Query) виберіть в списку в правій частині вікна елемент Конструктор (Design View) і натискуйте кнопку OK (мал. 24.6). На екрані відображуватиметься вікно запиту в режимі конструктора і діалогове вікно Додавання таблиці (Show Table) (мал. 24.7)яке дозволяє вибрати, по яких таблицях і запитах або їх комбінації буде створений новий запит. Вікно запиту розділене посередині по горизонталі. У верхній частині вікна відображують списки доступних в запиті полів всіх таблиць, в нижней — специфікація запитів. Кожен стовпець відображує поле, використовуване для виділення тих записів, які будуть включені в запит.

Мал. 24.6 Вибір методу створення запиту

Мал. 24.7 Вибір таблиці, для якої буде складений запит

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

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

Мал. 24.8 Вікно структури запиту: 1 — список полів, 2 — бланк запиту

У тих випадках, коли вас цікавлять записи, що відповідають певній умові, наприклад, прізвища клієнтів, що зробили замовлення дорожче певної суми, застосовують запит за зразком. При створенні запиту ви даєте, як би зразок (QBE, query by example — запит за зразком ), по якому буде складена відповідь або виконані операції. Тому в більш версіях Access використовувався термін бланк запиту за зразком (QBE).

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

•  у рядку Поле (Field) поставити курсор в потрібний стовпець і двічі клацнути ім'я поля в списку. Натискувати клавішу стрілка-управо або клавішу Tab, перемістити курсор в наступний стовпець і додати нове поле в запит;

•  клацнути на кнопці розкриття списку біля правого кордону вічка в рядку Поле (Field) або натискувати клавішу F4, аби відкрити список імен полів і вибрати необхідне поле;

•  перетягнути ім'я поля мишею із списку полів у верхній частині вікна. Для введення декілька полів із списку можна утримувати клавішу Ctrl при виділенні потрібних полів в списку, а потім перетягнете виділені поля мишею. Access автоматично розподілить вибрані поля по вічках рядка

Поле. Якщо вибрати в першому рядку зірочку «*», то це означає, що відповідає мають бути представлені всі стовпці таблиці.

Для виконання сортування результатів запиту по якому-небудь полю перемістите курсор в рядок Сортування (Sort) цього поля і натискуйте клавішу F4, для відображення варіантів сортування: за збільшенням (Ascending), по убуванню (Descending) або відсутній (not sorted). Детальніше вибір умов сортування далі в цій главі.

Якщо ви в запиті використовуєте яке-небудь поле для вказівки умов відбору, але не хочете, аби воно відображувало відповідає на запит, то зніміть прапорець в цьому полі в рядку Вивід на екран (Show) .

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

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

Запуск запиту

У режимі конструктора запит можна запустити наступними способами:

•  вибрати в меню Запит (Query) команду Запуск (Run);

•  натискувати кнопку Запуск (Run) на панелі інструментів.

Результати відповіді на запит будуть представлені в таблиці. Аби перервати запуск запиту, натискуйте клавіші Ctrl+Break.

Створення таблиці за допомогою запиту

Аби створити таблицю за допомогою запиту, відкрийте раніше створений запит в режимі конструктора. Для цього у вікні бази даних клацніть значок Запити (Query) у списку Об'єкти (Objects) виберіть потрібний запит і натискуйте кнопку Конструктор (Design) на панелі інструментів вікна бази даних. Виберіть в меню Запит (Query) команду Створення таблиці (Make Table Query) або клацніть на панелі інструментів в списку кнопки, що розкривається Тип запиту (Query Type) рядок Створення таблиці. (Make Table).

У діалоговому вікні Створення таблиці. (Make Table) заповните поле ім'я таблиці (Table Name) (мал. 24.9). Положенням перемикача вкажіть, де знаходитиметься створювана таблиця: у поточній базі даних (Current Database) або в іншій базі даних (Another Database). Якщо таблиця знаходитиметься в поточній базі даних, то її можна вибрати в списку, що розкривається, якщо в іншій базі даних, то в полі ім'я файлу (File Name) введіть повне ім'я файлу бази даних у форматі: «повне ім'я файлу» «ім'я бази даних», наприклад, «C:\Data\Sales» «Paradox».

Мал. 24.9 Створення таблиці за допомогою запиту

Натискуйте кнопку OK і закрийте запит, натискує кнопку закриття вікна. На екрані відображуватиметься вікно з питанням: «Зберегти зміни макету або структури об'єкту . ?)». Натискуйте кнопку Так (Yes). У вікні із списком запитів перед ім'ям створеного запиту з'явиться знак оклику.

Подвійним клацанням миші запустите новий запит. На екрані з'явиться повідомлення, що запит на створення таблиці приведе до зміни даних таблиці, створеної раніше на основі запиту (мал. 24.10). Натискуйте кнопку До (Yes) . Поглянете список таблиць у вікні бази даних, і двічі клацніть значок таблиці, створеної за результатами запиту, аби проглянути її.

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

Вибір умов відбору записів в запиті

У Access передбачена велика кількість варіантів відбору записів з бази даних. Розберемо деякі широко поширені умови відбору записів, використовувані в запиті.

Оператори

Оператор — це символ або слово, наприклад = або Проr, вказуючі виконання операції над одним або декількома елементами. Оператори дозволяють виконувати операції над елементами формули. Access дозволяє використовувати різних типів операторів для обчислень на аркуші:

•  арифметичні оператори — служать для виконання арифметичних операцій над числами (таблиця);

•  оператори порівняння — використовуються для порівняння двох значень. Результатом порівняння може бути логічне значення: або ІСТИНА, або БРЕХНЯ;

•  текстовий оператор конкатенації Амперсанд (&) — використовується для об'єднання декількох текстових рядків в один рядок;

•  оператори заслання — застосовуються для опису заслань на діапазони вічок.

Запит записів в певному діапазоні значень може виконуватися з використанням наступних операторів порівняння: = — рівно; > — більше, ніж; < — менше, ніж;

<> — не рівно;

>= — більше або рівно;

<= — менше або рівно.

Допустимо, вас цікавлять співробітники, зарплата яких перевищує 300 рублів. У рядок Умови відбору (Criteria) введіть: >300.

Як правило, оператори порівняння використовуються в числових полях або полях дат, але вони можуть застосовуватися і в текстових полях. Наприклад, якщо ввести в текстове поле умову відбору < 'П'то будуть вибрані значення поля, що починаються після букви «П».

Оператор LIKE

Якщо ви не упевнені в написанні якого-небудь слова, то можете використовувати оператора LIKE і підстановлювальний знак. Нагадаємо, що знак питання замінює один символ, а зірочка * — групу символів. Наприклад, вираження: LIKE П?Л виконує пошук слів, що починаються з букви П. Вираження: LIKE виконує пошук слів, співав, ліг, пів і тому подібне

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

Виконання стандартних обчислень над значеннями поля

Одним з широко поширених методів аналізу табличних даних є використання наступних підсумкових функцій для полів з числовими даними:

•  Sum — обчислення суми значень поля;

•  Avg — визначення середнього значення поля;

•  Min — знаходження мінімального значення поля;

•  Мах — знаходження максимального значення поля;

•  Count — підрахунок кількості записів поля (може застосовуватися для всіх полів);

•  StDav — розрахунок стандартного відхилення поля;

•  Var — розрахунок зміни значень поля.

Виконання групових операцій

Стовпець підсумків може містити підсумкові дані для всіх записів таблиці або згрупованих за яким-небудь принципом. Наприклад, нас може цікавити максимальна або середня ціна товарів (поле Ціна) кожного типа (поле Тип), представлених в таблиці. Для використання підсумкових операторів у вказаному завданні виконаєте наступні дії:

•  у запитальній формі в рядку Вивід на екран (Show) поставте прапорці в полях: Тип і Ціна, яке використовуватиметься для обчислень;

•  виберіть в меню Вигляд (View) команду Групові операції (Totals) або jm-жмите однойменну кнопку на панелі інструментів;

•  встановите курсор в полі, над значеннями якого виконуватимуться обчислення, і виберіть у вічку Групова операція (Total) необхідну функцію;

•  у меню Запит (Query) виберіть команду Запуск (Run).

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

Мал. 24.11 Складання запиту з використанням групової операції

Розширення умов відбору

Наприклад, ви хочете взнати об'єм продажів дорогих (дорожче 2500 крб.) і дешевих (менше 500 крб.) замовлень. Введіть у вічко Умови відбору (Criteria) : >2500, у вічко або (or) <500. Умови, задані у вічку або, сприйматимуться як додаткові.

Використання обчислюваного поля

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

Наприклад, якщо кожне замовлення коштує 5 рублів і ви хочете обчислити вартість всіх замовлень, зроблених кожним покупцем, то введіть у вічко Поле вираження: Вартість:[Кількість замовлень]*5.

Будівник виразів

Для відображення вікна будівника виразів (мал. 24.12) виконаєте наступні дії:

•  перейдіть в режим конструктора запиту;

•  у рядку Умова відбору (Criteria) клацніть правою кнопкою миші стовпець, для якого необхідно задати критерії відбору, і виберіть в контекстному меню команду Побудувати (Build) або натискуйте однойменну кнопку на панелі інструментів.

Мал. 24.12 Будівник виразів

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

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

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

Аби вивести повний список операторів, виберіть теку Оператори в нижньому лівому полі і потрібний тип в середньому полі. У правому полі будуть виведені всі оператори вибраного типа. Можливе безпосереднє введення частини вираження у верхнє поле.

Для повернення в бланк запиту натискуйте кнопку ОК, Побудоване вираження буде вставлено в те поле, де розташований курсор. Імена полів при введенні в бланк запиту слід укласти в квадратні дужки