Перехресний запит - поняття, використання і створення

20 0 Новини високих технологій

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

Поняття та використання

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


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

Вирази, сортування, умови запиту

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


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

Використання майстра для створення

Перехресний запит - поняття, використання і створення
Припустимо, у невеликому магазинчику з продажу предметів інтер'єру для будинку ведеться облік ваз. Вони виготовлені з різних матеріалів і мають певні розміри. Замовник хоче купити товари тільки з одного матеріалу одного розміру в кількості 25 штук. Щоб з'ясувати, яких саме ваз достатньо на складі, створюється запит, результати якого покажуть кількість предметів, які відповідають критеріям покупця. Створення перехресного запиту для цього прикладу зручно здійснити за допомогою "Майстра запросов". Його перевага в простоті, однак це ж і є недоліком. Він не дозволяє робити складні відбори з декількох таблиць. Але для нашого випадку "Майстер запитів" підійде ідеально. На вкладці "Створення" натискаємо "Майстер запитів" і у вікні вибираємо тип "Перехресний", а потім таблицю для вибірки – "Вази".
Перехресний запит - поняття, використання і створення
Припустимо, у підсумковій таблиці ми хочемо бачити матеріал, з якого виготовлені вази, в рядках і їх розмір в стовпцях. Вибираємо це.
Перехресний запит - поняття, використання і створення
Значення в комірках являють собою кількість ваз з кожного матеріалу і кожного розміру. Вони обчислюються як сума.
Перехресний запит - поняття, використання і створення
Отримуємо результат. У ньому зазначено, що магазин може запропонувати замовнику 25 середніх ваз зі скла або фарфору.
Перехресний запит - поняття, використання і створення

Створення в Конструкторі"

Для більш складних випадків створення перехресного запиту Access можливо за допомогою "Конструктора". Візьмемо більш складний приклад магазину, який торгує предметами декору. Вази знаходяться на різних складах. На цей раз покупцеві потрібно 45 однакових ваз, і він хоче їх забрати самостійно прямо зі складу.
У базі містяться таблиці "Вази" з інформацією про матеріал і розмір предметів, "Склади" з адресами сховищ і "Товари на складах" з типом продукції та її кількістю в кожному з сховищ. У "Конструктора запитів" вибираємо всі ці таблиці. У рядках містяться дані по складах з номерами та за адресами, у стовпцях – найменування ваз, в осередках – кількість товарів.
Перехресний запит - поняття, використання і створення
Судячи по таблиці результатів, ми можемо запропонувати покупцеві 3 склади, в яких є необхідна кількість однакових ваз, – № 2 4 та "Центральний". У перших двох є тільки одне найменування товару в потрібній кількості, в останньому – три.
Перехресний запит - поняття, використання і створення

Використання SQL

Створити перехресний запит до БД Access також можна на мові програмування SQL. Для цього використовується операція TRANSFORM. В тілі запиту розташована функція, яку ми застосовуємо для обробки значень у клітинках результуючої таблиці. У нашому випадку це сума чисел у полі "Кількість" таблиці "Товари на складах".
Дані можна отримати звичайним запитом на вибірку SELECT. У відбираються полях зазначаються ті стовпці, які в запиті складають заголовки рядків. У нашому прикладі це поля "Склад" та "Адреса" з таблиці "Склади". Для додавання інших таблиць використовується інструкція INNER JOIN із зазначенням полів для зв'язки. І, нарешті, команда PIVOT включає поле, значення якого в результаті являють собою заголовки стовпців – найменування ваз. Запит SQL набуває вигляду: TRANSFORM Sum([Товары по складам].Кількість) AS[Sum-Количество]

SELECT Склади.Склад, Склади.Адреса

FROM Склади INNER JOIN (Вази INNER JOIN[Товары по складам]ON Вази.Код =[Товары по складам].Найменування) ON Склади.Код =[Товары по складам].Склад

GROUP BY Склади.Склад, Склади.Адреса

PIVOT Вази.Ваза; Як ми переконалися, перехресні запити в базі Access – зручна функція, використовувати яку нескладно. Вона допомагає спростити подання даних і полегшити роботу над зведеними звітами.