Роздрукувати сторінку
Главная \ Методичні вказівки \ Методичні вказівки \ 1385 Практична робота 13 на тему Створення форм та звітів у середовищі СУБД MS Access

Практична робота 13 на тему Створення форм та звітів у середовищі СУБД MS Access

« Назад

Практична робота  № 13 Тема Створення форм та звітів у середовищі СУБД MS Access

Мета. Навчитися створювати форму, підпорядковану форму, вводити та редагувати дані з використанням форми та конструювати запити на оформлення підсумкових звітів.

Конструювання запиту на створення таблиці

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

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

Сформуємо запит на створення таблиці на прикладі раніше отриманого запиту на вибірку з груповими обчисленнями Число студентів у групах (див. рис. 7.11-7.13).

В області навігації викличемо названий запит у режимі конструктора запитів. Перетворимо цей запит у запит на створення таблиці, вибравши тип запиту на панелі конструктора Создание таблицы. У вікні Создание таблицы введемо ім'я створюваної таблиці "Кількість студентів" (рис. 7.18).

Для того, щоб переглянути, які записи будуть поміщені у нову таблицю, клацніть по кнопці панелі інструментів Выполнить. Виконайте запит, щоб таблиця КІЛЬКІСТЬ СТУДЕНТІВ була збережена у базі даних. Тепер цю таблицю можна побачити в списку таблиць вікна БД.

Конструювання запиту на відновлення

Відновлення полів значеннями, розрахованими з використанням групових операцій

Розглянемо технологію створення запиту на відновлення на прикладі відновлення поля КІЛ (кількість студентів групи в таблиці ГРУПА).

Кількість студентів у групах раніше було підраховано в запиті на вибірку Кількість студентів у групах (див. рис. 7.11-7.13) з використанням статистичної функції Count. Запит на відновлення безпосередньо на такому запиті побудувати не можна. Тому використаємо для відновлення не сам запит, а таблицю КІЛЬКІСТЬ СТУДЕНТІВ, отриману по запиті на створення таблиці в попередньому пункті.

Для формування запиту на відновлення спочатку створимо запит на вибірку на основі двох таблиць: обновлюваної таблиці ГРУПА і таблиці КІЛЬКІСТЬ СТУДЕНТІВ, яка містить дані для відновлення. У підсхемі даних запиту автоматично встановлюється зв'язок цих таблиць по полю з ім'ям НГ. Для перетворення запиту на вибірку в запит на відновлення (рис. 7.19) виберемо на панелі конструктора тип запиту Обновление.

Заповнимо бланк запиту. Перетягнемо обновлюване поле КІЛ з списку таблиці ГРУПА. У рядку Обновление введемо ім'я поля "Фактична кількість студентів" (таблиці КІЛЬКІСТЬ СТУДЕНТІВ), з якого вибираються значення для відновлення. Ім'я поля вводиться у квадратних дужках.

Запит можна виконати, не виходячи з режиму конструктора. Результат обновлюваного поля КІЛ можна переглянути в режимі таблиці до і після виконання запиту. Для наступного використання підготовленого запиту збережемо його під ім'ям "Оновлення ГРУПА_КІЛ".

Використання виразів у запиті на відновлення

Розглянемо формування запиту на відновлення з використанням виразів на прикладі заповнення поля ГОДИНИ для лекційних занять у таблиці НАВЧАННЯ. Нехай поле ГОДИНИ повинно обновлятися даними, що обчислюється на основі полів ЛЕК (ГОДИНИ лекцій) і ЧС (число семестрів) з таблиці ПРЕДМЕТ. Розрахункове число годин по лекціях визначимо по формулі ЛЕК/ЧС.

Відповідно до завдання у записах лекційних занять таблиці НАВЧАННЯ необхідно обновити поле ГОДИНИ розрахунковим числом годин. Записи про лекційні заняття можна вибрати за значенням поля ВИДЗ цієї таблиці. Дані для розрахунку середнього числа годин знаходяться у таблиці ПРЕДМЕТ. У такий спосіб запит повинен будуватися на основі таблиць НАВЧАННЯ і ПРЕДМЕТ.

Створимо спочатку запит на вибірку на основі таблиць НАВЧАННЯ і ПРЕДМЕТ. Потім перетворимо його в запит на відновлення, натиснувши відповідну кнопку панелі інструментів.

Включимо у бланк запиту обновлюване поле ГОДИНИ таблиці НАВЧАННЯ. У рядку Обновление для цього поля введемо вираз [ЛЕК]/[ЧС]. Для відбору в таблиці НАВЧАННЯ обновлюваних записів про лекційні заняття у бланк запиту включимо поле ВИДЗ і вкажемо у поле Условие отбора значення "лек".

Остаточно сформований запит показаний на рис.7.20.

Виконаємо запит, натиснувши кнопку Выполнить. У діалоговому вікні з'явиться повідомлення про число обновлюваних записів.

Щоб бачити результати відновлення в таблиці НАВЧАННЯ, відкрийте її одночасно із запитом.

Конструювання перехресного запиту

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

Побудова запиту починається як звичайно, наприклад, вибрати вкладку Создание/ Конструктор запросов у вікні бази даних. У вікні конструктора починається створення запита на вибірку. У будь-який момент запит на вибірку може бути перетворений у перехресний запит. Для цього треба вибрати тип запиту Перекрестный на панелі.

Скористаємося як приклад перехресним запитом Вивчення предметів у групах, отриманим майстром перехресних запитів. Для більшої інформативності отриманої перехресної таблиці замінимо в ній коди предметів їхніми найменуваннями. Полю, що містить результат підсумовування по рядках, дамо користувальницьке ім'я "Усього годин".

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

Поле НП (найменування предмету) розміщено в таблиці ПРЕДМЕТ, тому її потрібно додати до розроблювального запиту. Для цього, перебуваючи у вікні конструктора, натиснемо кнопку Отобразить таблицу.

Тепер схема даних запиту складається з таблиць ПРЕДМЕТ і НАВЧАННЯ, зв'язаних по полю КП (код предмета) відношенням один-до-багатьох (рис. 7.21).

Замінимо у бланку запиту поле КП на поле НП таблиці ПРЕДМЕТ. Для цього клацнемо правою кнопкою миші на області відображення полів таблиць і виберемо Имена таблиц, щоб одержати у бланку інформацію про приналежність поля до таблиці. Далі у поле КП у рядку Имя таблицы натиснемо кнопку списку і виберемо поле ПРЕДМЕТ, а в рядку Поле - поле НП.

Для зміни підпису поля ПІДСУМКОВЕ ЗНАЧЕННЯ, що містить суму по рядках, клацнемо правою кнопкою миші, перебуваючи в зоні цього поля. У контекстному меню виберемо пункт Свойства. У вікні Свойства введемо у рядок Подпись "Усього годин". Остаточно сформований перехресний запит наведений на  рис.

Результат виконання отриманого перехресного запиту наведений на рис. 7.22.

Рішення завдань на основі декількох запитів

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

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

Більш складні завдання вимагають послідовного виконання декількох запитів. Кожен із запитів має свої вхідні і вихідні дані. У найпростішому випадку вихідні дані попереднього запиту є вхідними для наступного побудованого на ньому запиту, і, тільки виконавши останній запит у ланцюжку запитів побудованих один на одному, ви ініціюєте послідовне виконання всіх запитів ланцюжка і повне рішення завдання. Функціонально-технологічна схема завдання, розв'язуваного за допомогою двох послідовно виконаних запитів, у загальному виді наведена на рис. 7.23.

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

Побудова запиту на основі іншого запиту

Виконаємо аналіз оцінок, отриманих студентами по різних предметах. Наприклад, підрахуємо число оцінок (2,3,4,5) по кожному із предметів.

Створимо спочатку багатотабличний запит на вибірку на основі таблиць СТУДЕНТ, УСПІШНІСТЬ, ПРЕДМЕТ, ВИКЛАДАЧІ, що формує відомості про оцінки, отриманих студентами з різних предметах. Для цього в режимі конструктора створимо схему даних запиту і бланк, як показано на рис.7.24. Збережемо цей запит з ім'ям "Оцінки".

У результаті виконання цього запиту буде отримана таблиця, джерелом записів якої є таблиця УСПІШНІСТЬ, а дані вибираються з таблиць: ПРЕДМЕТ, СТУДЕНТ, ВИКЛАДАЧІ. Таким чином, кожен рядок результату буде містити інформацію про одну оцінку, отриманої студентом по вказаному в рядку предмету. Число рядків у таблиці запиту буде дорівнює числу рядків у таблиці УСПІШНІСТЬ.

Для підрахунку числа різних оцінок (2,3,4,5) по кожному з предметів на основі цього запиту створимо новий запит - Кількість оцінок. При створенні нового запиту у вікні Отобразить таблицу/Добавление таблицы на вкладці Запросы виберемо з списку запит Оцінки. Заповнимо бланк запиту, як показано на рис.7.25.

Результат виконання запиту Кількість оцінок наведене на рис. 7.26, де в стовпці Выражение 1 відображена кількість оцінок, отриманих по кожному предмету.

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

Рішення завдання, що вимагає виконання декількох запитів із зберенням проміжних результатів

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

Перший запит. Створимо перший запит на вибірку, у якому по таблиці ВИКЛАДАЧІ за допомогою функції Count підрахуємо число викладачів по кафедрах. Збережемо запит під ім'ям "Число викладачів кафедри".

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

Цей запит побудуємо на базі таблиць ВИКЛАДАЧІ, НАВЧАННЯ, КАФЕДРА і запиту Число викладачів кафедри (рис. 7.25). Таблиці ВИКЛАДАЧІ і НАВЧАННЯ потрібні для підсумовування числа годин занять, проведених викладачами кожної кафедри. Таблиця КАФЕДРА необхідна для включення у результат найменування кафедри, а запит Число викладачів кафедри - для включення у результат числа викладачів на кафедрі.

Записи цього запиту формуються на основі записів таблиці НАВЧАННЯ, причому число записів до проведення угруповування дорівнює числу записів у цій таблиці. У результаті запиту до кожного запису додається найменування кафедри НКАФ, по якому і відбувається угруповування. Число викладачів кафедри Count_TAБ   ніяк не порушує необхідного об'єднання записів у групі, оскільки для кожної кафедри є єдиним. Число записів у таблиці результату запиту дорівнює числу кафедр.

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

Щоб зберегти отримані результати, перетворимо другий запит на вибірку в запит на створення таблиці. Таблиця, що буде створена запитом, дамо ім'я "Навантаження". Запит збережемо під ім'ям " Навантаження на кафедрі" (див. рис.7.27).

Другий запит у режимі таблиці наведений на рис. 7.28. Таблицю НАВАНТАЖЕННЯ, збережемо в базі даних, що містить результат рішення цього запиту (рис. 7.29).

Третій запит. Для остаточного рішення завдання розрахунку середнього навантаження викладача кафедри створимо на базі таблиці НАВАНТАЖЕННЯ. Третій запит на вибірку з обрахуванням поля. Для створення обрахованого поля, середнє навантаження викладача поставити у рядок Поле порожнього стовпця і введемо вираз [Sum_ГОДИНИ]/[Count_ТАБН]. Третій запит на вибірку на рис. 7.30.

У таблиці результату варто змінити заголовок стовпця Выражение1, формований за замовчуванням при обчисленні поля, а формат задати округленим до цілого. Для цього викличемо властивості поля за допомогою контекстного меню. Задамо підпис поля "Середнє навантаження викладача", формат поля визначимо як фіксований, а параметру Число десятичных знаков  привласнимо значення "0"

Результати виконання запиту після зміни підписів полів у властивостях наведені на рис. 7.31.

Процес рішення завдання. Для рішення поставленого завдання необхідно послідовно виконати другий запит - Навантаження на кафедрі (див. рис. 7.25), а потім третій запит - (див. рис. 7.30). Перший запит Число викладачів кафедри виконається автоматично при виконанні запиту Навантаження на кафедрі. Для того щоб автоматизувати рішення завдання, що вимагає виконання декількох запитів, потрібно використати засоби розробки додатка користувача, наприклад, написати макрос. Технологія підготовки макросу, що дозволяє виконати послідовність запитів, розглядається нижче.

Звіт по одній таблиці

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

Нехай у результаті проектування макета звіту СПИСКИ СТУДЕНТІВ визначені перераховані нижче вимоги. Макет формованого  звіту повинен мати вигляд у відповідності з рис.8.1. На макеті показане оформлення списку студентів для однієї групи. У звіті повинні послідовно виводитися зі своїми заголовками списки студентів для кожної групи. При формуванні звіту необхідно розрахувати середній прохідний бал для кожної групи і відобразити його у звіті. Записи списку групи повинні виводитися у порядку зростання номера студента в групі. Назва звіту повинна виводиться на кожній сторінці звіту.

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

У пункті меню Создание натискаємо кнопку Конструктор отчетов. В області даних викликаємо контекстне меню, вибираємо пункт Свойства. У вікні, що відкрилося, вибираємо вкладку Данные і заповнюємо відповідне поле ім'ям таблиці СТУДЕНТ, що буде джерелом записів для нашого звіту. У пункті меню Конструктор натискаємо кнопку Добавить поля.

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

Угруповання і сортування даних звіту

Для виконання вимоги до угруповання і сортування даних, відображених у звіті, натиснемо кнопку Группировка на панелі Группировка и итоги конструктора і задамо необхідні параметри у діалоговому вікні, що відкрилося, Группировка, сортировка и итоги (рис.8.3).

Угрупування по полю. Оскільки загальний список студентів відповідно до проекту звіту повинен бути розбитий по групах, виберемо у вікні Сортировка и группировка (Sorting and grouping) зі списку поле номера групи НГ, задамо угрупування по цьому полю. Для цього в області Свойства группы рядках Заголовок группы (НГ Header) і Примечание группы (НГ Footer) треба вибрати значення с разделом заголовка, с разделом примечания. Сортування для поля встановлюється автоматично.

Сортування по полю. Для виводу відсортованого списку студентів у кожній групі, задамо сортування по полю номера студента НС. Для цього в діалоговому вікні виберемо поряд з полем НГ поле НС. В області Свойства группы цього поля в рядках Заголовок группы (НГ Header) і Примечание группы (НГ Footer) треба вибрати значення без раздела заголовка, без раздела примечания, що і визначає сортування тільки по цьому полю.

Після визначення угруповання у вікні конструктора звітів (рис. 8.4) з'являються додаткові розділи Заголовок группы НГ (НГ Header), Примечание группы НГ (НГ Footer).

Розміщення даних у розділах звіту

Дані у звіті повинні бути розміщені, як показано на проекті макета звіту (див. рис. 8.1).

Розміщення полів з таблиць

Розміщення поля угрупування. Значення номера групи повинно бути представлене один раз у заголовку групи. Для цього розмістимо поле НГ у розділі Заголовок групи НГ (НГ Header).

Натиснемо кнопку панелі інструментів конструктора звітів Добавить поля  і перетягнемо поле НГ у розділ заголовка НГ. Відкоригуємо підпис поля, змінивши його на “Список студентів групи” (рис. 8.4). Установимо потрібний шрифт в елементах. Для установки розміру рамки по розміру текста підпису виконаємо команду контекстного меню Размер|по размеру данных (Size|Size To Fit) або відповідну кнопку панелі інструментів.

Форматування табличної частини звіту. Послідовно розмістимо поля НС, ПІБ, РІКН, ПБАЛ в області даних, що визначає вміст рядків табличної частини. Поле розміщається разом з підписом, які система бере із властивостей полів таблиці СТУДЕНТ. Підписи полів треба перенести в область заголовка шляхом вирізання і вставки. Якщо вони не збігаються з назвами стовпців у проекті макета, їх треба відкоригувати. Звертаємо увагу, що підписи також можна створити заново, скориставшись кнопкою на панелі елементів Надпись (Lable).

Включення обчисленого поля у звіт

Для включення розрахункового реквізиту Середній прохідний бал групи натиснемо кнопку Поле (Text Box) на панелі елементів і розмістимо елементи Свободный (Unbound) у розділ Примечание группы НГ (НГ Footer) (див. рис. 8.4). Визначимо у властивостях цього елемента вираз для розрахунку середнього значення. Для цього запишемо на вкладці Данные (Data) у рядок Данные (Control Source) функцію =Avg ([ПБАЛ]), у рядок Число десятичных знаков (Decimal Places) – “2”, на вкладці Макет (Format) у рядок Формат поля (Format) помістимо значення “Фіксований”(Fixed). Відредагуємо підпис поля. Для цього виділимо підпис і викличемо його властивості. У властивостях на вкладці Макет (Format) у рядку Подпись (Caption) запишемо: “Середній прохідний бал групи”. Такі дії, як зміна підпису або введення виразу у поле можна виконати, і не звертаючись до властивостей елементів.

Додавання поточної дати до сторінки

Для додавання у звіт поточної дати скористаємося убудованою функцією Now(). Для цього створимо у заголовку звіту вільний елемент, натиснувши кнопку Поле (Text Box), і задамо у вікні його властивостей на вкладці Данные (Data) у рядку Данные (Control Source) вираз =Now(). На вкладці Макет (Format) у рядку Формат поля (Format) виберемо значення Полный формат даты (General Date). Підпис цього поля виділимо і видалимо. Для додавання номера сторінки в нижній колонтитул створимо вільний елемент і задамо в його властивостях на вкладці Данные (Data) рядок Данные (Control Source) виразом =[Page]. Відредагуємо підпис цього поля, записавши в його властивостях на вкладці Макет (Format) у рядку Подпись (Caption) значення “Стор”.

Розглянемо інші способи формування поля дати і номери сторінки.

Поле поточної дати і часу можна додати у звіт, виконавши в режимі конструктора команду Дата и время (Date and Time…). Установка у діалоговому вікні Дата и время (Date and Time) прапорців Формат даты (Include Data) і /або Формат времени (Include Time) дозволяє вставити поточну дату/або поточний час і вибрати потрібний формат (рис. 8.5).

У звіт буде додане поле, у властивостях якого на вкладці Данные (Data) у рядку Данные (Control Source) буде записан відповідний вираз. Якщо у звіті є розділ заголовку, поле додається в цей розділ. У противному випадку поле вноситься у розділ даних. Як вираз записується функція Format, що формує значення на основі заданих їй аргументів - функції Date(), що повертає поточну системну дату, і формат, у якому повинна виводиться дата. Наприклад, при виборі параметрів, відображених на рис.8.5, функція прийме вигляд =Format(Date();”Long Date”).

Поле нумерації сторінок можна додати у звіт, виконавши у режимі конструктора команду Номера страниц (Insert|Page Number). У вікні діалогу Номера страниц (Page Numbers) вибираються параметри, що визначають формат, розташування і вирівнювання номерів сторінок (рис. 8.6). Для друку номера сторінки на першій сторінці встановлюється прапорець Отображать номер на первой странице (Show Number on First Page).

Зауваження. Вираз, що визначає вивід номерів сторінок, записується у властивостях поля на вкладці Данные (Data) у рядку Данные (Control Source). Вираз може мати вигляд: =”Сторінка”&[Page] або: =”Сторінка” & [Page] & ”з” & [Pages], що відповідає вибору Сторінка N (Page N) або Сторінка N з M (Page N of M).

Завершення оформлення звіту

Для остаточного оформлення введемо у розділ Заголовок отчета (Report Header) розділ Верхний колонтитул (Page Header) і виберемо потрібний шрифт. Далі треба вказати у властивостях звіту на вкладці Макет (Format) у рядку Верхний колонтитул (Page Header):”Без заголовка” (Not with Rpt Hdr). Властивості звіту можуть бути викликані при установці курсору на перетині лінійок.

Створимо лінії відповідно до макета, скориставшись кнопкою панелі елементів Линия (Line).

Перегляд і друк звіту

Перехід з режиму конструктора у режим попереднього перегляду здійснюється натисканням на кнопку Предварительный просмотр (Report View) (див. рис. 6.2). Для перегляду раніше створеного звіту потрібно вибрати його у вікні бази даних на вкладці Отчеты (Reports) і натиснути кнопку Просмотр (Preview). Звіт при перегляді відобразиться на екрані таким, яким він буде надрукован.

У режимі попереднього перегляду є своя панель інструментів (рис.8.7).

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

Кнопка Печать (Print) панелі інструментів режиму попереднього перегляду дозволяє вивести звіт на друк.

За допомогою команди Файл|Параметры страниц (File|Page Setup…)можна вибрати принтер, задати формат паперу, розмір полів, відстань між рядками, орієнтацію (книжкова, альбомна) і т.д. Команда Файл|Печать (File|Print…)дозволяє вибрати для друку окремі сторінки звіту або виділені записи, роздрукувати задане число копій, вивести звіт у файл, що повинен роздруковуватися іншим часом. На рис. 8.8 наведена перша сторінка розробленого звіту Списки студентів у режимі попереднього перегляду.

З повагою ІЦ “KURSOVIKS”!