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

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

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

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

...

...

...

...

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

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

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

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

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

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

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

В 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

Джакарта

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

Гра 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. За допомогою клавіш зі стрілками перейдіть до наступної пустої клітинки заголовка та введіть назву другого поля. (Перейти до наступної клітинки також можна, натиснувши клавішу Tab або двічі клацнувши нову клітинку). Повторюйте цю дію, доки не введете імена всіх полів.

  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

Джакарта

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

Гра 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 додає фразу "Сума_" до початку імені поля, значення якого ви сумуєте. Щоб змінити заголовок стовпця на зрозуміліший, як-от Загальна вартість доставки, поверніться до режиму конструктора та на бланку клацніть клітинку Поле в стовпці "Вартість доставки". Установіть курсор перед фразою Вартість доставки та введіть слова Загальна вартість доставки з двокрапкою. Ось так: Загальна вартість доставки: вартість доставки.

  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

Джакарта

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

Гра 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 в Інтернеті.

Функція

Опис

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

Середнє

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

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

Кількість

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

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

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

Максимум

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

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

Мінімум

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

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

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

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

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

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

Сума

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

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

Відхилення

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

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

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

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

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

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

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

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

×