Разработка базы данных для автоматизации учета продаж продукции фирмы Орифлейм

     Чтобы вывести на экран только те виды товаров, которые заказывались в  данный период времени на панели «Конструктор запросов» выбираем «Итоги». Появится строка «Групповая операция», где для  поля «НомерЗаказа» вписываем «Группировка», а для поля «Стоимость» - «Sum».

      Запрос  в режиме конструктора представлен  на рисунке 24. 
 

 

      Рисунок 24 – Запрос «Стоимость заказа» в режиме конструктора  

      Данный  запрос в режиме таблицы представлен  на рисунке 25. 

 

      Рисунок 25 - Запрос «Стоимость заказа» в режиме таблицы 

      Текст запроса «Стоимость заказа» в режиме SQL:

      SELECT Заказы.НомерЗаказа, Sum(Накладная.Стоимость) AS [Sum-Стоимость] INTO темп

      FROM Заказы INNER JOIN Накладная ON Заказы.НомерЗаказа = Накладная.НомерЗаказа

      GROUP BY Заказы.НомерЗаказа;

      Запрос 8. Создадим запрос на обновление поля «СтоимостьЗаказа».

       Чтобы создать этот запрос сначала необходимо нажать кнопку «КонструкторЗапросов» на панели «Создание» окна базы данных. Далее щелчком мыши выделяем таблицу «Заказы» и нажмем кнопку «Добавить», затем кнопку «Закрыть». Затем в бланк запроса добавляем поле «СтоимостьЗаказа». Затем в «Конструктор запросов» выбираем «Обновление». В строку «обновление» укажем «[темп]![Sum-Стоимость]».

      Запрос  в режиме конструктора представлен  на рисунке 26. 

        

      Рисунок 26 – Запрос «Обновление таблицы Заказы» в режиме конструктора 

      Данный  запрос в режиме таблицы представлен  на рисунке 27. 

      Рисунок 27 – Запрос «Обновление таблицы Заказы» в режиме  таблицы

      Текст запроса «Обновление таблицы Заказы» в режиме SQL:

      UPDATE Заказы INNER JOIN темп ON Заказы.НомерЗаказа = темп.НомерЗаказа SET Заказы.СтоимостьЗаказа = [темп]![Sum-Стоимость];

      Запрос 9. Теперь создадим перекрестный запрос для подсчета количества заказанных единиц каждого товара каждым клиентом за все поставки на текущий год.

      Чтобы создать этот запрос сначала необходимо нажать кнопку «КонструкторЗапросов»  на панели «Создание» окна базы данных. Далее щелчком мыши выделим все таблицы и нажмем кнопку «Добавить», затем кнопку «Закрыть». Добавим в бланк запроса поля «Название» из таблицы «Товары» и поля «Вид» из таблицы «ВидТовара», поля «ДатаЗаказа» из таблицы «Заказы», поля «ФИО» из таблицы «Клиенты», поля «Количество Товара» из таблицы «Накладная». Теперь определим тип создаваемого запроса. Для этого выполните команду меню «Работа с зспросами/Перекрестный». Далее укажем, что значения поля «Название» и «Вид» будут использоваться в качестве заголовков строк, выбрав в строке «Перекрестная таблица» бланка запроса для этого поля из списка «Заголовки строк». Затем укажем, что значения поля «ФИО» будут использоваться в качестве заголовков столбцов, выбрав в строке «Перекрестная таблица» бланка запроса для этого поля из списка «Заголовки столбцов». Теперь нужно указать, что поле «КоличествоТовара» содержит значения, на основании которых будет вычисляться количество всех поставленных товаров. Для этого нужно в строке «Перекрестная таблица» бланка запроса для поля «КоличествоТовара» выберите из списка «Значение», а далее в строке «Групповая операция» бланка запроса для поля «КоличествоТовара» выберем из списка функцию Sum. Добавим в перекрестный запрос столбец, содержащий сумму всех поставленных единиц товара за все поставки. Для этого нужно еще раз перенести в бланк запроса поле «КоличествоТовара», в строке «Перекрестная таблица» бланка запроса для этого поля выберем из списка «Заголовки строк», а затем в строке «Групповая операция» бланка запроса для этого поля выберите из списка функцию Sum. Присвоим вычисляемому полю имя «Всего товаров», установив щелчком мыши текстовый курсор перед именем поля «Количество поставленных единиц» и введя «Всего товаров:».

      Чтобы на экран вывести результат только за этот год в названии «ДатаЗаказа» укажем «Year([Заказы]![ДатаЗаказа])», для этого же поля, но в строке «Групповая операция» укажем «Условие» и в сроке «Условие отбора» укажем «=Year(Date())». Сохраним запрос в базе данных под именем «Всего поставлено товаров», для этого выполним команду меню «Файл/Сохранить», в поле имя запроса введем: «Всего заказано за год» и нажмем ОК. Запрос в режиме конструктора представлен на рисунке 28. 

 

      Рисунок 28 – Запрос «Всего заказано за год» в режиме конструктора 

      Данный  запрос в режиме таблицы представлен  на рисунке 29. 

 

      Рисунок 29 - Запрос «Всего заказано за год» в режиме таблицы 

     Запрос  в режиме SQL выглядит следующим образом:

     TRANSFORM Sum(Накладная.КоличествоТовара) AS [Sum-КоличествоТовара]

     SELECT Товары.Название, ВидТовара.Вид, Sum(Накладная.КоличествоТовара) AS [Всего товаров]

     FROM (ВидТовара INNER JOIN Товары ON ВидТовара.КодВида = Товары.КодВида) INNER JOIN (Клиенты INNER JOIN (Заказы INNER JOIN Накладная ON Заказы.НомерЗаказа = Накладная.НомерЗаказа) ON Клиенты.НомерКлиента = Заказы.НомерКлиента) ON Товары.НомерТовара = Накладная.НомерТовара

     WHERE (((Year([Заказы]![ДатаЗаказа]))=Year(Date())))

     GROUP BY Товары.Название, ВидТовара.Вид

     ORDER BY Товары.Название DESC , Клиенты.ФИО DESC

     PIVOT Клиенты.ФИО;

     Запрос 10. Создадим перекрестный запрос, чтобы подсчитать стоимость товаров каждого вида, которые заказали за каждый месяц.

        Чтобы создать этот запрос сначала необходимо нажать кнопку «КонструкторЗапросов» на панели «Создание» окна базы данных. Далее щелчком мыши выделим таблицы «Товары», «Накладная», «Заказы», «ВидТовара» и нажмем кнопку «Добавить», затем кнопку «Закрыть». Добавим в бланк запроса поля «Название» из таблицы «Товары» и поля «Вид» из таблицы «ВидТовара», поля «ДатаЗаказа» из таблицы «Заказы», поля «Стоимость» из таблицы «Накладная». Теперь определим тип создаваемого запроса. Для этого выполните команду меню «Работа с зспросами/Перекрестный». Далее укажем, что значения поля «Название» и «Вид» будут использоваться в качестве заголовков строк, выбрав в строке «Перекрестная таблица» бланка запроса для этого поля из списка «Заголовки строк». Затем укажем, что значения поля «"Месяц " & DatePart("m";[ДатаЗаказа])» будут использоваться в качестве заголовков столбцов, выбрав в строке «Перекрестная таблица» бланка запроса для этого поля из списка «Заголовки столбцов». Теперь нужно указать, что поле «Стоимость» содержит значения, на основании которых будет вычисляться стоимость всех поставленных товаров. Для этого нужно в строке «Перекрестная таблица» бланка запроса для поля «Стоимость» выберите из списка «Значение», а далее в строке «Групповая операция» бланка запроса для поля «Стоимость» выберем из списка функцию Sum. Добавим в перекрестный запрос столбец, содержащий сумму всех поставленных единиц товара за все поставки. Для этого нужно еще раз перенести в бланк запроса поле «Стоимость», в строке «Перекрестная таблица» бланка запроса для этого поля выберем из списка «Заголовки строк», а затем в строке «Групповая операция» бланка запроса для этого поля выберите из списка функцию Sum. Присвоим вычисляемому полю имя «ОбщаяСтоимость», установив щелчком мыши текстовый курсор перед именем поля «Стоимость»  и введя «ОбщаяСтоимость:».

     В полях «Вид» и «"Месяц " & DatePart("m";[ДатаЗаказа])» в строке «Сортировка» значения «По возрастанию». Запрос в режиме конструктора представлен на рисунке 30. 

 

     Рисунок 30 – Запрос «Промежуточная стоимость заказов по месяцам» в режиме конструктора 

     Данный  запрос в режиме таблицы представлен  на рисунке 31. 

 

     Рисунок 31 - Запрос «Промежуточная стоимость товаров» в режиме таблицы 

     Запрос  в режиме SQL выглядит следующим образом:

     TRANSFORM Sum(Накладная.Стоимость) AS [Sum-Стоимость]

     SELECT Товары.Название, ВидТовара.Вид, Sum(Накладная.Стоимость) AS ОбщаяСтоимость

     FROM (ВидТовара INNER JOIN Товары ON ВидТовара.КодВида = Товары.КодВида) INNER JOIN (Заказы INNER JOIN Накладная ON Заказы.НомерЗаказа = Накладная.НомерЗаказа) ON Товары.НомерТовара = Накладная.НомерТовара

     GROUP BY Товары.Название, ВидТовара.Вид

     ORDER BY ВидТовара.Вид, "Месяц " & DatePart("m",[ДатаЗаказа])

     PIVOT "Месяц " & DatePart("m",[ДатаЗаказа]); 

     3.3 Создание отчетов 

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

     Создадим  отчет при помощи мастера.  В  появившемся окне выбираем таблицу «Клиент» и из нее добавляем поле «ФИО». Далее добавляем таблицу «Заказ». Из нее добавляем поля «НомерЗаказа», «ДатаЗаказа» и «СтоимостьЗаказа». Добавляем таблицу «Накладная» и из нее поля «КоличествоТовара». Добавляем таблицу «Товары» и из нее «Название» Нажимаем кнопку «Далее». Появится окно «Выберите вид представления данных», мы ничего не выбираем и нажимаем кнопку «Далее». Появится окно «Добавить уровни группировки?». Без добавления уровней группировки нажимаем «Далее». В появившемся окне нажимаем на «Итоги», выбираем «Sum» для поля «КоличествоТовара». Затем выбираем макет для отчета «Ступенчатый». Нажимаем «Готово». У нас появляется отчет, который будет показывать фамилию, имя, отчество клиентов, даты заказов, количество заказанных товаров за каждый заказ и стоимость каждого заказа. Также по этим данным будет отображена сумма количества заказанных товаров каждым клиентом.

     Отчет сохраняем под названием «Отчет по клиентам». Заходим в отчет через конструктор и меняем названия поля «Sum»  на «Всего заказано товаров».

      Отчет представлен в приложении B.

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

     Выбираем  создание отчета с помощью мастера. В появившемся окне выбираем таблицу  «ВидТовара» и из нее добавляем поле «Вид». Далее выбираем таблицу «Товары» и выделяем поля «Название» и «Цена». Нажимаем кнопку «Далее». Появится окно «Выберите вид представления данных», мы ничего не выбираем и нажимаем кнопку «Далее». Появится окно «Добавить уровни группировки?». Без добавления уровней группировки нажимаем «Далее». В появившемся окне нажимаем на «Итоги», выбираем «Min» и «Max». Затем выбираем макет для отчета «ступенчатый». Нажимаем «Готово».

     Отчет сохраняем под именем «Вид товара». Названия полей «Min» и «Max» меняем на «Min цена» и «Max цена» соответственно. Отчет представлен в приложении Г. 

     3.4 Создание форм 

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

     Выбираем  создание формы с помощью мастера. В появившемся окне выбираем таблицу  «Товары» и из нее добавляем поля «Название» и «Цена». Далее добавляем таблицу «ВидТовара» и из нее выбираем поле «Вид» и таблицу «Заказы» с полями «Дата заказа» и «Стоимость заказа». Добавляем таблицу «Накладная» и оттуда «КоличествоТовара». Нажимаем кнопку «Далее». Появляется окно «Выберите вид представления данных». Здесь мы ничего не меняем, так как сначала мы выбрали главную, а затем подчинённую таблицу. По умолчанию стоит пункт «Подчинённые формы». Нажимаем кнопку «Далее». Появляется окно «Выберите внешний вид подчинённой формы». Выбираем вид «табличный», нажимаем кнопку «Далее». Нажимаем кнопку «Готово». Форму сохраняем под названием «Товар». Далее переходим в режим конструктора. В меню выбираем тему «Изящная». На панели элементов выбираем элемент «Кнопка». Переносим его на форму. Появляется диалоговое окно «Создание кнопок». Выбираем пункты: «Работа с отчётом» и «Просмотр отчёта». В следующем окне выбираем «Вид товара». Нажимаем кнопку «Готово». Таким же образом добавляем вторую кнопку. Переносим её на форму, в диалоговом окне выбираем: «Разное» и «Выполнить запрос». Далее выбираем «Всего заказано за год», нажимаем кнопку «Готово». Добавляем третью кнопку. Переносим её на форму, в диалоговом окне выбираем: «Приложение» и «Выйти из приложения». Нажимаем кнопку «Готово». В окне «Свойства» строке «Подпись» вписываем «Клиенты», «Запрос», «Выход» соотвественно. Кнопки используются для удобства вывода на экран нужной информации. Они выполняют определённые функции. Кнопка для отчётов позволяет вывести на экран отчёт, кнопка запросов – запрос, а кнопка выхода позволяет выйти из приложения. Форма «Товар» представлена на рисунке 32.