Работа с запросами в MS Access 97

Користувач може відкрити в режимі конструктора різні запити: запит на

вибірку, перехресний запит і запит на зміну. Запит на вибірку і перехресний

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

Можна створити ярлик для відкриття об’єкта бази даних, яка знаходиться

або на комп’ютері користувача, або на файловому сервері мережі або в

директорії для спільного доступу. В Microsoft Windows можна створити ярлик,

перемістивши за допомогою миші об’єкт з вікна бази даних в робочий стіл або

папку. Інший спосіб – клацнути правою кнопкою миші потрібний об’єкт (запит,

наприклад) і вибрати команду Создать ярлык. Щоб створити ярлик не на

робочому столі, треба ввести новий шлях в поле Размещение.

Для копіювання вибирається об’єкт і натискається кнопка Копировать на

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

закривається поточна база і відкривається та, в яку потрібно вставити

об’єкт. При відкритому вікні бази даних натиснути кнопку Вставить на панелі

інструментів.

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

панелі інструментів.

Для збереження копії об’єкта бази даних з новим ім’ям або в іншому

файлі, при умові що об’єкт відкритий або виділений, потрібно вибрати

команду Сохранить как/Экспорт в меню Файл. Щоб зберегти об’єкт в поточній

базі даних, треба вибрати параметр В текущей базе данных в діалоговому

вікні Сохранение объекта, ввести ім’я об’єкта і натиснути ОК.

Для перейменування запита потрібно впевнитися, що об’єкт бази даних

закритий. Далі в вікні бази даних вибрати вкладку Запросы, яка містить

потрібний об’єкт. Натиснути кнопку миші на імені об’єкта, а потім знову

натиснути кнопку миші, щоб змінити ім’я, ввести нове ім’я.

Для видалення об’єкта виділити його і натиснути кнопку Delete.

Оптимізація запитів

Існує ряд способів прискорення виконання запитів:

- Стискати бази даних

- Індексувати поля

- Вибирати типи даних мінімального розміру

- При створенні запиту не додавати лишні поля в запит. Зняти прапорець

Вывод на экран для полів, зміст яких не виводиться в запиті

- Використовувати для умов відбору вирази, які дозволяють оптимізувати

запит

2. ВИКОНАННЯ ОБРАХУНКІВ В ЗАПИТАХ

Можна виконувати обчислення з любими полями таблиці і зробити

обчислюваний вираз новим полем в наборі записів. Для цього можна

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

можуть містити дані, які отримані за допомогою арифметичних операцій над

полями таблиці.

Обчислюване поле може також містити результат конкатенції (об’єднання)

значінь текстових полів або рядкових (текстових) констант. Щоб створити

рядкову константу, потрібно заключити текст в подвійні або одинарні лапки.

В якості операції конкатенції використовується символ “&”. Наприклад, можна

створити поле, яке буде містити результат об’єднання поля Прізвище і поля

Ім’я: [Прізвище]& [Ім’я].

В виразах можна використовувати наступні оператори:

+ Додає два арифметичних вирази.

- Віднімає від першого арифметичного виразу другий.

* Перемножує два арифметичних вирази.

/ Ділить перший арифметичний вираз на другий вираз.

\ Заокруглює два арифметичних вирази до цілих значінь і ділить

перше на друге. Результат округлюється до цілого.

^ Вираховує степінь першого арифметичного виразу,

степінь задається другим арифметичним виразом.

MOD Округлює обидва арифметичних вирази до цілих значінь, ділить

перше на друге і повертає залишок.

& Створює текстовий рядок, як результат приєднання

другого рядка в кінець першого.

Якщо після клацання в чарунці рядка Поле (де ми маємо ввести вираз)

натиснути SHIFT+F2, то відкриється вікно Область ввода, в якому зручно

задавати довгі вирази.

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

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

Построитель выражений. Для того щоб запустити її, потрібно клацнути по

пустому полю в бланку запита і натиснути кнопку Построить на панелі

інструментів. Microsoft Access відкриє вікно Построитель выражений, яке

показане на мал.5

Мал. 5. Створювач виразів

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

призначена для створення виразів. Можна самому ввести вираз, але легше

використати різні кнопки операторів, які містяться прямо під областю

введення. В нижній частині вікна знаходяться три списки, призначені для

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

Наприклад, в нас є таблиця з даними про учнів 8-А класу, складається

вона з таких полів: Прізвище, Ім'я, По батькові, Дата народження, Фізика,

Хімія, Алгебра, Геометрія, Історія, Українська література, Українська мова.

Формат останніх семи полів числовий (оцінки за 12-ти бальною шкалою).

Необхідно обчислити середню оцінку за навчання для кожного учня.

В бланку запита вибирається вільне поле і запускається Построитель

выражений. В нижній частині вікна, що з’явилося, в правому полі відшукуємо

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

списку з’являються назви полів. Саме з цього списку потрібно обрати

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

Середня оцінка=Сума оцінок по всім предметам/Кількість предметів

У верхньому вікні Построителя выражений ця формула набуде такого

вигляду:

Выражение1:([8Аклас]![Фізика]+[8Аклас]![Хімія]+[8Аклас]![Алгебра]+

[8Аклас]![Геометрія]+[8Аклас]![Історія]+ [8Аклас]![Українська

література]+[8Аклас]![Українська мова])/7

Щоб не вводити назви полів вручну, достатньо двічі клацати на них в

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

області введення.

Якщо при побудові виразу потрібно додати стандартну вбудовану функцію,

потрібно в лівому нижньому списку вибрати папку Функции/Встроенные функции.

В результаті в правому списку з’являться назви функцій.

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

Для цього акуратно замість слова Выражение1 вводимо власну назву (більш

змістовну).

Після всіх виконаних попередніх дій потрібно перейти в режим таблиці і

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

в режим конструктора і виправити помилки.

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

клацнути по рядку Сортировка в стовпчику того поля, по значенням якого

потрібно відсортувати записи, і вибрати По возрастанию або По убыванию.

Підсумкові запити

Іноді цікаві не окремі записи таблиці, а підсумкові значіння по групам

даних. Наприклад, нам потрібно знати середню оцінку з Фізики для 8-А класу

(з попереднього прикладу). Або сумарну кількість балів з Хімії, набрану

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

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

кнопку Групповые операции на панелі інструментів конструктора запитів, щоб

у бланку QBE з’явився рядок Групповая операция.

Коли натиснути кнопку Групповые операции на панелі інструменті, Access

використовує установку Группировка в рядку Групповая операция для любого

поля, який занесений в бланк запиту. Тепер записи по кожному полю

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

замінити установку Группировка в рядку Групповая операция на конкретну

підсумкову функцію.

Access дає дев’ять функцій, які забезпечують виконання групових

операцій. Можна ввести ім’я функції з клавіатури в рядку Групповая операция

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

підсумкові функції Access:

Sum Вираховує суму всіх значень заданого поля в кожній групі.

Використовується тільки для числових та грошових полів.

Avg Вираховує середнє арифметичне всіх значень даного поля в кожній

групі. Використовується тільки для числових та грошових полів.

Min Повертає найменше значіння, яке знайдене в цьому полі всередині

кожної групи. Для числових полів повертається найменше значення.

Для текстових полів - найменше з символьних значень

незалежно від регістру.

Max Повертає найбільше значіння, яке знайдене в цьому полі всередині

кожної групи. Для числових полів повертається найбільше

значення. Для текстових полів - найбільше з символьних

значень незалежно від регістру.

Count.Повертає число записів, в яких значення даного поля відмінні від

Null (пусто).

StDev.Вираховує стандартне відхилення всіх значінь даного поля

в кожній групі. Ця функція використовується тільки для

числових або грошових полів.

Var Вираховує дисперсію значінь даного поля в кожній групі. Ця

функція використовується тільки для числових або грошових

полів.

First Повертає перше значення цього поля в групі.

Last Повертає останнє значення цього поля в групі.

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

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

створення фільтра треба в рядку Групповая операция вибрати установку

Условие, зняти прапорець Вывод на экран для цього поля і ввести умови

відбору.

3. ПЕРЕХРЕСНІ ЗАПИТИ

Access підтримує особливий тип підсумкових запитів, який називається

перехресними запитами. Вони дозволяють побачити обчислювані значення в

вигляді перехресної таблиці, яка нагадує електронну.

Для побудови перехресного запиту потрібно виділити ім’я потрібної

таблиці в вікні бази даних і вибрати Новый запрос зі списку кнопки Новый

объект на панелі інструментів. В вікні діалогу вибрати Конструктор, а потім

у вікні конструктора вибрати команду Запрос/Перекрестный. Access додасть в

бланк запиту рядок Перекрестная таблица. В цьому рядку для кожного поля

перехресного запиту може бути вибрана одна з чотирьох установок: Заголовки

строк, Заголовки столбцов, Значение (яке виводиться в чарунках перехресної

таблиці) і Не отображается. Для перехресного запиту потрібно визначити у

крайньому випадку одне поле в якості заголовків рядків, одне для заголовків

стовпчиків і одне поле значень. Кожне поле, яке є заголовком стовпчиків,

повинно мати в рядку Групповая операция установку Группировка. Для поля,

яке використовується в якості заголовків рядків, в рядку Групповая операция

повинна бути встановлена операція Группировка, вибрана одна з підсумкових

функцій або введений вираз, який містить підсумкову функцію. Для поля з

установкою Значение вибрати одну з підсумкових функцій або ввести вираз,

який використовує підсумкову функцію.

Як і в інших типах підсумкових запитів, для відбору даних, які

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

необхідно вибрати установку Условие в рядку Групповая операция і Не

отображается в рядку Перекрестная таблица, а потім ввести умову відбору.

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

якості заголовків стовпчиків, а дані можна сортувати по любим полям (з

мовчазної згоди Access сортує заголовки стовпчиків за зростанням).

Можна визначити порядок стовпчиків в перехресній таблиці,

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

місці верхньої частини (ззовні списків полів) вікна запиту в режимі

конструктора і натиснути кнопку Свойства на панелі інструментів. На екрані

з’явиться вікно властивостей запиту.

Щоб задати порядок виводу стовпчиків в перехресній таблиці, потрібно

ввести заголовки так, як вони виглядають в відформатованому вигляді, і в

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

що включені всі заголовки стовпчиків, які з’являються в підсумковій

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

введене його ім’я, то він не з’явиться в перехресній таблиці.

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

Набір записів, які створює Access при виконанні запитів, виглядає і

веде себе майже як реальна таблиця, яка містить дані. Дійсно, в багатьох

випадках можна вставляти рядки, видаляти їх і поновлювати дані в наборі

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

таблиці запиту.

Але в деяких випадках Access не розуміє, які зміни необхідно виконати.

Розглянемо для прикладу обчислюване поле. Якщо користувач спробує змінити

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

полів, Access не знає, яке з цих полів - він хоче змінити. В той же час

можна змінити значення любого з цих полій і відразу отримати результат у

підсумковому полі.

Access не дозволяє виконати зміни, які діють на декілька рядків базової

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

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

установку Avg або Sum, тому що результат у цьому випадку визначається

значеннями з багатьох записів.

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

Access дозволяє змінювати значення любих полів таблиці, яка знаходиться на

боці відношення “багатьох”, але не дозволяє поновлювати ключові поля

таблиці з боку “один”. Наприклад, один склад може мати багато товарів. В

наборі записів, який є результатом об’єднання таблиць Склади і Товари,

можна поновити любе поле, яке належить таблиці Товари, але не можна змінити

значення полів, які створюють первинний ключ таблиці Склади. Access

дозволяє змінити інші поля цієї таблиці.

Якщо не бути уважним, можливість поновлювати поля з боку “один” може

призвести до небажаних результатів.

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

1. В вікні бази даних перейти на вкладку Запросы і натиснути кнопку

Создать.

2. В діалоговому вікні Новый запрос вибрати майстра Перекрестный запрос.

3. Натиснути кнопку ОК.

4. Користувач слідує інструкціям, які з’являються в діалогових вікнах

майстра. В останньому діалоговому вікні користувачу пропонується вибір

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

За допомогою майстра сводных таблиц дані перехресних таблиць можна

вивести на екран, не створюючи окремого запиту. За допомогою зведеної

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

різними способами можна було аналізувати дані.

Настройка властивостей запиту

Microsoft Access дозволяє встановлювати ряд властивостей запиту, які

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

потрібно клацнути любому місці верхньої частини вікна запиту в режимі

конструктора ззовні списку полів і натиснути кнопку Свойства на панелі

інструментів. На мал. 6 показано вікно властивостей запита на вибірку.

Мал. 6. Вікно бланка запиту

Звичайно, користувач вибирає тільки деякі поля, які при виконанні

запиту на вибірку необхідно включити в набір записів. Але якщо

розробляється запит для використання в формі, і потрібно, щоб всі поля з

усіх базових таблиць запиту були доступні в цій формі, встановлюється для

властивості Вывод всех полей значення Да. В більшості випадків краще

залишити для цієї властивості встановлене з мовчазної згоди значення Нет, а

змінювати цю установку тільки для визначених запитів.

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

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

Якщо користувача цікавлять тільки “перші” або “верхні” рядки набору

записів, треба скористатися властивістю Набор значений для виводу

інформації на екран, як тільки Access знайде перші n або перші % рядків.

Якщо ввести ціле значення, Access виведе на екран результат відразу, щойно

знайде задане число рядків. При введенні десяткового значення менше 1

Access почне вивід даних після того, як знайде приблизно такий відсоток

рядків. Слід врахувати, що при виконанні сортування Access повинен

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

визначити перші n рядків. В цьому випадку властивість Набор значений не

прискорить вивід інформації на екран.

При виконанні запиту часто виникають ситуації, під час яких Microsoft

Access знаходить рядки, що повторюються. З мовчазної згоди він включає в

результуючий набір тільки унікальні записи. Це означає, що ідентифікатор

кожного рядка (первинний ключ таблиці для запиту з єдиною таблицею або

сполучення первинних ключів для багатотабличного запиту) унікальний. Якщо

користувач не просить Access повертати унікальні значення, в набір записів

запиту будуть включені тільки відмінні один від одного рядки. Якщо є

потреба бачити всі дані (враховуючи рядки-дублікати), необхідно встановити

обидві властивості Уникальные значения і Уникальные записи - в значення

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

Страницы: 1, 2, 3