Разработка базы данных отдела сбыта готовой продукции предприятия

По документам “Товарно-транспортная накладная” получить, на какую сумму отгрузили товар каждому заказчику по каждому договоруот начала года до концазаданногоквартала.Запрос: Сумма-фактТаблицы: Изделие, СТТН, ТТН, Договор, ЗаказчикЗапрос: ОтклонениеТаблицы: -Подсчитать отклонение в стоимостном выражении.Сформировать отчетПечать отчетаРассмотрим решение задачи по шагам.Шаг 1.Из таблиц Заказчик, Договор, СДОГ и Изделие получить, на какую общую сумму в руб., запланирована отгрузка каждому заказчику по каждому договору от начала года до конца заданного квартала.Будем создавать запрос в режиме Конструктора. В бланк запроса выберем поля: Наименование заказчика, Номер договора, Количество и Цена. Для определения квартала в следующее поле введем формулу: IIf([СДОГ]![Месяц отгрузки] In ("1";"2";"3");"1";IIf([СДОГ]![Месяц отгрузки] In ("4";"5";"6");"2";IIf([СДОГ]![Месяц отгрузки] In ("7";"8";"9");"3";"4"))).Вэтом поле в Условие отбора введем: <=[Введите квартал].Сгруппируем данные по полям Наименование заказчика и Номер договора, с помощью кнопки Итоги на панели инструментов Конструктор. А по полю Количествои Цена подсчитаем сумму, с помощью групповой операции Sum, как показано на рис. 17. Для расчета суммы по договору в следующем поле введем формулу: Сумма: [Sum-Количество]*[Sum-Цена].Рисунок 17 - Вид запроса Сумма-планв КонструктореТекст этого запроса имеет вид:SELECT Заказчик.[Наименование заказчика], Договор.[Номер договора], Sum(СДОГ.Количество) AS [Sum-Количество], Sum(Изделие.Цена) AS [Sum-Цена], [Sum-Количество]*[Sum-Цена] AS СуммаFROM ((Договор INNER JOIN СДОГ ON Договор.[Номер договора] = СДОГ.[Номер договора]) INNER JOIN Заказчик ON Договор.[Код заказчика] = Заказчик.[Код заказчика]) INNER JOIN Изделие ON СДОГ.[Код изделия] = Изделие.[Код изделия]WHERE (((IIf([СДОГ]![Месяц отгрузки] In ("1","2","3"),"1",IIf([СДОГ]![Месяц отгрузки] In ("4","5","6"),"2",IIf([СДОГ]![Месяц отгрузки] In ("7","8","9"),"3","4"))))<=[Введите квартал]))GROUP BY Заказчик.[Наименование заказчика], Договор.[Номер договора];Результат по данному запросу показан в таблице 14.Таблица 14 – Результаты запроса Сумма-планНаименование заказчикаНомер договораSum-КоличествоSum-ЦенаСуммаМясницкий ряд, сеть фирменных магазинов1117790564,35р.445836,5Мясницкий ряд, сеть фирменных магазинов1120550419,41р.230675,5МясновЪ, сеть мясных магазинов1111600615,83р.369498МясновЪ, сеть мясных магазинов1113340645,24р.219381,6МясновЪ, сеть мясных магазинов1114340570,04р.193813,6Ремит, сеть магазинов колбасных изделий1116820502,32р.411902,4Рублевские колбасы, сеть магазинов1112500541,69р.270845Рублевские колбасы, сеть магазинов1115650559,69р.363798,5Светоч, ООО, производственная компания1119390568,81р.221835,9Сгомонь, сеть магазинов1118430538,66р.231623,8Шаг 2.Из таблиц Изделие, СТТН, ТТН, Договор и Заказчик получить, накакую сумму отгрузили товар каждому заказчику по каждомудоговору от начала года до конца заданного квартала.Этот запрос создадим аналогично предыдущему, только выберем таблицыТТН и СТТН и вместо поля Дата отгрузкивведем выражениеFormat([Дата отгрузки];"q") для преобразования даты в номер квартала.Это показано на рис. 18.Рисунок 18 - Вид запроса Сумма-фактв КонструктореТекст этого запроса имеет вид:SELECT Заказчик.[Наименование заказчика], Договор.[Номер договора], Sum(СТТН.[Количество по ТТН]) AS [Sum-Количество по ТТН], Sum(Изделие.Цена) AS [Sum-Цена], [Sum-Количество по ТТН]*[Sum-Цена] AS СуммаFROM Заказчик INNER JOIN ((ТТН INNER JOIN (Изделие INNER JOIN СТТН ON Изделие.[Код изделия] = СТТН.[Код изделия]) ON ТТН.[Номер ТТН] = СТТН.[Номер ТТН]) INNER JOIN Договор ON ТТН.[Номер договора] = Договор.[Номер договора]) ON Заказчик.[Код заказчика] = Договор.[Код заказчика]WHERE (((Format([Дата отгрузки],"q"))<=[Введите квартал]))GROUP BY Заказчик.[Наименование заказчика], Договор.[Номер договора];Результат по данному запросу показан в таблице 15.Таблица 15 – Результаты запроса Сумма-фактНаименование заказчикаНомер договораSum-Количество по ТТНSum-ЦенаСуммаМясницкий ряд, сеть фирменных магазинов1117755564,35р.426084,25МясновЪ, сеть мясных магазинов1111575615,83р.354102,25МясновЪ, сеть мясных магазинов1113185645,24р.119369,4МясновЪ, сеть мясных магазинов1114320570,04р.182412,8Ремит, сеть магазинов колбасных изделий1116740502,32р.371716,8Рублевские колбасы, сеть магазинов1112500541,69р.270845Рублевские колбасы, сеть магазинов1115612559,69р.342530,28Шаг 3.Подсчитать отклонение в стоимостном выражении.Используем последние два запросадля определения отклонения.Эти запросы объединим связями по полям: Наименование заказчикаи Номер договора. Далее поочередно щелкнем два раза мышью по созданным связям и выберем второй вариант связи в появившемся окне. Это требуется для того чтобы в запросе были отображены все поля из запроса Сумма-плани только те поля из запроса Сумма-факт, в которых совпадают связанные поля. Это может случиться, когда изделие было заказано по договору, но по каким-то причинам не было отгружено, например,из-за отсутствиятранспорта. Запрос будем создавать в режиме Конструктора. Поместим в бланк запроса поля Наименование заказчика, Номер договора, Сумма из запроса Сумма-план. А также поле Сумма из запроса Сумма-факт. В следующее поле введем формулу: Отклонение: [Сумма-план.Сумма]-Nz([Сумма-факт.Сумма]), как показано на рис. 19. Результатом выполненияфункцииNzявляется ноль, и она разрешает вести расчеты с полем, которое указано в скобках. Рисунок 19 - Вид запроса Отклонение в КонструктореТекст этого запроса имеет вид:SELECT [Сумма-план].[Наименование заказчика], [Сумма-план].[Номер договора], [Сумма-план].Сумма, [Сумма-факт].Сумма, [Сумма-план.Сумма]-Nz([Сумма-факт.Сумма]) AS ОтклонениеFROM [Сумма-план] LEFT JOIN [Сумма-факт] ON ([Сумма-план].[Номер договора] = [Сумма-факт].[Номер договора]) AND ([Сумма-план].[Наименование заказчика] = [Сумма-факт].[Наименование заказчика]);Результат по данному запросу показан в таблице 16.Таблица 16 – Результаты запроса ОтклонениеНаименование заказчикаНомер договораСумма-план.СуммаСумма-факт.СуммаОтклонениеМясницкий ряд, сеть фирменных магазинов1117445836,5426084,2519752,25Мясницкий ряд, сеть фирменных магазинов1120230675,5230675,5МясновЪ, сеть мясных магазинов1111369498354102,2515395,75МясновЪ, сеть мясных магазинов1113219381,6119369,4100012,2МясновЪ, сеть мясных магазинов1114193813,6182412,811400,8Ремит, сеть магазинов колбасных изделий1116411902,4371716,840185,6Рублевские колбасы, сеть магазинов11122708452708450Рублевские колбасы, сеть магазинов1115363798,5342530,2821268,22Светоч, ООО, производственная компания1119221835,9221835,9Сгомонь, сеть магазинов1118231623,8231623,86. СОЗДАНИЕ ОТЧЁТОВ6.1. Получение отчёта с помощью Мастера отчётовПо результатам запроса Отклонениесоздадим однотабличный отчет. Отчет создается с помощью Мастера отчетов и редактируется в Конструкторе.От начала года до конца 3 квартала отчёт примет вид:Рисунок 20 – Вид отчета Отклонение6.2. Корректировка макета отчёта с помощью Конструктора отчётовЧтобы войти вКонструктор отчётов нажмем кнопку Режим на панели инструментов и выберем Конструктор. Теперь отчет можно корректировать.Удалим заголовок отчета в режиме Конструктор отчета.Создадим прямоугольную область для нового заголовка отчета с помощью инструмента Надпись и введем:Анализ выполнения плана по договорам до конца____квартала.Откорректируем текст подписи полей отчета.Изменим расположения, цвета фона заголовка, цвета и размера шрифта.Далее создадим поле Квартал, следующим образом:нажмем на кнопку Поле,разместим поле в заголовке отчета и в окне свойств на вкладке Данные в поле Данные введем: =[Введите квартал]. В примечании отчета под соответствующими полямипоместим формулы:=Sum([Сумма-план.Сумма]), =Sum([Сумма-факт.Сумма]), =Sum([Отклонение]).В итоге выполненных корректировок отчетдо конца 3 квартала, примет вид:Рисунок 21 - Вид отчета Отклонение6.3. Вывод отчёта на печатьДля печати полученного отчета нужно щелкнемпо названию отчета правой кнопкой мыши и выберем команду Печать.7. СОЗДАНИЕ КНОПОЧНОЙ ФОРМЫ С ПОМОЩЬЮ ДИСПЕТЧЕРА КНОПОЧНЫХ ФОРМДля удобства доступа к созданным объектам базы данных в работе были созданы специальные кнопочные формы, их внешний вид представлен на рис.22-25.Рисунок 22 – Главная кнопочная форма Рисунок 23 –Форма для добавления данныхРисунок 24 – Форма для редактирования данныхРисунок 25 – Форма для просмотра запросов и отчетаЗАКЛЮЧЕНИЕЦелью курсовойработы было разработать базу данных отдела сбыта готовой продукции предприятия. Цель, которая былапоставленна перед началом разработки, достигнута.Разработанная база выполняет следующие задачи:- содержит детальные сведения опродукции выпускаемой предприятием и о заказчиках продукции предприятия.- позволяетполучать доступ к информации оперативных документов предприятия: договоры, заключенные с заказчиком, товарно-транспортные накладные; а также легко изменять ее (добавлять, редактировать, удалять: при работе с ней сотрудниковотдела сбыта готовой продукции предприятия);- производит расчет отклонения фактической отгрузки заказчикам от плана отгрузки по договорам от начала года до конца заданного квартала;- база данных автоматизирует работу сотрудников отдела сбыта готовой продукции предприятия.В курсовойработебыло проведено проектирование создаваемой базы данных:инфологическое и даталогическое. Были установлены сущности, атрибуты, взаимосвязи между сущностями, ключи базы данных. Была создана диаграмма ER-типа,в которой были учтены все сущности и их связи. Построена схема проектируемой базы данных. Осуществлено проектирование реляционной модели изER-модели.А, также, была выполнена физическая реализация проектируемой базы данных. Описана структура таблиц, созданы схема данных в Access и пользовательские формы.Проведено описание формирования запросов к базе данных. Приведены способы повышения удобства доступа к данным в виде отчетов и кнопочных форм базы данных.Данная база данных имеет ряд достоинств и недостатков.К достоинствамотносятся:- легко и удобно получать доступ к данным, хранящимся в базе;- можно легко расширять базу данных;- осуществлять быстрый поиск необходимых данных;- легко переносить с одного компьютера на другой;- можно редактировать результаты запросов.К недостаткам относятся:- низкий уровень безопасности.СПИСОК ЛИТЕРАТУРЫБалдин К. В. Информационные технологии в менеджменте: учеб. для студ. Учреждений высш. проф образования / К. В. Балдин. – М.: Издательский центр «Академия», 2012. - 288 с.Гарсиа-Молина Г., Ульман Дж., Уидом Дж. Системы баз данных. Полный курс.Пер. с англ.: — М.: Изд. дом «Вильямс», 2004. — 1088 с.Дейт, К. Введение в системы баз данных: пер. с англ. /К.Дж. Дейт. 8-е издание. - М.: Вильямс , 2006. - 1326 с.Дунаев В. В. Базы данных. Язык SQL / В. В. Дунаев. – СПб. : BHV, 2006. – 288 с. Кошелев В.Е. Access 2007. Эффективное использование – М.: Бином-Пресс, 2009. – 590 с.Кузин А.В. Базы данных: учебное пособие / А.В. Кузин, С.В. Левонисова. – 5-е издание, исправ., – Москва: Академия, 2012. – 320с.Кузнецов С. Д. Основы баз данных. — 2-е изд. — М.: Интернет-Университет Информационных Технологий; БИНОМ. Лаборатория знаний, 2007. — 484 с.Малыхина М.П. Базы данных: основы, проектирование, использование, 2-е изд. перераб. и доп. – СПб.: БХВ-Петербург, 2007. – 528 с.Мартин Грабер. Введение в SQL, БХВ-Петербург, 2010. – 228 с.Мэтью Мак-Дональд. Access 2007 Недостающее руководство – СПб.:  БХВ-Петербург, 2007. – 784с.Проектирование баз данных. СУБД MicrosoftAccess: Учебное пособие для вузов / Н. Н. Гринченко, Е. В. Гусев, Н. П. Макаров.,А. Н. Пылькин, Н. И. Цуканова. - М.: Горячая линия-Телеком, 2004. - 240с.Сеннов А. Access 2010. – СПб.: «Питер», 2010. – с.288.Сергеев А.В.: Access 2007. Новые возможности. СПб.: Питер, 2008. –176с.Харитонова И., Рудикова Л. MicrosoftOfficeAccess 2007 – Изд.: «БХВ-Петербург», 2008 – 1280с.Хомоненко А.Д., Цыганков В.М., Мальцев М.Г. Базы данных: Учебник для высших учебных заведений / Под ред. Проф. А.Д. Хомоненко. – 6-е изд., СПб.: КОРОНА принт, 2009. – 736 с.