Базы данных «Учет продаж»

Тема занятия

Создание проекта базы данных в MS Access “Учет продаж”.

Цель работы:

Усвоить принципы организации и обработки больших массивов данных об объектах и явлениях реального мира.

Способствовать развитию умений и навыков по технологии создания простейших информационных систем на примере СУБД MS Access.

Способствовать развитию интереса к изучаемому предмету и воспитанию самостоятельности, активности, сообразительности при создании проекта.

Основные понятия СУБД MS Access.

Под базой данных (БД) следует понимать унифицированную совокупность специально организованных данных, совместно используемая различными задачами в рамках некоторой единой автоматизированной информационной системы. БД хранятся на каком-либо носителе, например на бумаге, на магнитном диске и т.д. Программы для хранения, обработки и поиска большого объема информации называются системами управления базой данных (СУБД).

Microsoft Access —это функционально полная реляционная СУБД. В ней предусмотрены все необходимые средства для определения и обработки данных, а также для управления ими при работе с большими объемами информации.MS Access – это набор инструментальных средств для создания и эксплуатации информационных систем.

Реляционными БДназываются базы данных, построенные на взаимоотношениях составляющих ее частей. Такие базы данных представляет собой одну или несколько прямоугольных таблиц, отсюда и формируется их название - табличная база данных.

Средствами MS Access производятся следующие операции:

1. Проектирование базjd[ объектов ИС – двухмерных таблиц, с разными типами данных, включая логические типы, поля объектов OLE и гиперссылки. Например, прежде чем заполнять таблицу ЗАКАЗЫ, надо создать ее макет.

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

3. Ввод, хранение, просмотр, сортировка, модификация и выборка данных из таблиц с использованием различных средств контроля информации, индексирования таблиц и аппарата алгебры и логики (для фильтрации данных).

Практическое задание по созданию проекта

базы данных «Учет продаж».

ЗАДАНИЕ 1. РАБОТА С ТАБЛИЦАМИ БАЗЫ ДАННЫХ.

Для учета и анализа продаж фирма может вести таблицу с именем ПРОДАЖИ со следующими полями, представленными в таблице 1.

Таблица 1. Общие сведения базы данных

№ поля	Имя поля	Примечание	Тип поля
1.	Номер продажи	-	Число
2.	Код клиента	-	Число
3.	Наименование клиента	-	Текст
4.	Адрес клиента	-	Текст
5.	Код продукта	-	Число
6.	Название продукта	-	Текст
7.	Количество	Штук	Число
8.	Дата продажи	ДД.ММ.ГГГГ	Дата
9.	Цена	Тенге/кг	Число
10.	Сумма продажи	тенге	Число

Каждая строка этой таблицы содержит полную информацию о конкретном заказе, а вся таблица в целом дает возможность не только вести учет, но и анализировать деятельность фирмы – по объемам продукции, стоимости, датам и т.д.

Таблица 1 не является нормализованной. Проведя нормализацию, можно отметить, что для оптимальной работы базы данных «Учет продаж» требуется создать три таблицы:

- одну оперативную таблицу ПРОДАЖИ (номер продажи, код клиента, код продавца, код товара, количество продажи, дата продажи);

- классификатор ПРОДУКТЫ (код продукта, название продукта, цена);

- классификатор КЛИЕНТ (код клиента, наименование клиента, адрес клиента).

Для создания перечисленных таблиц, для начала необходимо открыть MS Access. Ссылка на приложение Microsoft Access обычно находится в одном из подчиненных меню пункта Главного меню Windows [Пуск àВсе программыàMicrosoft Office].

Объектом обработки MS Access является файл базы данных, имеющий произвольное имя и расширение .accdb. В этот файл входят основные объекты базы данных MS Access: таблицы, формы, запросы, отчеты, модули и макросы.

После загрузки MS Access, в появившемся окне выберите пункт Новая база данных. В окне справа «Новая база данных» задайте имя вашей базы (поле ИМЯ ФАЙЛА) и выберите папку (поле ПАПКА), где ваша база данных будет находиться. По умолчанию MS Access предлагает вам имя базы База данных 1, а тип файла – Базы данных Microsoft Access. Имя задайте “Учет продаж”, а тип файла оставьте прежним, так как другие типы файлов нужны в специальных случаях. Щелкните на кнопке Создатьи в результате на экране откроется окно базы данных (см.рис.1.)

Рисунок 1. Окно базы данных

Любой объект (таблицу, запрос, форму, отчет) можно создать либо вручную (окна Новая таблица, Новый запрос т.д.), либо с помощью Мастера. Для начинающих пользователей рекомендуется таблицы и запросы создавать вручную, формы и отчеты создавать с помощью Мастера.

Создание таблицы «ТОВАРЫ» базы данных:

В окне базы данных (см.рис.1) выберите вкладку Создание, а затем щелкните по кнопке Конструктор таблиц

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

	Рисунок 2. Окно таблицы в режиме конструктора

Теперь следуетопределить поля таблицы:

Таблица 2. Поля таблицы «ТОВАРЫ»

Имя поля	Тип данных	Свойства поля
Код товара	Счетчик	Задайте значение КЛЮЧЕВОГО ПОЛЯ, однозначно идентифицирующее каждую запись [в контекстном меню, вызываемом щелчком правой кнопкой мыши, по полю «Код товара» следует выбрать «Ключевое поле»]
Наименование товара	Текстовый	На вкладке «Общие» В поле «Размер поля» следует указать 30 [Не бойтесь ошибиться – в дальнейшем можно скорректировать длину поля]
Цена	Числовой	На вкладке «Общие» в поле «Маска ввода» нужно ввести: #######” тенге“

Определение полей таблицы в режиме Конструктора:

введите в строку столбца «Имя поля» имя первого поля (например, «Код товара»);

в строке столбца «Тип данных» щелкните по кнопке списка (маленькая черная стрелочка) и выберите тип данных «Счетчик».

Поля вкладки Общиевнизу экрана позволяют произвести дополнительные настройки для выбранного типа данных.

v Примечание: Заполнение строки столбца «Описание» НЕОБЯЗАТЕЛЬНО и обычно используется для внесения дополнительных сведений о поле.

	Рисунок 3. Таблица «ТОВАРЫ» в режиме конструктора

Внимание! Обратите внимание на вкладку Общие в нижней части экрана.

Для текстового типа данных MS Access предлагает по умолчанию длину символов 50 символов. Но вряд ли поле «Фамилия» будет содержать более 15 символов, хотя лучше точно подсчитать, сколько символов в самой длинной фамилии. Не бойтесь ошибиться – в дальнейшем можно скорректировать длину поля.

Для числового типа MS Access предлагает Длинное целое, но ваши данные могут быть либо небольшие целые числа (в диапазоне от – 32768 до 32767) – тогда надо выбрать Целое, либо дробные числа – тогда надо выбрать с плавающей точкой. Для выбора необходимого параметра надо щелкнуть по полю, а затем нажать появившуюся кнопку списка и выбрать необходимые данные. В результате ваша таблица будет иметь более компактный вид, а объем базы данных уменьшится.

Для определения всех остальных полей, перечисленных в таблице 2, поступайте аналогичным образом. После введения всех полей, сохраните таблицу под названием «ТОВАРЫ»,для этого нажмите на кнопку и в появившемся окне введите имя, в результате она будет иметь вид аналогично рисунку 3. После сохранения, таблицу можно закрыть.

Создание таблицы «КЛИЕНТ» базы данных производится аналогично созданию первой таблицы «ТОВАРЫ» (поля таблицы «КЛИЕНТ» следует смотреть в таблице 3).

Таблица 3. Поля таблицы «КЛИЕНТ»

Имя поля	Тип данных	Свойства поля
Код клиента	Счетчик
Наименование клиента	Текстовый
Адрес клиента	Текстовый

Создание таблицы «ПРОДАЖИ» базы данных производится аналогично созданию первой таблицы «ТОВАРЫ» (поля таблицы «ПРОДАЖИ» следует смотреть в таблице 4).

Таблица 4. Поля таблицы «ПРОДАЖИ»

Имя поля	Тип данных	Свойства поля
Номер продажи	Счетчик
Код клиента	Числовой	На вкладке «ПОДСТАНОВКА»: 1) в поле «Тип элемента управления» выбирается «поле со списком»; 2) в поле «источник строк» - «КЛИЕНТ»; 3) в поле «число столбцов» указывается 2; 4) в поле «ширина столбцов» записывается: 0см;2см
Код товара	Числовой	На вкладке «ПОДСТАНОВКА»: 1) в поле «Тип элемента управления» выбирается «поле со списком»; 2) в поле «источник строк» - «ТОВАРЫ»; 3) в поле «число столбцов» указывается 2; 4) в поле «присоединенный столбец» указывается 1; 5) в поле «ширина столбцов» записывается: 0см;2см
Количество	Числовой	На вкладке «Общие» 1)в поле «Обязательное поле» указываем ДА 2) в поле «Маска ввода» нужно ввести: #####"шт"
Дата продажи	Дата/Время	На вкладке «Общие» 1) в поле «Маска ввода» нужно ввести: 00.00.0000; 2) в поле «Обязательное поле» указываем ДА

После создания таблиц окно базы данных выглядит следующим образом (см.рис.4).

	Рисунок 4. Окно базы данных «Учет продаж»

После того, как три таблицы созданы, необходимо их заполнить данными. Для этого в окне базы данных нужно открыть таблицу «ТОВАРЫ». Щелкните правой кнопкой мыши на таблице «ТОВАРЫ» и и выберите опцию «Открыть» или таблицу можно открыть двойным щелчком левой кнопкой мыши.

@ Примечание: С каждым объектом мы работаем в отдельном окне, причем предусмотрено два режима работы:

1) РЕЖИМ КОНСТРУКТОРА — когда мы создаем или изменяем макет, структуру объекта (например, структуру таблицы);

2) ОПЕРАТИВНЫЙ РЕЖИМ (или режим таблицы, запросаи т.п.) — когда мы решаем в окне задачи ИС, т.е. просматриваем, изменяем, выбираем и обрабатываем информацию.

После заполнения таблицы «ТОВАРЫ» - таблица будет иметь вид, аналогично рисунку 5.

	Рисунок 5. Таблица «ТОВАРЫ» в режиме запуска таблицы

Примечание: В том случае, если нужно что-то поменять в макете таблицы, нужно перейти в режим конструктора таблицы. Для этого нужно щелкнуть кнопку и выбрать опцию режим конструктора .И наоборот, если нужно быстро перейти из режима конструктора в режим запуска таблицы, можно воспользоваться той же кнопкой на той же панели и выбрать опцию режим таблицы. Аналогично следует заполнить остальные таблицы. Таблицы «КЛИЕНТ» (см.рис.6) и «ТОВАРЫ» могут содержать произвольные данные. Таблица «ПРОДАЖА» (см.рис.7) содержит данные по товару и клиенту из уже существующих таблиц, за счет созданной «ПОДСТАНОВКИ полей», а поле количества и даты продажи заполняются также произвольно.

Рисунок 6. Таблица «КЛИЕНТ» в режиме запуска таблицы

	Рисунок 7. Таблица «ПРОДАЖЫ» в режиме запуска таблицы

******************************************************************

ЗАДАНИЕ 2. РАБОТА С ФОРМАМИ БАЗЫ ДАННЫХ. Создание форм с помощью Мастера на основе созданных таблиц.

Создание формы «ПРАЙС-ТОВАРЫ» для ввода, редактирования, поиска, просмотра записей таблицы «ТОВАРЫ»:

Для создания формы откройте вкладку Создание в окне базы данных;

щелкните по кнопке ;

после того как появится форма, нажмите кнопку и в появившемся меню выберете опцию Мастер форм;

щелкните по значку списка в нижней части окна;

выберите из появившегося списка опцию таблицу: Товары;

в появившемся окне выберите поля, которые будут присутствовать в форме.

В данном примере присутствовать будут все поля, поэтому щелкните по кнопке ;

щелкните по кнопке <Далее>;

в появившемся окне уже выбран вид Форма в один столбец, поэтому щелкните по кнопке <Далее>;

в появившемся окне выберите стиль оформления. Для этого щелкните по сло­вам, обозначающим стили, либо перемещайте выделение стрелками вверх или вниз на клавиатуре. После выбора стиля щелкните по кнопке <Далее>;

в появившемся окне задайте имя формы «ПРАЙС-ТОВАРЫ». Остальные параметры в окне оставьте без изменений;

щелкните по кнопке <Готово>. Перед вами откроется форма в один столбец (см.рис 8.). Столбец слева — это названия полей, столбец справа — данные первой записи (в нижней части окна в строке параметра Запись стоит цифра "1").

	Рисунок 8. Форма «ПРАЙС-ТОВАРЫ»

Для ПЕРЕМЕ­ЩЕНИЯ ПО ЗАПИСЯМ ТАБЛИЦЫ, отображаемых на форме, следует пользоваться кнопками, находящимися в нижней части формы (см.рис.8):

- переход к следующей записи;

- переход к предыдущей записи;

—переход к первой записи;

— переход к последней записи.

v Для ПОИСКА записи в таблице (например нужно найти информацию по товару «Процессор Core2Duo E8400, 3.0GHz 6MB(оем)»):

ü переведите курсор во второе поле «Наименование товара»;

ü нажмите кнопку ;

ü в появившемся окне в строке ОБРАЗЕЦ введите наименование товара «Процессор Core2Duo E8400, 3.0GHz 6MB(оем)»;

ü в строке параметра ПРОСМОТР должно быть слово ВСЕ (имеется в виду искать по всем записям);

ü в строке параметра СОВПАДЕНИЕ выберите из списка параметр «С любой частью поля»;

ü в строке параметра «С учетом формата полей установите флажок (должна стоять "галочка")»;

ü щелкните по кнопке НАЙТИ.

ü щелкните по кнопке <3акрыть> для выхода из режима поиска.

v Для СОРТИРОВКИ данных в поле например «Цена» по убыванию:

ü щелкните по любой записи поля «Цена»;

ü щелкните по кнопке на панели управления. Все данные в таблице будут отсорти­рованы в соответствии с убыванием значений в поле «Цена».

v Для ФИЛЬТРАЦИИ данных по полю «Наименование товара»:

ü щелкните по записи поля «Наименование товара» и выделите в записи «Мышь Genius NetScrol 110 Optical PS/2 black» только слово «Мышь»;

ü щелкните по кнопке .

ü В форме останутся только записи о товарах — именно все мышки;

Фильтр по выделенному. В форме останутся только записи о мышках.

ü для отмены фильтра щелкните по кнопке на панели инструментов или В таблице появятся все данные.

Далее, аналогично первой форме создайте форму для работы с данными, касающихся клиентов (см.рис.9), и форму «ПРОДАЖИ ФИРМЫ»(см.рис.10).

	Рисунок 9. Форма «КЛИЕНТ»

	Рисунок 10. Форма «ПРОДАЖИ ФИРМЫ»

ЗАДАНИЕ 3. СОЗДАНИЕ СВЯЗЕЙ МЕЖДУ ТАБЛИЦАМИ. РАБОТА С ЗАПРОСАМИ БАЗЫ ДАННЫХ.

Разработайте схему данных, т.е. создайте связи между таблицами. Для этого:

1) Выберите вкладку «Работа с базами данных» щелкните по кнопке «Схема данных» на панели инструментов. На экране появится окно «Схема данных»;

2) Одновременно с открытием окна «Схемы данных» открывается окно добавления таблиц. Это окно позволяет добавлять таблицы, между которыми нужно установить связь. В данном случае нужны все три таблицы. Для этого нужно выделить все три таблицы одновременно (удерживая клавишу Ctrl или Shift) и нажать кнопку «Добавить». В том случае, если окно добавления таблиц не отобразилось или вы его закрыли, то для его открытия следует воспользоваться кнопкой меню на панели инструментов

	Рисунок 11. Окно «Схемы данных»

3) закройте окно, щелкнув по кнопке ЗАКРЫТЬ;

4) создайте связь между таблицами КЛИЕНТ и ПРОДАЖИ. Для этого подведите курсор мыши к полю «Код клиента» в таблице КЛИЕНТ, щелкните левой кнопкой мыши и, не отпуская ее, перетащите курсор на поле «Код клиента» в таблицу ПРОДАЖИ, а затем отпустите кнопку мыши. На экране откро­ется окно «СВЯЗИ» (см.рис.12);

	Рисунок 12. Окно «Связи»

5) установите флажок ("галочку") в свойстве Обеспечение целостности данных, щелкнув по нему;

6) установите флажок в свойстве Каскадное обновление связанных полей и Каскад­ное удаление связанных записей;

@ Примечание: Задание каскадного обновления связанных полей и каскадного удаления связанных записей позволит вам отредактировать записи только в таблице КЛИЕНТ, а в таблице ПРОДАЖИ эти действия будут со связанными за­писями выполняться автоматически. Например, если вы удалите из таблицы КЛИЕНТ одного клиента, то в таблице ПРОДАЖИ удалятся все строки, связан­ные с этим клиентом.

7) щелкните по кнопке СОЗДАТЬ, связь будет создана;

8) аналогично создайте связи между полем «Код товара» в таблице ТОВАРЫ и полем «Код товара» в таблице ПРОДАЖИ. Результат представлен на рисунке 13.

9) закройте окно схемы данных, ответив ДАна вопрос о сохранении макета.

Рисунок 13. Окно «Схема данных»

ФОРМИРОВАНИЕ ЗАПРОСОВ.

На основе таблиц КЛИЕНТ, ТОВАРЫ, ПРОДАЖИ создайте простой запрос на выборку, в котором должны отображаться следующие поля:

* Номер продажи

* Наименование клиента

* Адрес клиента

* Наименование товара

* Цена товара

* Дата продажи

* Количество

1) СОЗДАНИЕ ПРОСТОГО ЗАПРОСА.

Создание запроса производится следующим образом:

ü в окне базы данных откройте вкладку Создание и нажмите кнопку Мастер запросов;

ü из появившихся пунктов окна «Новый запрос» выберитеПростой запрос и щелкните по кнопке ОК;

ü в появившемся окне необходимо выбрать те поля из соответствующих таблиц, которые нужно отобразить в создаваемом запросе;

ü из таблицы ПРОДАЖИ выбираются поля: «НОМЕР ПРОДАЖИ», «ДАТА ПРОДАЖИ», «КОЛИЧЕСТВО». Добавление производится выбором нужного поля и нажатием кнопки .

ü из таблицы КЛИЕНТ следует добавить поля: «НАИМЕНОВАНИЕ КЛИЕНТА», «АДРЕС КЛИЕНТА».

ü из таблицы ТОВАРЫ выбираются поля: «НАИМЕНОВАНИЕ ТОВАРА», «ЦЕНА».

	Рисунок 14. Создание простого запроса

ü щелкните по кнопке ДАЛЕЕ;

ü в строке параметра Задайте имя запроса введите новое имя ПРОДАЖИ ФИРМЫ;

ü щелкните по кнопке ГОТОВО. На экране появится таблица с результатами за­проса.

	Рисунок 15. Результат простого запроса «ПРОДАЖИ ФИРМЫ»

2) СОЗДАНИЕ ВЫЧИСЛЯЕМОГО ПОЛЯ В ПРОСТОМ ЗАПРОСЕ.

После того, как создан простой запрос, необходимо дополнить его еще одним полем, которое бы позволяло отображать ОБЩУЮ СТОИМОСТЬ ПРОИЗВЕДЕННОЙ ПРОДАЖИ ТОВАРА ОПРЕДЕЛЕННОМУ КЛИЕНТУ. Для этого нужно создать вычисляемое поле:

v Для внесения каких-то изменений в структуру макета запроса, необходимо перейти в режим конструктора (с помощью кнопки панели инструментов. В режиме конструктора запрос имеет вид как на рисунке 16.

Рисунок 16. Запрос «ПРОДАЖИ ФИРМЫ» в режиме конструктора

ü Установите курсор в первое поле свободного столбца, в данном случае это столбец, следующий за столбцом ЦЕНА (на примере рисунка 16);

ü Щелкните правой кнопкой мыши и в появившемся контекстном меню выберите опцию ПОСТРОИТЬ (вызов окна построителя выражений);

ü В открывшемся окне вручную записывается название столбца таблицы: СУММА ПРОДАЖИ.

ü Затем нам нужно указать, что сумма продажи = количество проданной продукции (из таблицы ПРОДАЖИ) * цена продукции (из таблицы ТОВАРЫ). Для этого в левом крайнем поле открывшегося окна выбирается папка ТАБЛИЦЫ (щелкаем по +) в результате чего отображаются все созданные ранее таблицы базы данных, в открывшихся папках выбирается таблица ПРОДАЖИ. После выбора нужной таблицы в поле справа отображаются поля выбранной таблицы. Следует выбрать поле КОЛИЧЕСТВО и далее нажимаем кнопку ВСТАВИТЬ.После этого поля ставится знак умножения (см.рис.17).

	Рисунок 17. Окно построителя выражений

ü Точно так же добавляется значение поля ЦЕНА из таблицы ТОВАРЫ. В результате должно получиться как на рисунке 18. По окончании построения выражения нажимаем ОК.

	Рисунок 18. Построенной выражение вычисления стоимости продажи

ü В результате построенного выражения создается дополнительный вычисляемый столбец. Для проверки правильности вычисления этого поля нужно запустить запрос с помощью кнопки запуска запросов . или нажатием кнопки и в появившемся меню выбираем опцию Режим таблицы.

	Рисунок 19. Результат вычислений запроса

ЗАДАНИЕ 4. СОЗДАНИЕ ОТЧЕТА БАЗЫ ДАННЫХ.

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

Для создания отчета:

ü откройте вкладку Создание в окне базы данных и щелкните по кнопке Мастер отчетов;

ü щелкните по значку раскрывающегося списка в нижней части окна и выберите из появившегося списка запрос ПРОДАЖИ ФИРМЫ;

ü В появившемся окне выберите поля, которые будут присутствовать в отчете. В данном примере присутствовать будут все поля из запроса, поэтому щелкните по кнопке (см.рис.21);

ü щелкните по кнопке ДАЛЕЕ;

	Рисунок 21. Формирование отчета на основе запроса

ü в появившемся окне присутствует перечень полей; переведите выделение на по­ле «Наименование товара» кнопкой , затем на поле «Сумма продаж» (см.рис.22); таким образом вы задаете группировку данных по товару;

Рисунок 22. Формирование отчета на основе запроса

ü щелкните по кнопке ДАЛЕЕ;

ü параметры появившегося окна оставим без изменений, поэтому щелкните по кнопке ДАЛЕЕ;

ü в появившемся окне выберите макет для отчета (см.рис.23);

Рисунок 23. Выбор макета отчета

ü щелкните по кнопке ДАЛЕЕ;

ü в появившемся окне выберите стиль оформления отчета (см.рис.24);

	Рисунок 24. Выбор стиля оформления отчета

ü щелкните по кнопке <Готово>. На экране появится сформированный отчет (см.рис.25).

	Рисунок 25. Сформированный отчет

Аналогично другим объектам можно в режиме конструктора менять внешний вид отчета, отдельно полей сформированного документа и т.д.