- •3.3 Система управления базами данных Access Тема 1. Базы данных и системы управления ими
- •1. Основные понятия и определения
- •2. Реляционные субд
- •3. Технология работы в субд
- •4. Основные средства субд
- •Тема 2. Основы работы в субд ms Access
- •1. Основные характеристики ms Access
- •2. Объекты Access
- •3. Запуск программы и завершение работы с ней
- •4. Главное окно Access
- •5. Способы создания базы данных
- •6. Справочная система
- •Тема 3. Постановка задачи
- •1. Общие положения
- •2. Характеристика задачи
- •3. Выходная информация
- •4. Входная информация
- •План выпуска готовой продукции
- •Накладная № ___
- •5. Алгоритмизация задачи
- •3.1 Имя поля
- •3.2 Типы данных
- •3.3 Свойства поля
- •3.4 Первичный ключ
- •3.5 Сохранение таблицы
- •4. Создание таблицы с помощью мастера
- •5. Создание таблицы в режиме таблицы
- •6. Ввод данных в таблицу
- •7. Связывание таблиц
- •7.1 Окно схемы данных
- •7.2 Изменение существующей связи
- •7.3 Удаление связи
- •7.4 Обеспечение целостности данных
- •Тема 5. Загрузка, просмотр и корректировка базы данных
- •1. Способы загрузки базы данных
- •2. Назначение экранной формы
- •3. Средства создания формы
- •4. Последовательность создания формы
- •5. Использование мастера для создания формы
- •6. Создание форм в конструкторе
- •6.1 Объекты формы
- •6.2 Области формы
- •6.3 Панель элементов
- •6.4 Свойства объектов формы
- •6.5 Управление объектами
- •7. Режимы представления формы
- •8. Кнопки перемещения по записям
- •9. Разработка многотабличной формы
- •10. Создание кнопок управления
- •Тема 6. Запросы к базе данных
- •1. Понятие запроса
- •2. Типы запросов
- •3. Формирование запроса по образцу
- •4. Создание запроса в окне конструктора
- •4.1 Окно конструктора запросов
- •4.2 Условия отбора записей
- •4.3 Параметры запроса
- •4.4 Вычисляемые поля
- •5. Мастера создания запросов
- •6. Запуск запроса
- •7. Сохранение запроса
- •8. Итоговые вычисления
- •Тема 7. Конструирование отчетов
- •1. Понятие отчета
- •2. Средства создания отчета
- •3. Последовательность создания отчета
- •4. Использование мастера для создания отчета
- •5. Просмотр и печать отчета
- •6. Редактирование отчета в окне конструктора отчетов
- •Тема 8. Разработка кнопочного меню
5. Мастера создания запросов
Простейшие запросы некоторых видов могут быть созданы с помощью мастеров. Мастер последовательно запросит наименования таблиц, используемых в запросе, перечень полей таблиц, а также некоторые дополнительные параметры и создаст запрос на основе Ваших ответов.
С помощью мастера можно создать следующие запросы:
простой запрос на выборку;
перекрестный запрос;
запрос для поиска повторяющихся записей;
запрос для поиска записей, не имеющих подчиненных.
Для вызова мастера следует открыть диалоговое окно Новый запрос и выбрать из списка соответствующий мастер.
6. Запуск запроса
Для выполнения запроса используется команда Запрос—Запуск или кнопка на панели инструментов. Результат выполнения запроса отображается в режиме таблицы.
7. Сохранение запроса
Созданный запрос можно использовать и в дальнейшем. Для этого необходимо присвоить ему имя и сохранить. Сохранение запроса осуществляется командой Файл—Сохранить как/экспорт. Запросам рекомендуется присваивать информативные имена, несущие смысловую нагрузку.
8. Итоговые вычисления
Для выполнения итоговых вычислений следует дать команду Вид—Групповые операции или нажать кнопку на панели инструментов. При этом в бланке запроса появится новая строка с наименованием Групповая операция:. В этой строке указывается тип итоговой операции (табл. 3.17).
Таблица 3.17
Типы итоговых операций
Значение |
Операция |
Sum |
Сложение |
Avg |
Среднее значение |
Min |
Минимальное значение |
Max |
Максимальное значение |
Count |
Количество записей, содержащих значения |
StDev |
Стандартное отклонение |
Var |
Дисперсия |
First |
Значение в первой записи |
Last |
Значение в последней записи |
Группировка позволяет выполнить вычисления в группах записей. Для проведения группировки следует установить значение Группировка в строке Групповая операция:. Можно осуществлять группировку сразу по нескольким полям. Таким образом Вы можете создавать подгруппы внутри групп.
Итоговым полям присваиваются наименования в соответствии с принятым в Access соглашением. Начальная часть имени обычно содержит имя итоговой операции, за которым следует имя поля, над которым выполнялась итоговая операция. Можно изменить наименования итоговых полей по своему усмотрению. Для этого в строке Поле: бланка запроса перед именем поля следует ввести новое имя и отделить его двоеточием.
Контрольные вопросы
Разъясните понятие "запрос к БД".
Какие существуют типы запросов?
Что называется запросом по образцу?
Что такое SQL-запрос?
Какие запросы относятся к модифицирующим?
Как создается запрос по образцу?
Как задать условия отбора записей?
Что такое вычисляемое поле?
Какие мастера создания запросов Вам известны?
Как запустить запрос?
Практическая часть
На предыдущем этапе решения задачи в БД была загружена оперативная информация. Следующий этап решения задачи сводится к обработке таблиц базы данных. Для определения отчетного периода обеспечивается диалог с пользователем. Такая информация должна поступать с клавиатуры в процессе решения задачи в ответ на запрос в соответствующем диалоговом окне.
Процесс решения задачи может быть разбит на несколько модулей, реализуемых средствами запросов.
Модуль 1 (рис. 3.15). Выборка записей из таблицы Накладная по заданному периоду и соединение1 с записями таблицы Спецификация к накладной. Суммирование поставок каждого продукта по каждому цеху за каждый день.
Рис. 3.15. Схема
Модуля 1
Модуль 2 (рис. 3.16). Выборка записей из таблицы План по заданному периоду и соединение с записями таблицы Спецификация к плану.
Модуль 3 (рис. 3.17). Соединение запросов Модуль 1, Модуль 2 и таблицы Готовая продукция. Расчет стоимости продукции, отклонений фактических показателей от плановых и процента выполнения плана. Суммирование поставок каждого продукта по каждому цеху за указанный период.
Рис. 3.16. Схема
Модуля 2
Рис. 3.17. Схема
Модуля 3
1. Для реализации Модуля 1 создать запрос на выборку.
Открыть окно конструктора запросов.
В схему запроса добавить таблицы Накладная и Спецификация к накладной.
Поместить в бланк запроса поля: Цех, Код, Дата, Количество.
В строке Условие отбора: для поля Дата задать параметры запроса для определения начальной и конечной дат заданного периода:
Between [Начальная дата] And [Конечная дата]
Выполнить группировку данных по коду продукции по каждому цеху за каждый день. Для этого нажать кнопку на панели инструментов. В строке Групповая операция: для столбцов Цех, Код, Дата оставить значение Группировка, а для столбца Количество установить значение — Sum. Итоговому полю Количество присвоить новое имя – Факт.
Выполнить запрос. В появившемся диалоговом окне задать начальную и конечную даты периода (например, в обоих случаях 10.02.02).
Просмотреть результат запроса. Сохранить запрос под именем Модуль 1.
2. Для реализации Модуля 2 создать новый запрос в окне конструктора запросов. В схему запроса добавить таблицы План и Спецификация к плану. Поместить в бланк запроса поля: Цех, Код, Дата, Количество. Задать параметры запроса для определения заданного периода (аналогично Модулю 1). Полю Количество присвоить новое имя – План. Выполнить запрос. Сохранить запрос под именем Модуль 2.
3. Для реализации Модуля 3 создать третий запрос. Включить в схему запроса Модуль 1, Модуль 2 и таблицу Готовая продукция. Связать Модуль 1 и Модуль 2 по полям: Цех, Код и Дата. Перенести в бланк запроса следующие поля: Цех, Код, Группа, Наименование, Единица, Факт, План.
Добавить в бланк запроса вычисляемые поля:
Стоимость: [Факт] * [Цена]
Отклонение: [Факт] - [План]
Процент: [Факт] / [План] * 100
Выполнить группировку данных по коду продукции по каждому цеху. Для этого в строке Групповая операция: для столбцов Цех, Код, Группа, Наименование, Единица оставить значение Группировка, для столбцов Факт, План, Стоимость, Отклонение установить значение — Sum, а для столбца Процент — Avg.
Выполнить запрос.
Сохранить запрос под именем Модуль 3.
Дополнительные проверочные задания
1. На основе таблицы Готовая продукция создать запрос на выборку групп продукции, наименования которых начинаются на буквы: С или Д. Запрос должен включать все поля таблицы Готовая продукция.
2. Разработать запрос, позволяющий отобрать продукты с кодами, начинающимися с 02. В выборку включить следующие поля: Код, Наименование и Цех.
3. Создать запрос, в результате которого будут отобраны продукты с ценами в интервале от 5000 до 7000 руб. В выборку включить следующие поля: Код, Наименование, Цена и Единица.
4. Создать запрос, в результате которого будет получен список продуктов с наименованиями, начинающимися с букв: с М по Т. Запрос должен включать все поля таблицы Готовая продукция.
5. Разработать запрос на выборку всех колбас с ценами, превышающими 6000 руб. за 1 ц. В выборку включить следующие поля: Код, Группа, Наименование, Цена и Единица.
6. На основе таблицы Готовая продукция сформировать запрос на выборку:
Код продукции |
Наименование продукции |
Ед. изм. |
Цена за ед., руб. |
Цена за ед. с налогом, руб. |
|
|
|
|
|
Поле Цена с налогом является вычисляемым полем. Величина налога должна задаваться по приглашению в специальном диалоговом окне.
7. На основе таблиц Готовая продукция, Цеха, Накладная и Спецификация к накладной сформировать параметрический запрос на выборку записей о поступлении продукции на склад в течение дня:
Группа продукции |
Наименование продукции |
Начальник цеха |
Количество, ед. |
|
|
|
|
Дату определить в специальном диалоговом окне. В качестве параметра задать фразу: [Ввести дату]. Обеспечить группировку данных по наименованиям продукции.
8. Создать итоговый запрос для вычисления средней цены по каждой группе продукции. В выборку включить поля: Группа, Цена и Единица.
9. Создать итоговый запрос для определения суммарного количества продукции, поступившей на склад.
10. Создать итоговый запрос для определения суммарной стоимости колбас, поступивших на склад, по группам. В выборку включить поля: Группа и Стоимость. Поле Стоимость является вычисляемым полем.
11. Создать итоговый запрос для определения суммарной стоимости продукции, произведенной цехом 03. В выборку включить поля: Цех и Стоимость. Поле Стоимость является вычисляемым полем.
12. Разработать модифицирующий запрос на создание архивной таблицы Архив, куда поместить все записи о поступлении продукции до 11.02.02. Для создания модифицирующего запроса воспользоваться кнопкой Тип запроса, находящейся на панели инструментов (команда — Создание таблицы). Выполнить запрос. Открыть таблицу Архив и удостовериться в правильности создания таблицы.
13.* Разработать модифицирующий запрос на добавление в архивную таблицу Архив записей о поступлении продукции 11.02.02. Воспользоваться кнопкой Тип запроса (команда — Добавление). Выполнить запрос. Открыть таблицу Архив и удостовериться в добавлении записей.
14.* Разработать модифицирующий запрос на обновление данных в таблице Готовая продукция. Увеличить цены всех продуктов на 10%. Воспользоваться кнопкой Тип запроса (команда — Обновление). В строку Обновление ввести: [Цена]*1,1. Выполнить запрос. Открыть таблицу Готовая продукции и удостовериться в обновлении данных.
15.* Разработать модифицирующий запрос на уменьшение в таблице Готовая продукции цен всех видов колбас на 5 %. Выполнить запрос. Открыть таблицу Готовая продукции и удостовериться в изменении цен.