У цій статті говоримо про поля, що обчислюються в запити Access. У запиті, як і в таблиці, для кожного запису можуть проводитись обчислення з числовими, рядковими значеннями або значеннями дат з використанням даних з одного або кількох полів. Результат обчислення утворює в таблиці запиту нове поле, що обчислюється. На відміну від обчислюваних полів таблиці, поля, що обчислюються, у вихідних таблицях бази даних нових полів не створюють. При кожному виконанні запиту виробляються обчислення з урахуванням поточних значень полів.
У виразах полів, що обчислюються, крім імен полів можуть використовуватися константи і функції. В результаті обробки виразу може виходити лише одне значення.
Завдання 1.У таблиці ТОВАР є поля ЦІНА та СТАВКА_ПДВ, обчисліть ціну з урахуванням ПДВ та порівняйте її з отриманою в обчислюваному полі таблиці Ціна з ПДВ.
- Створіть у режимі конструктора запит на вибірку таблиці ТОВАР. Перетягніть у бланк запиту поля НАІМ_ТОВ, ЦІНА, СТАВКА_ПДВ та Ціна з ПДВ (рис. 4.6).
- Для підрахунку ціни з урахуванням ПДВ створіть поле, що обчислюється, записавши в порожньому осередку рядка Поле (Field) вираз [ЦІНА]+[ЦІНА]*[СТАВКА_ПДВ].
- Для відбору записів зі значенням вище 5000 у полі, що обчислюється, у рядок Умова відбору (Criteria) введіть > 5000
- Після введення виразу система за умовчанням формує ім'я поля, що обчислюється Вираз 1, яке стає заголовком стовпця в таблиці з результатами виконання запиту. Це ім'я вставиться перед виразом [ЦІНА]+[ЦІНА]*[СТАВКА_ПДВ]. Для кожного нового обчислюваного поля у запиті номер виразу збільшується на одиницю. Ім'я обчислюваного поля відокремлюється від виразу двокрапкою. Для зміни імені встановіть курсор миші в полі бланка запиту, що обчислюється, і натисніть праву кнопку миші. У контекстно-залежному меню виберіть Властивості(Properties) поля та в рядок Підпис (Caption) введіть нове ім'я поля ― Ціна з ПДВ1. Тепер у таблиці з результатами виконання запиту в заголовку стовпця, що обчислюється, відобразиться це ім'я. Ім'я поля може бути виправлено безпосередньо у бланку запиту.
- Для відображення результату виконання запиту натисніть кнопку Виконати(Run) у групі Результати(Results). Обчислюване поле таблиці та запиту мають однакові значення.
- Змініть в одному із записів запиту ціну товару. Значення в обох полях, що обчислюються, будуть моментально перераховані.
- Для формування складного вираження у полі, що обчислюється, або умови відбору доцільно використовувати будівельник виразів. Побудовник дозволяє вибрати необхідні у виразі імена полів із таблиць, запитів, знаки операцій, функції. Видаліть вираз у полі, що обчислюється, і використовуйте будівельник для його формування.
- Викличте будівельник виразів (Expression Builder), натиснувши кнопку Будівник(Builder) у групі Налаштування запиту(Query Setup) стрічки Конструктор (Design), або вибравши Побудувати(Build) у контекстно-залежному меню. Курсор миші має бути встановлений попередньо в комірці введення виразу.
- У лівій частині вікна Побудовник виразів(Expression Builder) (рис. 4.7) виберіть таблицю ТОВАР, на якій побудовано запит. Праворуч відобразиться список її полів. Послідовно вибирайте потрібні поля та оператори, подвійним клацаннямвставляючи у вираз. Вираз сформується у верхній частині вікна. Зверніть увагу, будівник перед ім'ям поля вказав ім'я таблиці, якій воно належить, і відокремив його від імені поля знаком оклику.
- Завершіть процес побудови виразу в полі, клацнувши на кнопці ОК.
- Збережіть запит під іменем ― Ціна з ПДВ та закрийте його.
- Виконайте збережений , виділивши його в області навігації та вибравши в контекстному менюкоманду Відкрити (Open).
Завдання 2.У обчислюваних полях та умовах відбору можна використовувати вбудовані функції. У Access визначено понад 150 функцій.
Нехай необхідно вибрати всі накладні, за якими здійснювалося відвантаження у заданому місяці. В НАКЛАДНА дата відвантаження зберігається в полі ДАТА_ОТГ з типом даних Дата/час (Date/Time).
- Створіть у режимі конструктора запит на вибірку таблиці НАКЛАДНА. Перетягніть у бланк поля НОМ_НАКЛ і КОД_СК (рис. 4.8).
- Створіть обчислюване поле в порожньому осередку рядка Поле(Field), записавши туди один із виразів: Format([НАКЛАДНА]![ДАТА_ВІДГР];»mmmm») ― ця функція поверне повну назву місяця
або Format([НАКЛАДНА]![ДАТА_ВІДГР];»mm») ― ця функція поверне номер місяця. - Для відбору накладних, виписаних у заданому місяці, в полі, що обчислюється, в рядок Умова відбору (Criteria) введіть назву місяця, наприклад березень (мал. 4.8), або номер місяця, наприклад 3 відповідно до параметра у функції Format.
- Виконайте запит, натиснувши кнопку Виконати(Run) у групі Результати(Results) на вкладці стрічки Робота із запитами | Конструктор(Query Tools | Design).
- Запишіть у обчислюваному полі функцію Month (НАКЛАДНА! ДАТА_ОТГ), і переконайтеся, що ця функція повертає виділений з дати номер місяця.
- Для вибору всіх рядків, що належать до другого кварталу, в рядок Умова відбору (Criteria) введіть оператор Between 4 And 6, який визначає, чи попадає значення виразу у вказаний інтервал.
- Запишіть у обчислюваному полі вираз MonthName (Month (НАКЛАДНА! ДАТА_ОТГ)) і переконайтеся, що функція MonthName перетворює номер місяця в його повну назву.
Для закріплення дивимось відеоурок.
Рядок "Підсумок" в Access дозволяє швидко переглядати зведену інформацію про дані в стовпцях таблиці. Наприклад, якщо додати рядок "Підсумок" до таблиці з відомостями про покупки, можна відобразити суму продажу, загальне числоодиниць товару чи кількість куплених товарів.
Примітка:Щоб відобразити суму значень стовпця, необхідно встановити для типу даних стовпця значення "Числовий", "Дробовий" або "Грошовий". Для нечислових стовпців можна вибрати лише підсумковий тип "Кількість значень".
Додавання рядка "Разом"
Вибір типу підсумку
Після додавання рядка підсумківви вибираєте тип підсумкового значення, яке відображається для кожного стовпця. Наприклад, агрегати, такі як суми, можуть відображатися, якщо вибрано тип даних "число", "десятковий" або "грошовий". Підрахунок значень можливий, якщо тип даних є текстовим значенням.
Давайте зробимо так, щоб у цьому прикладі таблиця відображала суму значень у стовпцях Ціна покупкиі Куплені товарита загальна кількість для стовпця Позиція, як показано на зображенні нижче.
Як працює "Сума" та інші агрегатні функції
Агрегатні функції виконують обчислення зі стовпцями даних та повертають один результат. Вони можуть бути корисні, якщо вам потрібно розрахувати єдине значення, наприклад, суму або середнє. Слід пам'ятати, що агрегатні функціїзастосовуються до стовпців даних. Це може здатися очевидним, але при розробці та використанні баз даних ви зазвичай приділяєте увагу рядкам даних та окремим записам, щоб користувачі могли вводити дані в поле, переміщати курсор праворуч або ліворуч для заповнення наступного поля і т.д. І навпаки, ви використовуєте агрегатні функції, приділяючи основну увагу групам записів у шпальтах.
Припустимо, що ви використовуєте Access для зберігання та відстеження даних про продаж. За допомогою агрегатних функцій можна вважати кількість проданих товарів в одному стовпці, загальну суму продажів - у другому та середній обсяг продажів для кожного товару - у третьому.
У нижченаведеній таблиці описані агрегатні функції Access, які доступні в рядку "Підсумок". Пам'ятайте, що в Access є інші агрегатні функції, але вони використовуються в запитах.
Функція | Опис | Типи даних, що підтримуються | Доступна у рядку "Підсумок"? |
|---|---|---|---|
|
Обчислює середнє значення стовпця. Стовпець повинен містити числові чи грошові величини чи значення дати чи часу. Функція ігнорує пусті значення. |
|||
|
Кількість значень |
Підраховує кількість елементів у стовпці. |
Всі типи даних, за винятком складних скалярних даних, що повторюються, таких як стовпець багатозначних списків. Додаткові відомості про багатозначні списки можна знайти у статті рекомендації щодо створення та видалення багатозначних полів у посібниках, присвячених багатозначним полям. |
|
|
Максимальне значення |
Повертає елемент із найбільшим значенням. Для текстових даних найбільшим буде останнє за алфавітом значення, причому Access не враховує регістр. Функція ігнорує пусті значення. |
, "Дата та час" |
|
|
Мінімальне значення |
Повертає елемент із найменшим значенням. Для текстових даних найменшим буде перше за алфавітом значення, причому Access не враховує регістр. Функція ігнорує пусті значення. |
"Числовий", "Дійсний", "Грошовий", "Дата і час" |
|
|
Стандартне відхилення |
"Числовий", "Дійсно", "Грошовий" |
||
|
Підсумовує елементи у стовпці. Підходить лише для числових та фінансових даних. |
"Кількість", "Дійсно", "Грошовий" |
||
|
Дисперсія |
Обчислює статистичну дисперсію всім значень в стовпці. Підходить лише для числових та фінансових даних. Якщо таблиця містить менше двох рядків, Access повертає пусте значення. Докладніше про функцію "Дисперсія" див. у наступному розділі . |
"Числовий", "Дійсно", "Грошовий" |
Додаткові відомості про функції "Стандартне відхилення" та "Дисперсія"
Функції Стандартне відхиленняі Дисперсіярозраховують статистичні значення. Зокрема, вони застосовуються для значень, які лежать в околиці свого середнього значення та підпорядковуються закону нормального розподілу (перебувають на кривій гауса).
Припустимо, що ви випадково вибрали 10 інструментів, виготовлених на одному верстаті, і виміряли їхню міцність на злам для перевірки верстата та контролю якості. Якщо ви розрахуєте середнє значення міцності на злам, то побачите, що для більшої частини інструментів міцність на злам близька до середнього значення, але є інструменти з більшими і меншими показниками. Проте якщо ви розрахуєте лише середнє значення міцності на злам, цей показник не дасть вам жодної інформації про ефективність контролю якості, оскільки кілька незвичайно міцних чи тендітних інструментів можуть збільшити або зменшити середнє значення.
Функції варіативностіі стандартних відхиленьпозначають цю проблему, вказуючи, наскільки близькі значення середнього. Для критичної сили менші числа, що повертаються однією з функцій, вказують на те, що ваші виробничі процеси працюють нормально, тому що деякі з них мають обмежений ступінь, тому що вище або нижче від середнього.
Докладний описдисперсії та стандартного відхилення виходить за рамки цієї статті. Додаткові відомості про ці функції можна знайти на веб-сайтах, присвячених статистиці. При використанні функцій Дисперсіяі Стандартне відхиленняпам'ятайте про такі правила.
У будь-якому звіті Access, який містить числа, можна використовувати підсумки, середні значення, відсотки або суми з накопиченням, щоб зробити дані більш зрозумілими. У цій статті розповідається, як додати такі елементи до звіту.
В цій статті
Типи агрегатів, які можна додати до звіту
У таблиці наведено типи агрегатних функцій у Access, які можна додати до звіту.
Обчислення | Опис | Функція |
|---|---|---|
|
Підсумовує елементи у стовпці. |
||
|
Визначає середнє значення всіх елементів шпальти. |
||
|
Підраховує кількість елементів у стовпці. |
||
|
Максимальне значення |
Повертає елемент, що має найбільше (числове або алфавіту) значення в стовпці. |
|
|
Мінімальне значення |
Повертає елемент, що має найменше (числове або алфавіту) значення в стовпці. |
|
|
Стандартне відхилення |
Показує, наскільки значення у стовпчику відхиляються від середнього значення. |
|
|
Дисперсія |
Обчислює дисперсію всім значень в стовпці. |
Додавання суми або іншого агрегату в режимі макету
Режим макета надає найбільше швидкий спосібдодавання сум, середніх значень та інших агрегатів до звіту.
Дані(Control Source) вираз, який виконує потрібне обчислення. Якщо у звіті є рівні угруповання, Access також додасть текстове поле, яке виконує такі ж обчислення у кожному розділі примітки групи.
Додаткові відомості про створення рівнів угруповання у звітах див. у статті Створення звіту з групуванням або зведеного звіту.
Додавання суми або іншого агрегату в режимі конструктора
Конструктор дозволяє точніше налаштовувати розміщення та зовнішній виглядпідсумкових значень. У згрупованих звітах можна помістити підсумки та інші агрегати у заголовок чи примітку кожної групи. Агрегати рівня звіту можна помістити в заголовок або колонтитул звіту.
Access додає текстове поле в колонтитул звіту і задає значення його властивості Дані(Control Source) вираз, який виконує потрібне обчислення. Якщо у звіті є рівні угруповання, Access також додасть текстове поле, яке виконує такі ж обчислення у кожному розділі примітки групи. Якщо для якості Сума із накопиченням(Running Sum) встановлено значення Для всього, то загальне підсумкове значення можна повторити у колонтитулі звіту. Створіть у ньому поле і задайте як значення його властивості Дані(Control Source) ім'я поля, в якому обчислюється сума із накопиченням, наприклад =[СумаЗамовлення].
Люди! Допоможіть зробити найпростіший запит. Є таблиця: Таблиця1 формату: Найменування замовлення | сума | Загальна сума всіх замовленьперший 10р. другий 20р.третій 30р.Скажіть як мені скласти рядки у стовпці Сума? Щоб вивести результат "Загальна сума всіх замовлень" = 60р. І таблиця згодом дописуватиметься... тобто. з'явиться і 100й замовлення... відповідно у 100го замовлення своя сума. Потрібно щоб "Загальна сума всіх замовлень" перераховувалася автоматично. Заздалегідь дякую за допомогу.
8 відповідей
За вказаними даними це неможливо. Чим визначається сортування рядків у таблиці?Ось в Excel це просто робиться. Наприклад команда = СУМ(A1: A100) - тобто. отримаємо суми по 100 осередках таблиці. Чому ж у БД такого не зробити? Кожному найменуванню замовлення (текст) відповідає відповідний номер (число). Власне ці числа (номери замовлень) йдуть по порядку. Тобто. приклад:1 | сумка | 100р. |2 | покришка | 50р. |...100 | книга | 60р. |...Як мені порахувати загальну суму всіх замовлень(1...100...)?
mivang, В Excel існує фізичний порядок записів у таблиці. Який визначає, що вище та що нижче. У реляційних БД такого порядку не існує. Порядок визначається заданим у запиті сортуванням, і за її відсутності може бути будь-яким. Хочете щоб питання мало сенс - чітко вкажіть критерій, на основі якого можна встановити порядок дотримання записів. У наведеному прикладі це, наприклад, номер рядка. Однак у структурі показаної таблиці такого поля немає.
Хочете щоб питання мало сенс - чітко вкажіть критерій, на основі якого можна встановити порядок дотримання записів. У наведеному прикладі це, наприклад, номер рядка. Однак у структурі показаної таблиці такого поля немає.У таблиці є поле №замовлення. Тобто. загальний вигляд приблизно такий: № замовлення | Найменування замовлення | Сума замовлення | 1 | сумка | 10rur |2 | krushka | 20rur |3 | banka | 30rur |... | ... |***|Тепер є прив'язка? Або я знову чогось не зрозумів ... : (Як знайти загальну суму всіх замовлень?
Тепер є прив'язка?Так.
SELECT Таблиця1.[№ замовлення], Таблиця1.[Найменування замовлення], Таблиця1.[Сума замовлення], Sum(q1.[Сума замовлення]) AS [Загальна сума всіх замовлень]FROM (SELECT t1.[№ замовлення], t1. [Найменування замовлення], t1.[Сума замовлення], t2.[№ замовлення] FROM Таблиця1 AS t1, Таблиця1 AS t2 WHERE (((t1.[№ замовлення])>=.[№ замовлення]))) AS q1 INNER JOIN Таблиця1 ON q1.t1.[№ замовлення] = Таблиця1.[№ замовлення]GROUP BY Таблиця1.[№ замовлення], Таблиця1.[Найменування замовлення], Таблиця1.[Сума замовлення];SELECT Таблиця1.[№ замовлення], Таблиця1.[Найменування замовлення], Таблиця1.[Сума замовлення], Sum(q1.[Сума замовлення]) AS [Загальна сума всіх замовлень] FROM [SELECT t1.[№ замовлення], t1. [Найменування замовлення], t1.[Сума замовлення], t2.[№ замовлення] FROM Таблиця1 AS t1, Таблиця1 AS t2 WHERE (((t1.[№ замовлення])>=.[№ замовлення]))]. AS q1 INNER JOIN Таблиця1 ON q1.t1.[№ замовлення] = Таблиця1.[№ замовлення]GROUP BY Таблиця1.[№ замовлення], Таблиця1.[Найменування замовлення], Таблиця1.[Сума замовлення]; Виводиться помилка:Неприпустиме використання дужок із ім'ям "SELECT t1.[№ замовлення". :((В чому проблема?Додано через 3 хвилини та 33 секунди
Народ! Готовий вислати БДку. Якщо зробите. З мене оплата у WM. ДУЖЕ ТЕРМІНОВО (протягом 1 години) Припустимо, у нас є наступна таблиця:таблиця: tblOrdersполе тип idOrders ЛічильникfldName ТекстовийfldSumma ГрошовийТоді запит буде приблизно таким: SELECT Sum(tblOrders.fldSumma) AS FROM tblOrders; Sum-fldSumma це псевдонім виразу. У результаті ми отримуємо таблицю з одним осередком. Ось для роз'яснення написав приклад коду на VS 2005, без обробки винятків, щоб було зрозуміліше. Для початку потрібно створити проект і додати туди кнопку GetSumButtonта текстове поле SumTextBoxтобто змінити їх імена. Ім'я форми замінити на MainFormта замінити її код нижчестоящим.