Вычисления на подмножестве записей

 

При обобщении данных, которые содержатся в полях таблиц, включенных в запрос, используются статистические функции (Таблица 2.2).

Статистические функции SQL можно задать в ячейке строки "Групповая операция" бланка запроса. Эта строка выводится, если нажать на панели инструментов кнопку "Групповые операции".AVG() Вычисляет среднее арифметичекое набора чисел, содержащихся в указанном поле запроса

Таблица 2.2.

Некоторые статистические функции SQL

AVG()	Вычисляет среднее арифметичекое набора чисел, содержащихся в указанном поле запроса
Count()	Вычисляет количество непустых записей, возвращаемых запросом
First()	Возвращает значение поля из первой записи результирующего набора
Last()	Возвращает значение из последней записи результирующего набора
Max()	Возвращает максимальное из набора значений, содержащихся в указанном поле.
Min()	Возвращает минимальное из набора значений, содержащихся в указанном поле
Sum()	Возвращает сумму набора значений, содержащихся в заданном поле

 

Задание 2.21.

Создайте запроса в котором используются статистические функции SQL для вывода данных о заказах, общего количества, общей, а также средней, наименьшей и наибольшей суммы, требуется добавить поле, которое бы содержало обобщенную информацию о каждом заказе.
1. Создайте новый запрос и добавьте в него таблицы "Заказы" и "Заказано".
2. Перетащите поле "КодЗаказа" таблицы "Заказы", а затем поле "ДатаРазмещения".
3. Введите в ячейке "Поле" третьего столбца бланка запроса выражение Итог: Sum([Количество]*[Цена]*(1-[Скидка])), которое вычисляет чистую сумму товаров, включенных в поставку. Для столбца "Итог" присвойте значение "Денежный" свойству поля "Формат поля".

4. Нажмите на панели инструментов кнопку "Групповые операции. В бланк запроса добавляется строка "Групповая операция", содержащая по умолчанию в каждой ячейке операцию "Группировка".

5. Выберите из списка статистических функций SQL в третьей ячейке строки "Групповая операция" элемент "Выражение".

6. Запустите запрос.

7. Закройте запрос, сохранив его под именем "Итоги по заказам"
Полученный запрос "Итоги по заказам" представляет собой обобщенные суммы по всем заказам и будет использоваться как источник данных для очередного запроса.

8. Создайте новый запрос и добавьте в него запрос "Итоги по заказам". Перетащите поле "Код заказа" на первый столбец, а затем четыре раза поле "Итог".
9. Установите групповые операции.

10. В столбце "Код заказа" для строки "Групповые операции" выберите функцию Count().

11. Для четырех столбцов "Итог" установите соответственно функции Sum(), Avg(), Min(), Max().

12. Запустите запрос. Так как условие отбора не было указано, то выведенные данные относятся ко всей таблице.

 

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

Перекрестные запросы - это запросы, в которых вычисляются статистические данные и определяется их внешний вид. В предложениях ACCESS SQL для обозначения перекрестного запроса используется ключевое слово TRANSFORM. С помощью рассматриваемого типа запросов можно:

указать поле, которое является заголовком строки, используя операцию "Группировка".

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

Указать место размещения данных в таблице.

 

Задание 2.22.

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

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

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

3. Выберите команду "Запрос, Перекрестный". В бланк запроса будет добавлена строка "Перекрестная таблица".

4. Выберите в списке ячейки "Перекрестная таблица" столбца "КодТовара" значение "Заголовки строк". Выполните тоже самое для столбца "Марка".
5. Для вывода в перекрестный запрос данных за 1995 год введите в ячейке "Групповая операция" значение "Условие отбора" для столбца "ДатаРазмещения и введите само условие отбора: <= #31.12.95# AND >=#01.01.95# (набирать без пробелов).

6. В первом свободном столбце введите следующее выражение: Объем продаж: Sum([Заказано].[Количество] * [Заказано].[Цена]). Выберите в ячейке "групповая операция того же столбца значение "Выражение", а затем в ячейке "Перекрестная таблица" значение "Значение". В столбце "Объем продаж" вычисляется общий объем заказов на каждый товар, который представляется в ячейке перекрестной таблицы.

7. Установите курсор в ячейку "Поле" следующего (пустого) столбца и введите выражение Format([ДатаРазмещения],'mmm'). Выберите из списка в ячейке "Перекрестная таблица" значение "Заголовки столбцов".
8. Запустите запрос.

В представленном запросе гораздо удобнее (проще упорядочить) было бы использовать фиксированные заголовки столбцов. Это можно сделать в окне "Свойств запроса", в котором для перекрестных запросов отображается атрибут "Заголовки столбцов". Введите следующую строку для получения запроса с правильной последовательностью месяцев.

"Янв";"Фев";"Мар";"Апр";"Май";"Июн";"Июл";"Авг";"Сен";"Окт";"Ноя";"Дек".
При построении перекрестного запроса, была использована встроенная функция VBA "Format", которая позволяет отформатировать значение первого аргумента в соответствии со вторым аргументом шаблона. ACCESS VBA содержит порядка 200 встроенных функций, с некоторыми из которых Вы уже познакомились. Изучение этих функций не входит в предмет рассмотрения данной лабораторной работы, хотя и немаловажно для более глубокого изучения возможностей ACCESS. Поэтому в тех случаях, когда требуется воспользоваться незнакомой функцией или возникают проблемы с пониманием работы той или иной функции пользуйтесь справочной системой ACCESS, содержащей подробное описание подкрепленное множеством примеров.

Задание 2.23.

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

Для этого:

1.Сохраните предыдущий созданный запрос под именем "Ежеквартальная выручка от продаж товаров по типам". Откройте его в режиме «конструктор».

2.Добавьте в запрос таблицу "Типы".

3. Вместо "Кода товара" и "Марки товара" используйте "КодТипа" и "Категорию" из таблицы "Типы" в качестве заголовков строк.
4.В качестве заголовков столбцов используйте выражение Format([ДатаРазмещения],"q"), которое определяет заголовки столбцов '1','2',....
5.Удалите фиксированные заголовки столбцов в бланке свойств запроса.
6. Запустите запрос, чтобы просмотреть результат.

7. Перейдите в режиме SQL для исследования SELECT предложения предложенного запроса

TRANSFORM Sum([Заказано].[Цена]*[Заказано].[Количество]) AS Выражение1

SELECT Товары.КодТипа, Типы.Категория

FROM Типы INNER JOIN (Товары INNER JOIN (Заказы INNER JOIN Заказано ON Заказы.КодЗаказа = Заказано.КодЗаказа) ON Товары.КодТовара = Заказано.КодТовара) ON Типы.КодТипа = Товары.КодТипа

WHERE (((Заказы.ДатаРазмещения)>=#1/1/94# And

(Заказы.ДатаРазмещения)<=#12/31/94#))

GROUP BY Товары.КодТипа, Типы.Категория

PIVOT Format([ДатаРазмещения],'q');

 

Инструкция содержит операцию TRANSFORM, в которой определяются данные, содержащиеся в таблице. В операции PIVOT задаются заголовки столбцов. Эти слова не являются зарезервированными словами ANSI SQL.

Перекрестные запросы очень часто используются для создания других обобщающих запросов и для построения графиков.

 

2.2. Контрольные вопросы

1.Сортировка данных в таблице
2. Поиск записей в базе данных
3. Использование фильтра по выделенному фрагменту
4. Использование обычного фильтра
5. Использование расширенного фильтра и расширенной сортировки
6. Бланк для создания запроса мастера запросов.
7. Типы соединений таблиц для создания многотабличных запросов
8. Создание внутреннего соединения по одному полю
9. Создание запросов на выборку данных из таблиц с косвенными связями
10. Создание внутреннего соединения по нескольким полям
11. Создание внешнего соединения
12. Создание рекурсивного соединения
13. Создание соединения по отношению
14. Использование функций агрегирования
15. Создание перекрестных запросов
16. SELECT предложение ACCESS SQL.

17. Сохранение фильтров.

18. Запросы с параметрами.

19. Сохранение запросов.

20. Задачи решаемые с помощью запросов.

 

 

СОЗДАНИЕ ФОРМ

 

Цель работы:

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