Создание запросов с вычислением полей.

Запрос 3. Вычислить стоимость услуги в у.е., курс которой вводится с клавиатуры.

Пример записи условия отбора в случае третьего запроса с вычисляемыми полями по условию задания приведен на рис. 11.

 

Рис.11. Бланк запроса для Запрос3_1_вычисляемые_поля

Выражение, введенное в строку Поле при помощи Построителя выражений для вычисляемого поля, следующее:

Стоимость услуги в $: [Услуги]![Стоимость] / [Введите курс $]

Замечание. Имена вычисляемых полей задают перед самим выражением. Если ввести только формулу, имя полю присвоится автоматически Выражение1, Выражение2, и т.д.).

В результате получится следующая выборка:

Запрос3_1_вычисляемые_поля
Наименование услуги	Стоимость	Стоимость услуги в $
Ремонт телевизора		7.00
Установка антенны		3.74
Вставка ДМ-блока		1.40
Ремонт видеомагнитофона		9.34
Подключение телефона		3.27
Подключение домофона		6.07
Подключение компьютера		7.94
Диагностика компьютера		5.61
Монтаж ЛВС		8.41
Подключение модема		1.87

Запрос 4. Определить дату выполнения заказа, а также день недели выполнения заказа

Пример записи условия отбора в случае четвертого запроса с вычисляемыми полями по условию задания приведен на рис. 12.

Рис.12. Бланк запроса для Запрос3_2_вычисляемые_поля

Выражения, построенные в построителе выражений для вычисляемых полей следующие:

Дата выполнения заказа:

[Заказы]![Дата принятия заказа] + [Услуги]![Количество дней]

День недели выполнения заказа: Format([Заказы]![Дата принятия заказа] + [Услуги]![Количество дней];”dddd”)

В результате получится следующая выборка:

Запрос3_2_вычисляемые_поля
Номер заказа	Наименование услуги	Дата выполнения заказа	День недели выполнения заказа
	Ремонт телевизора	19.01.2005	среда
	Установка антенны	21.03.2005	понедельник
	Вставка ДМ-блока	22.04.2005	пятница
	Ремонт видеомагнитофона	28.04.2005	четверг
	Подключение телефона	21.03.2005	понедельник
	Подключение телефона	27.05.2005	пятница
	Подключение домофона	26.03.2005	суббота
	Подключение компьютера	17.01.2005	понедельник
	Подключение компьютера	14.05.2005	суббота
	Диагностика компьютера	20.01.2005	четверг
	Диагностика компьютера	28.03.2005	понедельник
	Диагностика компьютера	17.05.2005	вторник
	Подключение модема	26.05.2005	четверг
	Подключение модема	26.05.2005	четверг

Встроенные функции, применяемые в запросах с вычислением полей:

Категория функций Управление:

Iif(логич_выражение; ист_выражение; лож_выражение) – в зависимости от проверяемого логического выражения возвращает либо истина_выражение, ибо ложь_выражение (аналогична функции ЕСЛИ в Excel).

Категория функций Дата/Время:

Now(), Date() – возвращает текущую дату.

DateAdd(“интервал”; число; дата) – в результате добавляет к дате число в размере указанного интервала. Возвращает данные типа Дата/время.

DateDiff(“интервал”; дата1; дата2 [; первыйденьнед [; перваянедгода]]) – в результате вычисляет разницу между двумя указанными датами и возвращает число в размере указанного временного интервала. Во избежание отрицательного ответа дата1 должна быть ранее дата2.

Если не задан первыйденьнед – по умолчанию считается 1, т.е. воскресенье. Иначе – 2 понедельник, 3 вторник и т.д.

Если не задана перваянедгода – по умолчанию считается та, на которую попадает 1 января (иначе должно быть задано 2 – если первой неделей считается неделя, содержащая по меньшей мере 4 дня нового года, 3 – первая полная неделя года)

Интервал должен быть задан в кавычках и может иметь один из видов:

yyyy – количество лет

q – количество кварталов

m – количество месяцев

y – количество дней в году

d – количество дней

w – количество дней недели

ww – количество недель

h – количество часов

n – количество минут

s – количество секунд

Категория функций Текстовые:

Format(выражение;”форматная_строка” [; первыйденьнед [; перваянедгода]]) – преобразовывает выражение в строку символов по правилу форматной строки.

Если форматируемое выражение имеет тип Дата/время, форматная строка должна быть задана в кавычках и может иметь один из видов:

d – номер дня месяца (если до 9 числа, то одна цифра)

dd – номер дня месяца (если до 9 числа, то две цифры, например, 01)

ddd – название дня недели сокращенным словом

dddd – название дня недели полным словом

w – номер дня недели

ww – номер недели

m – номер месяца (если до 9 месяца, то одна цифра)

mm – номер месяца (если до 9 месяца, то две цифры)

mmm – название месяца сокращенным словом

mmmm – название месяца полным словом

y – номер дня в году

yy – две последних цифры года

yyyy – четыре цифры года

 

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

Для создания итоговых запросов в бланк запроса надо включить только поля:

· по которым формируются группы

· по которым подводятся итоги

· которые участвуют в условиях отбора.

Затем в окне Конструктора запроса необходимо выполнить команду Вид à Групповые операции или воспользоваться пиктограммой . В появившейся строке Группировка надо выбрать:

Режим Группировка – для тех полей, по значениям которых требуется сгруппировать записи (как правило перед такими полями в словесной формулировке запроса говорят «для каждого»)

Одну из групповых функций для тех полей, для которых надо подвести итоги:

SUM – для вычисления суммы значений

COUNT – для подсчета количества непустых значений

MIN – для вывода минимального значения в группе

MAX – для вывода максимального значения в группе

Запрос 5. Сколько всего заказов сделал каждый клиент и на какую сумму

Пример записи условия отбора в случае итогового запроса приведен на рис. 13.

Рис.13. Бланк запроса для Запрос5_итоговый

В результате получится следующая выборка:

Запрос5_итоговый
Наименование клиента	Общее количество заказов	Суммарная стоимость услуг
ПКФ "Марс"
БрГТУ
ПКП "Сож"
ЗАО "Альбатрос"
ПКП "Мур-Мур"
ЦУМ
АПК "Западный Буг"
ООО "Санта"

Запрос 6. Сколько всего клиентов в базе данных

Включить в запрос одну таблицу Клиенты. Включить в бланк запроса одно поле – Код клиента. Добавить строку Групповые операции и выбрать в ней функцию COUNT. В результате выполнения запроса будет одно число – 10.

 

В задании необходимо создать. Перекрестный запрос:

Последовательность действий при создании перекрестного запроса без Мастера:

Ø находясь в окне БД на вкладке Запросы нажать кнопку Создать;

Ø в окне Новый запрос выбрать режим Конструктор;

Ø в появившемся диалоговом окне Добавление таблицы из списка следует выбрать имя таблицы-источника или запроса-источника, на основе которых будет проводиться выбор данных, и щелкнуть кнопку Добавить, после чего закрыть окно диалога Добавление таблицы кнопкой Закрыть;

Ø находясь в окне Конструктора из п.м. Запрос выбрать команду Перекрестный;

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

Ø для полей, значения которых будут представлены в запросе в качестве заголовков столбцов, выбрать в строке Перекрестная таблица опцию Заголовки столбцов и оставить в строке Групповая операция значение Группировка;

Ø для расчетных (итоговых) полей, значения которых будут находиться на пересечении строк и столбцов, выбрать в строке Перекрестная таблица опцию Значение, а в строке Групповая операция из списка выбирается необходимая статистическая функция.

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

Пример записи условия отбора в случае перекрестного запроса приведен на рис. 14.

Рис. 14. Бланк запроса для Запрос7_перекрестный

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

2. Для того, чтобы изменить подпись итогового столбца, необходимо, находясь в режиме Конструктора запроса, вызвать окно Свойства поля (щелкнуть кнопкой мыши в строке Поле бланка запроса для итогового столбца и вызвать команду Вид/Свойства) и ввести в строке Подпись необходимое текстовое выражение (в рассматриваемом примере, это – Средняя стоимость).

В результате получится следующая выборка:

Запрос7_перекрестный
Наименование клиента	15.01.05	19.03.05	23.03.05	21.04.05	12.05.05	25.05.05	Средняя стоимость
АПК "Западный Буг"
БрГТУ
ЗАО "Альбатрос"
ООО "Санта"
ПКП "Мур-Мур"
ПКП "Сож"
ПКФ "Марс"
ЦУМ