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

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

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

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

У цій статті

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

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

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

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

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

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

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

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

Підсумувати стовпець чисел у запиті можна за допомогою так званої агрегатної функції. Агрегатні функції виконують обчислення в стовпцях даних і повертають одне значення. В Access передбачено низку агрегатних функцій, зокрема Сума, Кількість, Середнє (для визначення середніх значень), Мінімум і Максимум. Щоб підсумувати дані, додайте до запиту функцію Сума, щоб підрахувати їх – функцію Кількість, тощо.

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

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

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

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

Місто

2003

2004

2005

Париж

254 556

372 455

467 892

Сідней

478 021

372 987

276 399

Джакарта

572 997

684 374

792 571

...

...

...

...

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

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

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

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

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

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

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

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

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

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

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

Категорія

Ляльки

Ігри та головоломки

Картини й рами

Відеоігри

DVD-диски та фільми

Моделі для збирання й захоплення

Спорт

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

Назва товару

Ціна

Категорія

Фігурка програміста

12,95 ₴

Ляльки

"Експерименти із C#" (настільна гра для всієї родини)

150,85 ₴

Ігри та головоломки

Схема реляційної бази даних

22,50 ₴

Картини й рами

"Магічна мікросхема" (500 деталей)

132,65 ₴

Ігри та головоломки

Access! Гра!

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

Jakarta

550,00 ₴

14.11.2005

15.11.2005

Sydney

760,00 ₴

16.11.2005

17.11.2005

Sydney

870,00 ₴

17.11.2005

18.11.2005

Jakarta

430,00 ₴

17.11.2005

18.11.2005

Paris

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

Access! Гра!

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. За допомогою клавіш зі стрілками перейдіть до наступної пустої клітинки верхнього колонтитула та введіть друге ім'я поля (можна також натиснути клавішу ТАБУЛЯЦІЇ або двічі клацнути нову клітинку). Повторіть цей крок, доки не введете всі імена полів.

  4. Введіть дані в зразку таблиці.

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

  5. Ввівши всі дані, натисніть кнопку Зберегти.

    Сполучення клавіш: натисніть Ctrl+S.

    Відкриється діалогове вікно Зберегти як.

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

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

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

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

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

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

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

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

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

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

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

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

  1. На вкладці Зовнішні дані в групі Імпорт натисніть кнопку Excel.

    -або-

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

    Відкриється діалогове вікно Отримати зовнішні дані – Таблиця <ім’я програми>.

  2. Натисніть кнопку Огляд, відкрийте раніше створений файл електронної таблиці й натисніть кнопку OK.

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

  3. За замовчуванням майстер вибирає перший аркуш книги (на аркуші " клієнти ", якщо ви виконали кроки, описані в попередньому розділі), і дані з аркуша відображаються в нижній частині сторінки майстра. Натисніть кнопку Далі.

  4. На наступній сторінці майстра встановіть прапорець Перший рядок містить заголовки стовпців, а потім натисніть кнопку Далі.

  5. За потреби на наступній сторінці за допомогою текстових полів і списків у розділі Параметри поля змініть імена й типи даних полів або виключіть певні поля з операції імпорту. Інакше натисніть кнопку Далі.

  6. Залиште вибраним параметр Дозволити Access додати первинний ключ і натисніть кнопку Далі.

  7. За замовчуванням Access застосовує до нової таблиці ім’я аркуша. Прийміть це ім’я або введіть інше й натисніть кнопку Готово.

  8. Повторюйте кроки 1–7, доки не створите таблицю з кожного аркуша в книзі.

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

Примітка.: Під час імпорту аркушів Access автоматично додає стовпець первинного ключа до кожної таблиці, за замовчуванням надає йому ім’я "Ідентифікатор" і встановлює для нього тип даних "Автонумерація". На прикладах нижче продемонстровано, як перейменувати кожне поле первинного ключа. Це дасть змогу чітко визначити всі поля в запиті.

  1. В області переходів клацніть правою кнопкою миші кожну раніше створену таблицю й виберіть команду Конструктор.

  2. Для кожної таблиці знайдіть поле первинного ключа. За замовчуванням Access надає таким полям ім’я Ідентифікатор.

  3. У стовпці Ім’я поля введіть ім’я таблиці для кожного поля первинного ключа.

    Наприклад, можна перейменувати поле ІДЕНТИФІКАТОРА для таблиці категорії на "ІДЕНТИФІКАТОР категорії", а поле для таблиці "замовлення" на "ІДЕНТИФІКАТОР замовлення". У таблиці "відомості про замовлення" перейменуйте поле на "ІДЕНТИФІКАТОР відомостей". У таблиці "Товари" перейменуйте поле на "ІДЕНТИФІКАТОР товару".

  4. Збережіть внесені зміни.

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

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

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

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

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

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

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

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

  3. Двічі клацніть поля таблиці, які потрібно використовувати в запиті.

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

    Кожне поле відображається в окремій клітинці бланка.

  4. Щоб виконати запит, натисніть кнопку Запуск Зображення кнопки .

    Набір результатів запиту з’явиться у вікні табличного подання даних.

  5. Ви можете повернутися до режиму конструктора та змінити запит. Для цього клацніть правою кнопкою миші вкладку документа для відповідного запиту й виберіть команду Конструктор. Потім за потреби запит можна змінити, додавши або видаливши поля таблиці. Щоб видалити поле, виділіть стовпець на бланку й натисніть клавішу Delete.

  6. Збережіть запит.

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

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

    -або-

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

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

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

  3. У рядку Підсумок клацніть клітинку поля, яке потрібно підсумувати, а потім зі списку виберіть Сума.

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

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

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

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

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

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

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

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

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

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

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

Ідентифікатор замовлення

Дата замовлення

Дата відвантаження

Місто доставки

Вартість доставки

1

14.11.2005

15.11.2005

Jakarta

550,00 ₴

2

14.11.2005

15.11.2005

Sydney

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

Access! Гра!

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" на початок імені поля, яке ви підсумовувати. Щоб змінити заголовок стовпця на щось значуще, наприклад загальний обсяг доставки, поверніться до подання конструктора та клацніть у рядку поле стовпця "вартість доставки" в сітці макета. Установіть курсор поруч із пунктом вартість доставки та введіть слово загальний обсяг доставки, а потім – двокрапка, як-от: загальний обсяг доставки: вартість доставки.

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

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

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

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

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

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

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

    Загальний обсяг збуту: (1-[Відомості про замовлення].[Знижка]/100)*([Відомості про замовлення].[Ціна за одиницю]*[Відомості про замовлення].[Кількість])

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

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

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

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

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

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

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

    Наприклад, якщо ви скористалися зразком даних і створили запит (згідно з попередніми кроками), буде відображено:

    Загальний обсяг збуту: Sum((1-[Відомості про замовлення].[Знижка]/100)*([Відомості про замовлення].[Ціна за одиницю]*[Відомості про замовлення].[Кількість]))

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

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

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

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

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

Ідентифікатор товару

Назва товару

Ціна

Категорія

1

Фігурка програміста

12,95 ₴

Ляльки

2

"Експерименти із C#" (настільна гра для всієї родини)

150,85 ₴

Ігри та головоломки

3

Схема реляційної бази даних

22,50 ₴

Картини й рами

4

"Магічна мікросхема" (500 деталей)

132,65 ₴

Картини й рами

5

Access! Гра!

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

Access! Гра!

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. Створіть стовпець, у якому обчислюватиметься обсяг збуту для кожної операції. Для цього в другому стовпці сітки введіть наведений нижче вираз.

      Загальний обсяг збуту: (1-[Відомості про замовлення].[Знижка]/100)*([Відомості про замовлення].[Ціна за одиницю]*[Відомості про замовлення].[Кількість])

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

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

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

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

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

    6. Не закривайте запит, щоб використовувати його в прикладах із наступного розділу.

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

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

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

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

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

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

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

      Додавання умов до запиту

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

      -або-

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

    11. У рядку Критерії стовпця "Ідентифікатор категорії" введіть =Ляльки Or Спорт Or Картини й рами.

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

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

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

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

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

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

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

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

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

      Загальний обсяг збуту: Sum((1-[Відомості про замовлення].[Знижка]/100)*([Відомості про замовлення].[Ціна за одиницю]*[Відомості про замовлення].[Кількість]))

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

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

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

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

Зразок перехресного запиту

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

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

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

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

Дата замовлення

Дата відвантаження

Місто доставки

Вартість доставки

14.11.2005

15.11.2005

Jakarta

550,00 ₴

14.11.2005

15.11.2005

Sydney

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

Access! Гра!

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. У наступній пустій клітинці рядка Поле скопіюйте та вставте або введіть такий вираз: Загальний обсяг збуту: Sum(CCur([Відомості про замовлення].[Ціна за одиницю]*[Кількість]*(1-[Знижка])/100)*100)

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

    На бланку з’являться рядки Підсумок і Перехресний.

  6. Клацніть клітинку в рядку Підсумок для поля "Місто" й виберіть Групування за. Зробіть те саме в полі "Дата відвантаження". Змініть значення в клітинці рядка Підсумок для поля "Загальний обсяг збуту" на Вираз.

  7. У клітинці рядка Перехресний для поля "Місто" виберіть значення Заголовок рядка, для поля "Дата відвантаження" – Заголовок стовпця та для поля "Загальний обсяг збуту" – Значення.

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

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

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

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

У цій таблиці перелічено агрегатні функції, які в Access містяться в рядку підсумків і в запитах. Пам'ятайте, що програма Access надає більше агрегатних функцій для запитів, ніж для рядка підсумків. Крім того, якщо ви працюєте з проектом Access (передній кінець Access, підключеним до бази даних Microsoft SQL Server), можна скористатися більшим набором агрегатних функцій, які надає SQL Server. Докладні відомості про набір функцій наведено в статті Microsoft SQL Server Books Online.

Функція

Опис

Підтримувані типи даних

Середнє

Обчислює середнє значення стовпця. Стовпець має містити числа, грошові одиниці або дату й час. Ця функція пропускає Null-значення.

"Число", "Грошова одиниця", "Дата й час"

Кількість

Підраховує кількість елементів у стовпці.

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

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

Максимум

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

"Число", "Грошова одиниця", "Дата й час"

Мінімум

Повертає елемент із найменшим значенням. Для текстових даних найменшим вважається перше значення в алфавітному порядку (Access не враховує регістр). Ця функція пропускає пусті значення.

"Число", "Грошова одиниця", "Дата й час"

Стандартне відхилення

Визначає, наскільки значення відхиляються від середнього.

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

"Число", "Грошова одиниця"

Сума

Додає елементи в стовпці. Працює лише з числовими та грошовими даними.

"Число", "Грошова одиниця"

Відхилення

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

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

"Число", "Грошова одиниця"

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

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×