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

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

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

Виберіть дію

Способи обчислення суми даних

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

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

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

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

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

Відомості про агрегатні функції

Способи обчислення суми даних

За допомогою агрегатних функцій можна обчислювати суму стовпця чисел у запиті. Агрегатні функції виконують обчислення у стовпці даних і повертають одне значення. У застосунку Access доступні такі агрегатні функції: Sum, Count, Avg (для обчислення середнього значення), Min і Max. Сума даних обчислюється за допомогою додавання до запиту функції Sum, кількість даних — за допомогою функції Count і т. д.

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

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

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

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

Місто

2003

2004

2005

Чернівці

254 556

372 455

467 892

Черкаси

478 021

372 987

276 399

Львів

572 997

684 374

792 571

...

...

...

...

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

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

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

Для отримання загальних відомостей про типи даних див. статтю Змінення типів даних, заданих для поля.

Вгорі сторінки

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

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

Застосунок Access надає кілька способів додавання таких зразків таблиць до бази даних. Можна ввести дані вручну або скопіювати кожну таблицю до програми, в якій використовуються електронні таблиці, наприклад Microsoft Office Excel 2007, після чого імпортувати аркуші до застосунку Access. Можна також перенести дані до текстового редактора, наприклад, програми «Блокнот», та імпортувати дані з текстових файлів.

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

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

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

Категорія

Ляльки

Ігри й мозаїки

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

Відеоігри

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

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

Спортивне спорядження

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

Назва товару

Ціна

Категорія

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

12,95 грн.

Ляльки

Гра на основі C# (Настільна гра для всієї родини)

15,85 грн.

Ігри й мозаїки

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

22,50 грн.

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

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

32,65 грн.

Ігри й мозаїки

Гра Access!

22,95 грн.

Ігри й мозаїки

Комп’ютерні фанати й міфічні створіння

78,50 грн.

Відеоігри

DVD-диск «Вправа для комп’ютерних фанатів!»

14,88 грн.

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

Досконала летюча піца!

36,75 грн.

Спортивне спорядження

Зовнішній накопичувач на гнучких магнітних дисках 5,25" (Масштаб 1:4)

65,00 грн.

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

Нерухома фігурка бюрократа

78,88 грн.

Ляльки

Морок

53,33 грн.

Відеоігри

Створіть свою клавіатуру

77,95 грн.

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

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

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

Дата доставки

Місто призначення

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

14.11.05

15.11.05

Львів

55,00 грн.

14.11.05

15.11.05

Черкаси

76,00 грн.

16.11.05

17.11.05

Черкаси

87,00 грн.

17.11.05

18.11.05

Львів

43,00 грн.

17.11.05

18.11.05

Чернівці

105,00 грн.

17.11.05

18.11.05

Харків

112,00 грн.

18.11.05

19.11.05

Житомир

215,00 грн.

19.11.05

20.11.05

Чернігів

525,00 грн.

20.11.05

21.11.05

Житомир

198,00 грн.

20.11.05

21.11.05

Чернівці

187,00 грн.

21.11.05

22.11.05

Черкаси

81,00 грн.

23.11.05

24.11.05

Львів

92,00 грн.

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

Номер замовлення

Назва товару

Код товару

Вартість одиниці

Кількість

Знижка

1

Створіть свою клавіатуру

12

77,95 грн.

9

5%

1

Нерухома фігурка бюрократа

2

78,88 грн.

4

7,5%

2

DVD-диск «Вправа для комп’ютерних фанатів!»

7

14,88 грн.

6

4%

2

Магічна мікросхема

4

32,65 грн.

8

0

2

Комп’ютерні фанати й міфічні створіння

6

78,50 грн.

4

0

3

Гра Access!

5

22,95 грн.

5

15%

4

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

1

12,95 грн.

2

6%

4

Досконала летюча піца!

8

36,75 грн.

8

4%

5

Зовнішній накопичувач на гнучких магнітних дисках 5,25" (Масштаб 1:4)

9

65,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. У полі Ім’я таблиці введіть ім’я зразка таблиці та натисніть кнопку ОК.

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

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

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

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

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

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

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

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

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

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

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

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

    -або-

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Наприклад, змініть ім’я поля «Ідентифікатор» таблиці «Категорії» на «Ідентифікатор категорії», а ім’я поля таблиці «Замовлення» — на «Номер замовлення». Для таблиці «Відомості про замовлення» змініть ім’я поля на «Ідентифікатор відомостей». Для таблиці «Товари» перейменуйте поле на «Код товару».

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

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

Вгорі сторінки

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

Можна додати рядок підсумків до запиту за допомогою відкриття запиту в поданні таблиці, додавання рядка та вибору потрібної агрегатної функції, наприклад Sum, Min, Max або Avg. У цьому розділі надано інструкції щодо створення базового запиту на вибірку та додавання рядка підсумків. Не обов’язково використовувати зразки таблиць, описані в попередньому розділі.

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

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

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

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

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

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

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

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

    Результати запиту відображаються в поданні таблиці.

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

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

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

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

    -або-

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

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

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

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

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

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

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

Вгорі сторінки

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

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

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

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

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

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

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

Номер замовлення

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

Дата доставки

Місто призначення

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

1

14.11.05

15.11.05

Львів

55,00 грн.

2

14.11.05

15.11.05

Черкаси

76,00 грн.

3

16.11.05

17.11.05

Черкаси

87,00 грн.

4

17.11.05

18.11.05

Львів

43,00 грн.

5

17.11.05

18.11.05

Чернівці

105,00 грн.

6

17.11.05

18.11.05

Харків

112,00 грн.

7

18.11.05

19.11.05

Житомир

215,00 грн.

8

19.11.05

20.11.05

Чернігів

525,00 грн.

9

20.11.05

21.11.05

Житомир

198,00 грн.

10

20.11.05

21.11.05

Чернівці

187,00 грн.

11

21.11.05

22.11.05

Черкаси

81,00 грн.

12

23.11.05

24.11.05

Львів

92,00 грн.

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

Ідентифікатор відомостей

Номер замовлення

Назва товару

Код товару

Вартість одиниці

Кількість

Знижка

1

1

Створіть свою клавіатуру

12

77,95 грн.

9

0,05

2

1

Нерухома фігурка бюрократа

2

78,88 грн.

4

0,075

3

2

DVD-диск «Вправа для комп’ютерних фанатів!»

7

14,88 грн.

6

0,04

4

2

Магічна мікросхема

4

32,65 грн.

8

0,00

5

2

Комп’ютерні фанати й міфічні створіння

6

78,50 грн.

4

0,00

6

3

Гра Access!

5

22,95 грн.

5

0,15

7

4

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

1

12,95 грн.

2

0,06

8

4

Досконала летюча піца!

8

36,75 грн.

8

0,04

9

5

Зовнішній накопичувач на гнучких магнітних дисках 5.25" (Масштаб 1:4)

9

65,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. Двічі клацніть поле, для якого потрібно обчислити суму. Переконайтесь, що поле має тип даних «Число» або «Грошова одиниця». Якщо виконати спробу обчислити суму значень у нечислових полях, наприклад у текстовому полі, відображається повідомлення про помилку Невідповідність типу даних у виразі критерію.

    У разі використання зразків даних двічі клацніть стовпець «Вартість доставки».

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

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

    Рядок Підсумок відображається в сітці конструктора, а рядок Групувати за — у стовпці «Вартість доставки».

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

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

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

  7. Можна зберегти й закрити запит.

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

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

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

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

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

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

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

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

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

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

  7. Змініть значення у другому стовпці клітинки рядка Підсумок на Sum. Функція Sum додає дані продажу для кожної операції.

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

  9. Збережіть запит як Обсяг продажу за день.

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

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

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

Вгорі сторінки

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

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

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

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

Код товару

Назва товару

Ціна

Категорія

1

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

12,95 грн.

Ляльки

2

Гра на основі C# (Настільна гра для всієї родини)

15,85 грн.

Ігри й мозаїки

3

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

22,50 грн.

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

4

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

32,65 грн.

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

5

Гра Access!

22,95 грн.

Ігри й мозаїки

6

Комп’ютерні фанати й міфічні створіння

78,50 грн.

Відеоігри

7

DVD-диск «Вправа для комп’ютерних фанатів!»

14,88 грн.

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

8

Досконала летюча піца!

36,75 грн.

Спортивне спорядження

9

Зовнішній накопичувач на гнучких магнітних дисках 5,25" (Масштаб 1:4)

65,00 грн.

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

10

Нерухома фігурка бюрократа

78,88 грн.

Ляльки

11

Морок

53,33 грн.

Відеоігри

12

Створіть свою клавіатуру

77,95 грн.

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

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

Ідентифікатор відомостей

Номер замовлення

Назва товару

Код товару

Вартість одиниці

Кількість

Знижка

1

1

Створіть свою клавіатуру

12

77,95 грн.

9

5%

2

1

Нерухома фігурка бюрократа

2

78,88 грн.

4

7,5%

3

2

DVD-диск «Вправа для комп’ютерних фанатів!»

7

14,88 грн.

6

4%

4

2

Магічна мікросхема

4

32,65 грн.

8

0

5

2

Комп’ютерні фанати й міфічні створіння

6

78,50 грн.

4

0

6

3

Гра Access!

5

22,95 грн.

5

15%

7

4

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

1

12,95 грн.

2

6%

8

4

Досконала летюча піца!

8

36,75 грн.

8

4%

9

5

Зовнішній накопичувач на гнучких магнітних дисках 5,25" (Масштаб 1:4)

9

65,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)*([Відомості про замовлення].[Вартість одиниці]*[Відомості про замовлення].[Кількість])

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

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

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

    4. Змініть значення у другому стовпці рядка Підсумок на Sum. Функція Sum додає дані продажу для кожної операції.

    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.05

15.11.05

Львів

55,00 грн.

14.11.05

15.11.05

Черкаси

76,00 грн.

16.11.05

17.11.05

Черкаси

87,00 грн.

17.11.05

18.11.05

Львів

43,00 грн.

17.11.05

18.11.05

Чернівці

105,00 грн.

17.11.05

18.11.05

Харків

112,00 грн.

18.11.05

19.11.05

Житомир

215, 00 грн.

19.11.05

20.11.05

Чернігів

525,00 грн.

20.11.05

21.11.05

Житомир

198,00 грн.

20.11.05

21.11.05

Чернівці

187,00 грн.

21.11.05

22.11.05

Черкаси

81,00 грн.

23.11.05

24.11.05

Львів

92,00 грн.

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

Номер замовлення

Назва товару

Код товару

Вартість одиниці

Кількість

Знижка

1

Створіть свою клавіатуру

12

77,95 грн.

9

5%

1

Нерухома фігурка бюрократа

2

78,88 грн.

4

7,5%

2

DVD-диск «Вправа для комп’ютерних фанатів!»

7

14,88 грн.

6

4%

2

Магічна мікросхема

4

32,65 грн.

8

0

2

Комп’ютерні фанати й міфічні створіння

6

78,50 грн.

4

0

3

Гра Access!

5

22,95 грн.

5

15%

4

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

1

12,95 грн.

2

6%

4

Досконала летюча піца!

8

36,75 грн.

8

4%

5

Зовнішній накопичувач на гнучких магнітних дисках 5,25" (Масштаб 1:4)

9

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

Функція

Опис

Використовувані типи даних

Average

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

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

Count

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

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

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

Maximum

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

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

Minimum

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

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

Standard Deviation

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

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

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

Sum

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

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

Variance

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

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

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

Вгорі сторінки

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

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

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

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

×