Створення запитів на вибірку даних в Microsoft Access

План

Вступ

1. Створення запитів на вібірку даних

1.1 Вибір даних з однієї таблиці

1.2 Встановлення властивостей полів

1.3 Введення умов відбору

1.4 Умови відбору дат та часу

1.5 Багатотабличні запити

1.6 Створення запиту на основі іншого запиту

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

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

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

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

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

4. Запити на зміну даних

4.1 Запити на поновлення

4.2 Запити на додавання

4.3 Помилки при використанні запитів на зміну Висновки Використана література

Вступ

Програмне забезпечення для роботи з базами даних використовується на персональних комп’ютерах досить давно. Взагалі, база даних — це набір записів і файлів, які організовані спеціальним чином. В комп’ютері, наприклад, можна зберігати прізвища і адреси друзів або клієнтів. Можливо, зберігати всі свої листи, і вони згруповані по адресатам, або набір файлів з даними по фінансовим справам: отримані або виставлені рахунки, витрати по чековій книжці або балансам. Один з типів баз даних — це документи, які набрані за допомогою текстових редакторів і згруповані за темами. Другий тип — файли електронних таблиць, які об'єднані в групи по характеру їх використання. Щоб керувати даними, які розкидані по сотням таблиць і файлів використовуються системи керування базами даних (СКБД). Microsoft Access 97 саме є такою системою.

Майже всі сучасні системи побудовані на реляційній моделі керування базами даних. Назва «реляційна» пов’язана з тим, що кожний запис в такій базі даних має інформацію, яка відноситься тільки до одного конкретного об'єкту. В реляційній СКБД всі дані представлені в вигляді таблиць. Інформація про об'єкти визначеного виду представляється в табличному вигляді - в стовпчиках таблиці містяться різні характеристики об'єктів — атрибути (наприклад, адреси клієнтів), а рядки призначені для опису величин всіх атрибутів окремого об'єкта (наприклад, дані про конкретного клієнта). В випадку, коли використовуються функції СКБД для вибору інформації з однієї або декількох таблиць (виконується запит, що є темою даної дипломної роботи), результат представляється у вигляді таблиці. Більше того, можна виконати запит із використанням результатів іншого запиту. Можна об'єднати інформацію з декількох таблиць або запитів.

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

В базі даних Access основними об'єктами є таблиці, запити, форми, звіти, макроси і модулі. Таблиця — об'єкт, який використовується для збереження даних. Таблиця складається з полів (стовпчиків), в яких зберігаються різні дані, і записів (рядків). В записи зібрана вся інформація про деякий об'єкт. Запит — об'єкт, який дозволяє користувачу отримати потрібні дані з одної або декількох таблиць. Для створення запиту можна використовувати бланк QBE (запит по зразку) або інструкцію SQL. Можна створювати запити на вибірку, поновлення, видалення або додавання даних. За допомогою запитів також можна створювати нові таблиці, використовуючи дані з одної або декількох існуючих таблиць. Форма — об'єкт, призначений в основному для вводу даних, відображення їх на екрані або керування роботою додатку. Звіт — об'єкт, призначений для створення документа, який в подальшому може бути роздрукований або включений в документ іншого додатку.

1. Створення запитів на вибірку даних

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

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

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

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

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

Щоб відкрити вікно нового запиту в режимі конструктора, і вікні бази даних потрібно перейти на вкладку Запросі натиснути кнопку Создать, яка міститься з правого боку від списку запитів. Access відкриє вікно діалогу Новый запрос. В нас є вибір: створити запит самостійно в режимі конструктора або скористатися допомогою майстра для створення одного з декількох типів запитів. Щоб відкрити існуючий запит в режимі конструктора, треба виділити його ім`я на вкладці Запросі натиснути кнопку Конструктор. Запит відкривається в режимі Конструктор. В верхній частині вікна запиту знаходяться списки полів (назви стовпчиків таблиці), в нижній частині - бланк запиту.

1.1 Вибір даних з однієї таблиці

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

Найкраще за все створити запит на основі одної таблиці так: відкрити вікно бази даних, вибрати потрібну таблицю, розкрити список кнопки Новыйобъектна панелі інструментів і вибрати пункт Новый запрос і натиснути кнопку ОК (якщо рядок Имя таблицыне виводиться в бланку запиту, слід вибрати команду Вид/Имена таблиц). Відкривається вікно конструктора, воно розділене на дві частини (мал. 1). В верхній частині знаходяться списки полів таблиць або запитів, на підставі яких створюється новий запит. В нижній розміщений бланк QBE (Query By Example — запит по зразку), в якому виконується вся робота по створенню нового запиту. Кожний стовпчик бланку представляє одне поле, яке використовується в запиті. Поле може просто належати одній з таблиць, бути обчислюваним (його значення розраховується на основі одного або декількох полів таблиці), або підсумковим, яке використовує одну із вбудованих функцій Microsoft Access.

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

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

В зв’язку з тим, що була виконана команда Вид/Имена таблиц, в даному рядку бланка запиту Access виведе ім'я таблиці, з якої вибране поле. В третьому рядку бланка можна задати, чи потрібно виконувати сортування по вибраному або обчислюваному полю.

Прапорці в бланкуВывод на екранвідповідають за вивід на екран полів в наборі записів. По замовчуванню Access виводить на екран всі поля, які містить бланк запиту. Але деякі поля включаються в запит тільки для відбору потрібних записів, а виводити їх на екран зовсім не обов`язково. Щоб виключити таке поле з набору записів, треба зняти його прапорець в рядку Вывод на екран.

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

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

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

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

1.2 Встановлення властивостей полів

В загальному випадку поля, які виводяться в наборі записів запиту, наслідують властивості для відповідних полів таблиці. Можна задати інші значення наступних властивостей: Описание(інформація, яка виводиться в рядку стану вікна запита в режимі таблиці, коли поле стає поточним), Формат поля(представлення даних на екрані), Число десятичных знаков(для числових даних), Маска вводаі Подпись(заголовок стовпчика).

Щоб задати властивості деякого поля, потрібно клацнути на любій чарунці відповідного стовпчика в бланку запита і натиснути кнопку Свойствана панелі інструментів або вибрати команду Вид/Свойства.

1.3 Введення умов відбору

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

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

Коли вводяться умови відбору для декількох полів, то всі вирази в рядку Условие отбора або в рядку илиповинні приймати значення Істина для любого запису, який включається в набір записів запиту. Це означає, що Access виконує логічну операцію ANDнад умовами відбору, які знаходяться в одному рядку. Щоб результат операції ANDмав значення Істина, умови повинні бути істинними; тільки в цьому випадку запис відбирається запитом. Наприклад, ми вибираємо записи з таблиці, в якій знаходяться дані про робітників. Умовою відбору обрано поле Загальний стаж і його значіння:

>10 AND <20

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

Коли задаються для деякого поля декілька умов відбору, які з'єднані логічним оператором OR, то для того, щоб запис був відібраний запитом, істинним повинна бути хоча б одна з них. Є два способи задати декілька пов’язаних оператором OR умов для одного поля. Можна ввести всі умови в одну чарунку рядка Условие отбора і з'єднати їх оператором OR. Наприклад, з таблиці про поставників продукції запис в чарунці Условие отбора:

«Київ» OR «Вінниця», означає, що будуть відібрані всі записи про поставників, що знаходяться в містах Київ і Вінниця.

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

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

BETWEEN. Визначає діапазон значень. Between 10 означає те саме, що і вираз And 20 >=10 And <=20.

IN. Задає список значень, що використовується для порівняння. Вираз IN («Київ»,"Вінниця") означає те саме, що і вираз «Київ» OR «Вінниця».

LIKE. Оператор, корисний для пошуку зразків в текстових полях. В зразок пошуку можна включити символи шаблона, «?» заміняє любий символ в даній позиції, а «*» означає любу кількість символів в даній позиції. Символ «#» вказує, що в даній позиції повинна бути цифра.

1.4 Умови відбору для дат і часу

Microsoft Access зберігає значіння дат і часу як числа з плаваючою комою і з подвійною точністю. Значіння з лівого боку від десяткової коми відповідає даті, а дробова частина числа представляє час доби.

Щоб повідомити Access про те, що вводиться дата і час, значення вміщується в символи числа (#). Наприклад, #10 Квітень 2003# і #10/04/03# визначають одну і ту саму дату.

Access дає декілька функцій, які можна використовувати при завданні умов відбору для дат і часу:

Day(дата). Повертає значення дня місяця в діапазоні від 1 до 31.

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

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

Weekday(дата). Повертає значення чисел від 1 (Неділя) до 7 (Субота), які відповідають дням тижня.

Hour(дата). Повертає ціле число від 0 до 23, які представляють значення часу.

DatePart(інтервал, дата). Повертає номер кварталу або номер тижня в залежності від того, який код інтервалу задається («q» — для визначення кварталу, «ww» — для визначення порядкового номера тижня в році).

Date(). Повертає поточну системну дату.

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

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

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

Для кожного параметра запиту можна вказати тип даних. Access використовує цю інформацію для перевірки введеного значення. Наприклад, якщо визначено параметр як числовий, Access відкине літерні символи в значенні параметра. З мовчазної згоди Access надає параметрам запиту текстовий тип даних. Якщо потрібно змінити тип даних, треба вибрати команду Запрос/Параметры, і Access виведе на екран вікно діалогу Параметры запроса. В цьому вікні діалогу вводиться ім'я кожного параметра, тип якого ми хочемо визначити, в стовпчик Параметрв такому вигляді, в якому воно було вказане в бланку запиту, але без квадратних дужок. В стовпчику Тип данныхтреба встановити потрібний тип даних, який вибирається зі списку, що розкривається. Після визначення всіх параметрів натискаємо кнопку ОК.

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

1.5 Багатотабличні запити

Розглянувши можливості запитів, які основані на одній таблиці, на базі отриманих знань легко організувати перегляд об'єднаних даних з декількох пов’язаних таблиць. Здатність запитів відбирати дані з декількох таблиць особливо корисна при створенні форм і звітів.

Розглянемо приклад, в якому об'єднується інформація з двох таблиць. В вікні бази даних треба перейти на вкладку Запросыі натиснути кнопку Создать. В вікні діалогу Новый запросвибрати Конструкторі натиснути кнопку ОК. Access відкриє вікно нового запиту в режимі конструктора і виведе на екран вікно діалогу Добавление таблицы. Вікно діалогу дозволяє вибрати таблиці і запити, які будуть базовими для нового запиту. Вибираються дві таблиці і закривається вікно.

Якщо зв’язок між базовими таблицями був раніше визначений, то верхня частина вікна запиту в режимі конструктора буде виглядати так, як показано на мал. 2. Access пов’язує використовувані в запиті таблиці на основі інформації про зв’язок, яка задана при їх створенні.

Мал. 2. Конструктор запиту

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

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

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

Одним з найцікавіших аспектів багатотабличних запитів є можливість зміни даних вихідних таблиць прямо в наборі записів.

1.6 Створення запиту на основі іншого запиту

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

Використання майстра запитів

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

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

3. В діалоговому вікні (мал. 4), що з’явилося, вказати ім'я таблиці або запита, на якому буде збудований новий запит. Потім вибрати поля, з яких повинні бути відновлені дані.

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

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

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

Можна створити ярлик для відкриття об'єкта бази даних, яка знаходиться або на комп’ютері користувача, або на файловому сервері мережі або в директорії для спільного доступу. В Microsoft Windows можна створити ярлик, перемістивши за допомогою миші об'єкт з вікна бази даних в робочий стіл або папку. Інший спосіб — клацнути правою кнопкою миші потрібний об'єкт (запит, наприклад) і вибрати команду Создать ярлык.Щоб створити ярлик не на робочому столі, треба ввести новий шлях в поле Размещение.

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

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

Для збереження копії об'єкта бази даних з новим ім'ям або в іншому файлі, при умові що об'єкт відкритий або виділений, потрібно вибрати команду Сохранить как/Экспортв меню Файл. Щоб зберегти об'єкт в поточній базі даних, треба вибрати параметр В текущей базе данныхв діалоговому вікні Сохранение объекта, ввести ім'я об'єкта і натиснути ОК.

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

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

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

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

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

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

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

— При створенні запиту не додавати лишні поля в запит. Зняти прапорець Вывод на экран для полів, зміст яких не виводиться в запиті

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

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

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

Обчислюване поле може також містити результат конкатенції (об'єднання) значінь текстових полів або рядкових (текстових) констант. Щоб створити рядкову константу, потрібно заключити текст в подвійні або одинарні лапки. В якості операції конкатенції використовується символ «&». Наприклад, можна створити поле, яке буде містити результат об'єднання поля Прізвище і поля Ім'я: [Прізвище]& [Ім'я].

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

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

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

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

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

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

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

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

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

Якщо після клацання в чарунці рядка Поле(де ми маємо ввести вираз) натиснути SHIFT+F2, то відкриється вікно Область ввода, в якому зручно задавати довгі вирази.

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

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

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

Наприклад, в нас є таблиця з даними про учнів 8-А класу, складається вона з таких полів: Прізвище, Ім'я, По батькові, Дата народження, Фізика, Хімія, Алгебра, Геометрія, Історія, Українська література, Українська мова. Формат останніх семи полів числовий (оцінки за 12-ти бальною шкалою). Необхідно обчислити середню оцінку за навчання для кожного учня.

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

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

Выражение1:([8Аклас]![Фізика]+[8Аклас]![Хімія]+[8Аклас]![Алгебра]+ [8Аклас]![Геометрія]+[8Аклас]![Історія]+[8Аклас]![Українська література]+[8Аклас]![Українська мова])/7

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

Якщо при побудові виразу потрібно додати стандартну вбудовану функцію, потрібно в лівому нижньому списку вибрати папку Функции/Встроенные функции. В результаті в правому списку з’являться назви функцій.

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

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

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

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

Іноді цікаві не окремі записи таблиці, а підсумкові значіння по групам даних. Наприклад, нам потрібно знати середню оцінку з Фізики для 8-А класу (з попереднього прикладу). Або сумарну кількість балів з Хімії, набрану класом, в порівнянні з паралельним класом. Відповіді на такі питання дає підсумковий запит. Для обчислення підсумкових значінь потрібно натиснути кнопку Групповые операциина панелі інструментів конструктора запитів, щоб у бланку QBE з’явився рядок Групповая операция.

Коли натиснути кнопку Групповые операции на панелі інструменті, Access використовує установку Группировкав рядку Групповая операция для любого поля, який занесений в бланк запиту. Тепер записи по кожному полю групуються, але підсумок не підводиться. Для отримання підсумків потрібно замінити установку Группировка в рядку Групповая операция на конкретну підсумкову функцію.

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

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

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

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

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

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

StDev.Вираховує стандартне відхилення всіх значінь даного поля в кожній групі. Ця функція використовується тільки для числових або грошових полів.

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

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

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

Якщо не потрібно включати в групи підсумкового запиту деякі записи, можна додати в бланк запиту одне або декілька полів для фільтра. Для створення фільтра треба в рядку Групповая операция вибрати установку Условие, зняти прапорець Вывод на экрандля цього поля і ввести умови відбору.

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

запит база дані access реляційний

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

Для побудови перехресного запиту потрібно виділити ім'я потрібної таблиці в вікні бази даних і вибрати Новый запрос зі списку кнопки Новый объектна панелі інструментів. В вікні діалогу вибрати Конструктор, а потім у вікні конструктора вибрати команду Запрос/Перекрестный. Access додасть в бланк запиту рядок Перекрестная таблица. В цьому рядку для кожного поля перехресного запиту може бути вибрана одна з чотирьох установок: Заголовки строк, Заголовки столбцов, Значение (яке виводиться в чарунках перехресної таблиці) і Не отображается. Для перехресного запиту потрібно визначити у крайньому випадку одне поле в якості заголовків рядків, одне для заголовків стовпчиків і одне поле значень. Кожне поле, яке є заголовком стовпчиків, повинно мати в рядку Групповая операция установку Группировка. Для поля, яке використовується в якості заголовків рядків, в рядку Групповая операция повинна бути встановлена операція Группировка, вибрана одна з підсумкових функцій або введений вираз, який містить підсумкову функцію. Для поля з установкою Значениевибрати одну з підсумкових функцій або ввести вираз, який використовує підсумкову функцію.

Як і в інших типах підсумкових запитів, для відбору даних, які включаються у набір записів, можна використати додаткові поля. Для них необхідно вибрати установку Условиев рядку Групповая операция і Не отображается в рядку Перекрестная таблица, а потім ввести умову відбору. Умову відбору можна також задати для любого поля, яке використовується в якості заголовків стовпчиків, а дані можна сортувати по любим полям (з мовчазної згоди Access сортує заголовки стовпчиків за зростанням).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В залежності від складності запиту Access може знадобитися декілька секунд (хвилин), щоб знайти всі рядки і почати вивід інформації на екран. Якщо користувача цікавлять тільки «перші» або «верхні» рядки набору записів, треба скористатися властивістю Набор значений для виводу інформації на екран, як тільки Access знайде перші n або перші % рядків. Якщо ввести ціле значення, Access виведе на екран результат відразу, щойно знайде задане число рядків. При введенні десяткового значення менше 1 Access почне вивід даних після того, як знайде приблизно такий відсоток рядків. Слід врахувати, що при виконанні сортування Access повинен спочатку відібрати всі записи, і тільки відсортувавши їх, він може визначити перші n рядків. В цьому випадку властивість Набор значений не прискорить вивід інформації на екран.

При виконанні запиту часто виникають ситуації, під час яких Microsoft Access знаходить рядки, що повторюються. З мовчазної згоди він включає в результуючий набір тільки унікальні записи. Це означає, що ідентифікатор кожного рядка (первинний ключ таблиці для запиту з єдиною таблицею або сполучення первинних ключів для багатотабличного запиту) унікальний. Якщо користувач не просить Access повертати унікальні значення, в набір записів запиту будуть включені тільки відмінні один від одного рядки. Якщо є потреба бачити всі дані (враховуючи рядки-дублікати), необхідно встановити обидві властивості Уникальные значенияі Уникальные записи— в значення Нет. Користувач не зможе змінювати поля в наборі записів запиту, якщо для властивості Уникальные записивстановлене значення Нет.

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

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

4. Запити на зміну даних

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

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

4.1 Запит на поновлення

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

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

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

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

Щоб повторити запит на зміну, потрібно вибрати його в вікні бази даних і натиснути кнопку Открыть. Після запуску запита на зміну з вікна бази даних Access виводить на екран вікно діалогу, яке запитує підтвердження для його виконання. В вікні підтвердження натиснути кнопку Да, щоб завершити поновлення даних. Якщо треба відмінити вивід на екран вікна підтвердження, вибирається команда Вид/Настройкаі на вкладці Правка/поисквікна діалогу Параметрыв секції Подтверджениезнімається прапорець Запросов на изменение.

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

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

Створення нової таблиці

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

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

Припустимо, існує запит на вибірку. Щоб перетворити запит на вибірку в запит на створення таблиці, треба вибрати команду Запрос/Созданиетаблицы. Access виведе на екран вікно діалогу Создание таблицы. Користувач вводить ім'я для підсумкової таблиці і тисне кнопку ОК, щоб закрити вікно діалогу.

Закінчивши з установками в запиті на створення таблиці, можна виконати його за допомогою команди Запрос/Запускабо кнопки Запускна панелі інструментів. Access створює записи, розміщає їх в нову таблицю і інформує користувача про кількість рядків, які вставляються в таблицю, на екран виводиться вікно діалогу для підтвердження створення таблиці. Натиснути кнопку Да, щоб створити нову таблицю і помістити до неї записи. Після цього потрібно переключитися в вікно бази даних і клацнути по вкладці Таблицы, щоб відкрити список таблиць. Вибирається в ньому тільки що створену таблицю і відкривається вона в режимі таблиці. Можна оглянути отримані дані. В режимі конструктора можна змінити імена полів або формати.

4.2 Запит на додавання

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

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

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

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

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

4.3 Помилки при виконанні запитів на зміну

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

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

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

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

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

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

Видалення групи записів

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

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

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

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

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

Висновки

В даній курсовій роботі була розглянута система керування базою даних (СКБД) Access, а власне організація запитів, різних за формою і змістом.

Було доведено, що запити — гнучкий і зручний інструмент обробки даних. Конструктор запитів в Access дозволяє створити практично любий запит. Конструктор дозволяє проглядати запит трьома різними способами — безпосередньо конструктор, режим SQL (в роботі не розглядався) і набір даних (таблиця).

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

В частині 1 було розглянуто створення запитів на вибірку даних і їх поновлення. Звичайно, для побудови запиту використовується конструктор, в якому розміщений бланк запиту QBE (Query By Example — запит по зразку). Саме тут виконується вся робота по створенню нового запиту. В кожний рядок бланка записуються різні умови: вибір полів, сортування, умови відбору.

Умови відбору даних різноманітні: за конкретним значенням поля, за допомогою спеціальних операторів OR, AND, BETWEEN, IN, LIKE. Також розглянуті умови відбору для дати і часу, використання параметрів запиту з відповідним типом даних, згаданий спосіб створення запиту на основі іншого запиту.

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

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

В частині 2 розглянута можливість обчислення будь-яких полів таблиці і створення нового поля таблиці на основі обчислюваного виразу. Обчислювані вирази будуються за допомогою арифметичних операцій над полями таблиці. Для побудови складних виразів використовується