292.1. ЗамовленняНаМаксимальнуСуму – для відображення даних замовлення на максимальну суму;
292.2. ДесяткаОсновнихПостачальників – для відображення десяти постачальників з максимальними загальними сумами постачань (створивши перед цим запит-джерело даних РейтингПостачальників);
292.3. ДесяткаОсновнихКлієнтів – для відображення десяти клієнтів з максимальними загальними сумами замовлень (створивши перед цим запит-джерело даних РейтингКлієнтів);
292.4. ДесяткаОсновнихТоварів – для відображення десяти товарів з максимальними загальними сумами замовлень (створивши перед цим запит-джерело даних РейтингТоварів);
Створення перехресних запитів.
293. Створіть перехресний (підсумковий по рядках і стовпцях) запит АналізСумЗамовленьТоварівЦьогоРокуПоМісяцях для відображення щомісячних сум замовлень кожного товару (в рядках звіту мають вказуватися дані замовлень окремих товарів, а в стовпцях - дані замовлень по кожному місяцю). Для цього:
293.1. Самостійно створіть запит-джерело даних ДаніЗамовленьТоварівЦьогоРоку, що відбирає дані замовлень поточного року з аналогічною структурою до запиту ДаніЗамовленьТоварівЗаПеріод (для відбору даних створіть поле для обчислення року від дати замовлення та вкажіть для нього умову відбору Year(Date()));
293.2. Створіть заданий перехресний запит за допомогою майстра, виконавши наступні дії:
293.2.1. Завантажте майстер створення перехресних запитів, натиснувши кнопку Создать та обравши пункт Перекресный запрос у вікні Новый запрос;
293.2.2. На першому кроці майстра оберіть запит ДаніЗамовленьТоварівЦьогоРоку для формування джерела даних перехресного запиту;
293.2.3. На другому кроці майстра перемістіть з списку доступних в список обраних поля НазваТовару та МаркаТовару для формування заголовків рядків;
293.2.4. На третьому кроці майстра оберіть поле ДатаЗамовлення для формування заголовків стовпців;
293.2.5. На четвертому кроці майстра оберіть зі списку інтервал групування Месяц для поля-заголовка стовпця;
293.2.6. На п'ятому кроці майстра оберіть поле Сума та групову функцію Сумма для здійснення підрахунків на перетині рядків і стовпців, оскільки запит повинен знаходити загальну щомісячну суму замовлень по кожному товару;
293.2.7. На останньому кроці майстра задайте назву запиту та завершіть його створення;
293.3. Переконайтеся в дієздатності створеного запиту;
293.4. Перегляньте текст сформованого запиту в режимах конструктора та SQL. Обгрунтуйте структуру всіх частин запиту в цих режимах.
294. Самостійно створіть перехресні запити (призначення кожного запиту відповідає його назві):
Завершальний етап заняття. Повторення вивченого матеріалу.
295. Стисніть створену БД;
296. Завершіть роботу Access;
297. Віднайдіть створену БД та заархівуйте її вміст;
298. Збережіть створений архів на гнучкому диску в двох екземплярах.
Контрольні запитання.
299. Що позначає символ * в першому рядку образів таблиць джерела даних запиту?
300. Як створити, перемістити чи видалити поля бланку запиту?
301. Які групові операції можна виконати над полями при конструюванні підсумкових запитів? Як кожна з цих операцій описується в режимі SQL?
302. Які параметри об'єднань можна встановлювати між таблицями джерела даних? Який з цих параметрів встановлюється автоматично? Де описуються параметри об'єднань в режимі SQL?
303. Для яких запитів результати виконання відрізняються від результатів перегляду в режимі таблиці?
304. Які з властивостей запиту, полів та таблиць джерела даних відображаються в режимі SQL, а які – ні? Чому?
305. Чим відрізняється перехресний запит від підсумкового? Які обмеження накладаються на джерело даних перехресного запиту? Які обмеження накладаються на структуру перехресного запиту?
Лабораторна робота № 11.
Тема.
Створення та використання запитів на корегування даних.
Мета.
Формування вмінь та навичок.
Підготовчий етап заняття. Актуалізація знань.
306. Віднайдіть на гнучкому диску збережений архів Sklad.rar та розархівуйте його вміст у власну папку в папці Мои документы.
307. Завантажте Access, відкрийте розроблену раніше БД Sklad.
Розробка запитів на створення таблиць. Використання будівничого виразів при конструюванні запитів.
308. Створіть запит СтворенняТиповоїПлатіжноїВідомостіВідділу для генерації таблиці ТиповаПлатіжнаВідомістьВідділу, що має містити алфавітний списком працюючих співробітників вказаного відділу з зазначенням відповідних окладів, утримань (прибуткового податку, профспілкового внеску, внеску до пенсійного фонду) та сум до видачі. Для цього:
308.1.Розпочніть створення запиту в режимі конструктора та сформуйте його джерело даних з таблиць Відділи та Співробітники;
308.2.Перетворіть створюваний запит на вибірку в запит на створення таблиці, обираючи в пункті головного меню Запрос підпункт Создание таблицы…. У вікні Создание таблицы, що з'явиться на екрані, вкажіть назву створюваної таблиці ТиповаПлатіжнаВідомістьВідділу та натисніть кнопку OK;
308.3.В перший та другий стовпці бланку запиту перетягніть відповідно поля ПІБ та Оклад з таблиці Співробітники;
308.4.Для першого стовпця встановіть ознаку сортування за зростанням;
308.5.Оскільки прибутковий податок вираховується за формулою де P – прибутковий податок, H – нарахована сума, то для автоматичного підрахунку прибуткового податку відносно окладу введіть в рядок Поле третього стовпця бланку запиту вираз Прибутковий податок: Round(IIf(Оклад<=17; 0; IIf(Оклад<=85; 0,1*(Оклад-17); IIf(Оклад<=170; 6,8+0,15*(Оклад-85); IIf(Оклад<=1020; 19,55+0,2*(Оклад-170); 189,55+0,3*(Оклад-1020)))));2) за допомогою будівничого виразів, виконуючи наступні дії:
308.5.1. В контекстному меню рядка Поле третього стовпця бланку запиту оберіть пункт Построить... для виклику вікна будівничого виразів (див. мал. 12);
308.5.2. В полі для редагування виразу введіть заголовок стовпця Прибутковий податок:;
308.5.3. Для введення у вираз функції Round в полі 3 відкрийте папку Функции, а в ній – папку Встроенные функции; в полі 4 оберіть категорію функцій Математические; серед математичних функцій оберіть у полі 5 функцію Round та натисніть кнопку Вставить. Обрана функція вставиться у полі 1 в положення курсора;
308.5.4. Для введення вкладеної функції виділіть у полі 1 перший аргумент вставленої функції Round та вставте замість нього функцію IІf з категорії Управление;
308.5.5. Назву поля Оклад введіть безпосередньо з клавіатури або вставте з полів нижньої частини будівника виразів, відкривши в полі 3 папку Таблицы і вкладену папку Співробітники та відмітивши поле таблиці ПІБ у полі будівничого 4;
308.5.6. Самостійно завершіть формування виразу, вводячи оператори безпосередньо з клавіатури чи за допомогою кнопок операторів 2;
308.5.7. Для запису сформованого виразу у бланк запиту натисніть кнопку OK;
1 – Поле для редагування виразу;
2 – кнопки операторів;
3 – папки об'єктів, констант, функцій, операторів та загальних виразів;
4 – елементи чи категорії елементів обраної папки;
5 – значення обраного елемента в двох попередніх полях.
мал. 12. Структура будівничого виразів.
308.6.В рядок Поле четвертого стовпця введіть вираз для обчислення профспілкового внеску (1% з нарахувань співробітника);
308.7.В рядок Поле п'ятого стовпця бланку запиту введіть вираз для обчислення внеску до пенсійного фонду, що розраховується згідно формули де V – внесок до пенсійного фонду, H – нарахована сума;
308.8.В рядок Поле шостого стовпця самостійно введіть вираз для обчислення сум до видачі, як різниці між відповідними нарахованими і утриманими сумами;
308.9.Для формування типової платіжної відомості лише для співробітників окремого відділу в сьомий стовпець бланку запиту перетягніть поле НазваВідділу з таблиці Відділи, приховайте його вивід на екран в режимі таблиці та вкажіть параметр умови відбору [Введіть назву відділу];
308.10. Перетягніть у восьмий та дев'ятий стовпці відповідно поля ДатаВлаштування та ДатаЗвільнення з таблиці Співробітники. Приховайте їх вивід на екран в режимі таблиці та вкажіть коректні умови відбору для аналізу даних лише працюючих співробітників;
308.11. У вікні властивостей числових полів запиту, що виводяться на екран, вкажіть потрібний формат поля та число десяткових знаків;
308.12. Збережіть запит та завантажте його на виконання. Після виконання запиту перегляньте вміст створеної таблиці ТиповаПлатіжнаВідомістьВідділу. Перегляньте структуру створеної таблиці в режимі конструктора.
309. Самостійно створіть запити:
309.1.СтворенняПлатіжноїВідомостіПреміїЗаСтаж – для генерації таблиці ПлатіжнаВідомістьПреміїЗаСтаж з алфавітним списком працюючихспівробітників та відповідними сумами нарахованої премії за стаж (за кожен рік стажу – п'ять гривень);
309.2.СтворенняПлатіжноїВідомостіПреміїЗаЗамовленняПеріоду – для генерації таблиці ПлатіжнаВідомістьПреміїЗаЗамовленняПеріоду з алфавітним списком працюючих співробітників та відповідними сумами нарахованої премії за оформлені замовлення (1% від загальної суми оформлених замовлень) протягом вказаного періоду.
Створення запитів на поновлення даних таблиць.
310. Доповніть таблицю ПунктиЗамовлень цілочисельним полем ПроцентЗнижки, встановивши для нього значення по замовчуванню рівним 0. Для сформованих раніше записів цієї таблиці автоматично внесіть в створене поле значення 0 при відсутності в ньому будь-яких значень. Для цього створіть запит ІніціалізаціяПроцентуЗнижкиВПунктахЗамовлень, виконуючи наступні дії:
310.1.Розпочніть створення запиту в режимі конструктора та сформуйте його джерело даних з таблиці ПунктиЗамовлень;
310.2.Перетворіть створюваний запит на вибірку в запит на поновлення даних, обираючи в пункті головного меню Запрос підпункт Обновление;
310.3.В перший стовпець бланку запиту перетягніть поле ПроцентЗнижки з таблиці ПунктиЗамовлень;
310.4.Для редагування під час виконання запиту лише записів з відсутнім значенням в полі ПроцентЗнижки вкажіть дляпершого стовпця бланку запиту умову відбору Is Null;
310.5.Для автоматичного внесення в поля ПроцентЗнижки відібраних записів значення 0 введіть в рядку Обновление першого стовпця запиту 0;
310.6.Збережіть створений запит та переконайтеся в його дієздатності. Перегляньте та обгрунтуйте структуру запиту в режимі SQL.
311. Створіть запит НарахуванняПроцентуЗнижкиВПунктахЗамовленьЗаПеріод для автоматичного заповнення поля ПроцентЗнижки в записах таблиці ПунктиЗамовлень, що відповідають оформленим замовленням протягом вказаного періоду згідно наступної шкали:
, де P – процент знижки, H – сума пункту замовлення. Для цього:
311.1.Розпочніть створення запиту в режимі конструктора, сформуйте його джерело даних з таблиць ЗаголовкиЗамовлень та ПунктиЗамовлень і перетворіть його в запит на поновлення даних;
311.2.В перший стовпець бланку запиту перетягніть поле ПроцентЗнижки з таблиці ПунктиЗамовлень;
311.3.В рядку Обновление першого стовпця, використовуючи будівничого виразів, введіть вираз для поновлення проценту знижки згідно шкали: IІf([Кількість]*[Ціна]<=10; 0; IІf(([Кількість]*[Ціна]<=100; 3; 5));
311.4.Для аналізу пунктів, що стосуються лише оформлених замовлень протягом вказаного періоду, перетягніть в другий стовпець бланку запиту поле ДатаЗамовлення з таблиці ЗаголовкиЗамовлень та вкажіть для нього умову відбору Between [Введіть початкову дату] and [Введіть кінцеву дату];
311.5.Збережіть створений запит та переконайтеся в його дієздатності.
312. Самостійно створіть запити:
312.1.НарахуванняСвятковогоПроцентуЗнижкиВПунктахЗамовлень – для автоматичного встановлення п'ятипроцентної знижки в пунктах замовлень, що оформлені 1 січня, 7 січня, 8 березня, 9 травня, 24 серпня будь-якого року;
312.2.ПідвищенняОкладівНаДесятьПроцентів – для автоматично підвищення всіх окладів працюючих співробітників на 10%.
Створення запитів на доповнення даних таблиць.
313. Створіть запит СтворенняЗаголовкаЗамовленняЗаЗразком для створення в таблиці ЗаголовкиЗамовлень нового запису з даними обраного запису та активною датою замовлення. Для цього:
313.1.Розпочніть створення запиту в режимі конструктора та сформуйте його джерело даних з таблиці ЗаголовкиЗамовлень;
313.2.Перетворіть створюваний запит на вибірку в запит на доповнення таблиці, обираючи в пункті головного меню Запрос підпункт Добавление.... У вікні Добавление, що з'явиться на екрані, вкажіть назву таблиці для дозапису даних ЗаголовкиЗамовлень та натисніть кнопку OK;
313.3.Для копіювання даних співробітника та клієнта з обраного замовлення в новий запис перетягніть в перший та другий стовпці бланку запиту відповідно поля КодСпівробітника та КодКлієнта з таблиці ЗаголовкиЗамовлень;
313.4.Для автоматичного встановлення в полі ДатаЗамовлення активної дати при створенні нового запису введіть для третього стовпця в рядку Поле вираз Date(), а в рядку Добавление оберіть зі списку поле ДатаЗамовлення;
313.5.Для доповнення таблиці даними лише обраного замовлення перетягніть в четвертий стовпець бланку запиту поле КодЗамовлення, очистіть для нього значення в рядку Добавление та вкажіть умову відбору [Введіть код замовлення];
313.6.Збережіть створений запит та переконайтеся в його дієздатності. Перегляньте та обгрунтуйте структуру запиту в режимі SQL.
314. Самостійно створіть запит СтворенняЗаголовкаПостачанняЗаЗразком для створення в таблиці ЗаголовкиПостачань нового запису з даними обраного запису та активною датою оформлення постачання.
Створення запитів на знищення записів таблиць.
315. Створіть запит ЗнищенняЗайвихТоварів для автоматичного знищення у таблиці Товари записів з даними товарів, що не постачалися і не замовлялися. Для цього:
315.1.В схемі даних підкорегуйте зв'язки між таблицею Товари та таблицями ПунктиПостачань і ПунктиЗамовлень так, щоб заборонити каскадне знищення даних (для уникнення вилучення товарів, дані яких використовуються в постачаннях чи замовленнях);
315.2.Розпочніть створення запиту в режимі конструктора та сформуйте його джерело даних з таблиці Товари;
315.3.Перетворіть створюваний запит на вибірку в запит на знищення записів, обираючи в пункті головного меню Запрос підпункт Удаление;
315.4.Для знищення записів саме з таблиці Товари перетягніть в перший стовпець бланку запиту символ * (позначення всіх полів) з її праобразу у верхній частині вікна;
315.5.Збережіть створений запит та переконайтеся в його дієздатності. Перегляньте та обгрунтуйте структуру запиту в режимі SQL.
316. Самостійно створіть запити на знищення записів ЗнищенняЗайвихПунктівЗамовлень (для знищення тих записів в таблиці ПунктиЗамовлень, в яких кількість не вказана або рівна нулю), ЗнищенняЗайвихПунктівПостачань (для знищення тих записів в таблиці ПунктиПостачань, в яких кількість не вказана або рівна нулю),, ЗнищенняЗайвихПостачальників, ЗнищенняЗайвихКлієнтів (заборонивши перед цим відповідні каскадні знищення в схемі даних).
Керування запитами на корегування з головної кнопкової форми.
317. Для впорядкування всіх запитів на корегування даних забезпечте їх виклик безпосередньо з головної кнопкової форми. Для цього:
317.1.В режимі конструктора доповніть набір вкладок форми Головна новою вкладкою Корегування даних. За допомогою пункту контекстного меню набору вкладок Последовательность вкладок... розташуйте її перед вкладкою Довідники;
317.2.Для завантаження кожного запиту на корегування створіть на цій вкладці відповідні кнопки, обираючи у вікні Создание кнопок майстра створення кнопок категорію Разное та дію Выполнить запрос;
317.3.Згрупуйте кнопки за типом операцій корегування, кожну групу кнопок помістіть в окремий прямокутник;
317.4.Збережіть зміни структури головної кнопкової форми та переконайтеся в її дієздатності.
Завершальний етап заняття. Повторення вивченого матеріалу.
318. Стисніть створену БД;
319. Завершіть роботу Access;
320. Віднайдіть створену БД та заархівуйте її вміст;
321. Збережіть створений архів на гнучкому диску в двох екземплярах.
Контрольні запитання.
322. У чому полягає відмінність між запитами на вибірку і запитами на корегування?
323. У чому полягає відмінність між дією запитів на корегування та самостійним корегуванням даних?
324. Які різновиди запитів на вибірку і запитів на корегування використовуються в Access?
325. Як різні варіанти запитів на корегування записуються в режимі SQL?
326. Які поля містить вікно будівничого виразів? Яке їх функціональне призначення?
327. Як використовуються параметри зв'язків між таблицями при виконанні запитів на вилучення?
328. Як здійснюється керування запитами на корегування у розроблених додатках?
Лабораторна робота № 12.
Тема.
Створення звітів за допомогою майстра.
Мета.
Формування вмінь та навичок.
Підготовчий етап заняття. Актуалізація знань.
329. Віднайдіть на гнучкому диску збережений архів Sklad.rar та розархівуйте його вміст у власну папку в папці Мои документы.
330. Завантажте Access, відкрийте розроблену раніше БД Sklad.
331. Перейдіть на закладку Отчеты.
Створення звітів на основі даних таблиць.
332. Створіть звіт АлфавітнийСписокВідділів для відображення відсортованого за зростанням списку назв відділів з зазначенням їх місця розташування за допомогою майстра, виконуючи наступні дії:
332.1. Запустіть майстер створення звітів одним з двох способів: завантажте ярлик Создание отчетов с помощью мастера, або натисніть кнопку Создать, оберіть в списку варіант створення звіту Мастер отчетов та натисніть кнопку OK;
332.2. На першому кроці майстра сформуйте перелік полів, що будуть відображатися в звіті. Для цього оберіть в списку Таблицы и запросы таблицю Відділи та перенесіть з списку доступних полів в список обраних полів всі поля, призначені для відображення даних звіту (в даному випадку – це поля НазваВідділу та МісцеРозташування). Після формування списку обраних полів натисніть кнопку Далее;
332.3. На другому кроці майстра не формуйте рівні групування, оскільки в звіті має відображатися звичайний список відділів, а лише натисніть кнопку Далее;
332.4. На третьому кроці оберіть поле НазваВідділу, згідно якого мають сортуватися дані звіту, вкажіть порядок сортування даних обраного поля за зростанням, встановивши кнопку-перемикач біля списку поля в положення По возрастанию та натисніть кнопку Далее;
332.5. На четвертому кроці майстра оберіть на власний розсуд макет звіту, встановіть книжну орієнтацію сторінки та натисніть кнопку Далее;
332.6. На п'ятому кроці майстра оберіть Строгий стиль оформлення звіту та натисніть кнопку Далее;
332.7. На останньому кроці майстра введіть назву звіту та натисніть кнопку Готово;
332.8. Перегляньте отриманий звіт. Звідки майстер створення звітів скопіював текст заголовків полів?
Створення звітів на основі запитів.
333. Створіть звіт АлфавітнийСписокСпівробітників для перегляду та друку алфавітного списку співробітників з зазначенням дати народження та віку. Для цього:
333.1. Розпочніть створення звіту за допомогою майстра;
333.2. Оскільки всі необхідні дані для формування звіту містяться лише в запиті АлфавітнийСписокСпівробітників, то на першому кроці майстра, формуючи перелік полів для відображення в звіті, виберіть назву саме цього об'єкта в списку Таблицы и запросы та перенесіть з списку доступних в список обраних всі його поля;
333.3. На другому кроці майстра не формуйте рівні групування;
333.4. На третьому кроці оберіть поле ПІБ, згідно якого мають сортуватися дані звіту та вкажіть порядок сортування даних обраного поля за зростанням;
333.5. На четвертому кроці майстра оберіть відмінний від попереднього макет звіту та встановіть книжну орієнтацію сторінки;
333.6. На п'ятому кроці майстра оберіть відмінний від попереднього стиль оформлення звіту;
333.7. Самостійно завершіть створення звіту та переконайтеся в його дієздатності.
Групування даних в звітах.5
334. Створіть звіт АсортиментВиробниківТоварів для відображення асортименту товарів кожного зареєстрованого в системі виробника. Для цього:
334.1. Розпочніть створення звіту за допомогою майстра;
334.2. На першому кроці майстра оберіть таблицю Товари для формування джерела даних звітута перенесіть з списку доступних в список обраних поля НазваТовару, МаркаТовару, ОдиницяВиміру, Придатно, Виробник та ДСТУ;
334.3. На другому кроці майстра створіть рівень групування даних за виробником товарів, перемістивши поле Виробник з списку полів в макет звіту з рівнями групування;
334.4. На третьому кроці оберіть поля НазваТовару та МаркаТовару для сортування записів груп звіту. Забезпечте сортування даних за зростанням значень обраних полів;
334.5. Самостійно завершіть створення звіту та переконайтеся в його дієздатності.
Формування підсумків у звітах.
335. Створіть звіт ДаніЗамовленьТоварівЦьогоРоку для відображення алфавітного списку назв та марок замовлених товарів з зазначенням відповідної дати замовлення, назви клієнта, кількості, ціни та суми, підсумків замовлень по кожному та по всіх товарах.
Завершальний етап заняття. Повторення вивченого матеріалу.
336. Стисніть створену БД.
337. Завершіть роботу Access.
338. Віднайдіть створену БД та заархівуйте її вміст.
339. Збережіть створений архів на гнучкому диску в двох екземплярах.
Контрольні запитання.
340. Як двома способами завантажити майстер створення звітів?
341. Які поля переносяться в список обраних полів на першому кроці майстра?
342. Які варіанти вигляду звіту пропонуються майстром на четвертому кроці? В яких випадках доцільно використовувати кожен з цих варіантів?
343. Де бере інформацію майстер Access про зв’язки між таблицями при генерації звітів?
344. Звідки копіюються властивості полів при створенні звітів?
де V – внесок до пенсійного фонду, H – нарахована сума;
, де P – процент знижки, H – сума пункту замовлення. Для цього: