Вправа 10.3. Створення перехресної таблиці в режимі конструктора

В цій вправі розглянемо, як створити перехресну таблицю на основі однієї таблиці ВИВЧЕННЯ, в якій заголовками рядків будуть табельний номер викладача (ТАБН), стовпці — коди предмету (КП), а на перетині рядків і стовпців — сума годин по полю ГОДИНИ.

1. Почніть побудову запиту як завжди, наприклад, подвійним клацанням миші на рядку Створення запиту в режимі конструктора (Create query in Design view) у вікні бази даних.

2. У вікні Додавання таблиці (Show Table) включіть в схему даних запиту таблицю ВИВЧЕННЯ, що містить всі необхідні поля.

3. В бланк запиту перетягніть поля ТАБН, КП, по яких здійснюватиметься угрупування, і які використовуватимуться в рядках і стовпцях перехресної таблиці відповідно, і поле ГОДИНИ, по якому підраховуватиметься сумарне число годин для одержаних груп.

4. У вікні конструктора перетворіть запит на вибірку в перехресний запит. Для цього виберіть тип запиту Перехресний (Crosstab Query) на панелі інструментів або виконайте команду меню Запит | Перехресний (Query | Crosstab Query). В бланку запиту з'являться два додаткові рядки Групова операція і Перехресна таблиця.

5. В рядку Перехресна таблиця для поля ТАБН виберіть із списку значення Заголовки рядків, для поля КП — Заголовки стовпців, для поля ГОДИНИ — Значення.

6. В рядку Групова операція для поля ГОДИНИ виберіть із списку значення Sum, залишивши в решті рядків значення Угрупування.

Зауваження

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

7. Для включення в перехресну таблицю підсумкових значень для кожного рядка доповніть бланк запиту ще одним полем ГОДИНИ, замініть значення Угрупування на Sum, а в рядку Перехресна таблиця виберіть Заголовки рядків.

8. Виконайте запит.

Зауваження

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

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

9. Поверніться в режим конструктора, натискуючи кнопку Вид (View). Якщо ви вже закрили запит, виберіть його у вікні бази даних і натисніть кнопку Конструктор (Design).

10. Поле з прізвищем викладача розміщено в таблиці ВИКЛАДАЧ, а з найменуванням предмету НП в таблиці ПРЕДМЕТ, тому додайте в схему запиту ці таблиці. Для цього виконайте команду Додати таблицю (Show Table) і додайте таблиці. Тепер схема даних запиту включає таблиці ПРЕДМЕТ і ВИВЧЕННЯ, зв'язане по полю КП (Код предмету) відношенням один-до-багатьох і таблиці ВИКЛАДАЧ і ВИВЧЕННЯ, зв'язані тим же відношенням по полю ТАБН (табельний номер викладача).

11. Замініть в бланку запиту поле КП на полі НП таблиці ПРЕДМЕТ, а поле ТАБН на полі ПІБ таблиці ВИКЛАДАЧ. Для цього в полі КП в рядку Ім'я таблиці (Table) натисніть кнопку списку і виберіть ПРЕДМЕТ, а в рядку Поле (Field) — НП. Аналогічними діями замініть ТАБН на ПІБ.

12. Для зміни підпису поля, що містить підсумкову суму по рядках, клацніть правою кнопкою миші в зоні цього поля. В контекстному меню, виберіть пункт Властивості (Properties). У вікні Властивості поля (Field Properties) введіть Всього годин в рядок Підпис поля (Caption). Можна ввести цей підпис безпосередньо в полі перед ім'ям ГОДИНИ, відділивши підпис від імені двокрапкою.

13. Перейдіть в режим SQL. Еквівалентна перехресному запиту інструкція TRANSFORM буде записана таким чином:

TRANSFORM Sum(ВИВЧЕННЯ.ГОДИНИ) AS Sum_ГОДИНИ

SELECT ВИКЛАДАЧ.ПІБ, Sum(ВИВЧЕННЯ.ГОДИНИ) AS [Всього годин]

FROM ВИКЛАДАЧ INNER JOIN (ПРЕДМЕТ INNER JOIN ВИВЧЕННЯ

ON ПРЕДМЕТ.КП = ВИВЧЕННЯ.КП)

ON ВИКЛАДАЧ.ТАБН = ВИВЧЕННЯ.ТАБН

GROUP ВИКЛАДАЧ.ПІБ

PIVOT ПРЕДМЕТ.НП;

TRANSFORM указує, по якому полю і за допомогою якої статистичної функції розраховуються підсумкові значення перехресної таблиці (Sum(ВИВЧЕННЯ.ГОДИНИ) AS Sum_ГОДИНИ).

Вбудована інструкція select указує поле, значення якого використовуються як заголовки рядків (викладач.ПІБ). Для формування заголовків рядків може використовуватися і декілька полів. Крім того. указується поле, що обчислює підсумкові значення по рядках (Sum(ВИВЧЕННЯ.ГОДИНИ) AS [Всього годин]). Пропозиція GROUP задає угрупування по рядках.

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