1. Откроите в режиме конструктора запрос "Ежемесячная статистика по заказам".

2. Установите курсор в ячейку "Поле" столбца "Месяц", нажмите клави­шу <F2> для выделения содержимого ячейки, а затем комбинацию клавиш <Ctrl> + <C>. чтобы поместить выделенное выражение в Буфер обмена.

3. Установите курсор в ячейку "Поле" столбца "ДатаРазмещения", на­жмите клавишу <F2> для выделения содержимого ячейки, а затем комбинацию клавиш <Ctrl>+<V>, чтобы заменить его на выражение, содержащееся в Буфере обмена.

4. Установите курсор в ячейку "Условие отбора" столбца "ДатаРазмещения" и замените выражение <=#31.12.94#And>=#01.01.94# строкой [Введите месяц и год в формате ММ-ГГ:] (рис. 50).

5. Нажмите на панели инструментов кнопку "Запуск". В появляющемся диалоговом окне "Введите значение параметра" выводится надпись, которая задана на шаге 4 в качестве критерия отбора.

6. Введите строку 03-94, чтобы вывести данные для марта 1995 года (рис. 10.51).

7. Нажмите кнопку ок для вывода результирующего множества запроса(рис.52).

7.2 Определение типа данных параметра

Все параметры по умолчанию имеют текстовый тип. Если условие отбора относится к данным с типом "Дата/Время" (Data/Time) или "Числовой" (Number), то необходимо определить тип данных для каждого такого аргу­мента, используя диалоговое окно "Параметры запроса" (Query Parameters).

Для задания типа данных параметра:

1. С помощью мыши выделите строку Введите месяц и год в форма­те мм-гг: в ячейке "Условие отбора" столбца "ДатаРазмещения" (не включайте квадратные скобки) и скопируйте текст в Буфер обмена, нажав комбинацию клавиш <Ctrl>+<0.

2. Выберите команду "Запрос, Параметры" (Query, Parameters) для вывода диалогового окна "Параметры запроса".

3. Вставьте текст приглашения в столбец "Параметр" (Parameter), устано­вив в него курсор и нажав комбинацию клавиш <Ctrl>+<V>. Строки в столбце "Параметр" и в ячейке "Условие отбора" должны совпадать в точности, что обеспечивается использованием Буфера обме­на. Не указывайте в столбце "Параметр" квадратные скобки!

4. Нажмите клавишу <Таb> для перемещения к столбцу "Тип данных" (Data Type) и выберите из списка элемент "Текстовый" (Text) (рис. 53). Нажмите

кнопку ОК для закрытия диалогового окна.

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

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

Перекрестные запросы — это запросы, в которых вычисляются статисти­ческие данные и определяется их внешний вид. Информация выводится в привычной форме: в виде таблицы. В предложениях Assess SQL для обо­значения перекрестного запроса используется ключевое слово transform . С по­мощью рассматриваемого типа запросов можно:

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

• Задать поле или поля, которые являются заголовками столбцов, а

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

• Указать место размещения данных в таблице. Перекрестные запросы обладают следующими достоинствами:

• Возможностью вывода значительного объема данных в компактном и

привычном виде.

• Итоговые данные представляется в формате, который идеально под­ходит для автоматического создания графиков и диаграмм с по­мощью Мастера диаграмм Access.

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

При использовании перекрестных запросов имеется только одно ограни­чение: нельзя сортировать таблицу результатов по значениям, содержа­щимся в столбцах. Таким образом, например, невозможно создать запрос. в котором товары упорядочены в строках по объему продаж: в подавляю­щем большинстве случаев одновременное упорядочивание данных в столбцах по всем строкам невозможно. Однако можно задать сортировку по возрастанию или по убыванию либо отсутствие упорядочивания для заголовков строк. Мастер перекрестного запроса помогает в создании перекрестных запро­сов. Однако он имеет одно ограничение: мастер создает перекрестный за­прос только на основе одной таблицы или одного запроса. Для боль­шинства баз данных потребуется использовать по крайней мере две табли­цы. В этом случае для применения Мастера перекрестного запроса необ­ходимо построить запрос, включающий все требуемые таблицы. Таким об­разом, в следующих разделах описывается создание перекрестного запроса без помощи мастера.

8.1 Создание перекрестного запроса "ежемесячная выручка от продаж"

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

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

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

3. Выберите команду "Запрос, Перекрестный" (Query, Crosstab). Заголо­вок окна запроса Запрос1: на выборку (Select Query: Query1) изменится на Запрос1: перекрестный запрос (Crosstab Query:

Query1). Кроме того, в бланк запроса будет добавлена строка "Перекрестная таблица" (Crosstab).

4. Выберите в списке ячейки "Перекрестная таблица" столбца "КодТовара" значение "Заголовки строк" (Row Heading). Выполните то же самое для столбца "Марка". Эти столбцы являются требуемыми за­головками строк перекрестной таблицы.

5. Выберите в списке ячейки "Групповая операция" столбца "КодТовара" значение "Условие". В ячейке "Условие отбора" этого столбца введите выражение <=#31.12 . 94#And>=#01. 01. 94# для вывода в перекрест­ной таблице данных за 1994 год.

6. Установите курсор в ячейку "Поле" следующего (пустого) столбца и введите следующее выражение:

Объем продаж: Sum([Заказано].[Количество]* [Заказано].[Цена])

Выберите в ячейке "Групповая операция" того же столбца значение "Выражение", а затем в ячейке "Перекрестная таблица" значение "Значение" (Value). В столбце "Объем продаж" вычисляется общий объем заказов на каждый товар, который подставляется в ячейки пе­рекрестной таблицы. Ссылка на таблицу "Заказано" обязательна, иначе будет выдано сообщение об ошибке "Неоднозначная ссылка на поле" (Ambiguous field reference).

7. Установите курсор в ячейку "Поле" следующего (пустого) столбца и введитe выражение Format([ДатаРазмещения]; "mmm"). Access добавляет имя поля "Выражение1" (Expr1), применяемое по умолчанию. Не меняйте его, т. к. функция Format () создает при запуске запроса имена столбцов, состоящие из трех первых букв названия месяца (формат "mmm"). Выберите из списка в ячейке "Перекрестная таблица" значение "Заголовки столбцов" (Column Heading) (рис. 54).

8. Нажмите кнопку "Запуск". Через несколько секунд появляется резуль­тирующее множество (рис. 55).

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

8.2 Использование фиксированных заголовков столбцов в перекрестных запросах

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

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

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

3. Нажмите кнопку "Запуск". Теперь заголовки столбцов перекрестной таблицы соответствуют календарной последовательности (рис. 57). Если результирующее множество выглядит не так, как на рисунке, проверьте правильность фиксированных заголовков столбцов, задан­ных в поле свойства "Заголовки столбцов". Если заголовок столбца введен неправильно, то столбец отсутствует в перекрестной таблице.

4. Выберите команду "Файл, Сохранить как" и сохраните запрос под именем "Ежемесячная выручка от продаж".Чтобы напечатать перекрестную таблицу, нажмите кнопку "Печать" (Print), а для предварительного просмотра данных перед печатью щелкните по кнопке "Предварительный просмотр" (Print Preview).

8.3 Понижение уровня детализации в перекрестных запросах

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

Для создания перекрестного запроса, который выводит ежеквартальную выручку от продаж по типам товаров (а не значению поля "КодЗаказа"):

1. Выберите команду "Файл, Сохранить как" и сохраните запрос под именем "Ежеквартальная выручка от продаж товаров по типам".

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

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

4. Перетащите поля "КодТипа" и "Категория" таблицы "Типы" на столбец "КолТовара". В ячейках "Перекрестная таблица" обоих столбцов выбе­рите значение "Заголовки строк".

5. Удалите столбцы "КодТовара" и "Марка", щелкнув по области выделе­ния столбца, а затем нажав клавишу <Delete> (рис. 58).

6. Введите в столбец "Выражение!" выражение Format ([ДатаРазмацания] ;

"""Квартал ""q"), которое определяет заголовки столбцов "Квартал 1", ..., "Квартал 4". Несколько кавычек требуется для указания того, что слово Квартал и пробел рассматриваются как строка, а q — как формат.

7. Откройте бланк свойств и очистите содержимое свойства "Заголовки столбцов", а затем закройте бланк. Если не удалить фиксированные заголовки, то в перекрестной таблице не будет данных. Чтобы уско­рить работу запроса, можно задать в поле свойства "Заголовки столб­цов" четыре фиксированных названия: "Квартал 1", ..., "Квартал 4".

8. Нажмите кнопку "Запуск" (рис. 59).

9. Выберите команду "Вид, Режим SQL" для вывода инструкции SQL (рис. 60). Инструкция содержит операцию transform, в которой определяются данные, содержащиеся в таблице. В операции pivot за­даются заголовки столбцов. После ключевого слова in указываются, если есть, фиксированные названия столбцов. Однако слова transform и pivot не являются зарезервированными словами ANSI SQL. Более того, в Access и ANSI SQL ключевое слово in интерпрети­руется по-разному.

10. Сохраните запрос, выбрав команду "Файл, Сохранить" (File, Save). За­прос "Ежеквартальная выручка от продаж товаров по типам" использу­ется ниже при рассмотрении многих вопросов.

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