хз. Лабораторная работа 8 Итоговые запросы Цели работы
 Скачать 0.57 Mb.
|
|
Лабораторная работа №8 Итоговые запросы Цели работы: Приобрести навыки по работе с данными путём группировки их по нескольким признакам. Задачи работы: Научиться создавать перекрёстные запросы и запросы с группировкой. Общие сведения. Перекрестные запросы — это запросы, в которых происходит статистическая обработка данных, результаты которой выводятся в виде таблицы, очень похожей на сводную таблицу 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. Нажмите кнопку Выполнить (восклицательный знак). Появится результирующее множество записей по тем месяцам, которые есть в вашей таблице «Заказы». Сохраните запрос под именем «Продажи по месяцам». 2. Использование фиксированных заголовков столбцов в перекрестных запросах. Для того чтобы в построенном запросе вывести все столбцы в порядке календарной очередности месяцев, нужно задать фиксированные заголовки столбцов. 2.1. Переключитесь в режим Конструктора и выведите на экран свойства запроса. Для этого щелкните левой кнопкой мыши на свободном поле панели и в меню выберите Свойства. В диалоговом окне свойств запроса выводится свойство Заголовки столбцов, которое имеется только у перекрестных запросов. Внимание! Если окно открылось не полностью, щёлкните мышкой ещё раз. 2.2. Введите краткие названия каждого месяца года в поле свойства Заголовки столбцов. Необходимо воспроизвести названия так, как они возвращаются функцией Format () и выглядят при стандартном отображении заголовков столбцов (первые три буквы названия месяца), в противном случае, данные для неправильно заданных месяцев не отображаются. Названия требуется заключить в кавычки и разделить символом точки с запятой. Пробелы  не нужны. По завершении ввода названий всех 12 месяцев закройте окно свойств (рис. 15). Рис. 15. Перекрёстный запрос. 2  .3. Перейдите в режим просмотра SQL: Режим/Режим SQL. Инструкция SQL содержит операцию TRANSFORM, в которой определяются данные, содержащиеся в таблице. В операции PIVOT задаются заголовки столбцов. После ключевого слова IN указываются, если они есть, фиксированные названия столбцов (рис. 16). Рис. 16. Окно режима SQL 2.4. Вернитесь в режим Конструктора и нажмите кнопку Выполнить для вывода результатов запроса. Теперь заголовки столбцов перекрестной таблицы соответствуют календарной последовательности. 2.5. Нажмите главную кнопку окна (левый верхний угол), выберите Сохранить как и сохраните запрос под именем "Ежемесячная выручка". 3. Изменение уровня детализации в запросе Для создания удобного для анализа представления или наглядного графика можно сократить число строк и столбцов в результатах построенного запроса. С этой целью создадим перекрестный запрос, который выводит ежеквартальную выручку от продаж по типам товаров. 3.1. В режиме Конструктора добавьте в уже созданный перекрёстный запрос «Ежемесячная выручка» таблицу "Типы". 3.2. Замените столбцы «КодТовара» и «Марка» в запросе столбцами «КодТипа» и «Категория» таблицы «Типы». Для этого сначала замените имя таблицы, а затем имя поля. В строке Перекрестная таблица обоих столбцов выберите значение Заголовки строк. 3.3. Измените выражение в столбце «Выражение»: Format([Заказы]! [ДатаРазмещения];"""Квартал ""q") , которое определяет заголовки столбцов "Квартал 1", "Квартал 2", "Квартал 3", "Квартал 4". Несколько кавычек требуется для указания того, что слово Квартал и пробел рассматриваются как строка, a q — как формат. 3.4. Откройте бланк свойств и очистите содержимое свойства Заголовки столбцов, а затем закройте бланк. Если не удалить фиксированные заголовки (названия месяцев), то в перекрестной таблице данные не будут отражаться. 3.5. Сохраните запрос Сохранить как под именем «По кварталам». Нажмите кнопку Выполнить для вывода результатов запроса. 4. Группировка в запросах. Определение пяти самых дорогих товаров. Access имеет множество встроенных функций. Некоторые из них называются функциями обобщения, поскольку работают с группами строк, а не с отдельными строками. Нужную функцию можно задать, набрав на клавиатуре её имя в строке «Групповая операция», или выбрав её из раскрывающегося списка. Основные функции обобщения (групповые операции): SUM - вычисляет сумму всех значений заданного поля (для числовых полей), отобранных запросом; AVG - вычисляет среднее значение среди записей, отобранных запросом (для числовых и денежных типов данных); MIN, MAX - выбирает, соответственно, минимальное и максимальное значение в записях поля, отобранных запросом; COUNT - вычисляет количество записей, имеющих ненулевое значение и отобранных запросом; FIRST, LAST - определяет, соответственно, первое и последнее значение в указанном поле записей. 4.1. Создайте запрос в режиме Конструктора по таблице «Товары». В нижнюю панель окна выберите поля «Марка» и «Цена». 4.2. На вкладке Конструктор в разделе Показать или скрыть выберите кнопку  Итоги. В появившейся строке Групповые операции в поле «Цена»выберите Мах, а в строке Сортировка - По убыванию. 4  .3. Щелкните правой кнопкой мыши по пустому месту в окне Конструктор и в контекстном меню выберите Свойства. В окне Свойства запроса в строке Вывод всех полей выберите Нет (рис.17). В строке Набор значений из раскрывающегося списка выберите 5. Рис. 17. Окно свойств запроса. 4.4. Закройте окно свойств запроса. Выполните запрос и сохраните его под именем «Самые дорогие товары». 5. Многотабличный запрос. Построение запроса на основе уже имеющегося запроса. Для того, чтобы ответить на вопрос: "Сколько заказов и на какую сумму разместил каждый клиент?" необходимо построить многотабличный запрос. 5.1. Создайте запрос "Сведения о заказах", добавив в окно Конструктора таблицы «Заказано» и «Товары». Из таблицы «Заказано» выберите поле «КодЗаказа», поля «Марка» и «Цена» из таблицы «Товары», поля «Количество» и «Скидки» - из таблицы «Заказано». 5.2. В следующее поле (пустое) введите выражение CCur([Товары]![Цена]* [Заказано]![Количество]*(1-[Заказано]![Скидка])/100)*100. Нажав клавиши Shift+F2 в окне Область ввода вместо Выражение 1: введите ОтпускнаяЦена. 5.3. Щелкните правой кнопкой по пустому полю следующего столбца и в открывшемся меню выберите Свойства. В строке «Вывод всех полей» выберите значение Нет. Запустите запрос и сохраните его под именем «Сведения о заказах». 5.4. Создайте новый запрос и добавьте в него таблицы «Клиенты», «Заказы» и запрос «Сведения о заказах». 5.5. Перетащите в бланк заказа поле «Название» из таблицы «Клиенты», поле «КодЗаказа» из таблицы «Заказы», а затем дважды выберите поле «Отпускная цена» из запроса «Сведения о заказах». 5.6. Нажмите кнопку Итоги на ленте инструментов. В бланк запроса добавляется строка «Групповая операция», содержащая по умолчанию в каждой ячейке операцию Группировка. 5.7.В строке «Групповая операция» задайте необходимые статистические функции. Для поля «Название» оставьте значение Группировка, для поля «КодЗаказа» выберите из списка функцию Сount (подсчёт количества записей в поле), а для поля «Отпускная цена» первый раз выберите Sum (суммирует все значения в поле). А для подсчёта среднего значения второй раз используйте функцию Avg. 5.8. Запустите запрос на выполнение, чтобы посмотреть результаты запроса. Должна получиться таблица, которая содержит список клиентов, для каждого клиента указывается количество заказов, которое он сделал, общая сумма и средняя стоимость этих заказов. Закройте запрос, сохранив eго под именем "Итоги по клиентам". 6. Самостоятельная работа. 6.1. Создайте запрос на выборку самых дорогих кондитерских товаров. 6.2. Создайте запрос на выборку клиентов с самыми большими заказами 6.3. Создайте запрос, в котором будут отражены все совладельцы предприятий в г. Тюмени среди клиентов. |