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

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

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

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

У цій статті

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

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

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

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

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

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

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

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

Підсумувати стовпець чисел у запиті можна за допомогою так званої агрегатної функції. Агрегатні функції виконують обчислення в стовпцях даних і повертають одне значення. В 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 ₴

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

Доступ! Гра!

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

Доступ! Гра!

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

Доступ! Гра!

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. Натисніть кнопку Запуск Зображення кнопки , щоб виконати запит і відобразити результати в табличному поданні даних.

    Порада.: Зверніть увагу, що «Сума _» додається до початку ім'я поля, ви сума. Щоб змінити заголовок стовпця на зрозуміліше ім'я, наприклад Загальний обсяг доставки, повернутися до режиму конструктора і натисніть кнопку в рядку поле вартість доставки стовпця у сітці макета. Помістіть курсор біля Вартість доставки та введіть слова Загальний обсяг доставки, а потім двокрапку, ось так: загальний обсяг доставки: вартість доставки.

  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

Доступ! Гра!

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

      Загальний обсяг збуту: (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

Доступ! Гра!

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 Online книги.

Функція

Опис

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

Середнє

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

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

Кількість

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

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

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

Максимум

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

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

Мінімум

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

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

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

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

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

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

Сума

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

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

Відхилення

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

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

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

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

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

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

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

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

×