Общие сведения.

Итоговые запросы

Лабораторная работа №8

Проверка отсутствующей даты выполнения заказа и срока выполнения.

Выборка товаров, цена которых превышает среднюю.

Вычисления срока задержки исполнения заказа.

Создайте запрос в режиме Конструктора по таблице «Заказы». Необходимые поля: «Клиент», «ДатаРазмещения», «ДатаИсполения». В следующем (пустом) поле, используя Построитель выражений, наберите выражение: DateDiff("d";[Заказы]! [ДатаРазмещения]; [Заказы]![ДатаИсполнения]). В окне Область ввода вместо Выражение 1 наберите СрокЗадержки. Выполните запрос.

7.1. Создайте запрос в режиме Конструктора по таблице «Товары». Выберите поля «Марка», «Цена».

7.2. В строке «Условие отбора» в поле «Цена» наберите выражение >(SELECT AVG([Товары]![Цена]) From [Товары]). SELECT и From набираются с клавиатуры. Выполните запрос. Если в вашем запросе кроме полей «Марка» и «Цена» показываются другие поля, выполните следующие действия:

Ø Вернитесь в Конструктор запросов

Ø В пустом столбце нижней панели окна Конструктора щёлкните правой кнопкой мыши и в появившемся меню выберите Свойства. Появится окно Свойства запроса с пустыми строками.

Ø Щелкните по пустому месту в окне Конструктор. В окне Свойства запроса в строке «Вывод всех полей» выберите Нет. Закройте окно Свойства запроса.

7.3. Выполните запрос и сохраните его под именем «Цена выше среднего»

Бывают ситуации, когда при заполнении таблицы некоторые ячейки остаются незаполненными. Чтобы проверить полное заполнение таблицы, создайте запрос в режиме Конструктора по таблице «Заказы». Выберите поля КодЗаказа, ДатаРазмещения, ДатаНазначения, ДатаИсполнения. В следующем (пустом) поле наберите выражение: IIf(IsNull([ДатаНазначения]-[ДатаИсполнения]); "Проверьте отсутствующую дату";[ДатаНазначения]-[ДатаИсполнения]). Вызовите окно Область ввода и вместо Выражение 1 наберите СрокИсполнения:. Закройте окно Область ввода. В результате выполнения запроса в поле будет указан срок исполнения заказа. Если не указана одна из дат, то в данной строке появится надпись: Проверьте отсутствующую дату.

Выполните запрос, сохранив его под именем «СрокИсполнения»

9. Самостоятельная работа.

9.1. Из таблицы «Сотрудники», используя оператор Between, выберите фамилии сотрудников, первая буква которых попадает в диапазон от Л до Ф.

9.2. Выберите 10 самых дешёвых товаров.

 

Цели работы:

Приобрести навыки по работе с данными путём группировки их по нескольким признакам.

Задачи работы:

Научиться создавать перекрёстные запросы и запросы с группировкой.

Перекрестные запросы — это запросы, в которых происходит статистическая обработка данных, результаты которой выводятся в виде таблицы, очень похожей на сводную таблицу Excel. Перекрестные запросы обладают следующими достоинствами:

· возможность обработки значительного объема данных и вывода их в формате, который очень хорошо подходит для автоматического создания графиков и диаграмм;

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

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

Однако перекрёстные запросы имеют и недостатки — например, нельзя сортировать таблицу результатов по значениям, содержащимся в столбцах, т. к. в подавляющем большинстве случаев одновременное упорядочивание данных в столбцах по всем строкам невозможно. При этом вы можете задать сортировку по возрастанию или по убыванию для заголовков строк.

1. Создание перекрёстных запросов.

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

1.1. Создайте новый запрос в режиме Конструктора на основе данных из таблиц "Товары", "Заказы" и "Заказано".

1.2. Поместите поля «КодТовара» и «Марка» таблицы «Товары», а затем поле «ДатаРазмещения» таблицы «Заказы» в первые три столбца бланка запроса.

1.3. Выберите на вкладке Конструктор в разделе Тип запроса кнопкуПерекрестный запрос. В бланк запроса будут добавлены строки Перекрестная таблица и Групповая операция, в которую во всех столбцах автоматически вводится операция Группировка (рис. 14).

Рис. 14. Групповые операции

 

1.4. Выберите в списке строки Перекрестная таблицаистолбца «КодТовара» значение Заголовки строк. Выполните то же самое для столбца «Марка». Эти столбцы являются требуемыми заголовками строк перекрестной таблицы.

1.5. Выберите в списке строки Групповая операцияистолбца «ДатаРазмещения» значение Условие. В строке Условие отбораэтого столбца введите выражение <=#31.12.08# And >=#01.01.08# для вывода в перекрестной таблице данных, например, за 2008 год.

1.6. Установите курсор в первую ячейку пустого столбца и, вызвав Построитель выражений, введите выражение: Sum([Заказано]![Количество]* [Товары]![Цена]). Название функции Sum выберите в списке Функции/встроенные функции, а названия полей выберите в списке Таблицы. Закройте окно Построителя выражений. Перед введённым выражением автоматически появится Выражение 1:. Нажмите клавиши Shift+F2 и в открывшемся окне Областьввода, вместо Выражение 1 введите Объем продаж:.

Это будет поле, значение которого вычисляется с помощью описанного выражения, а название поля — «Объем продаж». В выражении используются поля из таблиц, которые включены в запрос, хотя поля в текст запроса не включены. Это означает, что при создании вычисляемого поля в выражение можно включать ссылки не только на поля самого запроса, но и на поля, которые не включаются в текст запроса. Важно, чтобы они были в исходных таблицах, а таблицы обязательно присутствовали в бланке запроса.

1.7. Выберите в строке Групповая операция столбца «Объём продаж» значение Выражение, а затем в строке Перекрестная таблица— значение Значение. В столбце «Объём продаж»вычисляется общий объем заказов на каждый товар, который будет подставляться в ячейки результирующей таблицы запроса (рис. 14).

1.8. Установите курсор в первую ячейку следующего пустого столбца и введите выражение Format([Заказы]![ДатаРазмещения];"mmm"). Функция Format в данном случае будет возвращать три первые буквы месяца от значения даты в столбце «ДатаРазмещения». С помощью этой функции создаётся еще одно вычисляемое поле в запросе. Выберите из списка в строке Перекрестная таблицаэтого столбца значение Заголовки столбцов.

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

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