Использование СУБД Microsoft Access на примере создания базы данных по учету товаров

Рассмотрим использование СУБД MS Access на примере создания и работы с базой данных по учету товаров на складе и их поставщикам. База данных включают 2 таблицы (таблицу товаров на складе и таблицу поставщиков).

Таблица товаров на складе:

Таблица 1

Номер товара	Номер поставщика	Название товара	Цена, руб./шт.	Количество на складе
		Магнитола
		Приемник
		Плеер
		Кофеварка
		Электрочайник
		СD - Плеер

Таблица поставщиков:

Таблица 2

Номер поставщика	Название фирмы	Город	Адрес	Телефон
	ЗАО «Посредник»	Омск	ул. Космонавтов, 12
	«Импульс»	Москва	Пр. Мира, 5
	«Старт»	Тверь	ул. Зеленая, 11

Таблица товаров на складе связана с таблицей поставщиков через поле «Номер поставщика» (внешний ключ для таблицы товаров).

В появляющемся окне задаем имя и расположение файла новой базы данных Microsoft Access (расширение файла -.mdb).

После создания файла базы данных появляется окно для работы с созданной базой (разделы «Таблицы», «Запросы», «Формы», «Отчеты», «Макросы», «Модули»). Так как таблицы базы данных еще не созданы, то доступна только кнопка «Создать»

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

Поля таблицы содержат данные, являющиеся компонентами записи. Пользователь имеет возможность определить формат отображения данных, указывать значения по умолчанию и ускорить операции поиска и сортировки, задавая значения свойств полей в области «Свойства поля» в режиме конструктора таблицы.

В Microsoft Access свойства полей используются при просмотре или изменении данных пользователем. Например, заданные пользователем значения свойств Формат поля (Format), Маска ввода (InputMask) и Подпись (Caption) определяют вид базы данных таблицы и запроса. Элементы управления в новых формах и отчетах, присоединенные к полям таблицы, наследуют эти свойства полей базовой таблицы по умолчанию. Другие свойства позволяют определить условия на значения полей или задать обязательный ввод данных в поле. Microsoft Access будет проверять выполнение этих условий при каждом добавлении или изменении данных в таблице.

В появляющейся пустой таблице нужно задать имена всех полей таблицы и соответствующие им типы данных. Поле “Номер_товара” является ключевым и для него выбирается тип “Счетчик” (уникальность этого поля будет поддерживаться системой Microsoft Access автоматически).

Для остальных полей зададим соответствующие им типы данных (см. рис. 1). Для поля «Номер поставщика» (обеспечивает связь со второй таблицей).

После задания имен полей и их типа, таблица сохраняется с заданием ее имени («Товары на складе»).

Рис. 1.

Создание второй таблицы проведем в режиме Мастера таблиц (пункт меню «Мастер таблиц» при создании новой таблицы). В окне последовательно выбираются образец таблицы и необходимые поля из имеющихся в образце (выделение нужного поля и перенос в окно полей новой таблицы) (Рис. 2). При щелчке по кнопке «Далее» появляется окно для задания имени таблицы.

В окне вводится имя таблицы – «Поставщики», выбирается способ определения ключа

Рис. 2.

(выбрано автоматическое определение ключа в Microsoft Access) (Рис. 3). При выборе автоматического определения ключа в таблице создается поле счетчика. Поле счетчика является целочисленным полем, которое автоматически увеличивается на единицу системой управления базами данных, когда добавляется новая запись.

Рис. 3.

Далее осуществляется переход к окну (рис. 4) для задания связей между таблицами (изначально таблица «Поставщики» не связана с таблицей «товары на складе»). При щелчке по кнопке «Связи», появляется окно для задания типа связи между таблицами (рис. 5).

Рис. 4

Возможны несколько типов связи:

1. Одной записи таблицы «Поставщики» будет соответствовать много записей таблицы «товары на складе», т.е. один поставщик может поставлять несколько товаров. Такая связь называется «один ко многим».

2. Одной записи таблицы «Поставщики» будет соответствовать одна запись таблицы «товары на складе», т.е. один поставщик может поставлять только один товар. Такая связь называется «один к одному».

3. Одной записи таблицы «товары на складе» будет соответствовать много записей таблицы «Поставщики», т.е. один товар может поставляться несколькими поставщиками. Такая связь также называется «один ко многим».

4. Нескольким записям таблицы «товары на складе» будет соответствовать много записей таблицы «Поставщики», т.е. один товар может поставляться несколькими поставщиками и один поставщик может поставлять несколько товаров. Такая связь называется «многие ко многим».

Так как в исходных таблицах видно, что на складе имеется несколько товаров одного поставщика, то выбираем связь « один ко многим» (рис. 5). Мастер таблиц автоматически добавляет поле «Код поставщика» в таблицу «товары на складе» и создает эту связь.

Рис. 5.

Таким образом, созданное ранее поле «Номер поставщика» в таблице «товары на складе» оказывается излишним. Вместо него система автоматически добавила нужное поле.

Заданные таблицы и связи между ними можно посмотреть и выполнить Обеспечение целостности данных (в Горизонтальном меню пункт «Сервис», в раскрывающемся меню пункт «Схема данных»). Схема данных показана на рис. 6. В окне Схемы данных выбрать правой кнопкой контекстное меню объекта «связь» и из двух пунктов меню «изменить связь» и «удалить» выбрать первый. Далее включить необходимые флажки (см. п. 3.2).

Это лучше выполнить до заполнения таблиц данными, т.к. при заполненных таблицах невозможно изменение связей.

Рис. 6.

В созданные таблицы с заданными связями может быть введена требуемая информация. Информация может вводиться в таблицы или с помощью форм, которые создаются мастером (информация о готовности таблиц и возможных способах ввода данных выводится в заключительном окне, когда активизируется кнопка «Готово», рис. 7).

Рис. 7.

Перед вводом данных в таблицу «Товары на складе» удалим введенное ранее поле «Номер поставщика» (выбор таблицы в списке, щелчок по кнопке «Конструктор», выделение удаляемого поля, щелчок по правой кнопке мыши, выбор в контекстном меню «Удалить строки», рис. 8).

Макет базы данных (таблицы, связи между таблицами) создан.

Рис. 8.

В Microsoft Access существует набор средств для работы с базой данных. Рассмотрим некоторые из этих средств.

Раздел «Формы» (рис. 9) позволяет создавать различные формы данных для заполнения таблиц информацией на основе существующих связных таблиц. Используем «мастер форм».

Последовательно из таблиц выбираются требуемые поля для формы, в нашем случае мы выбираем все поля обеих

Рис. 9.

таблиц, т.к. необходимо внести все исходные данные (рис. 10 и 11). После выбора полей таблиц выбирается тип представления данных (в нашем случае выбираем Подчиненные формы, т.к. при этом внешний ключ во второй таблице будет формироваться автоматически, что предотвратит внесение лишних ошибок), внешний вид («ленточный») и стиль представления данных (рис. 12 и 13).

Рис. 10

Рис. 11

Рис. 12

На рис. 12 видно, что выбирается тип представления данных по «Поставщики», т.е. поставщику из этой таблицы ставится в соответствие информация по его товару на складе (таблица «товары на складе» является связанной с таблицей «Поставщики»). Возможны показанные на форме альтернативы представления данных.

Рис. 13

Для выбранной структуры, показанное на рис. 13 окно позволяет выбрать стиль представления данных.

В следующем окне (рис. 14) нужно задать имена для каждой связанной формы («Поставщики», «товары на складе»). При щелчке по кнопке «Готово» в соответствии с заданной опцией «открытие главной формы на просмотр и ввод данных» появляется окно формы «Поставщики1» с кнопкой открытия связанной формы «товары на складе». Обе связанные формы показаны на рис. 15.

Рис. 14

Рис. 15

Значения ключей в поле «код поставщика» и «Номер_товара» формируется автоматически.

Для каждого выбранного в форме поставщика в связанной форме «товары на складе» можно просмотреть список поставляемых им товаров.

Все созданные формы появляются в списке форм.

Заполним формы данными из таблицы 1 и таблицы 2 и добавим еще произвольно по одной записи по каждому поставщику.

Одной из функций СУБД является представление необходимых данных по запросу (раздел «Запросы»). Для создания запроса последовательно выбирается раздел «Запросы», «Конструктор» для создания запроса, выбираются существующие таблицы (отображаются в верхней части окна со связями между ними, см. рис. 16).

Последовательно выбираются имена таблиц (строка «Имя таблицы»), поле (строка «Поле»), условия отбора данных (строка «Условия отбора») для каждого заданного поля (рис. 16).

Для «Цены» задано условие: >1000 And < 4000.

Для «Количества на складе» задано условие: >10 And < 40.

Для «Города» задано условие: «Омск».

Т.е. требуются данные о товарах, стоимость которых лежит в пределах от 1000 до 4000, количество которых на складе в пределах от 10 до 40, которые поставляются из города Омск.

Рис. 16

При закрытии данного окна выводится окно для задания имени запроса (запрос сохраняется в списке запросов). При выделении запроса и щелчке по кнопке «Открыть» появляется таблица с результатами запроса (рис. 17).

Рис. 17.

Выводятся значения полей, заданных в запросе и для которых выполняются заданные условия поиска (имеется 15 единиц товара стоимостью 3500 руб. и поставляемого из Омска).

В пункте «Вид» Главного меню можно выбрать пункт «Режим SQL». Выводится окно с текстом запроса на языке SQL (рис. 18). Правила построения SQL-запросов приводятся в главе 2.

Рис. 18.

Рассмотренные запросы относятся к запросам с критериями поиска. Для избежания создания нового запроса для каждого значения критерия служат запросы с параметром. Для построения запроса с параметром необходимо в строке условие отбора указать имя поля в квадратных скобках и в окне Параметры запроса (пункт горизонтального меню Запрос \ Параметры) также указать имя поля (рис. 19).

Рис. 19

При выделении запроса и щелчке по кнопке «Открыть» появляется окно (рис. 20), где необходимо ввести конкретное значение критерия, таблица с результатами запроса аналогична представленной на рис. 17.

Рис. 20

Рис. 21

Как правило, при проектировании таблиц нужно стремиться не перегружать их данными. Так, например, в таблице, содержащей сведения о товарах, достаточно сохранить цену за единицу товара и количество товаров на складе, поле для общей стоимости будет лишним, т.к. его можно определить на основании уже имеющейся информации. Для этого следует составить запрос с вычисляемым полем Стоимость при помощи следующего выражения в новом столбце (рис. 21).

Стоимость: [Цена]*[Количество_наскладе]

Результат выполнения запроса представлен на рис. 22.

Рис. 22

Если в таблице содержится большое количество записей, то для вычисления итоговых значений используют итоговые запросы. Для построения итогового запроса необходимо воспользоваться командой Вид \ Групповая операция и выбрать для конкретных полей подходящую функцию. Примеры бланка и результата выполнения запроса приведены на рис. 23 и рис. 24.

В СУБД Access имеются развитые средства для создания отчетов по имеющейся в базе данных информации (раздел «Отчеты», щелчок по кнопке «Создать»). В окне «Новый отчет» выбирается «мастер отчетов» для создания отчета (рис. 25). В качества источника данных для отчета можно использовать исходные таблицы или результаты выполнения запросов (таблицы «Запросы»). Комбинированный список для выбора таблиц или запросов находится в нижней части окна (рис. 25).

Рис. 23

Рис. 24

Рис. 25

Появляется окно мастера (рис. 26) для задания полей данных, включаемых в отчет. Можно включить данные из разных таблиц (на рис. 27 и 28 последовательно выбираются данные для отчета из таблиц «Товары на складе» и «Поставщики»).

Рис. 26

Рис. 27

После выбора полей данных для отчета, выбираются тип представления (рис. 28), вид сортировки данных в выбранных полях (задана сортировка данных по алфавиту в поле «Название товара») и ввод в отчет рассчитываемых итоговых данных (рис. 29). Для ввода в отчет итоговых данных используется кнопка «итоги» (рис. 29) и задание вида итоговых расчетов (рис. 30).

Рис. 28

Рис. 29

Рис. 30

Выбираются суммирование по полям «Стоимость» и «Количество на складе» (флажки Sum) на рис. 30. Заданные итоговые поля будут добавлены к отчету.

Рис. 31.

Окно (рис. 31) позволяет задать макет отчета (расположение информации на странице, ориентация страницы).

Выбор стиля отчета показан на рис. 32.

Рис. 32.

Для сформированного отчета может быть задано имя (отчет сохраняется) и он может быть просмотрен (опция – «просмотр отчета»).

Полученный отчет показан на рис. 33.

Рис. 33

Дата добавления: 2017-01-13