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

У цій статті пояснюється, як використовувати агрегатну функцію в Access, щоб підсумувати дані в наборі результатів запиту. У ній також стисло пояснюється, як використовувати інші агрегатні функції, наприклад COUNT "І AVG", для підрахунку або підсумкового набору. Крім того, тут пояснюється, як за допомогою рядка підсумків підсумувати дані, не змінюючи структури запитів.

У цій статті

• Загальні відомості про способи підсумування даних
• Підготовка зразків даних
• Підсумування даних за допомогою рядка підсумків
• Обчислення загальних підсумків за допомогою запиту
• Обчислення підсумків групи за допомогою запиту підсумків
• Підсумування даних із кількох груп за допомогою перехресного запиту
• Довідник із використання агрегатних функцій

Загальні відомості про способи підсумування даних

Підсумувати стовпець чисел у запиті можна за допомогою так званої агрегатної функції. Агрегатні функції виконують обчислення в стовпцях даних і повертають одне значення. В Access передбачено низку агрегатних функцій, зокрема Sum, Count, Avg (для обчислення середніх значень) Minі Max. Щоб підсумувати дані, додайте Sum функцію до запиту. Обчислення кількості даних за допомогою функції Count тощо.

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

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

Місто	2003	2004	2005
Париж	254 556	372 455	467 892
Сідней	478 021	372 987	276 399
Джакарта	572 997	684 374	792 571
⁠...⁠	...	...	...

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

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

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

На початок

Підготовка зразків даних

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

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

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

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

Таблиця Категорії

Категорія
Ляльки
Ігри та головоломки
Картини й рами
Відеоігри
DVD-диски та фільми
Моделі для збирання й захоплення
Спорт

Таблиця Товари

Назва товару	Ціна	Категорія
Фігурка програміста	12,95 ₴	Ляльки
"Експерименти із C#" (настільна гра для всієї родини)	150,85 ₴	Ігри та головоломки
Схема реляційної бази даних	22,50 ₴	Картини й рами
"Магічна мікросхема" (500 деталей)	132,65 ₴	Ігри та головоломки
Доступ Гра!	122,95 ₴	Ігри та головоломки
"Комп’ютерні фанати та міфічні створіння"	78,50 ₴	Відеоігри
Вправи для комп'ютерних фанатів! DVD-диск	54,88 ₴	DVD-диски та фільми
"Піца, що літає" (повна версія)	136,75 ₴	Спорт
Зовнішній дисковод для дискет із діаметром 5,25 дюйма (масштаб 1:4)	165,00 ₴	Моделі для збирання й захоплення
Нерухома фігурка бюрократа	18,88 ₴	Ляльки
"Морок"	53,33 ₴	Відеоігри
"Збери клавіатуру"	177,95 ₴	Моделі для збирання й захоплення

Таблиця Замовлення

Дата замовлення	Дата відвантаження	Місто доставки	Вартість доставки
14.11.2005	15.11.2005	Джакарта	550,00 ₴
14.11.2005	15.11.2005	Сідней	760,00 ₴
16.11.2005	17.11.2005	Сідней	870,00 ₴
17.11.2005	18.11.2005	Джакарта	430,00 ₴
17.11.2005	18.11.2005	Париж	1050,00 ₴
17.11.2005	18.11.2005	Штутгарт	1120,00 ₴
18.11.2005	19.11.2005	Відень	2150,00 ₴
19.11.2005	20.11.2005	Маямі	5250,00 ₴
20.11.2005	21.11.2005	Відень	1980,00 ₴
20.11.2005	21.11.2005	Париж	1870,00 ₴
21.11.2005	22.11.2005	Сідней	810,00 ₴
23.11.2005	24.11.2005	Джакарта	920,00 ₴

Таблиця Відомості про замовлення

Ідентифікатор замовлення	Назва товару	Ідентифікатор товару	Ціна за одиницю	Кількість	Знижка
1	"Збери клавіатуру"	12	177,95 ₴	9	5%
1	Нерухома фігурка бюрократа	2	18,88 ₴	4	7,5%
2	Вправи для комп'ютерних фанатів! DVD-диск	7	54,88 ₴	6	4%
2	"Магічна мікросхема"	4	132,65 ₴	8	0
2	"Комп’ютерні фанати та міфічні створіння"	6	78,50 ₴	4	0
3	Доступ Гра!	5	122,95 ₴	5	15%
4	Фігурка програміста	1	12,95 ₴	2	6%
4	"Піца, що літає" (повна версія)	8	136,75 ₴	8	4%
5	Зовнішній дисковод для дискет із діаметром 5,25 дюйма (масштаб 1:4)	9	165,00 ₴	4	10%
6	Схема реляційної бази даних	3	22,50 ₴	12	6,5%
7	"Морок"	11	53,33 ₴	6	8%
7	Схема реляційної бази даних	3	22,50 ₴	4	9%

Введення зразків даних уручну

1. На вкладці Створення в групі Таблиці натисніть кнопку Таблиця. Програма Access додасть нову пусту таблицю до бази даних.

   Примітка.

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

2. Двічі клацніть першу клітинку в рядку заголовка та введіть ім’я поля в зразку таблиці. За замовчуванням програма Access позначає пусті поля в рядку заголовка текстом "Додати нове поле", як

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

4. Введіть дані в зразку таблиці. Коли ви вводите дані, Access визначає тип даних для кожного поля. Якщо ви не маєте досвіду роботи з реляційними базами даних, установіть певний тип даних для кожного поля в таблиці, наприклад "Число", "Текст" або "Дата й час". Це гарантує точність введення даних, а також допомагає уникнути помилок, наприклад, коли в арифметичній операції намагаються використати номер телефону. Для цих зразків таблиць дозвольте Access визначити тип даних автоматично.

5. Ввівши всі дані, натисніть кнопку Зберегти. Сполучення клавіш: натисніть клавіші Ctrl+S. Відкриється діалогове вікно Зберегти як.

6. У поле Ім’я таблиці введіть ім’я зразка таблиці й натисніть кнопку OK. Вводьте вказані імена зразків таблиць, оскільки вони використовуються в прикладах запитів.

7. Повторюйте ці кроки, доки не створите всі зразки таблиць, наведені на початку цього розділу.

Щоб не вводити дані вручну, виконайте наведені нижче кроки, щоб скопіювати дані до файлу електронної таблиці та імпортувати їх до Access.

Створення зразків аркушів

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

2. Скопіюйте зразок першої таблиці вище та вставте його в першій клітинці першого аркуша.

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

4. Повторюйте кроки 2 та 3, щоб скопіювати кожен зразок таблиці до пустого аркуша та перейменувати аркуш.

   Примітка.

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

5. Збережіть книгу в зручному розташуванні на комп’ютері або в мережі та перейдіть до наступних кроків.

Створення таблиць бази даних з аркушів

1. На вкладці "Зовнішні дані " в групі "Імпорт & зв'язування " натисніть кнопку "Нове джерело даних>із файлу>Excel". Відкриється діалогове вікно " Отримати зовнішні дані – електронна таблиця Excel ".
2. Натисніть кнопку Огляд, відкрийте раніше створений файл електронної таблиці й натисніть кнопку OK. Запуститься майстер імпорту електронних таблиць.
3. За замовчуванням майстер вибирає перший аркуш у книзі (" Клієнти ", якщо ви дотримувались усіх указівок у попередньому розділі). Дані з цього аркуша відобразяться в нижній частині сторінки майстра. Натисніть кнопку Далі.
4. На наступній сторінці майстра встановіть прапорець Перший рядок містить заголовки стовпців, а потім натисніть кнопку Далі.
5. За потреби на наступній сторінці за допомогою текстових полів і списків у розділі Параметри поля змініть імена й типи даних полів або виключіть певні поля з операції імпорту. Інакше натисніть кнопку Далі.
6. Залиште вибраним параметр Дозволити Access додати первинний ключ і натисніть кнопку Далі.
7. За замовчуванням Access застосовує до нової таблиці ім’я аркуша. Прийміть це ім’я або введіть інше й натисніть кнопку Готово.
8. Повторюйте кроки 1–7, доки не створите таблицю з кожного аркуша книги.

Перейменування полів первинних ключів

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

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

На початок

Підсумування даних за допомогою рядка підсумків

Щоб додати рядок підсумків до запиту, відкрийте запит у вікні табличного подання даних, додайте рядок, а потім виберіть потрібну агрегатну функцію, наприклад Sum, MinMaxабо Avg. На прикладах у цьому розділі продемонстровано, як створити простий вибірковий запит і додати рядок підсумків. Зразки таблиць, наведені в попередньому розділі, використовувати не обов’язково.

Створення базового вибіркового запиту

1. На вкладці Створити в групі Запити натисніть кнопку Конструктор запитів.
2. Двічі клацніть одну або кілька таблиць, які потрібно використовувати в запиті. Вибрані таблиці відобразяться як окремі вікна вгорі області конструктора запитів.
3. Двічі клацніть поля таблиці, які потрібно використовувати в запиті. Ви можете додати поля, які містять описові дані, наприклад імена й описи, проте головне – додати поле з числовими або грошовими даними. Кожне поле відображається в окремій клітинці бланка.
4. Натисніть кнопку "Виконати ", щоб виконати запит. Набір результатів запиту з’явиться у вікні табличного подання даних.
5. Ви можете повернутися до режиму конструктора та змінити запит. Для цього клацніть правою кнопкою миші вкладку документа для відповідного запиту й виберіть команду Конструктор. Потім за потреби запит можна змінити, додавши або видаливши поля таблиці. Щоб видалити поле, виділіть стовпець на бланку й натисніть клавішу Delete.
6. Збережіть запит.

Додавання рядка підсумків

1. Переконайтеся, що запит відкрито у вікні табличного подання даних. Для цього клацніть правою кнопкою миші вкладку документа для відповідного запиту й виберіть команду Подання таблиці. -або- В області переходів двічі клацніть запит. Програма виконає запит і завантажить результати до таблиці даних.
2. На вкладці Основне в групі Записи натисніть кнопку Підсумки. У таблиці даних з’явиться новий рядок Підсумок.
3. У рядку Підсумок клацніть клітинку поля, яке потрібно підсумувати, а потім зі списку виберіть Сума.

Приховання рядка підсумків

• На вкладці Основне в групі Записи натисніть кнопку Підсумки.

Докладні відомості про те, як використовувати рядок підсумків, див. в статті Відображення підсумків стовпців у таблиці даних.

На початок

Обчислення загальних підсумків за допомогою запиту

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

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

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

Таблиця "Замовлення"

Ідентифікатор замовлення	Дата замовлення	Дата відвантаження	Місто доставки	Вартість доставки
1	14.11.2005	15.11.2005	Джакарта	550,00 ₴
2	14.11.2005	15.11.2005	Сідней	760,00 ₴
3	16.11.2005	17.11.2005	Сідней	870,00 ₴
4	17.11.2005	18.11.2005	Джакарта	430,00 ₴
5	17.11.2005	18.11.2005	Париж	1050,00 ₴
6	17.11.2005	18.11.2005	Штутгарт	1120,00 ₴
7	18.11.2005	19.11.2005	Відень	2150,00 ₴
8	19.11.2005	20.11.2005	Маямі	5250,00 ₴
9	20.11.2005	21.11.2005	Відень	1980,00 ₴
10	20.11.2005	21.11.2005	Париж	1870,00 ₴
11	21.11.2005	22.11.2005	Сідней	810,00 ₴
12	23.11.2005	24.11.2005	Джакарта	920,00 ₴

Таблиця "Відомості про замовлення"

Ідентифікатор відомостей	Ідентифікатор замовлення	Назва товару	Ідентифікатор товару	Ціна за одиницю	Кількість	Знижка
1	1	"Збери клавіатуру"	12	177,95 ₴	9	0,05
2	1	Нерухома фігурка бюрократа	2	18,88 ₴	4	0,075
3	2	Вправи для комп'ютерних фанатів! DVD-диск	7	54,88 ₴	6	0,04
4	2	"Магічна мікросхема"	4	132,65 ₴	8	0,00
5	2	"Комп’ютерні фанати та міфічні створіння"	6	78,50 ₴	4	0,00
6	3	Доступ Гра!	5	122,95 ₴	5	0,15
7	4	Фігурка програміста	1	12,95 ₴	2	0,06
8	4	"Піца, що літає" (повна версія)	8	136,75 ₴	8	0,04
9	5	Зовнішній дисковод для дискет із діаметром 5,25 дюйма (масштаб 1:4)	9	165,00 ₴	4	0,10
10	6	Схема реляційної бази даних	3	22,50 ₴	12	0,065
11	7	"Морок"	11	53,33 ₴	6	0,08
12	7	Схема реляційної бази даних	3	22,50 ₴	4	0,09

Обчислення простого загального підсумку

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

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

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

4. На вкладці " Конструктор запиту " в групі " Відобразити або приховати " натисніть кнопку "Підсумки". На бланку з’явиться рядок Підсумок, а в клітинці стовпця "Вартість доставки" відображатиметься напис Групування за.

5. Змініть значення в клітинці рядка Підсумок на Сума.

6. Натисніть кнопку Запуск, щоб виконати запит і відобразити результати в табличному поданні даних.

   Порада.

   Access додається SumOf на початку імені поля, значення якого ви сумуєте. Щоб змінити заголовок стовпця на зрозуміліший, наприклад "Загальна вартість доставки", поверніться до режиму конструктора та клацніть на бланку рядок "Поле " стовпця "Вартість доставки". Помістіть курсор поруч із пунктом "Вартість доставки " та введіть Total Shipping: Shipping Fee.

7. За потреби збережіть запит і закрийте його.

Обчислення загального підсумку, з якого виключено певні записи

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

2. Двічі клацніть таблиці "Замовлення" та "Відомості про замовлення".

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

4. У рядку "Критерії " першого стовпця введіть Date() -1". Цей вираз виключає з обчислюваного підсумку записи з поточною датою.

5. Потім створіть стовпець, у якому обчислюватиметься обсяг збуту для кожної операції. У рядку Поле другого стовпця бланку введіть такий вираз: Total Sales Value: (1-[Order Details].[Discount]/100)*([Order Details].[Unit Price]*[Order Details].[Quantity]) Переконайтеся, що вираз посилається на поля, для яких установлено тип даних "Число" або "Грошова одиниця". Якщо він посилається на поля з іншими типами даних, під час спроби виконати запит Access відобразить повідомлення Невідповідність типу даних у виразі критерію.

6. На вкладці " Конструктор запиту " в групі " Відобразити або приховати " натисніть кнопку "Підсумки". На бланку з’явиться рядок Підсумок, а в першому та другому стовпцях відображатиметься напис Групування за.

7. У другому стовпці змініть значення в клітинці рядка Підсумок на Сума. Функція Сума підсумує окремі показники збуту.

8. Натисніть кнопку Запуск, щоб виконати запит і відобразити результати в табличному поданні даних.

9. Збережіть запит з іменем Щоденний збут.

   Примітка.

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

   Наприклад, якщо ви скористалися зразком даних і створили запит (згідно з попередніми кроками), буде відображено: Total Sales Value: Sum((1-[Order Details].Discount/100)*([Order Details].Unitprice*[Order Details].Quantity))

На початок

Обчислення підсумків групи за допомогою запиту підсумків

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

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

Таблиця "Товари"

Ідентифікатор товару	Назва товару	Ціна	Категорія
1	Фігурка програміста	12,95 ₴	Ляльки
2	"Експерименти із C#" (настільна гра для всієї родини)	150,85 ₴	Ігри та головоломки
3	Схема реляційної бази даних	22,50 ₴	Картини й рами
4	"Магічна мікросхема" (500 деталей)	132,65 ₴	Картини й рами
5	Доступ Гра!	122,95 ₴	Ігри та головоломки
6	"Комп’ютерні фанати та міфічні створіння"	78,50 ₴	Відеоігри
7	Вправи для комп'ютерних фанатів! DVD-диск	54,88 ₴	DVD-диски та фільми
8	"Піца, що літає" (повна версія)	136,75 ₴	Спорт
9	Зовнішній дисковод для дискет із діаметром 5,25 дюйма (масштаб 1:4)	165,00 ₴	Моделі для збирання й захоплення
10	Нерухома фігурка бюрократа	18,88 ₴	Ляльки
11	"Морок"	53,33 ₴	Відеоігри
12	"Збери клавіатуру"	177,95 ₴	Моделі для збирання й захоплення

Таблиця "Відомості про замовлення"

Ідентифікатор відомостей	Ідентифікатор замовлення	Назва товару	Ідентифікатор товару	Ціна за одиницю	Кількість	Знижка
1	1	"Збери клавіатуру"	12	177,95 ₴	9	5%
2	1	Нерухома фігурка бюрократа	2	18,88 ₴	4	7,5%
3	2	Вправи для комп'ютерних фанатів! DVD-диск	7	54,88 ₴	6	4%
4	2	"Магічна мікросхема"	4	132,65 ₴	8	0
5	2	"Комп’ютерні фанати та міфічні створіння"	6	78,50 ₴	4	0
6	3	Доступ Гра!	5	122,95 ₴	5	15%
7	4	Фігурка програміста	1	12,95 ₴	2	6%
8	4	"Піца, що літає" (повна версія)	8	136,75 ₴	8	4%
9	5	Зовнішній дисковод для дискет із діаметром 5,25 дюйма (масштаб 1:4)	9	165,00 ₴	4	10%
10	6	Схема реляційної бази даних	3	22,50 ₴	12	6,5%
11	7	"Морок"	11	53,33 ₴	6	8%
12	7	Схема реляційної бази даних	3	22,50 ₴	4	9%

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

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

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

2. Виберіть потрібні таблиці та натисніть кнопку "Додати". Кожна таблиця відобразиться як окреме вікно вгорі області конструктора запитів. Якщо ви працюєте з наведеними вище зразками таблиць, додайте таблиці "Товари" та "Відомості про замовлення".

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

   1. Додайте до бланку поле "Категорія" з таблиці "Товари".

   2. Створіть стовпець, у якому обчислюватиметься обсяг збуту для кожної операції. Для цього в другому стовпці сітки введіть наведений нижче вираз. Total Sales Value: (1-[Order Details].[Discount]/100)*([Order Details].[Unit Price]*[Order Details].[Quantity]) Переконайтеся, що для полів, посилання на які наведено у виразі, установлено тип даних "Число" або "Грошова одиниця". Якщо ви посилаєтеся на поля з іншими типами даних, під час спроби перейти до вікна табличного подання даних Access відобразить повідомлення про помилку Невідповідність типу даних у виразі критерію.

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

   4. У другому стовпці змініть значення в рядку Підсумок на Сума. Функція Сума підсумує окремі показники збуту.

   5. Натисніть кнопку Запуск, щоб виконати запит і відобразити результати в табличному поданні даних.

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

   7. Умови, які пропускають певні групи під час обчислення підсумків. Наприклад, можна обчислити підсумки лише для категорій "Відеоігри", "Картини й рами" та "Спорт".

   8. Умови, які приховують певні підсумки після їх обчислення. Наприклад, можна відобразити лише підсумки, які перевищують 150 000 грн.

   9. Умови, які виключають окремі записи під час обчислення підсумку. Наприклад, можна вилучити окремі операції збуту, для яких значення (Unit Price * Quantity) не перевищує 100 грн. На прикладах нижче продемонстровано послідовне додавання умов і вплив цього на результат запиту. Додавання умов до запиту

   10. Відкрийте в режимі конструктора запит, створений у прикладах із попереднього розділу. Для цього клацніть правою кнопкою миші вкладку документа для відповідного запиту й виберіть команду Конструктор. -або- В області переходів клацніть правою кнопкою миші запит і виберіть команду "Режим конструктора".

   11. У рядку "Критерії " стовпця "Ідентифікатор категорії" введіть =Dolls Or Sports or Art and Framing".

   12. Натисніть кнопку Запуск, щоб виконати запит і відобразити результати в табличному поданні даних.

   13. Поверніться до режиму конструктора та введіть у рядку "Критерії" стовпця "Загальний обсяг збуту" .>100

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

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

       Примітка.

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

   16. Скопіюйте вираз із другого до третього стовпця.

   17. У рядку підсумків для нового стовпця виберіть Where , а в рядку "Критерії " введіть >20.

   18. Виконайте запит, щоб переглянути результати, і збережіть його.

       Примітка.

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

       Total Sales Value: Sum((1-[Order Details].Discount/100)*([Order Details].Unitprice*[Order Details].Quantity)) Також з'явиться четвертий стовпець. Це копія другого стовпця, але умови, указані в другому стовпці, насправді відображаються як частина нового стовпця.

На початок

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

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

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

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

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

Таблиця "Замовлення"

Дата замовлення	Дата відвантаження	Місто доставки	Вартість доставки
14.11.2005	15.11.2005	Джакарта	550,00 ₴
14.11.2005	15.11.2005	Сідней	760,00 ₴
16.11.2005	17.11.2005	Сідней	870,00 ₴
17.11.2005	18.11.2005	Джакарта	430,00 ₴
17.11.2005	18.11.2005	Париж	1050,00 ₴
17.11.2005	18.11.2005	Штутгарт	1120,00 ₴
18.11.2005	19.11.2005	Відень	2150,00 ₴
19.11.2005	20.11.2005	Маямі	5250,00 ₴
20.11.2005	21.11.2005	Відень	1980,00 ₴
20.11.2005	21.11.2005	Париж	1870,00 ₴
21.11.2005	22.11.2005	Сідней	810,00 ₴
23.11.2005	24.11.2005	Джакарта	920,00 ₴

Таблиця "Відомості про замовлення"

Ідентифікатор замовлення	Назва товару	Ідентифікатор товару	Ціна за одиницю	Кількість	Знижка
1	"Збери клавіатуру"	12	177,95 ₴	9	5%
1	Нерухома фігурка бюрократа	2	18,88 ₴	4	7,5%
2	Вправи для комп'ютерних фанатів! DVD-диск	7	54,88 ₴	6	4%
2	"Магічна мікросхема"	4	132,65 ₴	8	0
2	"Комп’ютерні фанати та міфічні створіння"	6	78,50 ₴	4	0
3	Доступ Гра!	5	122,95 ₴	5	15%
4	Фігурка програміста	1	12,95 ₴	2	6%
4	"Піца, що літає" (повна версія)	8	136,75 ₴	8	4%
5	Зовнішній дисковод для дискет із діаметром 5,25 дюйма (масштаб 1:4)	9	165,00 ₴	4	10%
6	Схема реляційної бази даних	3	22,50 ₴	12	6,5%
7	"Морок"	11	53,33 ₴	6	8%
7	Схема реляційної бази даних	3	22,50 ₴	4	9%

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

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

1. На вкладці Створити в групі Запити натисніть кнопку Конструктор запитів.
2. Двічі клацніть таблиці, які потрібно використовувати в запиті. Кожна таблиця відобразиться як окреме вікно вгорі області конструктора запитів. Якщо ви працюєте зі зразками таблиць, двічі клацніть таблиці "Замовлення" та "Відомості про замовлення".
3. Двічі клацніть поля, які потрібно використовувати в запиті. Імена полів відображаються в пустих клітинках рядка «Поле» сітки конструктора. Якщо ви працюєте зі зразками таблиць, додайте поля "Місто доставки" та "Дата відвантаження" з таблиці "Замовлення".
4. У наступній пустій клітинці рядка "Поле " скопіюйте та вставте або введіть такий вираз: Total Sales: Sum(CCur([Order Details].[Unit Price]*[Quantity]*(1-[Discount])/100)*100)
5. На вкладці " Конструктор запиту " в групі " Тип запиту" натисніть кнопку "Перехресний запит". На бланку з’являться рядки Підсумок і Перехресний.
6. Клацніть клітинку в рядку Підсумок для поля "Місто" й виберіть Групування за. Зробіть те саме в полі "Дата відвантаження". Змініть значення в клітинці рядка Підсумок для поля "Загальний обсяг збуту" на Вираз.
7. У перехресному рядку виберіть для клітинки в полі "Місто" значення "Заголовок рядка", у полі "Дата відвантаження" – значення "Заголовок стовпця", а для поля "Загальний обсяг продажів" – "Значення".
8. На вкладці " Конструктор запитів " у групі "Результати " натисніть кнопку "Виконати". Результати запиту відобразяться у вікні табличного подання даних.

На початок

Довідник із використання агрегатних функцій

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

Функція	Опис	Підтримувані типи даних
Середнє	Обчислює середнє значення стовпця. Стовпець має містити числа, грошові одиниці або дату й час. Ця функція пропускає пусті значення.	"Число", "Грошова одиниця", "Дата й час"
Кількість	Підраховує кількість елементів у стовпці.	Усі типи даних, складних повторюваних скалярних даних, як-от стовпець багатозначних списків. Докладніше про створення та видалення багатозначних полів див. в статті.
Максимум	Повертає елемент із найбільшим значенням. Для текстових даних найбільшим вважається останнє значення в алфавітному порядку (Access не враховує регістр). Ця функція пропускає пусті значення.	"Число", "Грошова одиниця", "Дата й час"
Мінімум	Повертає елемент із найменшим значенням. Для текстових даних найменшим вважається перше значення в алфавітному порядку (Access не враховує регістр). Ця функція пропускає пусті значення.	"Число", "Грошова одиниця", "Дата й час"
Стандартне відхилення	Визначає, наскільки значення відхиляються від середнього. Докладні відомості про те, як використовувати ці функції, див. в статті Відображення підсумків стовпців у таблиці даних.	"Число", "Грошова одиниця"
Сума	Додає елементи в стовпці. Працює лише з числовими та грошовими даними.	"Число", "Грошова одиниця"
Відхилення	Вимірює статистичне відхилення всіх значень у стовпці. Цю функцію можна використовувати лише з числовими та грошовими даними. Якщо таблиця містить менше двох рядків, Access повертає Null-значення. Докладні відомості про функції відхилення див. в статті Відображення підсумків стовпців у таблиці даних.	"Число", "Грошова одиниця"

На початок