Запити Access в Борей

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

Розглянемо деякі запити Access, що входять у комплект бази даних Борей.

У вікні бази даних клацніть на рядку Запросмы у меню Объекты ліворуч. Відкриється список запитів бази даних Борей:

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

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

P таблиця результату включає тільки поля КодТовара й Марка;

P таблиця результату сортується по полю Марка, у порядку зростання;

P будуть виводитися тільки товари, у яких у поле ПоставкиПрекращ записано значення Нет;

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

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

SELECT [Список товаров].КодТовара, [Список товаров].Марка

FROM Товары AS [Список товаров]

WHERE ((([Список товаров].ПоставкиПрекращены)=No))

ORDER BY [Список товаров].Марка;

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

Щоб переглянути результат виконання запиту, виконаєте команду Запрос/Запуск:

Як і слід було сподіватися, у результаті представлені тільки поля Код товара й Марка. Усього виведено 69 записів. Якщо заглянути в таблицю Товары, там виявиться всього 77 записів, але частина з них виявилася відфільтрованої умовою, по якому значення поля ПоставкиПрекращ повинне бути Нет.

Як це працює – запит «Запрос Заказы»

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

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

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

Тепер переведемо запит у режим SQL. Незважаючи на деяку ускладненість оператора SELECT, запит, власне кажучи, виводить вміст полів, зазначених у списку. Головна частина запиту викладена в його останньому рядку:

SELECT Заказы.КодЗаказа, Заказы.КодКлиента, Заказы.КодСотрудника, Заказы.ДатаРазмещения, Заказы.ДатаНазначения, Заказы.ДатаИсполнения, Заказы.Доставка, Заказы.СтоимостьДоставки, Заказы.НазваниеПолучателя, Заказы.АдресПолучателя, Заказы.ГородПолучателя, Заказы.ОбластьПолучателя, Заказы.ИндексПолучателя, Заказы.СтранаПолучателя, Клиенты.Название, Клиенты.Адрес, Клиенты.Город, Клиенты.Область, Клиенты.Индекс, Клиенты.Страна

FROM Клиенты INNER JOIN Заказы ON Клиенты.КодКлиента = Заказы.КодКлиента;

У цьому рядку дається команда СКБД на вибірку даних з таблиць Сотрудники й Заказы, з’єднаних по полю КодКлиента. Наприклад, якщо в одному рядку даних, обраних з таблиці Заказы, значення поля КодКлиента дорівнює ALFKI (це клієнт на ім‘я Alfreds Futterkiste), відбувається пошук всіх таких кодів клієнта у таблиці Заказы, зі значенням у полі Заказы.КодКлиента, рівним ALFKI.

Частина результату, генерованого запитом Запрос Заказы, показана на наступному рисунку:

Поля Индекс получателя й Страна получателя належать таблиці Заказы, а поля Название, Адрес, Город – таблиці Клиенты.

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

Як це працює – «Сведения о заказах»

Джерелом записів для вкладеної форми Заказы (Подчиненная форма заказов) є запит Сведения о заказах, який надає цій формі дані про замовлені товари. Причина звертання форми до цього запиту – надане ним обчислюване поле, ОтпускнаяЦена:

Розглянемо запит у режимі SQL:

SELECT Заказано.КодЗаказа, Заказано.КодТовара, Товары.Марка, Заказано.Цена, Заказано.Количество, Заказано.Скидка, CCur(Заказано.Цена*[Количество]*(1-[Скидка])/100)*100 AS ОтпускнаяЦена

FROM Товары INNER JOIN Заказано ON Товары.КодТовара = Заказано.КодТовара

ORDER BY Заказано.КодЗаказа;

Зверніть увагу на цікаву особливість оператора SELECT:

CCur(Заказано.Цена*[Количество]*(1-[Скидка])/100)*100 AS ОтпускнаяЦена

Вираз, зазначений як параметр функції CCur, власне кажучи, обчислює вартість кожного замовленого товару, множачи значення полів Цена й Количество таблиці Заказано і віднімаючи з результату знижку (Скидка). Щоб було зрозуміліше, розглянемо це по частинам. Головна частина формули:

Цена * Количество * (1 – Скидка)

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

Очевидно, що добуток Цена * Количество утворить основну вартість замовленого товару. Його вартість із урахуванням знижки визначається множником (1-Скидка).

Чому знижка віднімається з одиниці? Тому що в таблиці Заказано вона зберігається саме як частка одиниці. Наприклад, знижка в 15 % записана як 0.15.

Функція CCur виконує перетворення отриманої суми вартості товару у грошовий формат.

Щоб уникнути помилок внаслідок округлення, обчислювана вартість перед перетворенням функцією CCur ділиться на 100, потім, після перетворення, множиться на 100.