Вычисления в запросах: простые, групповые

И сводные

 

 

Цель: знакомство с запросами как инструментом анализа данных, овладение техникой создания вычисляемых полей и применения статистических функций в итоговых запросах, освоение приемов анализа данных при помощи сводных таблиц.

 

5.1. Общие сведения о расчетах в запросах [6]

 

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

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

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

Три области поиска элементов для записи выражения размещены в нижней части окна. Левая область содержит список источников данных (таблиц и др.) и средств для записи выражений (функции и др.); средняя область – элементы, которые входят в выбранный объект в левом окне; правая область предназначена для формирования нужного объекта (элемента).

Поиск нужных элементов начинается с левой области. Списки объектов в левой, средней и правой областях имеют иерархическое подчинение.

 

Рис. 5.1. Окно Построитель выражений со сформированным запросом

 

Последовательность вставки элемента выражения в верхнее поле ввода:

1. В левой области выбрать необходимую папку и открыть ее для выбора нужного объекта (элемента).

2. Выбрать объект из той папки, где он находится.

3. Последовательно в средней и правой части областях выбрать нужный элемент и перенести его в поле ввода выражения (верхняя часть окна) кнопкой или дважды щелкнув элемент мышью.

4. В зоне кнопки операторов выбрать нужный оператор и щелкнуть мышью для переноса в создаваемое выражение.

5. После формирования нужного выражения нажать кнопку . Выражение появится в строке поле бланка запросов. Каждое поле итоговой таблицы должно иметь имя. Access автоматически присвоит имя Выражение1, которое отделяется от выражения двоеточием. В законченном виде в бланке-запросе можно заменить имя поля итога на «говорящее», например «Сумма» или «Наценка».

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

Для создания итогового запроса надо, находясь в окне Конструктора запросов, подать команду Вид„Групповые операции или нажать кнопку Групповые операции на панели инструментов. После этого в Бланке запросов (внизу окна конструктора запросов) появится новая строка под названием Групповые операции. В ней в соответствующей графе указываем тип выполняемого вычисления, раскрывая список типов операций, приведенный в таблице 5.1.

Для итоговых запросов можно задавать условия отбора, вводя их в соответствующую строку Бланка запроса в окне Конструктора запросов.

 

Таблица 5.1

Типы операций [8, с. 128]

Значение	Выполняемые операции
Sum	Сложение
Avg	Среднее значение
Min	Минимальное значение
Max	Максимальное значение
Count	Количество записей
StDev	Стандартное отклонение
Var	Дисперсия
First	Значение в первой записи
Last	Значение в последней записи

 

Для представления информации в компактном виде применяют перекрестные запросы. В перекрестном запросе отражаются результаты расчетов (средние величины, суммы и т.д.) по значению одного поля таблицы. Результаты вычислений группируются по двум наборам данных: один находится в левом столбце таблицы, другой – в верхней строке. Таким образом, данные сгруппированы как по горизонтали, так и по вертикали и размещены компактно в виде двумерной таблицы. Сравните данные, представленные в таблице 5.2, и те же данные, отображенные в перекрестном запросе (табл. 5.3).

Для названия заголовков колонок можно использовать значения нескольких полей или выражений, например, всего.

Для создания перекрестного запроса можно использовать как Мастер по созданию перекрестного запроса, следуя его указаниям, так и Конструктор запроса.

 

Таблица 5.2

Сведения о стипендиатах

Группа	Стипендия	Количество студентов
Э-621	Повышенная
Э-621	Средняя
Э-631	Повышенная
Э-631	Средняя
Э-631	Минимальная

 

Таблица 5.3

Перекрестная таблица

Группа	Повышенная	Средняя	Минимальная
Э-621
Э-631

Однако в Access 2003 есть более мощный аналог перекрестных запросов – сводные таблицы. Применение сводных таблиц удобно, когда в базе данных есть таблицы с большим количеством записей, которые невозможно проанализировать без специальных средств.

Сводная таблица строится на основе простого запроса на выборку, в котором объединяются поля из нескольких таблиц.

 

5.2. Задание для самостоятельной работы

 

1. Изучить:

- назначение и функции построителя выражений;

- понятие и алгоритм создания сводного запроса;

- выбор типа выполняемого вычисления в итоговых запросах;

- понятие и алгоритм создания перекрестного запроса;

- задание критериев выбора;

- сохранение, открытие и модифицирование запросов.

2. Создать запросы к базе данных по индивидуальному заданию:

- запрос на выборку, содержащий вычисляемое поле;

- итоговый запрос;

- итоговый запрос с условием отбора;

- перекрестный запрос;

- сводную таблицу.

 

Варианты заданий

Вариант 1

1) Вывести список «залежавшихся» товаров со скидкой в цене 20 % (например, товары поставленные раньше некоторой даты).

2) Подсчитать среднюю цену товаров каждого типа.

3) Подсчитать сумму поставок по поставщикам.

4) Подсчитать, сколько каких типов товаров поставляет каждый поставщик.

5) Подсчитать, на какую сумму были поставлены различные типы товаров каждым поставщиком в течение одной недели.

Вариант 2

1) Сделать скидку 15 % на товары, количество которых на складе не превышает некоторого числа (например, 1).

2) Подсчитать сумму продаж за день.

3) Подсчитать среднюю выручку за каждый день работы в течение некоторого промежутка времени (например, одной недели).

4) Подсчитать сколько каких типов товаров имеется в продаже.

5) Подсчитать, какой продавец на какую сумму продал за товаров каждого типа за некоторый промежуток времени (день).

Вариант 3

1) Подсчитать комиссионные фирмы в размере 6 % от цены на квартиру.

2) Подсчитать количество трехкомнатных квартир, выставленных на продажу.

3) Подсчитать среднюю цену двухкомнатной квартиры в одном районе.

4) Подсчитать, сколько квартир (1, 2, 3 комнатных) выставлено на продажу в каждом районе.

5) Подсчитать среднюю стоимость квартир, находящихся на первом этаже (1, 2, 3-комнатных), выставленных на продажу в каждом районе.

Вариант 4

1) Подсчитать комиссионные агентства в размере 5 % от стоимости заказа.

2) Подсчитать количество заказов по каждому типу рекламных объявлений.

3) Отобрать рекламодателей, разместивших свои заказы более, чем на некоторую сумму.

4) Подсчитать, на какую сумму в каждом издании была размещена реклама каждого типа.

5) Подсчитать количество реклам каждого типа, размещенных в каждом издании за некоторый промежуток времени.

Вариант 5

1) Подсчитать комиссионные агентства в размере 5 % от оклада вакансии.

2) Подсчитать количество вакансии по каждому работодателю.

3) Подсчитать средний желаемый претендентами оклад на определенную должность.

4) Подсчитать, сколько претендентов на вакансию у каждого работодателя.

5) Подсчитать сотрудников, дела чьих претендентов были отобраны работодателями за определенный промежуток времени.

Вариант 6

1) Сделать скидку 10 % на один вид услуги за время рекламной акции.

2) Подсчитать среднюю занятость каждого мастера.

3) Подсчитать выручку мастера за день.

4) Подсчитать число обращений клиентов по каждому виду услуг.

5) Подсчитать выручку мастера по каждому виду услуг за один день.

Вариант 7

1) Подсчитать 30 % взнос на услуги, оказываемые в кредит.

2) Подсчитать сумму заказа клиента.

3) Подсчитать среднюю выручку фирмы за день.

4) Подсчитать число обращений каждого клиента по каждому виду услуг.

5) Подсчитать, на какую сумму были проведены работы каждым мастером у каждого клиента за определенный промежуток времени.

Вариант 8

1) Рассчитать премию сотрудникам в размере 75 % от оклада.

2) Средний оклад сотрудников по каждому отделу.

3) Подсчитать количество сотрудников по каждому отделу, прошедших повышение квалификации за определенный промежуток времени (например, за год).

4) Подсчитать средний стаж работы сотрудников по подразделениям и отделам.

5) Подсчитать, сколько сотрудников из каждого отдела проходило повышение квалификации каждого вида за некоторый период времени, например последние 5 лет.

Вариант 9

1) Подсчитать комиссионные управляющей компании в размере 3 % от договора аренды.

2) Подсчитать за каждое число выручку от договоров аренды.

3) Подсчитать для каждого арендатора размер его арендуемой площади.

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

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

Вариант 10

1) Сделать скидку 5 % на товары, отгруженная партия которых превышает некоторое число (например, 1000).

2) Подсчитать сумму оплаты по каждой реализации.

3) Подсчитать среднюю реализацию за каждый день работы в течение некоторого промежутка времени (например, одной недели).

4) Подсчитать оплату реализации каждого клиента по месяцам.

5) Подсчитать для каждого клиента реализацию товаров каждого вида за некоторый промежуток времени.

Вариант 11

1) Сделать наценку на материалы в размере 2 %, поставляемые одним поставщиком.

2) Подсчитать себестоимость выпуска одного изделия.

3) Подсчитать расход материалов при выпуске изделий за некоторый промежуток времени.

4) Подсчитать, сколько каких материалов присутствует в каждом изделии.

5) Подсчитать, какие материалы каких поставщиков были израсходованы при выпуске изделий за некоторый промежуток времени.

Вариант 12

1) Подсчитать для каждой партии допустимый процент брака.

2) Подсчитать среднее количество выбракованных изделий каждого вида.

3) Отобрать партии, в которых количество выбракованных изделий не превышает допустимый процент.

4) Подсчитать количество выбракованных изделий каждого вида за каждый день работы.

5) Для каждой партии выпускаемой продукции подсчитать за каждый день работы количество изделий, выбракованных по одному типу.

Вариант 13

1) Подсчитать сумму перевозки каждого груза.

2) Подсчитать по каждому клиенту общую сумму по перевозке грузов.

3) Подсчитать общий объем грузов, перевозимых одним сотрудником за некоторый промежуток времени.

4) Подсчитать для каждого сотрудника объем грузов, которые должны быть доставлены по определенным направлениям.

5) Подсчитать для клиента суммы грузовых перевозок за каждый день за определенный промежуток времени.

Вариант 14

1) Подсчитать возраст клиентов.

2) Подсчитать по каждому клиенту количество поданных анкет.

3) Подсчитать по каждому клиенту сумму вознаграждения за определенный промежуток времени.

4) Подсчитать среднее время сотрудничества каждого клиента по каждому вознаграждению.

5) Подсчитать за определенный промежуток времени количество поданных анкет каждым клиентов и их вознаграждение.

Вариант 15

1) Подсчитать возраст претендентов.

2) Подсчитать число претендентов, поступающих на каждую должность.

3) Подсчитать средний балл, полученный каждым претендентов при сдаче, вступительном отборе.

4) Подсчитать число мужчин и женщин, претендующих на каждую вакансию.

5) Проанализировать состав претендентов: стаж и средний возраст подавших заявление за определенный промежуток времени на определенную вакансию.

5.3. Пример выполнения задания

 

5.3.1.Вычисления в запросе

Снизим на 20 % цену на товары, по которым не будут возобновляться поставки. Чтобы создать такой запрос, нам потребуются данные из таблицы Товары учебной базы Борей.

На вкладке Запросы щелкните дважды на ярлычке Создание запроса в режиме конструктора и добавьте таблицу Товары в появившееся окно запроса (рис. 5.2).

 

 

Рис. 5.2. Создание запроса на выборку

 

Из таблицы Товары добавьте поля Марка, Цена, ПоставкиПрекращены и НаСкладе.

В строке Условия отбора определите критерии, по которым будут отбираться товары, для снижения цены. В нашем случае необходимо, чтобы отобрались все товары, которые имеются на складе и у которых установлен флажок в поле ПоставкиПрекращены. Для этого нужно ввести два условия отбора:

- в строку Условия отбора столбца ПоставкиПрекращены значение Да, так как для этого поля установлен тип Логический;

- в строку Условия отбора столбца НаСкладе значение >0.

Окно запроса будет выглядеть так, как показано на рисунке 5.2

Нажмите на кнопку Вид и просмотрите результат работы запроса (рис. 5.3).

Рис. 5.3. Результат работы запроса

Убедившись, что записи отобраны верно, нажмите кнопку Вид, чтобы переключиться в окно Конструктора запросов, и сохраните запрос под именем Уценка.

Справа от столбца НаСкладе нам необходимо расположить новую цену товара. Для этого перейдите в строку Поле первого пустого столбца в Бланке запроса и нажмите комбинацию клавиш Shift+F2. Появится диалоговое окно Область ввода (рис. 5.4), в котором можно настраивать шрифт, нажав кнопку Шрифт.

 

 

Рис. 5.4. Диалоговое окно области ввода

 

Введите в область ввода строку НоваяЦена:Цена*0,8.

Первая часть до двоеточия будет именем нового поля. Вторая часть – выражение, значением которого является произведение содержимого поля Цена на коэффициент 0,8 (80 %). Квадратные скобки вокруг имени поля можно не вводить – Access автоматически добавит их позже.

Нажмите кнопку . Строчка будет вставлена в бланк запроса.

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

 

 

Рис. 5.5. Бланк запроса с вычисляемым полем

 

Переключитесь в режим таблицы, нажав кнопку Вид на панели инструментов. Получившаяся выборка показана на рисунке 5.6.

 

Рис. 5.6. Результат работы вычислений в запросе

 

Для изменения формата отображения информации в столбце, содержащем новую цену, переключитесь в режим конструктора и воспользуйтесь окном свойств поля НоваяЦена. Щелкните левой клавишей мыши по полю НоваяЦена и нажмите на панели инструментов кнопку Свойства.

В окне диалога Свойства поля щелкните в строке Формат поля, выберите формат Денежный, в строке Число десятичных знаков установите 2 (рис. 5.7) и закройте окно свойств.

 

 

Рис. 5.7. Задание свойств вычисляемого поля

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

 

Рис. 5.8. Вычисления в запросе

 

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

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

На вкладке Запросы щелкните дважды на ярлычке Создание запроса в режиме конструктора и добавьте таблицы Заказы и Заказано в появившееся окно запроса (рис. 5.9).

 

Рис. 5.9. Задание вычисляемого поля

 

Добавьте в бланк запроса из таблицы Заказы поле ДатаРазмещения, щелкнув на нем дважды мышью. В строке Сортировка установите По возрастанию. В строке Условие отбора введите: Between 01.01.97 And 31.01.97.

Для подсчета выручки необходимо ввести вычисляемое поле, в котором будет вычислено произведение значений, содержащихся в полях Количество и Цена таблицы Заказано. Вычисляемое поле можно ввести сразу в бланке запроса. Для этого в строку Поле первого пустого столбца в бланке запроса справа от столбца ДатаРазмещения введите:

Сумма:Цена*Количество

Отметьте флажком вывод на экран вычисляемого поля, а также установите Свойства: денежный формат и 2 десятичных знака после запятой.

Если имена полей введены правильно, Access преобразует автоматически введенное выражение к следующему виду:

Сумма: [Цена]*[Количество]

Окно запроса будет выглядеть, как показано на рисунке 5.9.

Переключитесь в режим таблицы . Ваша выборка будет выглядеть, как изображено на рисунке 5.10.

 

 

Рис. 5.10. Результат запроса на выборку с вычисляемым полем

 

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

В бланке запроса под строкой Имя таблицы появится новая строка – Группировка. Щелкните в ячейке Группировка столбца Сумма, а затем на появившейся стрелке вниз.

Выберите в раскрывшемся списке функцию Sum. Окно конструктора запроса должно выглядеть так, как показано на рисунке 5.11.

 

 

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

 

Переключитесь в режим таблицы . Ваша выборка будет выглядеть так, как показано на рисунке 5.12. Закройте запрос и сохраните его.

 

 

Рис. 5.12. Результат выполнения запроса с групповыми операциями

 

5.3.3. Сводные таблицы ^[7]

Предположим, вы хотите узнать, на какую сумму каждый сотрудник фирмы за каждый месяц в течение первого квартала 1997 г., оформил заказы.

В нашем примере понадобятся фамилии сотрудников из таблицы Сотрудники, ДатаРазмещения из таблицы Заказы и вычисляемое поле Сумма, посчитанное как произведение полей Количество и Цена из таблицы Заказано.

В окне базы данных выберите и нажмите кнопку Создать в режиме конструктора.

 

Рис. 5.13. Подготовка таблицы

 

В окне диалога Добавление таблицы на вкладке Таблицы дважды щелкните Сотрудники, Заказы и Заказано и нажмите кнопку .

Добавьте поля в строку Поле в бланке запроса:

- дважды щелкните на поле Фамилия в таблице Сотрудники;

- дважды щелкните на поле ДатаРазмещения в таблице Заказы;

- в пустой ячейке бланка запроса справа от столбца ДатаРазмещения введите вычисляемое поле Сумма:Цена*Количество

Щелкните на стрелке на кнопке Вид и выберите в раскрывающемся списке элемент Сводная таблица. На экране появится окно с макетом сводной таблицы с четырьмя областями:

- самая верхняя горизонтальная полоса называется областью фильтра, здесь будут находиться поля, по которым вы сможете фильтровать данные;

- вторая горизонтальная полоса – прообраз заголовков столбцов таблицы;

- вертикальная полоса слева будет содержать заголовки строк;

- средняя большая область – область данных, будет содержать собственно данные.

Справа вы должны увидеть отдельное окно – Список полей сводной таблицы с перечнем полей запроса. Для отображения окна Список полей сводной таблицы нажмите на панели инструментов кнопку Поля.

 

Рис. 5.14. Макет сводной таблицы

 

В верхней части диалогового окна Access появилась специальная панель инструментов Сводные таблицы с кнопками выполнения действий со сводной таблицей, с которыми мы познакомимся по мере освоения этих действий.

Сначала определим строки таблицы. Найдите в списке полей запроса в окне Список полей сводной таблицы поле ФИО и перетащите его мышью в область заголовков строк – вертикальную область у левого края формы. В этой области появится столбец ФИО со списком всех сотрудников фирмы.

 

 

Рис. 5.15. Определение строк сводной таблицы

 

Определим столбцы таблицы. Найдите в списке полей запроса в окне Список полей сводной таблицы поле Дата размещения по месяцам и перетащите его в область заголовков столбцов. Появится три столбца, которые будут содержать сгруппированные по годам данные и один столбец итоговый по строке (рис. 5.16).

 

Рис. 5.16. Определение строк сводной таблицы

 

Строки и столбцы определены, нужно установить, что будет отображаться в самой таблице. Найдите в списке полей запроса в окне Список полей сводной таблицы поле Сумма и переместите в центральную область (область данных).

 

 

Рис. 5.17. Сводная таблица для запроса продажи

 

В этой таблице не хватает итоговых значений. Щелкните на заголовке любого столбца с надписью Сумма. Все столбцы, кроме последнего, итогового, окажутся выделенными. Нажмите на панели инструментов кнопку Автовычисления и выберите в раскрывающемся списке функцию Сумма.

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

 

Рис. 5.18. Сводная таблица – итоговые данные