Запит з обичслюваним полем

1.1. Обчислювальні поля

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

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

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

Наприклад : =[сума_кВт]*0.5

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

Ø арифметичні: множення (*); додавання (+); віднімання (–); ділення (/); піднесення до степеня (^);

Ø з'єднання частин тексту за допомогою знака &, наприклад:

=[Прізвище]&“ “&[Ім'я]

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

1.2. Використання Построителя выражений (Майстера побудови виразів).

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

Рис. 1. Створення запиту з обчислювальним полем

Рис.2. Вікно Построителя выражений (Майстера побудови виразів).

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

Для початку потрібно клацнути двічі в лівому списку по папці Таблицы (Таблиці), і вибрати потрібну таблицю, а потім у другій колонці саме поле і клацнути по кнопці Вставить (Вставити).

Наприклад, як показано на рис 2, спочатку була обрана таблиця РАХУНОК і поле сума_кВт, а потім – таблиця ТАРИФ і поле Ліміт.

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

Помилку при складанні виразу можна скасувати, клацнувши по кнопці Отмена (Скасування).

У результаті зроблених дій вийде вираз, наприклад як показано на рис.2, який є добутком даних про спожиту енергію (сума_кВт) та вартість одного кіловата електроенергії (ліміт).

Якщо клацнути по кнопці Оk, то отриманий результат буде перенесений у бланк QBE, тобто ми знову переходимо в вікно Запрос на выборку (Запит на вибірку) (рис.3).

Рис. 3. Запит з створеним обчислювальним полем в режимі Конструктора

В вибраному нами чистому полі з’явився вираз: Выражение1: [РАХУНОК]![сума_кВт]*[ТАРИФ]![Ліміт] (рис. 3). Якщо залишити цей вираз без змін, то в результуючій таблиці запиту з’явиться нове поле з назвою Выражение1. Змінемо назву цього поля, замінивши Выражение1 на Нараховано і закриємо вікно. Після цього відкриваємо запит на перегляд за допомогою кнопки Открыть (Відкрити). Як видно з рис. 4 у запит Відомості додано ще одне поле Нараховано.

Рис. 4. Запит з створеним обчислювальним полем в режимі Перегляду

Примітка: у Microsoft Access існує багато різних функцій для роботи з даними: математичні, статистичні тощо (рис. 5)

Рис.5. Список вбудованих функцій MS Access

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

Підсумкові запити значно відрізняються від звичайних. У них поля поділяються на 2 типи:

Ø поля, по яких здійснюється групування даних;

Ø поля, для яких проводяться обчислення.

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

У результаті чого в бланку запиту з'явиться рядок Групповая операция (Групова операція). Якщо для відповідного поля зі списку вибрати функцію Группировка (Групування) (рис 6), то при виконанні запиту записи по цьому полю групуються за значеннями в цьому полі, але підсумок не підбивається.

Груповання в підсумковому запиті виробляються тільки по одному полю. В всіх інших полях вводяться підсумкові функції.

Рис 6. Рядок Групповая операция (Групова операція) в бланку QBE

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

Основні групові функції, якими можна скористатися:

SUМ – обчислює суму всіх значень заданого поля (для числових чи грошових полів), відібраних запитом;

AVG – обчислює середнє значення в тих записах визначеного поля, що відібрані запитом (для числових чи грошових полів);

MIN – вибирає мінімальне значення в записах визначеного поля, відібраних запитом;

MAX – вибирає максимальне значення в записах визначеного поля, відібраних запитом;

COUNT – обчислює кількість записів, відібраних запитом у визначеному полі, у яких значення даного поля відмінні від нуля;

FIRST – визначає перше значення в зазначеному полі записів;

LAST – визначає останнє значення в зазначеному полі записів.

3. Запит на вибірку з параметром.

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

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

Ø створити простий запит чи використати (скопіювати) вже створений запит на вибірку;

Ø відкрити запит в режимі Конструктор (Конструктор);

Ø в рядок Условие отбора (умова відбору) при формуванні запиту в режимі Конструктор (Конструктор) ввести оператор Like[текст] (рис. 7)

Примітка: Like – команда мовою SQL; [текст] – будь який текст звернення до користувача, наприклад: “Введіть прізвище”.

Рис.7. Створення запиту з параметром

Ø закрити запит, зберігши внесені зімни.

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

Рис. 8. Відкриття запиту з параметром

Примітка: результатом виконання цього запиту буде вивід на екран всіх достних даних про споживання електроенегії абонентом на прізвище Тимців (рис. 9)

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

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

Якщо потрібно знайти записи в таблиці, знаючи лише фрагмент написання параметра, то можна використати оператор Like і метасимволи (*,?), що значно розширює можливості запитів.

4. Перехресні запити

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

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

Для перетворення підсумкового запиту-вибірки необхідно перейти в режим його Конструктора і виконати команду Запрос ® Перекрестный (Запит ® Перехресний).

Після цього в бланку запиту рядок Вивод на экран (Вивід на екран) буде замінений на рядок Перекрестная таблица (Перехресна таблиця). У цьому рядку потрібно задати, як буде використане дане поле в перехресній таблиці: у виді заголовок рядків, чи полів значень (рис. 10).

Рис.10. Вид вікна перехресного запиту

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

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

4.1 Використання Майстра для створення перехресної таблиці

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

При створенні перехресної таблиці має бути виконати наступні дії:

Ø Перейти на закладку «Запросы» («Запити») і вибрати кнопку Создать (Створити), а потім у вікні діалогу вибрати опцію Перекресный запрос (Перехресний запит) (рис. 11).

Рис.11 Вид вікна діалогу «Новый запрос» («Новий запит»)

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

На першому кроці Майстер пропонує вибрати вихідну таблицю чи запит (рис.12).

Рис.12. Перше вікно Майстра створення перехресної таблиці

На наступному кроці вам необхідно вказати поля, значення яких будуть використовуватися як заголовки рядків. Можна вказати від одного до трьох полів шляхом їхнього переносу зі списку Доступные поля (Доступні поля) в список Вибраные поля (Обрані поля). Наступним йде вікно діалогу визначення заголовків стовпців. Як заголовок стовпців може використовуватися тільки одне поле.

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

Рис.13. Вид вікна для визначення типу групування

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

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

Для завершення процесу створення необхідно натиснути кнопку Готово (Готово), на екрані з'явиться побудована перехресна таблиця.

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

1.1. Поняття запитів-дій

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

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

створення таблиці , обновлення , видалення , додавання .

Створення таблиці. Цей запит дозволяє зберегти набір даних, що вийшли у відповідь на запит, у виді таблиці.

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

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

Додавання. Використовується для додавання даних у наявну таблицю.

ACCESS відзначає запиту-дії спеціальними значками (рис.1).

Рис. 1. Меню Запрос (Запит) в режимі конструктора запиту для вибору типу запиту

1.2. Особливості роботи з запитами-діями

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

Для створення резервної копії таблиці необхідно перейти у вікно бази даних, вибрати таблицю, у якій відбудуться зміни, а потім скористатися командою Правка ® Копировать (Правка ® Копіювати). Потім виконати команду Правка ® Вставить (Правка ® Вставити), і в діалоговому вікні вказати ім'я для копії таблиці чи скористатись контекстним меню або відповідними кнопками на панелі інструментів.

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

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

Перш ніж клацнути по кнопці Запуск або вибравши команду меню Запрос®Запуск (Запит®Запуск), перегляньте результат у режимі таблиці, скориставшись кнопкою Вид або командою меню Вид®Режим таблицы (Вид®Режим таблиці).

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

2. Запити на створення таблиці

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

Наприклад, зведену інформацію з років необхідно зберігати протягом 5 років. Для цього створюється підсумковий запит і результат його виконання зберігається у виді окремої таблиці. Для цього в режимі Конструктора запитів потрібно вибрати команду Запрос ® Создание таблицы (Запит ® Створення таблиці) .

Access виведе на екран діалогове вікно «Создание таблицы» («Створення таблиці»), де потрібно вказати ім'я таблиці, у якій будуть збережені результати вибірки (рис.2). Натиснути кнопку ОK.

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

Після запуску запиту на виконання з’явиться діалогове вікно з коротким повідомленням про кількість перекинутих запитів (рис. 3).

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

Після натиску кнопки Да (Так) всі дані, що містилися в запиті, будуть перекинуті в новостворену таблицю. І в результаті цих дій утвориться таблиця із збірними даними, котрі містилися в раніше створених зв’язаних таблицях (рис. 4).

Рис.4. Створена таюлиця запитом-дій в режимі перегляду.

3. Запити на обновлення записів

Цей тип запитів-дій призначений для зміни значень деяких полів для всіх обраних записів. Після входу в режим Конструктор (Конструкто) запиту потрібно вибрати команду Запрос ® Обновление (Запит ® Обновлення). Після цього в бланку запиту з'явиться рядок Обновление (Обновлення). Цей рядок використовується для того, щоб задати нові значення для обраних записів (рис. 5).

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

Примітка: наприклад, збільшити значення в полі Ліміт в таблиці Тариф на 11% за березень місяць цього року.

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

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

Рис.6. Вікно повідомлення про внесення змін

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

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

4. Запит на додавання записів

За допомогою запиту на додавання записів можна скопіювати обрані записи і помістити їх в іншу таблицю. Можна також використовувати запит на додавання для перенесення даних у використовувану базу даних з іншого джерела (з якої-небудь іншої таблиці).

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

Якщо необхідно додати запис до таблиці в іншій базі даних, спочатку варто приєднати таблицю-джерело до бази, що містить цільову таблицю, за допомогою команди Файл ® Внешние данные ® Связь с таблицями… (Файл ® Зовнішні дані ® Зв'язок з таблицями…) (рис. 8).

Рис.8. Приєднання таблиці-джерела даних до бази

Для відбору записів, що додаються, потрібно скласти запит на вибірку. Потім слід виконати складений запит і оцінити результат, переключивши в режим таблиці за допомогою команди Вид ® Режим таблицы (Вид ® Режим таблиці). Після цього необхідно повернутися в режим Конструктор і активізувати команду Запрос ® Добавление (Запит®Додавання). У результаті цих дій з’явиться діалогове вікно для вибору таблиці, в якому планується додати дані (рис. 9).

Рис.9. Діалогове вікно Добавление (Додавання)

У вікні діалогу, що відкривається, потрібно в полі Имя таблицы (Ім'я таблиці) вибрати ім'я таблиці, до якої потрібно приєднати дані з результуючого набору записів запиту. Закриття діалогового вікна кнопкою ОK приводить до того, що Access 2003 додає в бланк запиту рядок Добавление (Додавання)(рис. 10).

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

У рядок Добавление (Додавання) автоматично (чи вручну) вставляються імена тих полів цільової таблиці, що збігаються з іменами полів запиту. Далі варто виконати запит. Для цього виконаєте команду Запрос ® Запуск (Запит ® Виконати) чи клацніть по кнопці Запуск (Виконати)на панелі інструментів. У спеціальному діалоговому вікні Access 2003 вкаже скільки записів буде додано до цільової таблиці, і зажадає підтвердження на виконання цієї операції (рис. 11).

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

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

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

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

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

5. Запит на видалення записів

Цей вид запитів служить для видалення групи записів бази даних, що задовольняють визначеним умовам. Звичайний запит можна перетворити в запит на видалення з допомогою команди Запрос ® Удаление (Запит ® Видалення) в режимі Конструктора запитів. При цьому в бланку запиту з'явиться рядок Удаление (Видалення), де можна ввести умову для видалення записів (рис. 12).

Рис. 12. Вид вікна запиту на видалення записів

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

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

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

– таблиця є частиною відносини один-до-багатьох

– як умову забезпечення цілісності даних використана опція Каскадное удаление связаных полей (Каскадне видалення зв'язаних полів).

Наприклад, у зв’язку із знесенням будівлі потрібно видалити відповідні записи з таблиці АБОНЕНТ. При цьому існує також таблиця РАХУНОК, що зв'язана з таблицею АБОНЕНТ, відношенням один-до-багатьох. Якщо для забезпечення цілісності даних обрана опція опція Каскадное удаление связаных полей (Каскадне видалення зв'язаних полів), у результаті видалення записів про абонентів будуть вилучені записи про їхні пільги, оплату, тобто інформація про споживання ними електроенергії!

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

Дата добавления: 2015-05-09