- •Лабораторна робота № 10
- •Завдання
- •Виконання завдання. Створення таблиці в середовищі табличного процесора ms Excel
- •Імпорт таблиці з робочого листа ms Excel в базу даних ms Access
- •Створення запиту на вибірку в середовищі субд ms Access
- •Створення зв'язку між таблицями бази даних
- •Конструювання запиту на вибірку на основі пов'язаних таблиць
- •Створення звіту за запитом
- •Створення звіту по лабораторній роботі
Імпорт таблиці з робочого листа ms Excel в базу даних ms Access
Імпортуйте підготовлену таблицю з робочого листа Excel в нову таблицю Access так:
Запустіть Access. Відкрийте базу даних з таблицею ПОСТАЧАЛЬНИКИ. Виберіть команду Файл → Внешние данные → Импорт.
Перейдіть у папку, в якій міститься файл робочого листа Excel. У розкриваючому списку Тип файлів виділіть: Microsoft Excel. Потім у верхньому вікні виділіть ім'я файла: УГОДИ.xls,дані з якого будуть імпортовані, натисніть на кнопку Импорт.
Виберіть потрібний лист із списку: Лист 1. Натисніть на кнопку Далее.
Встановіть прапорець Первая строка содержит заголовки столбцов і натисніть на кнопку Далее.
Виберіть збереження даних у новій таблиці. Натисніть на кнопку Далее.
Змінювати відомості про поля імпорту в цьому випадку не потрібно. Натисніть на кнопку Далее.
Задайте автоматичне створення первинного ключа. Натисніть на кнопку Далее.
Вкажіть ім'я таблиці, в яку буде виконаний імпорт: УГОДИ. Натисніть на кнопку Готово.
Access видасть повідомлення про те, що імпорт завершено. Натисніть на кнопку ОК.
У вікні бази даних додалось ім'я імпортованої таблиці. Після імпорту можна змінити структуру таблиці, вибравши команду Вид → Конструктор з вікна бази даних.
Якщо з'явилися помилки з назвою полів, відкоригуйте їх.
Якщо Access неправильно визначив тип даних деяких полів, змініть тип даних.
Перевірте встановлені властивості полів. Зверніть увагу на розмір і формат усіх полів імпортованої таблиці (наприклад, Тип поля коду постачальника — числовий, розмір — довге ціле).
Створення запиту на вибірку в середовищі субд ms Access
Створимо запит на вибірку на основі двох таблиць бази даних: ПОСТАЧАЛЬНИКИ і УГОДИ.
Створення зв'язку між таблицями бази даних
Для того, щоб встановити зв'язок між таблицями ПОСТАЧАЛЬНИКИ й УГОДИ, виконайте таке:
3 вікна бази даних, попередньо закривши всі вікна таблиць, виберіть команду Сервис → Схема данных.
Виберіть команду Связи —> Добавить таблицу. По черзі виберіть таблиці, між якими буде встановлений зв'язок (ПОСТАЧАЛЬНИКИ, УГОДИ). За кожним разом натискайте на кнопку Далее.
Закрийте вікно Добавление таблицы.
Перетягніть у поле Код постачальника, натиснувши ліву кнопку мишки, із списку полів ПОСТАЧАЛЬНИКИ в список полів УГОДИ на поле з тим же іменем. Відпустіть клавішу мишки.
У вікні Изменения связей відображаються імена полів, що зв'язуються, обох таблиць.
Встановіть прапорець Обеспечение целостности данных.
Натисніть на кнопку Создать.
Між таблицями з'явилася лінія, яка з'єднує два поля, які збігаються. Встановились відношення один — до багатьох (рис. 1).
• Закрийте вікно Схема данных.
Рис. 1. Структура бази даних
Конструювання запиту на вибірку на основі пов'язаних таблиць
Відкрийте вкладку Запроси у вікні бази даних. Натисніть на кнопку Создать.
У вікні Новий запрос виберіть режим Конструктора. Натисніть на кнопку ОК.
У вікні Добавление таблицы по черзі виберіть таблиці, які будуть використовуватися в запиті (ПОСТАЧАЛЬНИКИ, УГОДИ). Кожний раз натискайте на кнопку Добавить.
Закрийте вікно Добавление таблицы.
Верхня частина вікна Конструктор запроса містить список усіх полів таблиць, включених в область дії запиту.
Нижня частина містить бланк QBE (Query-by-Example — запит за зразком), в якому ви визначаєте параметри запиту. Кожен стовпець бланка відповідає одному з полів.
У рядку Поле включіть імена полів, що використовують ся у запиті (назва постачальника, місцезнаходження постачальника, дата, кількість (шт.), сума ($)). Для цього встановіть курсор на імені поля, що вибирається, у верхній частині вікна конструктора і двічі натисніть лівою клавішею мишки.
У рядку Условие отбора задайте умову відбору записів у стовпці Дата: #12.01.02.#.
У рядку Сортировка по полю Эмитент виберіть порядок сортування записів по результатах: по возрастанию.
Для обчислення сумарних обсягів акцій в шт. і в дол. за постачальниками виберіть команду Вид → Групповые операции.
У рядку Групповая операция натисніть лівою кнопкою мишки на клітинці стовпця:
Кількість (шт.), натисніть на кнопку розкриття списку і виберіть рядок Sum.
Щоб перейменувати поле, встановіть курсор перед першою буквою імені Кількість (шт.) і введіть Сум Кількість (шт.): (в кінці повинна бути набрана двокрапка).
Аналогічні дії зробіть у стовпці Сума ($):
У рядку Условие вьібора задайте умову відбору записів в стовпці Кількість (шт.): >=20 000.
Для обчислення середньозваженої ціни акцій створіть поле Середи Ціна ($).
Виділіть порожню комірку в рядку Поле і введіть такий вираз:
Средн Цена ($):[УГОДИ]х[Сума ($)]/[УГОДИ]хКількість (шт.)]
Посилання на поле Сума ($) Посилання на поле Кількість (шт.) в таблиці УГОДИ в таблиці УГОДИ
Щоб побачити весь вираз, збільшіть ширину стовпця, натиснувши двічі лівою кнопкою мишки на правому розділювачі полів стовпця.
У рядку Групповая операция в стовпці Средн Цена ($) натисніть на кнопку розкриття списку і виберіть рядок Avg (розрахунок середнього значення).
• Щоб встановити формат даних у полі Сума ($), встановіть курсор на полі Сума($) і виберіть команду Вид → Свойства. У вікні Свойства поля у рядку Описание вкажіть ім'я поля, у рядку Формат поля виберіть потрібний формат (фіксований), у рядку кількість десяткових знаків — встановіть 2.
Аналогічні операції виконайте по полю Средн Цена ($).
Закрийте вікно Свойства поля.
У рядку Вывод на эран відзначте поля, які будуть включені в результатну таблицю (назва постачальника, місцезнаходження постачальника, загальна кількість (шт.), загальна сума ($), середня ціна ($)).
Заповнений бланк запиту представлений на рис. 2:
Рис. 2. Заповнений бланк запиту
Щоб побачити результати запиту (рис. 3), виберіть команду Вид → Режим таблицы.
Збережіть запит і закрийте його.
Рис. 3. Результати виконання запиту