Методичні рекомендації до лабораторних робіт для студентів спеціальності 186 «Видавництво та поліграфія» сторінка 3

29
Рис. 15. Результат виконання перехресного запиту,
що обчислює кількість товарів кожної категорії за роками
Розглянемо основні функції категорії «Дата/Час».
1. Now () повертає поточну системну дату та час. Аргументів не має.
Значення функції автоматично оновлюється.
2. Date () повертає поточну дату в числовому форматі або в одному з форматів, явно зазначеному користувачем у властивостях поля.
3. Year («number») повертає номер року в вигляді цілого числа
(діапазон допустимих значень залежить від вибору системи літочислення
(для Григоріанського календаря: від 2.08.718 до 31.12.9999). Наприклад,
Year (1.09.18
) = 2018. Тут і далі в якості аргументу «number» можна ви- користовувати ім'я поля таблиці.
4. Month («number») повертає номер місяця в році, приймає зна- чення від 1 до 12.
5. Day («number») повертає номер дня у місяці, приймає значення від 1 до 31.
Аналогічні функції Access існують і для більш дрібних інтервалів часу. Ознайомтеся з ними самостійно.
6.
DateDiff («interval»; «date1»; «date2»; «firstweekday»; «firstweek») повертає різницю між двома датами (date2 – date1) у форматі, визначеному аргументом "interval". Найбільш часто використовують значення аргументу "
interval" "у" і "d", які дозволяють визначити різницю між двома датами в днях.
Результат буде негативним, якщо date1> date2. Аргументи firstweekday та firstweek не є обов'язковими для визначення, тому що використовують деякі значення за замовчуванням. Firstweekday задає, який день тижня вважати першим (точніше «нульовим», для понеділка цей параметр = 2, тому що рахунок починається з суботи = 0). Значення firstweek задає,

30 який тиждень року вважати першим. За замовчуванням першим вважа-
ється тиждень, на який припадає 1 січня.
7. DatePart ( «interval»; «date»; «firstweekday»; «firstweek») повертає фрагмент дати, що відповідає значенням interval. Наприклад, DatePart
("yyyy"; [ДатаРозміщення]) поверне значення 1996, 1997 або 1998, а DatePart
("m"; [ДатаРозміщення] поверне значення від 1 до 12.
8. Format (
«expr»; «fmt»; «firstweekday»; «firstweek») також дозволяє отримати подібний результат. Аргумент expr задає вираз, який треба перетворити до формату, визначеному аргументом fmt. Слід пам'ятати, що функція формат перетворює дату до текстового типу; в цьому її суттєва відмінність від функції Format. Наприклад, Format (# 1.06.86 #;
"mmm") поверне скорочену назву місяця, (скажімо, черв.).
9. IIf ( «expr»; «truepart»; «falsepart») перевіряє істинність логічного виразу expr і в разі його виконання повертає значення, що визначається аргументом truepart і, в іншому випадку, значення, що визначається аргу- ментом falsepart. Якщо аргументи явно не визначені, функція поверне значення True і False відповідно (аналогічна функції ЕСЛИ в MS Excel).
10. CCur (expression)
– функція перетворення формату, яка перетво- рює числове або рядкове вираження аргументу до грошового формату.
Як аргумент може використовуватись ім'я поля, чий формат треба при- вести до грошового.
Модифікуючі запити
MS Access пропонує такі типи модифікуючих запитів, що дозволяють внести якісь зміни у таблиці БД : запит на створення нової таблиці
; запит на видалення записів
; запит на додавання записів у таблицю
; запит на оновлення значень полів у таблиці
Запит на створення нової таблиці
Запит на створення нової таблиці зберігає результат запиту у ви- гляді нової таблиці. У результаті виконання такого запиту створюється нова таблиця з даними та структурою, взятими, можливо, з різних таблиць.

31
Завдання умов відбору даних для нової таблиці також можливе. Нова таблиця може бути, за вибором користувача, збережена як у поточній, так і в новій базі даних.
Збереження набору записів запиту використовується, наприклад, якщо необхідно довго зберігати підсумкову інформацію, а вихідні дані з БД, на яких базувався запит, будуть видалені, або дозволяє експорту- вати її в інші додатки. Наприклад, доцільно зберегти в окремій таблиці дані про відрахованих студентів у БД «Деканат» перед тим, як видалити
інформацію про них з основних таблиць.
Для конструювання запиту на створення таблиці необхідно створити запит на вибірку або викликати в режимі конструктора раніше створений запит і змінити його тип на «Створення таблиці» (відповідна кнопка на па- нелі інструментів). У результаті з'явиться діалогове вікно «Створення таблиці», де необхідно ввести назву нової таблиці та клацнути на кнопці
«ОК» (рис. 16).
Після цього запит необхідно виконати. З'явиться повідомлення про кількість рядків, що додаються в нову таблицю, та вимога підтвердити створення нової таблиці (рис. 17). Після натискання кнопки «Так» таблиця буде створена.
Створювана таблиця може містити також розрахункові поля.
Для того щоб побачити результат виконання запиту на створення нової таблиці, у вікні БД треба перейти до об'єктів «Таблиці» та переко- натися, що у переліку таблиць з'явилася нова.
Рис. 16. Вікно «Створення таблиці» запиту на створення таблиці

32
Рис. 17. Попередження про створення нової таблиці
Як ви думаєте, чи пов'язана нова таблиця з іншими таблицями у схемі даних? Перевірте своє припущення.
Запит на оновлення полів
Запит на оновлення полів використовується для зміни значень груп записів, які відповідають заданим умовам відбору. Він дозволяє одно- часно змінювати значення певних полів будь-якої кількості записів. Такий запит доцільно застосовувати, коли необхідно змінити велику кількість записів таблиці, які відповідають умовам вибору. Інакше записи можна змінювати безпосередньо в таблицях «вручну».
Наприклад, підприємство вирішило збільшити ціни на товари ка- тегорії «Рибопродукти» на 15 % у зв'язку зі збільшенням власних витрат.
Інший приклад – потрібно підвищити ціну на всі вироби, що поставля- ються фірмами-постачальниками з кодами 132 і 151, на певну однакову кількість відсотків.
Для вирішення цього завдання спочатку створюється запит на ви- бірку, що відбирає записи з відомостями про товари заданих фірм. Потім вихідний запит перетворюється в запит на оновлення відповідною кнопкою з типом запиту. У нижній частині бланка запиту додасться рядок
«Оновлення», в якому користувач формує правила оновлення (наприклад, підвищення ціни на певний відсоток). Рекомендується за можливістю використовувати запит з параметром (рис. 18), що дозволяє змінювати умову підвищення цін.

33
Рис. 18. Приклад формування виразу для підвищення ціни
на певний відсоток у вікні Будівника виразів
У результаті виконання запиту на оновлення ціна для всіх записів, що підходять за умовами відбору, буде збільшена на певний відсоток, який введе користувач. Якщо запит буде виконаний повторно, то ціна збільшиться знову порівняно з її попереднім значенням.
Запит на оновлення не буде виконаний, якщо спроба його виконан- ня може призвести до таких помилок: дублювання значень ключового поля; помилки перетворення даних (наприклад, у спробі додати до число- вих значень текстові); зміна записів, які в цей час оновлюються іншими користувачами та заблоковані ними; порушення умов на значення.
Запит на додавання записів
Запит на додавання записів забезпечує додавання одного або де- кількох записів, що відповідають умовам відбору, з однієї таблиці в кі- нець однієї або декількох таблиць. Створюється на основі запиту на ви- бірку шляхом зміни типу запиту на «Додавання» (див. рис. 16) у режимі конструктора.
У результаті виконання такого запиту в існуючу таблицю додаються записи з іншої таблиці, що має подібну структуру.

34
Запит на видалення записів
Запит на видалення записів дозволяє видаляти групу записів з однієї або декількох пов'язаних таблиць. Такий запит доцільно застосовувати, коли необхідно видалити велику кількість записів, які відповідають одній умові. Інакше, записи можна видалити безпосередньо з таблиці «вручну».
Це створюється на підставі запиту на вибірку шляхом зміни типу запиту на «Видалення» (див. рис. 16) у режимі конструктора.
Якщо в схемі даних бланка запиту на видалення відкрито більше однієї таблиці, то необхідно вказати, з якої саме таблиці видаляти за- писи. Це здійснюють шляхом перетягування символу «*» (що означає
«всі поля») з переліку полів таблиці в порожній стовпець рядка «Вида- лення» бланка запиту. Якщо дана таблиця пов'язана з іншими таблицями
і є для них головною та включено властивість «Каскадне видалення по- в'язаних записів», то відповідні записи будуть видалені з усіх пов'язаних таблиць.
Наприклад, необхідно видалити записи із заданим номером анульо- ваного замовлення (якщо такого запису немає, можна ввести 0 – запису з таким номером замовлення напевно не існує) та записи із замовленнями більш ніж річної давності (припустимо, відомості про замовлення зберіга- ють тільки один рік). Бланк запиту на видалення зображений на рис. 19.
Рис. 19. Приклад запиту на видалення

35
Після натискання кнопки «Виконати» зазвичай з'являється діало- гове вікно для підтвердження видалення. У ньому вказується кількість записів, що будуть видалені у разі натискання кнопки «Так» (рис. 20).
Поновити записи після видалення неможливо, тому доцільно створювати заздалегідь резервну копію БД.
Рис. 20. Діалогове вікно видалення записів
Переконатися у виконанні запиту можна шляхом перегляду кіль- кості записів у таблиці після виконання запиту (вона повинна зменши- тися). Як ви думаєте, чи призведе до успіху спроба повторно виконати цей запит?
Хід роботи
Виконання завдань, помічених «*», надає право на отримання
оцінки «відмінно».
1. Виведіть список клієнтів із зазначенням номера замовлення
(
«КодЗаказу»), які розмістили свої замовлення в квітні 1998 р.
2. Виведіть прізвища співробітників і список замовлень (КодЗаказу), які ще не виконані (поле «ДатаВиконання» для таких замовлень не за- повнене).
3.
Виведіть список клієнтів з Норвегії, які замовили будь-які напої
(поле «Категория» з таблиці «Типи») у постачальників з Австралії.
4.
Виведіть список усіх постачальників, країна яких починається на букву Н, а мінімальний запас товарів, що поставляються ними, більше нуля.

36 5.
Виведіть список марок товарів, кількість яких на складі виража-
ється тризначним числом.
6.
Виведіть список клієнтів, назва яких починається літерами A, B, C
(
англійські символи). Виконати завдання двома способами: спочатку ви- користовуючи логічну функцію OR, а надалі – шаблон Like "[A-С] *"
7. *
Виведіть список клієнтів, у номері телефону яких відсутній код
(тобто номер телефону починається не на дужку).
8.
Виведіть список замовлень, розміщених влітку 1997 року, в які увійшли товари категорії Напої з 10-відсотковою знижкою.
Для конструювання умови фільтрації на полі «ДатаРозміщення», доцільно скористатися оператором Between 1.01.97 And 31.01.97. Можна також застосувати вираз >= 1.01.97 And <= 31.01.97.
9.
Визначте вартість поставок товарів з різних країн («Марка»,
«Ціна», «Очікується» – з таблиці «Товари»; «Країна» – з таблиці «Поста- чальники»). Урахувати тільки ті товари, щодо яких поставки очікуються
(значення поля «Очікується» більше 0). Визначити найдорожчу поставку.
Вартість поставки розрахувати як [Ціна] * [Очікується].
10. Визначте вартість кожного товару на складі.
11. *Розрахуйте вік і стаж роботи кожного співробітника фірми (ре- зультат округлити до цілих). Яку посаду обіймає наймолодший та най- старший співробітник фірми?
12.
Виведіть список товарів із зазначенням ціни, які належать до ка- тегорії, що задається користувачем (параметр по полю «Категорія»).
13.
Виведіть список замовлень із зазначенням коду замовлення, країни клієнта, прізвища співробітника з обслуговування та способу до- ставки, причому країна клієнта та спосіб доставки задаються користу- вачем.
14.
Виведіть список співробітників і назви клієнтів, для яких замов- лення було виконано в призначений день.
15.
Виведіть список кодів замовлень, оформлених будь-яким спів- робітником (параметр) у проміжок часу, що задається користувачем.
16. *Розрахуйте вартість замовлених товарів в у.о. Використовуйте параметр для введення поточного курсу долара із розрахунком вартості.
17.
Розрахуйте кількість клієнтів з кожної країни.
18. Розрахуйте сумарну вартість кожного замовлення.

37 19. Для кожного клієнта обчисліть сумарну вартість, сплачену за всі його замовлення. Упорядкувати список клієнтів у порядку спадання вар- тості замовлень.
2 0. Визначте максимальну знижку, отриману кожним клієнтом.
21. *Для кожного співробітника розрахуйте суму забезпеченої ним виручки від продажу товарів у ту чи іншу країну (країна отримувача – параметр).
22. *Розрахуйте загальну суму виручки від реалізації всіх товарів за місяцями 1997 року. Щоб виконати угруповання за місяцями, викорис- товуйте або функцію Month ([ДатаРозміщення]) – у цьому випадку місяці будуть виводитися як число, або функцію Format для виведення назв місяців. Щоб місяць виводився повністю, треба задати формат "mmmm".
23. Для кожного клієнта визначте першу й останню дати звернення у фірму та кількість замовлень, розміщених протягом усього часу.
24. Визначте середню вартість доставки в кожну країну. Країни клієнтів – заголовки рядків; спосіб доставки – заголовки стовпців.
25
. Для кожного співробітника розрахуйте вартість оформлених ним замовлень за місяцями 1997 року. Спочатку слід розрахувати вартість проданих товарів.
26
. За допомогою перехресного запиту виведіть суму продажів то- варів кожної категорії, проданих у кожному кварталі 1997 року.
27
. Для кожного клієнта визначте найдорожче замовлення за кожен рік.
28. За допомогою запиту на створення таблиці виділіть замовлення клієнтів зі США в окрему таблицю. Спочатку створіть відповідний запит на вибірку на основі таблиць «Замовлення» та «Клієнти».
29. За допомогою запиту на додавання додайте в створену таб- лицю «Замовлення клієнтів з США» також клієнтів з Бразилії, Мексики та Венесуели. Назвіть її «Замовлення з Америки».
30. Видаліть замовлення за 1996 рік і пов'язані з ними записи таб- лиці «Замовлено».
31.
Установіть знижку 25 % у разі замовлення, кількість якого для од- ного товару становить не менше 50-ти одиниць (таблиця «Замовлено»).
32. *За допомогою запиту на оновлення оновіть дати розміщення, призначення та виконання замовлень – «збільшіть» їх на 10 років. Вико- ристовуйте функцію DateAdd для цих полів у запиті на оновлення.

38 33. Самостійно сформулюйте умови та виконайте запит на створен- ня нової таблиці для індивідуальної БД.
34. Самостійно сформулюйте умови та виконайте два запити на онов- лення для індивідуальної БД.
35. Самостійно сформулюйте умови та виконайте два запити на ви- далення записів для індивідуальної БД, причому один із запитів повинен видаляти записи одночасно з кількох пов'язаних таблиць.
36. Оформіть звіт і захистіть його перед викладачем.
Контрольні запитання
1.
Визначте призначення запитів на вибірку.
2. Що таке запит з параметром? Які переваги надає його засто- сування?
3. Назвіть відомі вам засоби в MS Excel, які відіграють подібну за- питам на вибірку роль. Порівняйте їх можливості.
4. Які засоби відбору записів, крім запитів на вибірку, існують у MS
Access
? Порівняйте можливості відбору записів безпосередньо в табли- цях і за допомогою запитів.
5.
Охарактеризуйте обчислюване поле. Чи можна створити обчис- люване поле безпосередньо в таблиці? Обґрунтуйте свій висновок.
6. Опишіть послідовність дій для створення запиту на вибірку в ре- жимі конструктора.
7. Які способи створення запитів існують у MS Access?
8. Яке призначення групових запитів? Порівняйте їх із перехрес- ними запитами.
9. Наведіть приклад завдання, яке доцільно розв'язати за допо- могою перехресного запиту.
10. У чому полягає специфіка запитів на видалення порівняно з ін- шими модифікуючими запитами?
11. Як впливає властивість зв'язку «Каскадне видалення пов'язаних записів» на результати виконання такого запиту?
12. Як впливає властивість зв'язку «Каскадне оновлення видалення пов'язаних полів» на результати виконання такого запиту?
13. У яких випадках запит на видалення може призвести до помилки?
14. Як перевірити результати виконання запиту на оновлення?
15. Наведіть приклади, коли доцільно створювати запит на оновлення.

39
Лабораторна робота 10
.
Створення форм. Налаштування елементів форми
Мета – набути навички створення та використання форм у середо- вищі MS Access.
Лабораторна робота сприяє напрацюванню таких компетентностей відповідно до Національної рамки кваліфікації:
знання:
поняття та призначення форм; основні способи створення форм; структура форм і призначення її розділів; різновиди керівних елементів для форми та їх властивості і параметри; засоби обчислення значень і підсумків за групами записів на формі;
інструменти фільтрації та сортування даних на формі;
уміння:
обирати керівні елементи форми та налаштовувати їх параметри; додавати, коректувати, видаляти дані таблиць за допомогою форм; виконувати сортування та фільтрацію у формах; засвоїти елементарні знання з проектування та модифікації форм у режимі конструктора; розробляти кнопкові форми;

1 2 3 4 5

скачати