Реферат: Н. І. Гомза Рецензент: канд екон наук, доцент В. О. Костюк Рекомендовано кафедрою «Прикладна математика та інформаційні технології», протокол №7 від «13» березня 2007 р

МІНІСТЕРСТВО ОСВІТИ І НАУКИ УКРАЇНИ

ХАРКІВСЬКА НАЦІОНАЛЬНА АКАДЕМІЯ МІСЬКОГО ГОСПОДАРСТВА


До друку

дозволяю

Перший проректор


_____________ Г. В.Стадник


МЕТОДИЧНІ ВКАЗІВКИ

до виконання лабораторної роботи

Робота з базами даних «MS Access»©

з дисципліни «Управлінські інформаційні системи в аналізі і аудиті» (для студентів 5 курсу спеціальності 8.050106 «Облік і аудит»), «Інформаційні системи і технології на підприємстві» (для студентів 4 курсу спеціальності 7.050100 «Економіка і підприємництво»), «Інформаційні системи в менеджменті» (для студентів 4 курсу спеціальності 6.050200 «Менеджмент організацій» всіх спеціалізацій) та системи дистанційного навчання.


^ ХАРКІВ – ХНАМГ – 2007

Методичні вказівки до виконання лабораторної роботи «Робота з базами даних MS Access®» з дисципліни «Управлінські інформаційні системи в аналізі і аудиті» (для студентів 5 курсу спеціальності 8.050106 «Облік і аудит»), «Інформаційні системи і технології на підприємстві» (для студентів 4 курсу спеціальності 7.050100 «Економіка і підприємництво»), «Інформаційні системи в менеджменті» (для студентів 4 курсу спеціальності 6.050200 «Менеджмент організацій» всіх спеціалізацій) та системи дистанційного навчання.


Укл.: Карпенко М. Ю. , Уфимцева В.Б., Гринчак М.В. , Гомза Н.І. — Харків: ХНАМГ, 2007.— 56 с.


Укладачі: М. Ю. Карпенко,

В. Б. Уфимцева,

М. В. Гринчак,

Н. І. Гомза


Рецензент: канд. екон. наук, доцент В.О. Костюк


Рекомендовано кафедрою «Прикладна математика та інформаційні технології», протокол № 7 від «13» березня 2007 р.



Зміст


Створення бази даних, робота з таблицями 4

Створення бази даних (БД) 4

Створення таблиць 4

Створення зв'язків між таблицями 9

Робота з даними у режимі таблиці 10

Введення нових записів 10

Пошук і заміна даних у всіх записах 10

Копіювання даних 10

Видалення записів 11

Сортування даних 11

Формування запитів 11

Робота з однією таблицею 11

Запит, що відбирає дані з однієї таблиці за умовою 12

Підсумкові запити 13

Перехресний запит 15

Запити, що відбирають дані із декількох таблиць 16

Створення полів для обчислень 17

Формування підсумкового багатотабличного запиту 18

Формування запитів типу «Записи без підлеглих» 19

Модифікація даних за допомогою запитів 21

Запит на формування нових таблиць 22

Створення запитів на видалення даних 23

Будування форм 24

Створення форм для перегляду і введення даних 24

Створення форми з підлеглою формою 26

Конструктор форм 27

Формування звітів 29

Контрольні запитання 31

Варіанти індивідуальних завдань 32

Література 52
^ Створення бази даних, робота з таблицями Створення бази даних (БД)
Запустіть програму Microsoft Access: «Пуск → Программы → Microsoft Access». У вікні , що з'явилося , виберіть «Новая база данных» і клацніть на «OK». Створити нову базу даних можна також, обравши пункт меню «Файл → Создать базу данных». У вікні «Файл новой базы данных» виберіть папку, в якій будете поміщати БД, а в нижній частині вікна вкажіть ім'я файлу «Бибилиотека.mdb» (розширення «mdb» Access додасть автоматично). Клацніть на кнопку «Создать». Відкриється вікно, у якому будуть присутні всі компоненти бази даних:

таблиці - об'єкти, в яких зберігається інформація про якусь предметну область (наприклад, робота бібліотеки, складального цеху і т.д.). У таблицях інформацію подано в стовпчиках, що називаються полями, і в рядках, що називаються записами. Кожне поле має ім'я, тип, розмір, заголовок, що задає користувач при створенні таблиць;

запити - вибірки з декількох таблиць, що задовольняють деяким умовам;

звіти - інформація з таблиць, підготовлена для друку;

форми - зображення даних із таблиць на екрані у формі зручної для запровадження, перегляду і коригування інформації;

макроси і модулі - програми обробки даних, що зберігаються в БД на мові VBA.
^ Створення таблиць
Створимо три таблиці, що містять інформацію про роботу бібліотеки університету:

«Книги» - містить інформацію про книги, що зберігаються в бібліотеці;

«ЧитКниги» - містить інформацію про книги, що одержані читачами;

«Читатели» - містить інформацію про читачів бібліотеки.

Для створення таблиці клацніть на кнопку «Создать» у вікні бази даних на вкладці «Таблицы». У вікні , що з’явиться виберіть пункт «Конструктор». У сучасних версіях Access є простіший шлях: на вкладці «Таблицы» вибрати пункт клацнути пункт «Создание таблицы в режиме конструктора» (рис. 1).

«Новая таблица» виберіть «Конструктор» і клацніть «OK». У вікні конструктора таблиць введіть інформацію, як показано на рис. 2.


Вам необхідно задати для кожного поля його властивості:

«
Имя поля» може складатись з символів латиниці або кирилиці, цифр, спеціальних знаків та може містити символ «пробєл»;

«Тип данных поля» вибирають із списку, що розкривається;

«Размер поля» — це кількість символів для типу «Текстовый», або один з числових типів типу «Числовой»;

«Описание» містить коментар щодо призначення поля й інформації, що в ньому записана;

«Формат поля» впливає на зовнішній вигляд даних, що вводять;

«Подпись» дозволяє задати заголовок поля при відображенні таблиці на екрані; якщо заголовок не вказано, то виводиться ім'я поля;

«Условие на значение» містить логічні вирази для значень, які до нього вводять; наприклад; для поля «Стоимость» можна поставити логічний вираз «<100», якщо вартість книги не мусить перевищувати 100 гривень;

«Сообщение об ошибке» містить повідомлення, яке буде показане користувачу у разі введення помилкових значень.

Характеристики полів виберіть, виходячи із їх змісту.

Одне з полів таблиці може бути заявлене ключовим. Відразу після створення таблиці Access «запропонує» побудувати ключові поля. Але це можна зробити і пізніше у будь-який момент.

Значення в цьому полі однозначно вказують на запис. Ключове поле повинно бути «Обязательным» й «Индексированным(без повторений»). Таким полем у таблиці «Книги» є поле «Інв№». Щоб зробити його ключовим, виділіть поле, клацніть правою кнопкою миші й виберіть з контекстного меню (рис. 3) «Ключевое поле».

Порада. Кнопка для створення ключових полів є на панелі інструментів.

Закрийте вікно «Конструктора таблиц», щоб зберегти структуру таблиці надайте їй ім’я і натисніть у вікні запиту «ОК».

Тепер треба ввести дані в таблицю. Це можна зробити багатьма способами. Приведемо декілька найпростіших:

1) Перегляд і введення даних в режимі таблиці: для цього у вікні бази даних необхідно виділити таблицю і натиснути на кнопку «Открыть» (рис. 1). На екрані з’явиться порожня таблиця. Введіть до неї декілька записів про книги вашої бібліотеки як вказано у табл. 1.

2). Виділить таблицю «Книги», Використання «автоформи»: натисніть на кнопку «Новый объект» на панелі інструментів і виберіть «Автоформа».

3). Двічі клацніть лівою кнопкою миші на рядку з іменем таблиці. Таблицю буде відкрито у режимі введення даних.

Access побудує форму для перегляду і редагування записів і відкриє її у окремому вікні. Введіть щонайменше 10 записів в таблицю «Книги».

Створіть нову таблицю «ЧитКниги» з такими полями (рис. 4):

«Инв№» інвентарний номер книги, виданої читачу;

«NB» – номер читацького квитка читача;

«Дата выдачи» - дата видачі книги читачу;

«Дата возврата» – дата повернення читачем книги до бібліотеки.

Виберіть відповідні до змісту типи полів і їх розміри. Поля «Інв№» і «NB» повинні бути індексованими й обов'язковими. У цій таблиці можна не призначати ключового поля. Заповніть таблицю даними про видані книги (табл. 2). Стежте за тим, щоб дані в таблицях були узгоджені, тобто не видавайте книг, котрих немає в бібліотеці.

Табл. 2. Список книг, що були видані (таблиця «ЧитКниги»)

Инв№

NB

Дата выдачи

Дата возврата

Прим.

1

700

03.01.07

14.02.07




2

830

20.01.07

13.02.07




3

830

02.02.07

06.03.07




6

830

21.02.07

23.03.07




5

700

03.02.07

04.04.07




3

1220

10.02.07

11.03.07




4

830

02.02.07

03.04.07




Створіть нову таблицю «Читатели» з полями:

«NB» - номер читацького квитка читача;

«Фамилия» - прізвище читача;

«Кафедра» - кафедра, де працює читач, або група, у якій він навчається;

«Телефон» - робочий телефон читача

^ Таблиця 1- Список книг з таблиці «Книги»

Инв №

Шифр

Автор

Название

Издат.

Город

Год

Ст-ть, грн.

Аннотация




1

2

3

4

5

6

7

8

9

1

681.3

Пинчук В.П.

Объектно-ориентированное программирование

ЗНТУ

Запорожье

2002

10,00

Конспект лекций для студентов по программированию.

7

681.3

Гарнаев А.

Самоучитель VBA. Технология создания пользовательских приложений.

БХВ

Санкт-Петербург

1999

14,00

Подробный справочник по VBA, самоучителем по VBA.

2

681.3

Белая Н.И.

Программирование на VBA.

ЗНТУ

Запорожье

2000

10,00

Конспект лекций

3

681.3

Гарнаев А.

Использование Excel в экономике и финансах.

БХВ

Санкт-Петербург

1997

23,00

Полный перечень функций и примеры их использования.

4

681.3

Вейскас Дж.

Эффективная работа с Microsoft Access 2000

Питер

Санкт-Петербург

2000

35,00

Новая версия системы управления базами данных.

5

681.3

Керри Н.Праг

Библия пользователя Microsoft Office 2000

Диалектика

Киев

2001

43,00

Описаны возможности Word, Excel, Access, Power Point, Outlook

6

681.3.

06

Бухвалов А.В., Идельсон А.В.

Финансовые вычисления для профессионалов.

БХВ

Санкт-Петербург

2001

23,00

Решения финансовых задач. методики вычислений в Excel.

Вигляд таблиці у режимі «Конструктора» показано на рис. 5.

Ключовим полем в останній таблиці є поле «NB», тому що саме воно однозначно визначає кожний запис. Це поле повинно бути індексованим і обов'язковим. Заповніть і цю таблицю даними із табл. 4. Стежте, щоб не видавати книг читачам, що не існують. У цьому випадку ви не зможете встановити необхідні зв'язки поміж таблицями.

Табл. 4. Список читачів до таблиці «Читатели»

NB

Фамилия

Кафедра

Телефон

700

Корнич В.Г.

Физика

2-92

890

Нечай И.С.

Физика

2-91

1220

Цокотун П.В.

ВМ

4-14

555

Романиченко Г.В.

ВМ

4-14

830

Белая Н.И.

ВМ

4-14
Створення зв'язків між таблицями
Проектування бази даних з нашого прикладу можна вважати завершеним. Залишилося встановити зв'язки між таблицями, щоб можна було вибирати дані з декількох таблиць відповідно зі значеннями збіжних полів. Входимо до меню «Сервис → Схема данных» (можна скористатись піктограмою «Схема данных» на панелі інструментів. з'явиться вікно, як на рис. 6. За допомогою кнопки «Добавить» додайте до вікна схеми даних три створені таблиці. Вікно схеми даних показано на рис. 7.

Щоб встановити зв’язок між таблицями треба скористатись методою «зачепити і перетягнути». Встановіть курсор миші на поле «Інв№» у таблиці «Книги» і протягніть до такого ж поля в таблиці «ЧитКниги». На схемі з'явиться лінія, що з'єднує поля. Оскільки поля індексовані, Access запропонує конкретизувати тип зв’язку, що буде встановлено. На екрані з’явиться вікно, зображене на рис. 8.

Встановіть параметри контролю цілісності даних і параметрів каскадного оновлення й видалення записів. Натисніть кнопку «Создать». На схемі з'явиться лінія, що з'єднує ці поля. Аналогічним чином встановіть зв'язок двох інших таблиць по полю «NB».
Робота з даними у режимі таблиці Введення нових записів
У режимі «Таблица» треба відкрити таблицю. Створити вільний рядок (порожню строку), вибравши команду «Правка → Перейти → Новая запись» с або на панелі інструментів «Новая запись». Для того, щоб запам'ятати внесені дані, натисніть клавішу «Да» або команду «Записи → Сохранить запись».

Для редагування записів треба виділити дані. Для цього можна двічі натиснути на слові, що всередині комірки або клацніть на лівому кінці комірки (якщо стрілка миші прийме форму білого контурного знака плюс). Дані, які будуть введені із клавіатури, замінять виділені.
Пошук і заміна даних у всіх записах
Над таблицею бази даних можна виконувати операції контекстної заміни. Встановіть курсор першу клітку стовпця таблиці, у межах якого треба скоригувати інформацію. Виберіть команду «Правка → Заменить». У вікні діалогу вкажіть значення, яке потрібно змінити, і значення на яке потрібно замінити). Натисніть кнопку «Заменить» або «Заменить все».
Копіювання даних
Для копіювання даних спочатку треба їх виділити, потім вибрати команду «Правка → Копировать», вказати місце розташування даних (іншу таблицю або копію) і звернутись до команди «Правка → Копировать».

Щоб зробити резервну копію треба:

перейти у вікно бази даних;

вибрати необхідну таблицю;

виконати команду «Правка → Копировать»;

вибрати команду «Правка → Вставить» і у вікні діалогу ввести нове ім'я для копії таблиці.
Видалення записів
Щоб вилучити записи з бази даних треба виділити їх, натиснути клавішу «Del». Також можна скористатись правою кнопкою миші: виділити записи, клацнути на них правою кнопкою миші й вибрати з контекстного меню «Удалить запись». З’явиться вікно, у якому треба підтвердити своє рішення.
Сортування даних
Сортування даних можна виконати декількома способами. Перший: виберіть стовпець, по якому плануєте упорядкувати інформацію, натисніть на панелі інструментів кнопку «Сортування по зростанню» або «по убыванию». Другий спосіб: виконати сортування за допомогою «Расширенного фильтра». Цей спосіб допомагає при сортуванні декільком стовпцям. Відкрийте «Фильтр → Расширенный фильтр». Виберіть необхідні поля в порядку сортування й для кожного вкажіть порядок сортування. Далі виберіть команду «Фильтр → Применить фильтр».
^ Формування запитів
Запит - це об'єкт бази даних, що дозволяє відбирати інформацію з однієї або декількох таблиць за вказаними умовами. Запит може не тільки вибирати інформацію (це робиться в запитах «на вибірку»), а й робити обчислення, коригувати поля бази даних, вилучати записи тощо. Запит — найважливіший об'єкт роботи з даними. Розглянемо послідовно основні типи запитів, що працюють в системі Access.
^ Робота з однією таблицею
Насамперед потрібно навчитися записувати умови відбору даних. Без умов дія запиту не має сенсу.

Для числових полів умови записують як арифметичні або логічні вирази. Наприклад, «<100» , «>40». В умовах можна використати логічні функції «And» , «Or», «Not». Наприклад, умова «>40 And <60» відбирає значення поля, що є більші за 40 і менші за 60.

При формуванні запитів, що мають відбирати значення, перевіряючи їх належність до інтервалу, зручно застосовувати функцію:

«Beetween (НижняяГраница) And (ВерхняяГраница)»

Наприклад, вираз «Beetwen 40 And 60» відбирає значення, що знаходяться в інтервалі від 40 до 60.

При роботі з текстовими полями часто виникає потреба перевіряти інформацію на відповідність певному шаблону. Прикладом такого запиту може бути вибір з базі даних всіх осіб, чиє прізвище починається з «КАР».

Таку дію можна реалізувати з використанням функції «Like “Рядок пошуку із символами шаблона”». Знак «*» у шаблоні заміняє довільну кількість символів, що можуть стояти на місці даної позиції. Знак шаблону «?» заміняє поодинокий символ у позиції, де він знаходиться. Символ «#» вказує, що на його місці має стояти цифра. Наприклад, умова «Like “КАР*”» , будучі накладеною на поле прізвищ відбирає з бази даних записи, чиє прізвище починається з «КАР».
^ Запит, що відбирає дані з однієї таблиці за умовою
Побудуємо запит, що відбирає з таблиці «Книги» екземпляри, ціна яких перебільшує 20 гривень і водночас прізвище автора починається або з букви «Г» або «К». Переходимо на вкладку «Запити». Натискаємо «Создание запроса в режиме конструктура» (рис. 9).

Я
к альтернатива — можна натиснути кнопку «Создать» і вибрати варіант «Конструктор» або скористатися правою кнопкою миші. У вікні діалогу «Додавання таблиці» на вкладці «Таблиці» вибираємо таблицю, з якої потрібно відібрати дані (рис. 10).

У нашому випадку такою таблицею є «Книги». Натисніть кнопку «Добавить». Закрийте вікно «Добавление таблицы». Виберіть поля, які треба включити до запиту, а саме: «Автор», «Название», «Год», «Стоимость». Щоб помістити їх до бланку запиту, двічі клацніть кнопкою миші на імені поля у таблиці. Вибрати поле таблиці можна безпосередньо у вікні запиту, вибравши їх з випадаючого списку (рис. 11).

У вікні записуємо умови для відбирання записів. У рядку «Условия отбора» в полі «Стоимость» ставимо умову «>20». Для поля «Автор» ставимо умову «Like "Г*" or Like "R*"». Назву функції «Like» можна не вводити, вона буде додана автоматично. Щоб переглянути результат натисніть кнопку «!» або виберіть пункт меню «Вид → Режим таблицы». Результат роботи запиту показано на рис. 12.
^ Підсумкові запити
Щоб вибрати не окремі записи, а підсумкові значення для певної групи даних (наприклад, кількість книг по інформатиці, по кожному видавництву, кількість читачів по кафедрах тощо), треба скористатись підсумковими запитами. Таким запитам треба вказати характер групування даних і тип групової операції. Щоб конкретизувати характер групування входимо до «Груповые операции → Группировка». Це можна зробити так:

ввійти до режиму «Конструктора» запиту;

сформувати поля, що будуть складати запит;

встановити курсор всередину таблиці із параметрами запиту ( у режимі «Конструктора») запитом;

натиснути на піктограму, що знаходиться на панелі інструментів (виглядає як значок «»). Замість піктограми можна клацнути правою кнопкою миші й вибрати з контекстного меню пункт «Групповые операции».

У таблиці з параметрами запиту Access додасть рядок з іменем «Групповая операция». Щоб створити групу записів з однаковими значеннями по деякому полю, треба в рядку «Групповая операция» цього поля ввести «Группировка». Access вибере множину записів з однаковим значенням поля групування й підрахує для них підсумки. Такі дії Access повторить для кожної групи. Спосіб рахування підсумків вказують у тому ж рядку «Групповая операция». Для цього переходимо до поля, по якому планують визначити підсумкове значення. Клацаємо лівою кнопкою миші на списку «Группировка». Список розкриється, з нього вибираємо тип операції для одержання підсумків. Список операцій має такі функції:

SUM – обчислення суми значень для групи;

AVG – середнє значення поля для даних із групи записів;

MIN – мінімальне значення для даних із групи записів;

MAX – максимальне значення для даних із групи записів;

COUNT – кількість записів, у яких є значення із групи;

STDEV – стандартне відхилення;

VAR – дисперсія;

FIRST – значення в першому записі групи;

LAST – значення в останньому записі групи.

Продемонструємо процес будування підсумкового звіту на такому прикладі: порахуємо кількість читачів з кожної кафедри. Спочатку будуємо звичайний запит у режимі «Конструктора», додаємо до нього таблицю «Читатели». Включаємо до результуючого звіту поля «Кафедра» і «Прізвище». Натискаємо на панелі інструментів піктограму «Групповые операции». У полі «Кафедра» вказуємо «Группировка». Переходимо до поля «Фамилия». У рядку «группировка» для цього поля вибираємо функцію «Count» (рис. 13). Результат обробки запиту показано на рис. 14.

Важливо! Щоб при перегляді запиту поля мали зрозумілу назву, треба скоригувати їх властивості. Для цього ставимо курсор на колонку поля у вікні «Конструктора запитів», входимо до меню «Вид → Свойства». У полі «Подпись» друкуємо заголовок (рис. 15)
^ Перехресний запит
Це особливий тип підсумкового запиту, який дозволяє організувати підсумкові значення у таблицю, що нагадує зведену таблицю в Excel. Головною перевагою перехресного запиту є те, що його можна будувати по двом ключовим полям. Таким чином, перехресний запит може трансформувати лінійну таблицю у таблицю з двома вимірами.

Наприклад, побудуємо запит для визначення сумарної вартості книжок для різних років видання по кожному видавництву. Найзручніший спосіб будування перехресних запитів — скористатися кнопкою «Создать» у вікні роботи із запитами (рис. 9). Натискаємо її. На екрані з’явиться вікно з переліком типів запитів. Вибираємо «Перекрестный запрос» (рис. 16).

Кожний етап діалогу щодо будування перехресного запиту представлено в окремому вікні. По черзі вибираємо таблицю, назви рядків, назви стовпців і функцію обробки даних, кожного разу натискаючи кнопку «Далее». У нашому випадку вказуємо ім’я таблиці «Книги», найменування рядків «Год»; найменування колонок «Издательство», для поля «Стоимость» вказуємо функцію обробки даних «Sum». Встановимо прапорець «Итоговое значение по срокам» в активне положення. Закінчивши формування запиту, натискаємо на кнопку «Виконати». Результат показано на рис.17.

Перехресний запит можна сформувати також в режимі «Конструктора». На рис. 18 показано параметри щойно побудованого запиту, який відкрито у режимі «Конструктора». Зрозуміло, що джерелом даних для запиту є таблиця «Книги».



^ Запити, що відбирають дані із декількох таблиць
Для формування таких запитів треба включити до верхньої частини вікна «Конструктора запитів» декілька таблиць. Якщо між таблицями існують зв’язки (було побудовано «схему даних»), вони відобразяться у цьому вікні автоматично. Тепер можна включати до запиту поля із декількох таблиць.

Існує два різновиди об'єднання двох таблиць: «внутрішнє» і «зовнішнє». Якщо об'єднання внутрішнє (саме його ви встановили), то у запиті будуть об'єднані записи, у яких співпадають значення полів, що зв'язані. Якщо будь-який запис в одній таблиці не має відповідного запису в іншій таблиці, він не буде включений до результуючого звіту. Зовнішнє об'єднання ми розглянемо пізніше.
^ Створення полів для обчислень
На відміну від електронних таблиць у таблицях баз даних ніколи не зберігають інформацію, яку можна одержати в результаті обчислень. Це пов’язано з тим, що здебільшого такий підхід вимагає значних витрат пам'яті й сповільнює пошук і обробку даних. Тому всі необхідні обчислення виконують у запитах, створюючи для цього спеціальні поля. Щоб створити таке поле у вільному стовпці конструктора запитів записують нове ім'я, знак “:”, а потім вираз, що обчислює необхідне значення. У виразі можна використати знаки операцій, функції, звертатися до значень інших полів. Наприклад, щоб обчислити вартість замовлення, маючи значення «СтоимостьЕдиницы» і «КоличествоЕдиниц» треба у новому стовпці записати формулу «СтоимостьЗаказа:[СтоимостьЕдиницы]*[КоличествоЕдиниц]». Імена полів бази даних записують у квадратних дужках. У формулах можна використати вбудовані функції Access. Ось деякі з них:

iif(умова; вираз1; вираз2) — якщо вираз «умова» виконується , функція обчислює значення «вираз1», якщо умова не виконується — обчислює значення «вираз2». Функція iif() подібна до ЕСЛИ() в Excel.

DateDiff(“d”; дата1; дата2) — знаходить різницю між двома датами, результат представляє у днях. Якщо перший аргумент дорівнює «m», різницю буде представлено у місяцях, якщо він дорівнює “y” — у роках. Повний перелік функцій та їх параметрів можна побачити у довідковій системі або у вікні «Построитель выражений». «Построитель виражений» — це спеціальний інструмент, що суттєво полегшує набір складних формул. Оскільки основні складові формули при використанні «Построителя…» набираються автоматично, використання цього інструменту суттєво знижує кількість помилок.

Ми проілюструємо використання обчислювальних полів на запиті, що відбирає й поєднує записи з двох таблиць: «Книги» і «ЧитКниги». Мета запиту — показати, які книги були видані читачам. У запиті створимо поле для обчислення з іменем «Пеня». У цьому полі буде нараховуватись пеня на кожну книгу, яка не булла повернута вчасно. Розмір пені дорівнює 1% від вартості книги за кожний прострочений день.

Переходимо на закладку «Запросы». Натискаємо кнопку «Создать». У вікні, що з’явиться, вибираємо варіант «Конструктор». Це означає, що параметри запиту будемо формувати в режимі «Конструктора…». Додаємо до запиту таблиці «Книги» й «ЧитКниги». Оскільки для вказаних таблиць було створено схему даних, її буде відображено у верхній частині вікна запиту. Якщо схеми даних не існує, створіть її. Це можна зробити прямо у вікні запиту. Нагадаємо, що ми маємо зв’язати таблиці за інвентарним номером книги (поле «Инв№» ). Тип зв’язку таблиць «Книги» та «ЧитКниги» — «один до багатьох».

До запиту включаємо такі поля: «Автор», «Название», «Стоимость», «Инв№», «Дата выдачи», «Дата возврата», «NB». Останнє поле нам знадобиться для організації зв’язку з таблицею «Читатели». Зберігаємо запит з іменем «Пример_1». У першому вільному стовпчику нижньої частини вікна створіть поле, що обчислюється, з ім'ям «Пеня». Для цього наберіть у верхньому рядку (де розташовано ім'я поля) такий текст: «Пеня: IIf([Дата возврата]< Date();DateDiff("d";[Дата возврата];Date())*0,01* [Стоимость];0)».

Для створення виразу можна використати «Построитель виражений». Натискаємо кнопку «Построить» на панелі інструментів. Відкриється вікно «Построителя…». У лівій частині вікна перелічені доступні об’єкти Access для будування формули: «Таблицы», «Запроси», «Формы», «Функції», «Отчеты» тощо. Якщо ліворуч від назви об’єкту стоїть позначка «+»(«плюс»), вказаний об’єкт має багато значень. Розкриваючи потрібні закладки, ви одержите доступ до окремих елементів поточної бази даних, з яких можна побудувати формулу. Наприклад, розкриваючи таблиці або звіти, ви можете дістатись до полів, що до них входять. Натискаючи кнопку «Вставить» об’єкт буде перенесено до спеціального вікна, де будується формула. Лишається розставити знаки операцій поміж об’єктами і завершити формулу. Детальне опанування «Постороителя выражений» ми лишаємо для самостійного опрацювання. Збережіть запит з іменем «Список1». Виконайте запит і перегляньте результат. Розмір пені, зрозуміло, залежить від поточної дати. Спробуйте змінити дату повернення книги. Переконайтесь, що сума пені також змінилась.
^ Формування підсумкового багатотабличного запиту
Спробуємо обчислити для кожного читача кількість взятих ним книг, їх загальну вартість і пеню, що нарахована. Для цього сформуємо новий запит. Включаємо до нього таблиці «Книги», «Читатели» і запит «Пример_1». З таблиці «Читатели» до запиту включаємо поля «Фамилия», з таблиці «Книги» — поля «Инв№» і «Стоимость», із запиту «Пример_1» — поле «Пеня». На панелі інструментів вибираємо піктограму «Групповые операции», у бланку запиту з'являється рядок «Групповая операция». Для поля «Фамилия» вибираємо «Группировка», для поля «Стоимость» операцію підрахунку суми – «SUM», для поля «Инв№» — операція обчислення кількості «Count» і для поля «Пеня» — операцію «SUM». Параметри запита у режимі «Конструктора» показані на рис. 19. Щоб результат мав придатний вигляд, поставимо зрозумілі підписи для кожної колонки. Для цього треба ввійти у коригування властивостей полів (перейти до режиму «Конструктора…», встановити курсор на поле, натиснути праву кнопку миші й вибрати з контекстного меню «Свойства»). У переліку «Свойства» треба поставити зрозумілий текст у параметрі «Подпись». Результат обробки запиту показано на рис. 20.
^ Формування запитів типу «Записи без підлеглих»
Ці запити відбирають записи, що не зв’язані із записами в іншому списку. Для роботи з такими запитами треба змінити тип об'єднання таблиць на «зовнішній». При «зовнішньому» зв’язуванні до запиту обов'язково потраплять всі записи із першої таблиці. Записи з другої таблиці будуть додані, коли значення зв'язаних полів співпадають. Якщо в другій таблиці такого запису немає, то до запису з першої таблиці додаються поля з порожніми значеннями. У конструкторі таблиць зовнішнє зв’язування зображається лінією зі стрілкою.

Для прикладу побудуємо запит, що знаходить список книг, які не видані на руки читачам. До запиту включимо дві таблиці «Книги» і «ЧитКниги». Нам знадобляться поля «Инв№», «Шифр», «Автор», «Назва» з таблиці «Книги» і поле «Инв№» з таблиці «ЧитКниги». Знайдемо ті записи, які є в таблиці «Книги» і відсутні в таблиці «ЧитКниги». Змінимо параметри об'єднання таблиць. Ставимо курсор на лінію зв’язку між таблицями, клацаємо правою кнопкою миші й обираємо з контекстного меню пункт «Параметры объединения». Із запропонованих варіантів обираємо «Объединение ВСЕХ записей из «Книги» и только тех записей из «ЧитКниги», в которых связанные поля совпадают».(рис. 21).

М
и створили зовнішнє об'єднання, до якого включені всі записи з таблиці «Книги». Для книг, що не мають записів у таблиці «ЧитКниги» встановлюється значення «Null». Тому для поля «Инв№» з таблиці «ЧитКниги» встановимо параметр «Условие отбора» в значення «Is Null» і знімемо прапорець «Вывод на экран». Параметри запиту показано на рис. 22. Виконуємо запит, результат роботи показано на рис. 23.
^ Модифікація даних за допомогою запитів
Запити, які змінюють значення групи записів називаються запитами на оновлення даних. За допомогою такого запиту можна, наприклад, змінити вартість книг залежно від дати їх видання, збільшити вартість на 20% для книг 1998 року видання тощо. Оскільки запит на оновлення змінює інформацію у базі даних, для захисту від можливих помилок завжди робіть бази. Для цього перейдіть у вікно бази даних, виберіть таблицю і виконайте команду «Правка →  Копировать». Далі виберіть команду «Правка → Вставить» і у вікні діалогу дайте копії таблиці нове ім'я.

Запит на відновлення роблять на основі простого запиту. Тому спочатку сформуємо запит на вибірку. Ретельно переконаємось у тому, що створений запит без помилок вибирає потрібні записи. Після цього трансформуємо запит на вибірку у запит на оновлення. До запиту включимо поля «Год» і «Стоимость» з таблиці «Книги». При цьому запит побудуємо так, щоб дати можливість вводити рік видання як параметр. Для цього в рядку «Условие отбора» у квадратних дужках запишемо вираз «[Вкажіть рік]». Під час виконання запиту цей текст з'явиться в діалоговому вікні, куди можна ввести потрібне значення.

Щоб перетворити запит на вибірку у запит на оновлення переходимо до режиму «Конструктора запросов»», в основному меню вибираємо «Запрос → Обновление». Бачимо новий рядок в параметрах запиту. Він називається «Обновление». У цьому рядку для поля «Стоимость» вводимо формулу «[Стоимость]*([Наценка]+100)/100» для перерахунку вартості книг (рис. 24). Цей вираз використовує ще один параметр «[Наценка]», що дозволяє вводити розмір націнки у відсотках.


Виконуємо запит. На екрані послідовно з'явиться вікно із запитом щодо розміру націнки, вводимо «50». Потім Access виведе вікно діалогу із запитом щодо року видання. Вводимо значення «2000». Access попередить, що буде змінено 2 записи (згідно нашого прикладу). Підтверджуємо дозвіл на заміну. Відкриваємо таблицю і аналізуємо результат: вартість двох книжок відання 2000 р. збільшена на 50% (рис. 25).
^ Запит на формування нових таблиць
Запити цього типу створюють нову таблицю і записують до неї дані з вже існуючих таблиць або запитів. Для прикладу ми побудуємо за допомогою запиту таблицю з даними про читачів, які вчасно не повернули книги. Як і у попередньому випадку, починаємо з формування запиту на вибірку. До запиту включимо таблицю «Читатели» і запит «Пример_1», який знаходив суму пені для кожного читача. З таблиці «Читатели» вибираємо поля «NB», «Фамилия», «Кафедра», «Телефон».

Із запиту «Пример_1» вибираємо поля:

«Инв№», для нього порахуємо функцію «Count»;

«Стоимость» для нього порахуємо функцію «Sum»;

Щоб відібрати тільки читачів, що мають заборгованість, вказуємо для поля «Пеня» умову «>0» (рис. 26).



Виконуємо запит і переконуємось у тому, що він правильно відбирає записи.

Щоб перетворити запит на вибірку у запит на формування нової таблиці режим «Конструктора запросов», в меню вибираємо «Запрос → Создание таблицы». Access запропонує ввести ім’я нової таблиці, у прикладі було вибрано ім’я «Должники».

Виконуємо запит. Access повідомить в окремому вікні про кількість записів, що будуть передані до нової таблиці. У цьому ж вікні пропонується підтвердити намір на створення таблиці або відмовитись від цієї дії. Натискаємо кнопку «Да». Переходимо на закладку «Таблицы». У списку маємо побачити нову таблицю «Должники». Відкриваємо її на перегляд. Результат показано на рис. 27.
^ Створення запитів на видалення даних
Такі запити дозволяють видаляти з таблиць групи записів, що задовольняють вказаним умовам. Наприклад, створимо запит, що видаляє запис з таблиці «ЧитКниги» записи про читачів на момент повернення ними книги.

Важливо! Для захисту від можливих помилок зробіть резервну копію таблиці. Для цього перейдіть у вікно бази даних, виберіть таблицю, виконайте команду «Правка → Копировать». Далі виберіть команду «Правка → Вставить», дайте копії таблиці нове ім'я.

Як і в попередніх випадках, спочатку розробляємо запит на вибірку даних. В подальшому ми перетворимо його у запит на видалення даних. Запит побудуємо на основі таблиці «ЧитКниги», з якої візьмемо поля «Инв№» і «NB». Для цих полів вводимо параметри «[Введите Инв№ книги]» і «[Введите номер читат. билета]». При виконанні цього запиту послідовно з'являться два вікна для введення значень цих полів у діалоговому режимі.

Щоб перетворити запит на вибірку у запит на видалення даних переходимо в режим «Конструктора запросов», в меню вибираємо «Запрос → Удаление». У параметрах запиту з’явиться рядок «Удаление».

Запишемо у цьому рядку умову «Условие». Запустіть запит на виконання. На екрані з’явиться вікно з повідомленням про кількість записів, що будуть видалені з таблиці. Для підтвердження натисніть кнопку «Да». Відкрійте таблицю й переконайтесь, що записи видалено.
^ Будування форм
Форми — основний засіб інтерфейсу користувача при роботі з Access. Форми можуть вирішувати декілька завдань:

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

^ Управління ходом виконання програми. Форма може мати в своєму складі елементи управління (кнопки, списки, поля тощо), які пов’язані з макросами й процедурами Visual Basic. Робота цих макросів і програм може бути спрямована на обробку даних, ситуаційну зміну алгоритмів обробки даних, виконувати підпорядковані форми, запити, звіти, фільтрувати інформацію, виконувати багато інших дій.

^ Повідомлення про хід роботи. Через макрокоманду «Сообщения» або функцію MsgBox() Visual Basic можна повідомити користувача про хід роботи програми, проміжні результати, помилки, що виникають.

^ Друкування інформації з бази даних. Форму разом з її даними можна надрукувати. Для цього ви повинні задати параметри друку.
^ Створення форм для перегляду і введення даних
Найпростіший шлях для створення форми для роботи з таблицями – заподіяти інструмент «Автоформа». Переходимо на закладку «Таблицы». Становимось на таблицю «Читатели». Входимо до меню «Вставка →  Автоформа». Access побудує форму, що зображено на рис. 28.<