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

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

Обчислювальні поля можна вводити не тільки в перехресних запитах, але й у звичайних запитах на вибірку.
Отже, ми будемо створювати перехресний запит, у якому в рядках виводяться товари, а в стовпцях – відповідні їм щомісячні обсяги продажів. Для цього:
1. Створіть новий запит у режимі Конструктора і додайте в нього таблиці "Товари" (Products), "Замовлення" (Orders) і "Замовлене" (Order Details).
2. Перетягніть поля "КодТовара" (ProductID) і "Марка" (ProductName) таблиці "Товари", а потім поле "ДатаРазмещения" (OrderedDate) таблиці "Замовлення", у перші три стовпці бланка запиту.
3. Виберіть команду меню Запит, Перехресний (Query, Crosstab Query). Заголовок вікна запиту Запит1: на вибір (Select Query: Queryl) зміниться на Запит1: перехресний запит (Crosstab Query: Queryl). Крім того, у бланк запиту буде доданий рядок Перехресна таблиця (Crosstab) і рядок Групова операція (Total), у якій у всіх стовпцях автоматично вводиться операція Групування(Group By).
4. Виберіть у списку чарунки Перехресна таблиця стовпця "КодТовара" значення Заголовки рядків (Row Heading). Виконайте те ж саме для стовпця "Марка". Ці стовпці є необхідними заголовками рядків перехресної таблиці.
5. Виберіть у списку чарунки Групова операція стовпця "ДатаРазмещения" значення Умова. В чарунці Умова вибору цього стовпця введіть вираз<=#31.12.2007#And>=#01.01.2007# для виводу в перехресній таблиці даних за 2007 рік.
Встановіть курсор в чарунці Поле наступного (порожнього) стовпця і введіть наступний вираз:
Обсяг продажів: Sum([Кількість]*[Замовлена].[Ціна])
Поле, що ми в такий спосіб створили, обчислюється. Його значення обчислюється за допомогою описаного виразу, а назву поля ми визначили як "Обсяг продажів". У виразі використовуються поля з таблиць, що включені в запит, однак, зверніть увагу, що самі ці поля в результат запиту не включені. Це означає, що при створенні що обчислювальні поля у вирази можна включати посилання не тільки на поля самого запиту, але і на поля, що не включаються в результат запиту. Важливо, щоб вони були у вихідних таблицях. При посиланні на поле "Ціна" у виразі ми вказали ще ім'я таблиці "Замовлене", а при посиланні на поле "Кількість" не вказували. Вказати ім'я таблиці довелося тому, що поле з ім'ям "Ціна" є присутнім і в таблиці "Товари" і в таблиці "Замовлене". Якщо не вказати у виразі імені таблиці, то Access не може визначити, з якої таблиці брати значення, тому видасть повідомлення про помилку.
6.Виберіть в чарунці Групова операція того ж стовпця значення Виразу(Expression), а потім в чарунці Перехресна таблиця значення Значення (Value). У стовпці "Обсяг продажів" обчислюється загальний обсяг замовлень на кожен товар, що буде підставлятися в осередки результуючої таблиці запиту. Посилання на таблицю "Замовлені" обов'язкова, інакше буде видане повідомлення про помилку Неоднозначне посилання на поле (Ambiguous field reference).
7.Встановіть курсор в чарунці Поле наступного (порожнього) стовпця і введіть Вираз format [Дата Розміщення]; "mmm"). Функція Format() повертає три перші букви місяця від значення дати в стовпці "ДатаРазмешения". За допомогою цієї функції ми створили ще одне поле, що обчислюється, у запиті, причому після закінчення введення цього виразу Access автоматично створює ім'я цього поля "Вираз" (Exprl). Так відбувається кожен раз, коли ми не визначаємо ім'я обчислювального поля. Виберіть зі списку в чарунці Перехресна таблиця цього стовпця значення Заголовки стовпців (Column Heading). Це означає, що даний стовпець запиту буде перетворений у процесі виконання запиту в рядок, що містить заголовки стовпців результуючої таблиці.
8. Натисніть кнопку Запуск. З'явиться результуюча множина записівВарто звернути увагу, що виведена перехресна таблиця має один недолік: розташування стовпців визначається алфавітним порядком їхніх заголовків, а не хронологічною послідовністю. Цієї неприємності можна уникнути. Для цього потрібно задати порядок сортування заголовків стовпців.
Використання фіксованих заголовків стовпців у перехресних запитах
Щоб у побудованому запиті вивести стовпці в порядку календарної послідовності місяців, необхідно задати фіксовані заголовки стовпців:
1. Перейдіть в режим Конструктора і виведіть властивості запиту. У бланку властивостей виводиться властивість Заголовки стовпців (Column Headings), що міститься тільки в перехресних запитах.
2.Введіть короткі назви кожного з місяців року в поле властивості Заголовки стовпців. Необхідно відтворити назви без помилок, у противному випадку дані для неправильно заданих місяців не відображаються. Скорочення потрібно взяти в лапки і розділяти назви крапками з комами. Пробіли не потрібні. По завершенні введення всіх 12 місяців закрийте бланк властивостей.
3. Перейдіть у режим перегляду SQL (мал. 5). Зверніть увагу на використання виразів у полях, що обчислюються, і критерії запиту, а також на використання ключових слів transform і pivot.

Інструкція SQL містить операцію transform, у якій визначають дані, що містяться в таблиці. В операції pivot задаються заголовки стовпців. Після ключового слова in вказуються, якщо є, фіксовані назви стовпців.
У ANSI SQL слова transform і pivot не є зарезервованими словами, а ключове слово in інтерпретується no-різному в Access і ANSI SQL.
4. Натисніть кнопку Запуск для висновку результатів запиту.
Тепер заголовки стовпців перехресної таблиці відповідають календарній послідовності Якщо якийсь стовпець відсутній у перехресній таблиці, перевірте спочатку, чи правильно ви ввели заголовки стовпців. Вони повинні точно співпадати з тими значеннями, що повертає функція Format про ().
5.Виберіть команду Файл, Зберегти як і збережіть запит під ім'ям "Щомісячний виторг від продажів у 2011 році".

Зміна рівня деталізації в запиті
Для створення, зручного для аналізу представлення чи наочного графіка можна скоротити число рядків і стовпців у результатах побудованого запиту. Для цього можна створити перехресний запит, що виводить щоквартальний виторг від продажів по типах товарів. Щоб створити такий запит:
1. Додайте у вже створений запит таблицю "Типи".
2. Замініть стовпці "КодТовара" і "Марка" у запиті стовпцями "КодТипу" і "Категорія" таблиці "Типи". Для цього спочатку знищіть зайві стовпці, а потім перенесіть потрібні стовпці в бланк замовлення. В чарунках Перехресна таблиця, обох стовпців виберіть значення Заголовки рядків.
3. Змініть вираз в стовпці "Вираз" на Format([ДатаРазмещение];"""Квартал ""q"), що визначає заголовки стовпців "Квартал 1",..., "Квартал 4". Кілька лапок потрібно для вказівки того, що слово квартал і пробіл розглядаються як рядок, a q – як формат.
4.Відкрийте бланк властивостей і очистіть вміст властивостей Заголовки стовпців, а потім закрийте бланк. Якщо не знищити фіксовані заголовки, то в перехресній таблиці не буде даних. Щоб прискорити роботу запиту, можна задати в поле властивості Заголовки стовпців чотири фіксованих назви: "Квартал 1,..., "Квартал 4".
5. Натисніть кнопку Запуск для виведення результатів запиту (мал. 8).
Приклад. Відкрити базу даних “Спортивна гімнастика”. Вивести на екран поля “Прізвище”, “Перекладина”, “Кільця”, “Кінь” для спортсменів України.
Висновки.
Запити є основою для більшості форм і звітів, що розробляються в Access. У багатьох випадках прості запити розробляються спеціально ля створення одного звіту чи форми. Запити, які розглянуто у роботі дозволяють не лише відбирати дані у складній системі взаємопов’язаних таблиць, але й виконувати різноманітні зміни у цих таблицях, аналізувати дані різними способами, автоматично сортувати і фільтрувати дані.
Наприклад, в одному із розділів роботи, присвяченій перехресним запитам, описано створення запиту, який виводить дані для аналізу тенденції розвитку, на що витрачається менше п’яти хвилин. Порівнюючи Access з іншими системами, можна відмітити, що ця операція займає в десять разів менше часу, ніж створення еквівалентної електронної таблиці. Хоча за допомогою середовища Excel Сводная таблица. Заповнення перехресних

таблиць простіше, Access все ж таки виграє, коли необхідно опрацювати велику кількість записів.

Самостійна робота № 13

Тема: Звіти. Перетворення даних в наклейки.

План:

1. Створення та внесення змін в поштові наклейки.

2. Друк наклейок.

Література:

Довідник Windows.

Створення поштових наклейок

Звіт з поштовими наклейками відрізняється від інших звітів. У ньому можна друкувати іменні бирки, наклейки для листів, мітки паперових файлів або навіть товарні етикетки. Сфера застосування таких звітів залежить лише від вашої фантазії — для будь-яких даних з таблиці можна надрукувати наклейку або етикетку.

Мал. Зміна властивості Підпис

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

У вікні Властивості відображаються значення властивостей для поточного об'єкту. Щоб відкрити це вікно, двічі клацніть на об'єкті або виберіть команду Від>свойства. Клацання на покажчику запису дозволяє у будь-який момент викликати вікно властивостей звіту. У розділу 13 описувались методи форматування елементів управління і особливості використання намели елементів. У отчети елементи управління додаються аналогичним чином, тому можна скористатися інструкціями, приведеними в розділі 13. У разі потреби ще раз прочитайте потрібний розділ і виконаєте пропоновані там вправи, використовуючи замість форми звіт.

1. Викличте команду Вставка>Отчет.
2. У діалоговому вікні Новий звіт виберіть опцію Поштові наклейки потім із списку, що розкривається, — таблицю Каталоги а потім клацніть на кнопці ОК.
3. У майстрові зберігаються дані про розміри більшості стандартних наклейок. Встановите перемикачі Британська у розділі Система одиниць і На листах у розділі Тип наклейок. Потім виберіть значення Avery у списку Фільтр по виготівникові щоб обмежити кількість варіантів наклейок.
4. Виберіть код товару 5095, як показано на мал. У Access міститься інформація про багато типів наклейок від різних виробників. Якщо відповідної наклейки не знайшлося, клацніть на кнопці Налаштування для введення параметрів наклейки уручну. Клацніть на кнопці Далі.
5. У наступному вікні можна визначити шрифти і інші параметри. Але ви не вносите ніяких змін — тільки клацніть на кнопці Далі.
6. Тепер можна вказати поля, що містять дані, які будуть надруковані на наклейках. Поля імені і адреси каталога додаються таким чином: двічі клацніть на опції Ім'я у списку Доступні поля щоб перенести її у вікно Прототип наклейки. Натисніть клавішу <Enter> для перенесення точки введення на наступний рядок. Двічі клацніть на опції Адресу і ще раз натисніть клавішу <Enter>, потім двічі клацніть на опції Місто введіть символ коми і пропуск. Нарешті, двічі клацніть на опції Область, додайте пропуск, потім виконаєте подвійне клацання на опції Почтовийіндекс щоб створити прототип наклейки, показаний на мал. Клацніть на кнопці Далі.

 

Мал. Наклейки можна вибирати за іменем товару, номеру або розміру

7. Наклейки можна сортувати по будь-якому полю таблиці. Виберіть поле Ім'я і клацніть на кнопці Далі.
8. У останньому вікні майстер привласнить звіту з наклейками базове ім'я, яке змінювати не варто. Клацніть на кнопці Готово і ви зможете проглянути створений звіт (мал. 14.8).
Тепер залишилося лише завантажити в принтер папір і клацнути на кнопці Друк.

Самостійна робота № 14

Тема: Звіти. Створення діаграм

План:

1. Створення діаграми за допомогою Майстра.

2. Редагування діаграми

Література:

Довідник Windows.

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

Найпростіший спосіб створення діаграми в Access – за допомогою майстра, який дозволяє створити один із можливих типів діаграми (мал. 5) (які аналогічні до ти­пів діаграм у Excel). Зокрема, у програмі можна створювати:

• кругові діаграми;

• точкові діаграми;

• діаграми з областями (діаграми площ);

• гістограми (лінійні та стовбцеві).

мал.4

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

Точкова діаграма дозволяє наочно відобразити послі­довність величин (визначених чи обчислених). Маркерами у такій діаграмі виступають точки, які розміщені у декартовій системі координат. Точкові діаграми використову­ють для відображення взаємозв'язку між двома чи декіль­кома величинами.

У діаграмах з областями у ролі маркерів виступають області, зафарбовані різними кольорами. Такого типу діа­грами використовують лише у випадку зв'язаних між со­бою даних.

У гістограмах числові значення подаються смугами. Такий тип діаграм зручно використовувати для порівнян­ня значень, які стосуються певного моменту часу чи окре­мого об'єкта. До різновидів гістограм належать графіки, маркерами яких є лінії, які інтерполюють відповідні зна­чення. Якщо дані не зв'язані між собою, графіки викорис­товувати не рекомендується.

Об'ємні діаграми різних типів відрізняються від пло­щинних тим, що вони побудовані у тривимірному просторі.

Самостійна робота № 15

Тема: Звіти. Групування та сортування даних в звітах.

План:

1. Групування даних в звітах

2. Сортування даних в звітах.

Література:

Дибкова Л.М. ”Інформатика і комп'ютерна техніка”, стор.309-310