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

Общие сведения о расчетах в запросах

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Таблица 17

Типы операций

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

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

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

Таблица 18

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

Таблица 19

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рисунок 104 – Результат работы запроса

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

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

Рисунок 105 – Диалоговое окно области ввода

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

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

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

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

Рисунок 106 – Бланк запроса с вычисляемым полем

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

Рисунок 107 – Результат работы вычислений в запросе

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

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

Рисунок 108 – Задание свойств вычисляемого поля

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

Рисунок 109 – Вычисления в запросе

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

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

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

Рисунок 110 – Задание вычисляемого поля

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Сводные таблицы

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

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

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

Рисунок 114 – Подготовка таблицы

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

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

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

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

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

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

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

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

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

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

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

Рисунок 115 – Макет сводной таблицы

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

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

Рисунок 116 – Определение строк сводной таблицы

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

Рисунок 117 – Определение строк сводной таблицы

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

Рисунок 118 – Сводная таблица для запроса продажи

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

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

Рисунок 119 – Сводная таблица – итоговые данные