Полегшення читання зведених даних за допомогою перехресного запиту

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

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

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

2. Цей параметр відображає значення поля як заголовки рядків.

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

4. Ці параметри створюють зведені значення.

Створення запиту

1. На вкладці Створення в групі Запити натисніть кнопку Конструктор запитів.

   

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

   Якщо використовується кілька джерел записів, переконайтеся, що таблиці або запити об'єднані в спільні поля. Докладні відомості про об'єднання таблиць і запитів див. в розділі Див. також .

3. Закрийте діалогове вікно Відображення таблиці.

4. На вкладці Конструктор у групі Тип запиту натисніть кнопку Перехресний.

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

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

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

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

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

   Щоб обмежити результати для поля заголовка стовпця, можна ввести вираз у рядку Умови. Проте використання виразу умови з полем заголовка стовпця не обмежує кількість стовпців, повернутих перехресним запитом. Натомість воно визначає, які стовпці містять дані. Наприклад, поле заголовка стовпця, яке має три можливі значення: червоний, зелений і синій. Якщо застосувати умову ="синій" до поля заголовка стовпця, у перехресному запиті й надалі відображатимуться стовпець для червоного та стовпець для зеленого, але лише стовпець для синього міститиме дані.

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

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

10. У сітці макета запиту в рядку підсумків для поля зведених значень виберіть агрегатну функцію для обчислення значень.

11. У рядку Перехресний для поля зведених значень виберіть Значення.

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

12. На вкладці Конструктор у групі Результати натисніть кнопку Запуск.

Указання фіксованих значень для заголовків стовпців

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

1. Відкрийте перехресний запит у режимі конструктора.

2. Якщо вікно властивостей не відображається, натисніть клавішу F4, щоб відобразити його.

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

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

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

Синтаксис SQL для перехресного запиту

Перехресний запит у режимі SQL виражається як оператор TRANSFORM. Оператор TRANSFORM має такий синтаксис:

TRANSFORM aggfunction
selectstatement
Зведене поле PIVOT [IN (значення1[; значення2[; ...]])]

Оператор TRANSFORM складається з таких частин:

Частина	Опис
агрегатна_функція	Агрегатна функція SQL, яка працює на основі вибраних даних.
оператор_select	Оператор SELECT.
зведене_поле	Поле або вираз, за допомогою якого потрібно створити заголовки стовпців у наборі результатів запиту.
значення1, значення2	Фіксовані значення, за допомогою яких створюються стовпці.

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

1. На вкладці Створення в групі Інші натисніть кнопку Макет запиту.

2. Закрийте діалогове вікно Відображення таблиці.

3. На вкладці Конструктор у групі Подання натисніть кнопку Подання й виберіть Режим SQL.

4. На вкладці Об’єкт SQL введіть або вставте наведені нижче оператори SQL.

   TRANSFORM 
   SELECT 
   FROM 
   GROUP BY 
   PIVOT 
   ;
   

5. У першому рядку після оператора TRANSFORM введіть вираз, за допомогою якого потрібно обчислювати зведені значення (наприклад, Sum([Сума])).

   Якщо як джерело записів ви використовуєте кілька таблиць або запитів, до імені кожного поля додайте ім’я таблиці або запиту (наприклад, Sum([Витрати].[Сума]))..

6. У другому рядку після оператора SELECT введіть список полів або виразів полів, які потрібно використовувати для заголовків рядків. Відокремлюйте елементи списку за допомогою ком (наприклад, [Бюджет].[Ід_відділу],[Витрати].[Тип]).

7. У третьому рядку після оператора FROM введіть список таблиць або запитів, які використовуються як джерела записів (наприклад, Бюджет, Витрати).

8. У четвертому рядку після оператора GROUP BY введіть список полів, який ви використали в реченні SELECT на кроці 6.

9. У п’ятому рядку після оператора PIVOT введіть ім’я поля або вираз, який потрібно використати для заголовків стовпців (наприклад, PIVOT [Бюджет].[Рік]).

Іноді потрібно згрупувати значення поля в діапазони, а потім використовувати ці діапазони (а не окремі значення) для заголовків рядків або стовпців. Наприклад, для заголовків стовпців використовується поле "Вік". Замість окремих стовпців для кожного віку можна використовувати стовпці, які представляють вікові групи.

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

1. Відкрийте перехресний запит у режимі конструктора.

2. У сітці макета запиту в рядку Поле клацніть правою кнопкою миші пустий стовпець, а потім виберіть пункт Масштаб у контекстному меню.

3. У вікні Масштабування введіть псевдонім поля, а потім двокрапку (:).

4. Введіть текст IIf().

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

   Наприклад, потрібно створити діапазони для поля "Вік", причому кожен діапазон має становити двадцять років. Порівняльний вираз для першого діапазону – [Вік]<21.

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

   Наприклад, після [Вік]<21 введіть кому, а потім введіть "0–20 років".

7. Введіть кому після імені діапазону (поза лапками), а потім виконайте одну з наведених нижче дій.

   • Щоб створити інший діапазон, введіть текст IIf() і повторіть кроки 5, 6 і 7.

   • Для останнього діапазону введіть лише ім’я діапазону.

     Наприклад, повний вкладений вираз IIf, який розділяє поле "Вік" на діапазони по двадцять років, може виглядати так (розриви рядків додано для зручності читання):

     IIf([Age]<21,"0-20 years",
      IIf([Age]<41,"21-40 years",
      IIf([Age]<61,"41-60 years",
      IIf([Age]<81,"61-80 years", "80+ years"))))
     

     Примітка.: Програма Access припинить обчислення виразу, щойно один з операторів IIf прийме значення "істина". Вказувати нижню межу кожного діапазону не обов’язково, оскільки будь-яке значення, яке знаходиться нижче нижньої межі заданого діапазону, вже матиме значення "істина".

8. У сітці макета запиту в рядку підсумків виберіть пункт Групувати за.

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

Можливо, вам буде потрібно, щоб під час виконання перехресний запит пропонував введення. Наприклад, якщо використовується кілька заголовків рядків, один із яких – "Країна/регіон". Замість того щоб постійно відображати дані для кожної країни або регіону, запит може запропонувати ввести ім’я, а потім відобразити дані відповідно до введених користувачем даних.

Запит параметра можна додати до будь-якого поля заголовка рядка.

1. Відкрийте перехресний запит у режимі конструктора.

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

   Наприклад, якщо під час виконання запиту в рядку Умови ввести [Яка країна або регіон?], відобразиться діалогове вікно з запитанням "Яка країна або регіон?", полем вводу та кнопкою OK.

   Порада.: Якщо параметр має бути гнучкий, об’єднайте вираз із символами узагальнення за допомогою оператора Like. Наприклад, замість умови [Яка країна або регіон?] можна ввести умову Like [Яка країна або регіон?]&"*", щоб параметр відповідав ширшому діапазону введених даних. Використання оператора Like не змінює вигляду запиту параметра.

3. На вкладці Конструктор у групі Відобразити або приховати натисніть кнопку Параметри.

4. У діалоговому вікні Параметри запиту у стовпці Параметр введіть той самий запит параметра, який було використано в рядку Умови. Додайте квадратні дужки, але не додавайте жодні об’єднані символи узагальнення або оператор Like.

5. У стовпці Тип даних виберіть тип даних для параметра. Тип даних має відповідати типу даних поля заголовка рядка.

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

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

Синтаксис функції Nz

Nz ( варіант [, значення_якщо_null ] )

Синтаксис функції Nz має такі аргументи:

Аргумент	Опис
Варіант	Обов’язковий. Змінна, що належить до тип даних варіант (Variant).
значення_якщо_null	Необов’язковий (за винятком використання в запитах). Вираз типу варіант (Variant), що вказує значення, яке слід повернути, якщо аргумент варіант має Null-значення. За допомогою цього аргументу можна повертати значення, відмінні від нуля або рядка нульової довжини. Примітка.: Якщо використати функцію Nz у виразі в запиті без використання аргументу значення_якщо_null, то для полів, які містять Null-значення, повертатиметься рядок нульової довжини.

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

2. У контекстному меню виберіть пункт Масштаб.

3. У вікні Масштаб візьміть ім’я поля або вираз у дужки, а потім введіть Nz перед дужками.

4. Введіть , 0 безпосередньо перед правою дужкою.

   Наприклад, якщо для заміни Null-значень на нулі було використано Nz з полем "Втрачені години", кінцевий вираз матиме такий вигляд:

Nz([Hours Lost], 0)

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

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

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

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

На початок сторінки