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

     Динамическая  таблица должна содержать поля «КодТовара», «Название», «Вид» и «КоличествоТовара».

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

     Чтобы вывести на экран только те товары, которые заказывались в данный период времени на панели «Конструктор запросов» выбираем «Итоги». Появится строка «Групповая операция», где для поля «ДатаЗаказа» вписываем «Условие», а для поля «КоличествоТовара» - «Sum», для всех остальных - «Группировка». Теперь для поля «ДатаЗаказа» вместо названия впишем «Month(Date())-Month([Заказы]![ДатаЗаказа])», а для поля «КоличествоТовара» - «КоличествоТовара:КоличествоТовара», кроме того для поля «Month(Date())-Month([Заказы]![ДатаЗаказа])» в строку «Условие отбора» впишем <[Введите число месяцев].  Запрос в режиме конструктора представлен на рисунке 12. 

 

     Рисунок 12 – Запрос «Заказанные  товары за последние  месяцы» в режиме конструктора 

     Результат запроса в режиме таблицы представлен на рисунке 13. 

 

 

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

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

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

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

     WHERE (((Month(Date())-Month([Заказы]![ДатаЗаказа]))<[Введите число месяцев]))

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

     Запрос 2. Далее создадим запрос для отбора данных о клиентах по номеру заказов.

      Динамическая  таблица должна содержать поля «НомерЗаказа», «ФИО», «Телефон» и «СтоимостьЗаказа».

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

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

 

      Рисунок 14 – Запрос «Заказы и Клиенты» в режиме конструктора 

      Результат запроса в режиме таблицы представлен  на рисунке 15 

 

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

      Текст запроса «Заказы и Клиенты» на языке SQL:

      SELECT Заказы.НомерЗаказа, Клиенты.ФИО, Клиенты.Телефон, Заказы.СтоимостьЗаказа

      FROM Клиенты INNER JOIN Заказы ON Клиенты.НомерКлиента = Заказы.НомерКлиента;

      Запрос 3. Создадим запрос, который будет отражать стоимость заказов на все виды товаров в период времени, указанный пользователем.

      Динамическая  таблица должна содержать поля «КодВида», «Вид» и «Sum-Стоимость».

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

     Чтобы вывести на экран только те виды товаров, которые заказывались в данный период времени на панели «Конструктор запросов» выбираем «Итоги». Появится строка «Групповая операция», где для полей «ДатаЗаказа» вписываем «Условие», а для поля «Стоимость» - «Sum». Для «ВидТовара» пишем «Группировка». Теперь для поля «ДатаЗаказа» вместо названия впишем «Month([Заказы]![ДатаЗаказа])», а в строку «Условие отбора» впишем >=[Введите месяц, с которого вести расчет]. Для такого же поля «ДатаЗаказа» вписываем вместо названия «Year([Заказы]![ДатаЗаказа])» ])», а в строку «Условие отбора» впишем >=[Введите год, с которого вести расчет]. Для «КоличествоТовара» - «КоличествоТовара:КоличествоТовара» в название поля. Запрос в режиме конструктора представлен на рисунке 16. 

 

      Рисунок 16 – Запрос «Продажа товаров по видам» в режиме конструктора 

      Результат запроса в режиме таблицы представлен  на рисунке 17 

 

 

 

      Рисунок 17 – Запрос «Продажа товаров по видам» в режиме таблицы 

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

      SELECT ВидТовара.КодВида, ВидТовара.Вид, Sum(Накладная.Стоимость) AS [Sum-Стоимость]

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

      WHERE (((Year(Заказы!ДатаЗаказа))>=[введите год, с которого вести расчет]) And ((Month(Заказы!ДатаЗаказа))>=[Введите месяц, с которого вести расчет]))

      GROUP BY ВидТовара.КодВида, ВидТовара.Вид

      ORDER BY Sum(Накладная.Стоимость) DESC;

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

      Динамическая  таблица должна содержать поля «Месяц», «КоличествоЗаказов».

     Чтобы создать этот запрос сначала необходимо нажать кнопку «КонструкторЗапросов»  на панели «Создание» окна базы данных. Далее щелчком мыши выделяем таблицу  «Заказы» и нажмем кнопку «Добавить», затем кнопку «Закрыть». Затем в  бланк запроса добавляем поля «ДатаЗаказа», «НомерЗаказа».  Проверим наличие флажков в строке «Вывод на экран» бланка запроса для всех добавленных полей.

     Чтобы подсчитать количество заказов на панели «Конструктор запросов» выбираем «Итоги». Появится строка «Групповая операция», где для поля «ДатаЗаказа» вписываем «Группировка», а для поля «НомерЗаказа» - «Count». Запрос в режиме конструктора представлен на рисунке 18. 

 

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

     Результат запроса в режиме таблицы представлен  на рисунке 19. 

 

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

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

    SELECT "Месяц " & DatePart("m",[ДатаЗаказа]) AS Месяц, Count(Заказы.НомерЗаказа) AS [Количество заказов]

    FROM Заказы

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

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

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

     Чтобы вывести на экран только те виды товаров, которые заказывались в  данный период времени на панели «Конструктор запросов» выбираем «Итоги». Появится строка «Групповая операция», где для  полей «ДатаЗаказа» вписываем «Условие», а для поля «Стоимость» - «Avg». Для «ВидТовара» и «ФИО» пишем «Группировка». Теперь для поля «ДатаЗаказа» вместо названия впишем «Month([Заказы]![ДатаЗаказа])», а в строку «Условие отбора» впишем >=[Введите месяц, с которого вести расчет]. Для такого же поля «ДатаЗаказа» вписываем вместо названия «Year([Заказы]![ДатаЗаказа])», а в строку «Условие отбора» впишем >=[Введите год, с которого вести расчет].Для поля Стоимость вместо названия впишем «Средняя стоимость: СтоимостьЗаказа» и в строке «Сортировка» укажем «По убыванию». Запрос в режиме конструктора представлен на рисунке 20. 

 

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

      Результаты  запроса в режиме таблицы представлены на рисунке 21. 

 

 

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

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

     SELECT Клиенты.НомерКлиента, Клиенты.ФИО, Avg(Заказы.СтоимостьЗаказа) AS [Средняя стоимость]

     FROM Клиенты INNER JOIN Заказы ON Клиенты.НомерКлиента = Заказы.НомерКлиента

     WHERE (((Year([Заказы]![ДатаЗаказа]))>=[введите год, с которого вести рассчет]) AND ((Month([Заказы]![ДатаЗаказа]))>=[Введите месяц, с которого вести рассчет]))

     GROUP BY Клиенты.НомерКлиента, Клиенты.ФИО

     ORDER BY Avg(Заказы.СтоимостьЗаказа) DESC;

     Запрос 6. Создадим запрос на обновлегие, который заполнит поле "СтоимостьЗаказа» таблицы «Заказы».

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

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

 

     Рисунок 22 – Запрос «Вычисление стоимости товара в накладной» в режиме конструктора

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

 

     Рисунок 23 – Запрос «Вычисление стоимости товара в накладной» в режиме таблицы 

      Текст запроса «Вычисление стоимости товара в накладной» в режиме SQL:

      UPDATE Товары INNER JOIN Накладная ON Товары.НомерТовара = Накладная.НомерТовара SET Накладная.Стоимость = [Накладная]![КоличествоТовара]*[Товары]![Цена];

      Запрос 7. Создадим итоговый запрос для подсчета стоимости заказов клиентов.

     Чтобы создать этот запрос сначала необходимо нажать кнопку «КонструкторЗапросов»  на панели «Создание» окна базы данных. Далее щелчком мыши выделяем таблицу  «Заказы», «Стоимость» и нажмем кнопку «Добавить», затем кнопку «Закрыть». Затем в бланк запроса добавляем  поля «НомерЗаказа», «Стоимость».  Проверим наличие флажков в строке «Вывод на экран» бланка запроса для этих полей.