Проектування бази даних. створення таблиць та форм в Ms Access

Мета: поняття бази даних. Проектування бази даних. Знайомство з системою управління базами даних Microsoft Access. Познайомитися з технологією створення таблиць та зв’язку між даними різних таблиць.

Теоретичні відомості

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

Система управління базами даних Microsoft Access входить до складу пакета Microsoft Office. Вона дозволяє розв’язувати широке коло завдань користувачів без програмування. Запуск системи Access здійснюється з ОС Windows аналогічно тому, як запускається будь-яка прикладна програма. Після її завантаження на екрані з’являється вікно створення або відкриття бази даних. На малюнку 10.1 показано вигляд відкритої БД.

Екран поділений на дві частини. В лівій знаходиться область переходів між елементами, а в правій відкриті елементи. Меню області переходів дозволяє змінити порядок відображення елементів у панелі, встановити фільтр для елементів (Рис.12.1).

Елементами БД Microsoft Access є таблиці, запити, форми, звіти, макроси, модулі.

 

Рис. 12.1

Створити елемент БД можна використовуючи панель Створення. Там представлені групи команд для кожного з елементів БД (Рис.12.2).

 

Рис. 12.2

Створювати елемент БД можна в декількох режимах: конструктор, майстер, шаблон.

Процес створення бази даних проходить у декілька етапів:

1. Створення структури БД;
2. Введення даних;
3. Редагування структури i даних;
4. Оформлення звітів.

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

Створення бази даних в Microsoft Access.

1. Запустити програму Microsoft Access.
2. У діалоговому вікні вибрати пункт Новая база данных.
3. На правій панелі вікна вибрати місце зберігання бази даних і ввести її назву. Натиснути Создать (Рис.12.3).

Одразу після створення нової бази відкривається вікно таблиці в режимі заповнення даних. Можна скористатися цим і розпочати введення даних. Автоматично програма буде визначати типи полів. Якщо створення таблиці в такий спосіб не зручне, то можна перейти в режим К онструктор на панелі Главная (Рис.12.4).

Таблиця. Основою бази даних є таблиця - це місце зберігання даних. БД може містити одну або багато таблиць, в залежності від кількості даних та мети створення БД. Стовпці в таблиці називають полями, рядки таблиці називають записами. Кожне поле має ім’я, тип і властивості.

Одна БД може містити декілька таблиць. Наприклад, в одній таблиці розташовані дані про авторів книг, в другій – відомості про книги, в третій – назви розділів. Розподіл даних за різними таблицями роблять для того, щоб систематизувати дані, покращити швидкодію програми, зробити легшою роботу з БД (ввід даних, огляд даних, отримання даних), уникнути надмірності введення даних або їх недостатності.

Одне або декілька полів необхідно визначити як ключові. Ключове поле (Ñ) однозначно визначає будь-який запис у таблиці, тому в якості такого поля можна вибрати № по порядку, ідентифікаційний код, номер паспорту, машини та ін. Але такі поля, як ім’я, прізвище не можуть бути ключовими, оскільки для різних людей вони можуть співпадати.

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

Приклад таблиці.

 

Таблиця Банк
Назва поля	Тип даних
Вкладник банку	Текст
Відділення банку	Текст
ÑШифр рахунку	Числовий
Дата вкладу	Дата/час
Сума вкладу	Грошовий
Термін вкладу	Числовий

Дана таблиця Банк містить шість полів. Кожне з полів має свій тип. Поле Шифр рахунку є ключовим полем.

Створення таблиці у режимі конструктора.

1. На панелі Создание натиснути кнопку Конструктор таблиц.
2. У вікні конструктора заповнити назви полів та їх типи, заповнити властивості кожного поля окремо (розмір, формат та ін.) (Рис.12.1).
3. Призначити ключове поле (меню Конструктор-Ключевое поле).
4. Зберегти таблицю, дати їй назву.

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

Типи полів таблиці:

1. Текстове поле – літери, цифри та інші символи. Розмір поля: від 1 до 255 символів. Можна змінювати формат поля, використовувати маску вводу.
2. Числове поле – цілі числа, числа з плаваючою крапкою. Поле може мати різний розмір:

¾ байт (від 0 до 255)

¾ ціле (від –32 768 до 32 767)

¾ довге ціле (від -2 147 483 648 до 2 147 483 647)

¾ з плаваючою крапкою (4 байта)

¾ з плаваючою крапкою (8 байт).

1. Поле Дата / время (для запису дат та часу)
2. Логічне поле (може записуватись одне з двох значень: “ так” чи “ні”)
3. Поле типу Лічильник (Счётчик). Використовують як лічильник записів, часто цей тип призначають для ключового поля. Поле типу Лічильник не потрібно заповнювати, воно заповнюється автоматично.
4. Поле типу Memo. Це довге поле для тексту або чисел (до 64 000 символів).
5. Поле об’єкта OLE. Вміщує не інформацію про об’єкт, а посилання на ім’я об’єкта (ім’я додатку, малюнка та ін.).
6. Поле Мастер подстановок. Цей тип використовують для створення зв’язку між таблицями. Майстра підстановок використовують у підлеглій таблиці. З його допомогою вказують дані, з якого поля основної таблиці буде використовувати підлегла. Тип поля після підстановки стає числовим не залежно від того, який тип був у головній таблиці.

Наприклад, для дати можна встановити:

середній формат: 1 вер. 12 р. або короткий формат: 1.09.2012.

Робота з таблицями. Режим Таблица дозволяє легко фільтрувати і впорядковувати (сортувати) дані. Для цього на панелі Главная є відповідні команди (Рис.12.5). Діють вони так само, як в MSExcel. Потрібно стати курсором на поле і натиснути команду. Для пошуку запису в таблиці можна скористатися командою Найти.

Рис. 12. 5

Форма - це засіб створення діалогового інтерфейсу користувача для роботи з БД. За допомогою форми зручніше вводити або шукати необхідні дані. Microsoft Access дозволяє створення форм на основі таблиці в режимі макета, конструктора або майстром (Рис.12.6).

Рис. 12. 6

Створення форм в режимі макета.

1. Виділити таблицю в області переходів.

2. Натиснути кнопку Форма або Разделенная Форма на панелі Создание.

3. З’явиться форма в режимі макету (Рис.12.6), де можна переміщати поля, міняти їх місцями і змінювати їх розміри. Якщо поле зайве, то його видаляють (клавішею Del). Якщо у форму треба додати поле, то його перетягують мишею зі Списку полів у правій частині вікна програми.

4. Зберегти форму.

Для заповнення форми її слід відкрити в режимі Формы. Кнопки переходу між записами дозволяють проглянути всі записи або додати нові (Рис.12.7). Щоб змінити зовнішній вигляд форми, слід скористатися командами в меню Работа с макетами форм, яке з’являється, коли переходять в режим макету.

Режим Конструктора форм являє собою робочу область, на яку виносяться поля з панелі Конструктор -Э лементы управления. Ця панель з’являється як тільки форма переходить у режим конструктора (Рис.12.8).

Рис. 12.7

 

Рис. 12.8

Додавання поля у форму в режимі конструктора.

1. На панелі Конструктор вибрати інструмент поле.
2. В робочій області протягнути затисненою лівою кнопкою миші там, де має стояти поле.
3. З’явиться зображення нового елемента. У вікні Свойства можна поміняти назву об’єкта і його параметри.
4. Якщо у вікні немає панелі Свойстваполя, то на панелі Конструктор треба активізувати кнопку Страница с войств.

Поле крім даних з таблиці може містити результати обчислень (Рис.12.9). Таке поле називають обчислювальним. В поле потрібно ввести вираз, який починається зі знаку «=». Операндами можуть бути імена полів (вони вказуються в квадратних дужках [ ]), константи, функції.

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

 

Рис. 12.9

ЗАВДАННЯ

1. Створити нову базу даних.

2. Створити структуру таблиці Банк_Інтеграл за наступною схемою:

Назва поля	Тип даних	Властивості поля
Вкладник банку	Текст	Розмір поля 25
Відділення банку	Текст	Розмір поля 20
ÑШифр рахунку	Числовий	Розмір поля – цілий
Дата вкладу	Дата/час	Формат поля – коротка дата
Сума вкладу	Грошовий	Формат поля - # «грн»
Термін вкладу	Числовий	Розмір поля – цілий Формат поля - # «міс.»

3. Ввести дані відповідно до зразка.

Відділення банку	Вкладник банку	Шифр рахунку	Дата вкладу	Сума вкладу	Термін вкладу
Північне	Савчук М.Р.		13.03.02	40 000 грн.	40міс.
Західне	Гончар О.О.		30.12.03	130 000 грн.	55міс.
Північне	Фірман П.О.		02.02.11	10 000 грн.	34міс.
Тернопільське	Кличко П.І.		05.03.10	120 000 грн.	23міс.
Східне	Кіт П.Т.		16.11.06	75 000 грн.	18міс.
Північно-Західне	Свічка Т.А.		06.02.12	16 000 грн.	12міс.
Північно-Східне	Корж С.О.		13.04.11	46 000 грн.	25міс.
Тернопільське	Шевчук В.С.		17.10.09	56 000 грн.	28міс.
Південне	Кравчук І.І.		23.08.08	230 000 грн.	45міс.
Центральне	Бондар І.І.		05.06.10	102 000 грн.	32міс.
Північно-Східне	Ткачук П.П.		03.05.11	132 000 грн.	44міс.
Тернопільське	Іванків А.Т.		24.09.09	98 000 грн.	33міс.
Північне	Ярема Н.О.		14.05.05	76 000 грн.	41міс.
Східне	Ковальчук П.В.		15.11.11	45 000 грн.	36міс.

4. У режимі Таблиця відсортувати дані за полем Дата вкладу.

5. Показати в таблиці дані тільки по Тернопільському відділенню. Відмінити фільтр.

6. Показати в таблиці дані тільки по Тернопільському і Північному відділенням. Відмінити фільтр.

7. Вибрати в таблиці всі записи про вкладників, які зробили вклади у 2011 році. Відмінити фільтр.

8. Вибрати в таблиці всі записи про вкладників, у яких сума вкладу більша ніж 50 000 грн, але менша ніж 100 000 грн.

9. Створити форму Форма_Банк_1 на основі таблиці Банк_Інтеграл в режимі макета (Форма).

10. Зменшити розміри полів в макеті форм.

11. Форму Форма_Банк_1 відкрити в режимі форми і ввести 2 нових записи.

12. Створити форму Зведена діаграма, на якій відображено суми вкладів за банками.

13. Створити форму Форма_Банк_2 у вигляді таблиці за допомогою майстра форм (команда Создать-Другие формы-Мастер форм). Включити туди поля Відділення банку, Вкладник, Дата вкладу, Сума вкладу, Термін вкладу. Перейти в режим конструктора. У примітці форми створити елемент Поле для підсумовування по полю Сума вкладу. Встановити формат поля: # «грн». Формула для обчислення: =Sum([Сума вкладу]).

14. Побудувати таблицю під назвою Валюта з такими даними:

Назва поля	Тип даних	Властивості поля
ÑID	Лічильник	Розмір поля Ціле
Назва валюти	Текст	Розмір поля 25

15. Ввести дані в таблицю Валюта за зразком

ID	Назва валюти
	Долар США
	Євро
	Фунти стерлінгів
	Німецькі марки
	Французькі франки

16. Зберегти таблицю.

17. Створити форму в режимі Разделенная форма на основі таблиці Банк_Інтеграл, в яку включити такі поля: Відділення банку, Вкладник, Дата вкладу та Сума вкладу.

18. У формі розмістити елементи Поле зі списком на основі поля Назва валюти таблиці Валюта та Поле з назвою курс валюти. Для поля Курс валюти у вікні властивостей на вкладці Все вказати назву поля - Курс валюти.

19. За допомогою елемента Поле створити обчислювальне поле під назвою Сума вкладу у валюті для обчислення еквівалента суми вкладу в іншій валюті. Формула для обчислення:

=[Сума вкладу]/[Курс валюти].

20. Зберегти форму.

21. У меню області переходів відсортувати елементи за типом об’єктів.

22. Зберегти файл.

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

1. Як створити базу даних?
2. З якими об’єктами працює база даних?
3. Що таке таблиця?
4. Як задавати властивості полів?
5. Що таке ключове поле?
6. Як змінити структуру таблиці?
7. Що таке форма?
8. Які види форм створює база даних?
9. Які є режими відображення форми?
10. Як створити обчислюване поле в формі?

 

 

15. Технологія створення запитів
та звітів в Ms Access

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

Теоретичні відомості

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

Рис. 13.1

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

Створення запиту через майстра.

1. Перейти на вкладку Создание. Натиснути кнопку Мастер запросов.

2. У діалоговому вікні вибрати тип запиту простий і таблицю, на основі якої буде створено запит. Якщо БД містить зв’язані таблиці, то поля слід вибирати із основної таблиці, бо підлегла містить тільки коди записів, за якими ведеться зв’язок.

3. В наступних діалогових вікнах вибрати тип звіту і вписати його назву. Натиснути Готово.

Для внесення змін у готовий запит потрібно скористатись кнопкою Конструктор запросов (Рис.13.1). З’явиться вікно побудови запиту (Рис.13.2). У верхній частині вікна виводяться таблиці, на основі яких будується запит, а в нижній частині вказані поля, що вибрані для запиту.

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

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

Видалення полів зі звіту: в нижній частині виділити стовбець і натиснути Del.

Рис. 13.2

В області вибраних полів є рядок Условия отбора, який слід використовувати для відбору і фільтрації даних (Рис.13. 3). Рядок Или цього вікна дозволяє ввести додаткову умову для фільтру. В умові використовують знаки <, >, = для даних типу Число, Счётчик, Дата, параметри True, False, And, Or, порівнюють текстові значення.

Для числових значень використовують знаки порівняння <, >, =, <>, >=, <=.

Для дати використовують знак «#». Наприклад запис: > #1.09.2012# означає всі дати після 1.09.2012.

Для текстових значень використовують лапки «». Наприклад, запис «Західне» означає відбір всіх записів із такою назвою.

Також в умові використовують квадратні дужки [ ]. Якщо, потрібно вивести запит з якоюсь назвою, то в полі умови в квадратних дужках пишуть текст запиту. Такий запит називають параметричним. Тоді після виклику запиту з’явиться діалогове вікно, в якому слід ввести дані. Наприклад (Рис.13. 3):

Рис. 13.3

Побудова виразів (Построитель выражений) для вибірки.

1. Викликати контекстне меню в Условие отбора і вибрати команду Построить (Рис.13.4).

2. У вікні Побудови виразів (Построитель выражений)побудувати вираз, використовуючи елементарні операнди та вбудовані функції. Подвійний клік мишею у вікні на елементі запиту робить підстановку його у вираз.

3. Натиснути ОК.

Рис. 13.4

Існує декілька видів запитів: на вибірку, на додавання, на виділення, на оновлення.

Змінити тип запиту можна у вікні конструктора, викликавши контекстне меню в області таблиць для запиту Тип запроса (Рис.13.5).

Рис. 13.5

Запит на оновлення робить заміну значень полів у таблиці. Наприклад: змінилася назва установи і потрібно у всій таблиці знайти стару назву і змінити її на нову.

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

1. Створити простий запит для таблиці, вибравши всі поля таблиці.

2. Перейти в режим конструктора і в контекстному меню в області таблиць вибрати команду Тип запроса-Обновление (Рис.13.6. а).

1. В полі, яке потрібно змінити, в рядку Условие отбора вказати старе значення поля, в рядку Обновление вказати нове значення. В режимі таблиці можна переглянути записи, які будуть оновлені.
2. На панелі інструментів натиснути кнопку . В діалоговому вікні прийняти зроблені зміни.
3. Закрити вікно запиту. Переглянути результати у таблиці даних.

а б

Рис. 13.6

 

Запит на додавання дозволяє додавати записи з таблиці 1 у таблицю 2 БД. Тому потрібно, щоб у запиті були вказані поля, спільні для обох таблиць БД.

Порядок створення запиту на додавання.

1. Створити простий запит для таблиці 1, вибравши всі поля таблиці.

2. Перейти в режим конструктора.

3. Вибрати (в контекстному меню) тип запиту Добавление.

4. В діалоговому вікні вказати назву таблиці 2, куди будуть додані записи (Рис.13.6.б).

5. В конструкторі в рядку Додавання будуть відображені всі поля таблиці 2, що відповідні полям таблиці 1. В режимі таблиці можна переглянути записи, які будуть додані.

6. На панелі інструментів натиснути кнопку . У діалоговому вікні прийняти зроблені зміни. Якщо таблиці містили записи з однаковим ключовим полем, то такі записи не будуть додані в таблицю 2.

7. Закрити вікно запиту. Переглянути результати у таблиці 2.

Запит на видалення дозволяє вилучати записи з таблиці БД.

Запит на вилучення робиться аналогічно запитам на додавання (Рис.13.7).

Рис. 13.7

Звіти. Звіти будують на основі таблиць і запитів у зручному для огляду (роздрукування) вигляді. Звіти створюються в тих випадках, коли потрібно наочно представити на екрані або папері інформацію, яка є в базі даних (Рис.13.8).

Рис. 13.8

Способи створення звітів:

· Конструктор.

· Майстер звітів.

· Автоматичне формування звіту через макет.

Створення звіту через майстра.

1. Перейти на вкладку Создание. Натиснути кнопку Мастер отчетов.

2. У діалоговому вікні вибрати таблицю або запит, на основі яких буде створено звіт.

3. Вибрати поля цієї таблиці, які переносяться у звіт. З вікна Доступные поля перенести потрібні поля у вікно Вибранные поля. Натиснути кнопку Далее.

4. В другому діалоговому вікні вибрати Уровни группировки. Натиснути кнопку Далее.

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

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

7. Дайте назву звіту. Поставте перемикач у поле Просмотреть отчет. Натиснути кнопку Готово.

Коли звіт створено, відкривається меню для роботи зі звітами (Рис.13.9).

Рис. 13.9

На вкладці Упорядочить знаходяться команди зміни макету звіту.

Для огляду звіту потрібно виділити назву звіту в області переходів і натиснути кнопку ОТКРЫТЬ.

За допомогою кнопки КОНСТРУКТОР можна змінити структуру звіту. Побудова нового звіту за допомогою конструктора робиться аналогічно побудові форми.

 

ЗАВДАННЯ

1. Відкрити створену раніше базу даних.

2. Побудувати запит 1 на основі таблиці Банк_Інтеграл в який включити поля Відділення банку, Вкладник та Сума вкладу. Переглянути результати запиту.

3. Побудувати запит 2 на основі таблиці Банк_Інтеграл в який включити всі поля таблиці. Зняти відображення полів Відділення банку, Шифр рахунку та відсортувати за полем Вкладник. Переглянути результати запиту.

4. Створити запит 3, в якому відобразити вкладників, які внесли вклади у 2011 році. (використати умову для поля Дата вкладу *.*.2011). Переглянути результати запиту.

5. Створити запит 4 в якому відобразити Вкладників із Терміном вкладу більше ніж 30 міс. Для поля Відділення банку вивести параметричний запит на назву відділення (поставити в умові: [Введіть назву відділення банку]). Переглянути результати запиту.

6. Побудувати запит 5 на основі таблиці Банк_Інтеграл в який включити поля Відділення банку, Вкладник, Дата вкладу, Термін вкладу та Сума вкладу для тих вкладів, що внесені більше ніж 1 рік тому. Формула для розрахунку: Year(Date())-Year([Дата вкладу])>1. Переглянути результати запиту.

7. Зробити дві копії таблиці Банк_Інтеграл, дати їм назви відповідно Банк1 і Банк2.

8. Побудувати для таблиці Банк2 запит на оновлення за допомогою якого зробити заміну назви відділення банку «Північне» на «Головне». Подивіться результати в таблиці Банк2.

9. Відкрийте таблицю Банк2 і в ручну змініть значення ключового поля для всіх записів, щоб їх значення не співпадали зі значеннями таблиці Банк1 (наприклад збільшити на 1).

10. На основі таблиці Банк1 створіть звіт на додавання записів з таблиці Банк1 в таблицю Банк2.

11. Відкрийте таблицю Банк2. Перегляньте результат.

12. На основі таблиці Банк_Інтеграл за допомогою майстра звітів створити звіт1. Переглянути звіт. В звіт включити всі поля.

13. На основі Запиту 1 за допомогою майстра звітів створити звіт2. Змінити вигляд звіту на табличний. Встановити внутрішні поля звіту вузькі, а зовнішні – широкі. Переглянути звіт.

14. На основі Запиту 3 за допомогою майстра звітів створити звіт3. Перейти в макет звіту. Назву звіту зробити більшим шрифтом на 2 пт. Поміняти колір назви звіту на синій. В рядок заголовку звіту вставити емблему банку. В рядок примітки звіту вставити нове поле і вписати в нього функцію поточної дати: = Date (). Переглянути звіт.

15. Створити звіт-діаграму на основі запиту 1.

16. Створити звіт-наклейки для листів вкладникам на основі будь-якого запиту.

17. Зберегти файл.

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

1. Що таке запит?

2. Які існують види запитів?

3. Як створити запит в режимі конструктора?

4. Як додати таблиці для використання їх у звіті?

5. Назвіть елементи вікна конструктора звітів.

6. Як використовують умову у запиті?

7. Для чого використовують конструктор побудови виразів у запиті?

8. Які оператори використовуються у запитах?

9. Як створити запит з обчислювальним полем?

10. Які функції використовуються в обчислювальних полях?

11. Що таке звіт?

12. Як створити звіт?

13. Як змінити вже побудований звіт?

14. Як провести фільтрацію даних у звіті?

15. Як змінити форматування звіту?