СОЕДИНЕНИЕ ТАБЛИЦ ДЛЯ СОЗДАНИЯ МНОГОТАБЛИЧНОГО ЗАПРОСА
В режиме запроса по образцу выводятся четыре типа соединений:
· Внутреннее соединение (или эквисоединение) обычно используется при создании запросов на выборку.
Access автоматически создает соединения между таблицами, если в них имеются поля с одинаковыми именами, одно из которых является ключевым, либо если соединение было задано в окне связей.
· Внешнее соединение используется для создания новой таблицы, которая содержит записи, исключая повторяющиеся, связанные поля которых совпадают. Внешнее соединение позволяет вывести данные одной из таблиц независимо от того, имеются ли соответствующие записи в другой таблице.
· Рекурсивное соединение связывает данные в одной таблице. Создание этого типа соединения выполняется путем добавления в запрос копии таблицы (Access назначает псевдоним для копии) и связывания полей идентичных таблиц.
· Соединение по отношению (или тэта-соединение) связывает данные некоторым отношением (это может быть любое отношение, исключая равенство). Соединение по отношению выполняется с помощью предложения where, а не с помощью ключевого слова SQL join. Кроме того, в окне конструктора запросов соединения по отношению не выводятся, также как они не отображаются в окне схемы данных.
1.1 Создание внутреннего соединения по одному полю
Соединения, в которых участвует по одному полю из двух разных таблиц, называются внутренними соединениями по одному полю. В большинстве реляционных баз данных такие соединения реализуются на основе отношений "один-ко-многим".
Все соединения в базе данных "Борей", обозначенные линиями, которые соединяют имена полей (см. рис. 1), являются внутренними соединениями по одному полю, реализованные на основе отношений "один-ко-многим".
Чтобы построить запрос, в котором имеется внутреннее соединение одной таблицы с другой отношением "один-ко-многим", и связать имена клиентов с размещенными ими заказами:
- Если база данных "Борей" открыта, закройте все окна, исключая окно "База данных", дважды щелкнув по кнопке "Закрыть" каждого открытого окна. В противном случае загрузите базу.
- Раскройте вкладку "Запросы" (Queries) окна "База данных", а затем нажмите кнопку "Создать" (New) для создания нового запроса. В появившемся диалоговом окне "Новый запрос" (New Query) выделите элемент "Конструктор" (New Query) и нажмите кнопку ОК. Access отображает диалоговое окно "Добавление таблицы" поверх пустого окна запроса.
- Выберите таблицу "Клиенты" и нажмите кнопку "Добавить" либо дважды щелкните по имени таблицы, чтобы добавить ее в запрос. Access отображает список полей таблицы "Клиенты".
- Дважды щелкните по имени таблицы "Заказы", а затем нажмите кнопку "Закрыть". Access добавляет список полей таблицы "Заказы", а также линию, обозначающую соединение полей "КодКлиента" двух таблиц (см. рис. 2). Соединение создается автоматически, т. к. поле "КодКлиента" является ключевым в таблице "Клиенты" и в таблице "Заказы" найдено поле с тем же именем (внешний ключ).
- Чтобы отыскать заказы, размещенные клиентом, выберите поле "Название" в таблице "Клиенты" и перетащите поле в строку "Поле" (Field) первого столбца бланка запроса.
- Выберите поле "КодЗаказа" в таблице "Заказы" и перетащите строку "Поле" (Field) второго столбца. Перетащите поле "ДатаЗаказа" в третий столбец (см. рис. 3).
- Нажмите кнопку "Запуск" (Run) либо кнопку "Режим таблицы" (Query View) для вывода результирующего множества запроса (см. рис. 4). Обратите внимание на то, что заголовками полей в созданном наборе записей являются подписи полей (включая пробелы), а не имена в таблице, в которых пробелы не допускаются.
1.2. Определение порядка сортировки результатов запроса
Access выводит результирующее множество, записи которого упорядочены по значению первичного ключа. Если первичный ключ включает несколько полей, то записи в динамическом наборе сортируются в порядке указания полей первичного ключа в бланке запроса, где поля, стоящие левее, имеют больший приоритет. Так как поле "Название" является крайним левым ключевым полем, заказы некоторой компании упорядочиваются по номеру заказа. Кроме того, можно явно указать порядок сортировки. Например, если требуется вывести первыми заказы, размещенные последними с упорядочением записей по убывающей дате заказа:
Нажмите кнопку "Конструктор" (Design View) для возврата в режим конструктора запроса.
- Установите курсор в поле "Сортировка" столбца "ДатаРазмещения", а затем нажмите клавишу <F4> для вывода содержимого списка.
- Выделите элемент "по убыванию", чтобы определить сортировку записей в динамическом наборе по убывающей дате заказа (см. рис. 5).
- Нажмите кнопку "Запуск" либо кнопку "Режим таблицы" для вывода нового результирующего множества запроса (см. рис. 6).
1.3. Создание запросов на выборку данных из таблиц с косвенными связями
Запросы можно использовать для вывода косвенно связанных записей, например для отображения категорий товаров, закупленных некоторым клиентом. В запрос следует включить каждую таблицу, которая служит звеном в цепочке соединений. Для рассматриваемого примера необходимо включить все промежуточные таблицы, соединяющие таблицу "Клиенты" с таблицей "Типы". Тогда в цепочку входят следующие таблицы: "Клиенты", "Заказы", "Заказано", "Товары" и "Типы". Однако добавлять поля этих таблиц в бланк запроса не требуется — достаточно полей "Название" и "Категория".
Чтобы вывести в запросе данные косвенно связанных записей:
- Удалите в режиме конструктора запроса все столбцы, исключая "Название", щелкнув по области выделения столбца над строкой "Поле" и нажав клавишу <Delete>.
- Нажмите кнопку "Добавить таблицу" на панели инструментов либо выберите команду "Запрос, Добавить таблицу" и добавьте в запрос таблицы "Заказы", "Заказано", "Товары" и "Типы", а затем нажмите кнопку "Закрыть" диалогового окна "Добавление таблицы". Access автоматически связывает таблицы "Заказы" и "Типы", соединяя промежуточные таблицы с помощью поля первичного ключа в одной таблице и поля внешнего ключа с идентичным именем в другой (рис. 7).
Добавленные таблицы появляются в верхней части окна запроса. Однако, если список полей таблицы не виден, воспользуйтесь вертикальной полосой прокрутки для вывода "скрытых" таблиц. Списки полей можно перетащить наверх, расположив их требуемым образом (рис. 7).
- Перетащите поле "Категория" из списка полей таблицы "Типы" на строку "Поле" второго столбца бланка запроса. Можно добавить название поля в первую свободную ячейку строки "Поле", дважды щелкнув по имени поля.
- Чтобы вывести эквивалентную созданному запросу инструкцию SQL, выберите команду "Вид, Режим SQL" (View, SQL) (рис. 8). Соединения таблиц задаются операцией inner join.. .on. . .. Косвенные соединения реализуются на основе выражения inner join. . .on. . .on. .
- Нажмите кнопку "Конструктор" для закрытия окна SQL, а затем кнопку "Запуск" на панели инструментов. Результирующее множество показано на рис 9.
- Закройте запрос, нажав кнопку "Закрыть". Данный запрос лишь пример, поэтому сохранение не требуется.
Запросы, соединяющие косвенно связанные записи, используются часто, особенно при анализе данных с помощью статистических функции SQL или перекрестных запросов Access.
1.4. Создание внутреннего соединения по нескольким полям
Между двумя таблицами может быть задано несколько соединений. Например, допустим, что требуется вывести имена клиентов, у которых совпадают официальный адрес и адрес доставки. Адрес клиента хранится в поле "Адрес" таблицы "Клиенты", а адрес доставки — в поле "АдресПолучателя" таблицы "Заказы". Таким образом, необходимо сопоставить поля "КодКлиента" в обеих таблицах, а также поля "Адрес" и "АдресПолучателя". Для этого требуется использовать внутреннее соединение по нескольким полям.
Для вывода имен клиентов, официальный адрес и адрес доставки которых совпадают:
- Создайте новый запрос, раскрыв вкладку "Запросы" (Queries) окна "База данных" и нажав кнопку "Создать" (New). В появившемся диалоговом окне "Новый запрос" выделите элемент "Конструктор" (New Query) и нажмите кнопку ОК.
- Добавьте в запрос таблицы "Клиенты" и "Заказы", выбрав каждую из них в диалоговом окне "Добавление таблицы" и нажав кнопку "Добавить". Нажмите кнопку "Закрыть".
- Выберите в списке полей таблицы "Клиенты" поле "Адрес" и перетащите его на поле "АдресПолучателя" таблицы "Заказы". Это приводит к созданию соединения полей Адрес и АдресПолучателя (рис. 10). Линия, обозначающая связь, имеет с двух сторон точки, которые указывают на то, что соединение выполнено между полями, связь которых в схеме данных не задана, их имена не совпадают и они не являются первичными ключами.
- Перетащите поля "Название" и "Адрес" таблицы "Клиенты", а затем поле "АдресПолучателя" таблицы "Заказы" на бланк запроса.
- Задайте сортировку по возрастанию в столбце "Название".
- Нажмите на панели инструментов кнопку "Запуск". На рис. 11 показано результирующее множество запроса.
- Чтобы подавить вывод одинаковых строк, необходимо изменить значение свойства "Уникальные значения" (Unique Values) в бланке свойств запроса. Для отображения бланка свойств нажмите кнопку "Конструктор", а затем кнопку "Свойства" (Properties) на панели инструментов, либо дважды щелкните по пустой области верхней части окна запроса (рис. 12). Если заголовком бланка является строка "Свойства списка полей" (Field List) или "Свойства поля" (Field Pro-
perties), щелкните по пустому месту, чтобы вывести свойства запроса. Кроме того, можно щелкнуть правой кнопкой мыши по свободной области верхней части окна запроса и выбрать в контекстном меню команду "Свойства" (Properties)
- По умолчанию свойство запроса "Уникальные записи" (Unique Values) имеет значение True (Да), а свойство "Уникальные значения" — False (Нет). Поэтому в инструкцию SQL, связанную с запросом, добавляется ключевое слово Access SQL distinctrow (рис. 8). Измените значение свойства "Уникальные значения" на "Да". При этом вместо ключевого слова SQL distinct в инструкции SQL будет участвовать ключевое слово Access distinctrow. Нажмите кнопку "Свойства" для закрытия бланка свойств.
- Нажмите на панели инструментов кнопку "Запуск". Результирующее множество не содержит повторяющихся записей (рис. 13).
- Выберите команду "Вид, Режим SQL" для вывода инструкции SQL (рис. 14). Обратите внимание на то, что ключевое слово distinct инструкции select исключает записи, которые содержат повторяющиеся значения в отобранных полях.
- Нажмите кнопку "Закрыть" для закрытия запроса и не сохраняйте его. В этом случае вы избежите хранения ненужного примера в базе данных. Для большинства заказов адрес клиента и адрес доставки совпадают, поэтому было бы лучше отыскивать заказы, для которых адреса различаются. Однако в Access нельзя создать такой запрос с помощью соединения по нескольким полям, т. к. в операции Access SQL inner join использовать оператор <> не допускается. Подробную информацию о выполнении соединения по неравенству смотрите ниже в разделе "Создание соединения по отношению".
2. Использование полей подстановок
Мастер подстановок позволяет заменить обычные поля таблиц полями со списком. Это позволяет создать для определенного поля список допустимых значении, при выборе элемента в котором в текущую запись автоматически заносится требуемая величина. Поля подстановки имеют следующие типы:
· Для поля, содержащего значения внешнего ключа, создастся список значении одного или нескольких полей связанной базовой таблицы. Например, таблица "Заказы" базы данных "Борей" имеет два поля внешнего ключа: "КодКлиента" и "Код Сотрудника".Для поля "КодКлиента" отображается список значений поля "Название" таблицы "Клиенты", а для поля "КодСотрудника" — список значений полей "Фамилия" и "Имя" таблицы "Сотрудники", разделенных запятой и пробелом. Для поля "КодКлиента" подстановка реализуется на основе простого запроса на выборку: select dictinctrow [КодКлиента], [Название] FROM [Клиенты] ORDER BY [Название];.
· Для любого другого поля, исключая поле простого первичного ключа, список содержит фиксированные значения.
Поле подстановки можно задать либо в режиме конструктора, либо в режиме таблицы. Однако в режиме конструктора подстановку можно определить только для существующего поля. В режиме таблицы всегда выводится поле со списком, даже если был задан простой список. Поле со списком или список отображается также в форме, для которой таблица
является источником записей. На практике чаще всего используется поле со списком, у которого значение свойства "Ограничиться списком" (Limit to List) равно Да. Ниже описывается, как связать списки подстановки значений внешнего ключа или фиксированных величин с полями таблицы.
2.1 Добавление списка, задающего значение внешнего ключа, при помощи Мастера подстановок
Рассмотрим построение списка подстановки значений внешнего ключа таблицы
"Персональные мероприятия", созданной в предыдущих главах. Чтобы заменить поле "Код Сотрудника" полем подстановки:
1. Выделите таблицу "Персональные мероприятия" в окне "База данных", а затем нажмите комбинацию клавиш <Ctrl>+<C> для копирования таблицы в Буфер обмена.
2. Нажмите комбинацию клавиш <Ctrl>+<V> для вывода диалогового окна "Вставка таблицы" (Paste Table As). Введите имя копии, например, "Персональные мероприятия (поля подстановки)" и нажмите кнопку ОК.
- Откройте копию в режиме конструктора и выделите поле "КодОтветственного". Раскройте вкладку "Подстановка" (Lookup) для вывода текущих свойств подстановки. Поле не имеет этих свойств. В ячейке столбца "Тип данных" (Data Type) выберите из списка элемент "Мастер подстановок" (рис. 16). Отображается первое диалоговое окно Мастера подстановок.
- Поле подстановки должно включать значения из другой таблицы (таблица "Сотрудники"), поэтому изменять установки в первом окне не требуется (рис. 17). Нажмите кнопку "Далее" (Next) для вывода второго диалогового окна Мастера подстановок.
- Выбрав переключатель "Таблицы" (Tables), выделите таблицу "Сотрудники", с которой связано поле "КодОтветственного" (рис. 18). Нажмите кнопку "Далее" для вывода третьего диалогового окна Мастера подстановок.
- Нажмите кнопку ">" три раза для добавления полей "КодСотрудника", "Фамилия" и "Имя" в список подстановки (рис. 19). Включение ключевого поля базовой таблицы, связанное с полем внешнего ключа, является обязательным. Нажмите кнопку "Далее".
- Измените ширину столбцов, обеспечив достаточный интервал между фамилией и именем. Двойной щелчок по правому краю заголовка столбца приводит к тому, что Мастер подстановок изменяет размер, но не оптимальным образом. Кроме того. Мастер подстановок определяет, что поле "КодСотрудника" является ключевым, и рекомендует скрыть его (рис. 20). Нажмите кнопку "Далее" для вывода последнего окна Мастера подстановок.
- Не изменяйте предлагаемой подписи для поля подстановки (рис. 21). Если задать другое значение, то изменится имя поля, а не его подпись.
- Нажмите кнопку "Готово" (Finish) для завершения работы Мастера подстановок, а затем кнопку ОК в ответ на приглашение сохранить структуру таблицы. В свойствах подстановки для поля появляются новые значения (рис. 22). Мастер подстановок также создает следующую инструкцию SQL:
SELECT DISTINCTROW [Сотрудники].[КодСотрудника], [Сотрудники].[Фамилия], [Сотрудники].[Имя] FROM [Сотрудники];.
- Нажмите кнопку "Режим таблицы" для вывода данных таблицы. В поле "КодОтветственного" выводится только первый столбец списка. Увеличьте ширину поля до ширины списка, предварительно раскрыв его (рис. 23).
- Вернитесь в режим конструктора, выберите свойство "Источник строк" (Row Source) и нажмите кнопку "Построить" (Build) для вывода окна "Инструкция SQL" (Row Source SQL) в режиме конструктора запросов (рис. 24), а затем закройте его.
2.2 Добавление списка постоянных значений к полю таблицы
Добавление списка фиксированных значений к полю таблицы очень похоже на создание списка значений внешнего ключа, описанное в предыдущем разделе. Чтобы связать поле "Тип Мероприятия" копии таблицы "Персональные мероприятия" со списком постоянных величин:
- Выберите поле "ТипМероприятия". В ячейке столбца "Тип данных" (Data Type) выберите из списка элемент "Мастер подстановок" для запуска Мастера подстановок.
- В появившемся диалоговом окне выберите переключатель "Фиксированный набор значений" (I Will Туре in the Values That I Want) и нажмите кнопку "Далее".
- Во втором диалоговом окне Мастера подстановок введите значение 2 в поле "Число столбцов" (Number of Columns) и нажмите клавишу <Таb> для создания второго столбца.
- Введите значенияП, Принять; К, Квартальный отчет; Г, Годовой отчет; О, Изменить оклад в столбцы Столбец1 и Столбец2 первых четырех строк. Измените ширину столбцов, обеспечив достаточный интервал между ними (рис. 25). Нажмите кнопку "Далее" для вывода третьего окна Мастера подстановок.
- В поле "ТипМероприятия" используется один символ для обозначения типа мероприятия, поэтому выберите "Столбец!" в качестве столбца, содержащего значения, которые необходимо добавить в таблицу (рис. 26). Нажмите кнопку "далее" для вывода четвёртого и последнего окна Мастера подстановок.
- Не изменяя предлагаемой Мастером подстановок подписи для списка подстановки, нажмите кнопку "Готово" (Finish). Выведите новые свойства подстановки (см рис. 27).Свойству "Тип источника строк" (Record Source Type) присваивается значение "Список значений", свойству "Источник строк" – значение
"П"; "Принять"; "К"; "Квартальный отчёт"; "Г"; "Годовой отчёт"; "О"; "Изменить оклад".
- Нажмите кнопку "Режим таблицы" и сохраните внесённые изменения. Увеличьте ширину столбца "Тип" до ширины списка, раскройте список и убедитесь в том, что в нём содержатся фиксированные значения (рис.28).
- Если необходимо скрыть сокращённое название типа меропрятия, замените первую величину в поле свойства "Ширины столбцов" (Column Widths) на 0.
3. Внешнее, рекурсивное соединения и соединение по отношению
В предыдущих заданиях лабораторной работы были созданы внутренние соединения. Внутренние соединения чаще других используются в базах данных. Однако Access позволяет задать еще три типа соединений, которые применяются реже: внешнее соединение, рекурсивное соединение и соединение по отношению.
3.1 Создание внешнего соединения
Внешние соединения позволяют вывести данные всех записей таблицы, участвующей в соединении, в независимости от того, имеются ли соответствующие им записи в связанной таблице. Внешние соединения могут быть левыми или правыми.
Запрос, в котором участвуют таблицы с левым внешним соединением (left join или *= в SQL), выводит все записи таблицы "один" с уникальным значением первичного ключа в независимости от того, имеются ли соответствующие им записи в таблице "многие". И наоборот, запрос, в котором участвуют таблицы с правым внешним соединением (right join или =* в SQL), выводит все записи таблицы "многие" в независимости от того, имеются ли соответствующие им записи в таблице "один". Обычно, но не обязательно, записи таблицы "многие", которые не подчиняются ни одной записи в таблице "один", являются "висячими" и могут быть связаны отношением "многие-к-одному" с записями другой таблицы.
Чтобы с помощью левого внешнего соединения создать запрос, который обнаруживает отсутствие записей для некоторого сотрудника в таблице "Персональные мероприятия":
- Создайте новый запрос и добавьте в него таблицы "Сотрудники" и "Персональные мероприятия".
- Перетащите поле "КодСотрудника" таблицы "Сотрудники" на поле "КодСотрудника" таблицы "Персональные мероприятия" для создания внутреннего соединения между ними. Access автоматически создает соединение, если только была установлена связь между этими полями при создании таблицы "Персональные мероприятия".
- Выделите и перетащите поля "Фамилия" и "Имя" таблицы "Сотрудники", а также поля "КодСотрудника" и "ДатаПлан" таблицы "Персональные мероприятия" соответственно в первые четыре столбца бланка запроса.
- Выделите линию, соединяющую поля "КодСотрудника", щелкнув по ней (рис. 29). Увеличение толщины тонкого отрезка линии указывает на то, что линия выделена. На рис. 29 два списка полей стоят на расстоянии друг от друга, так что можно заметить тонкий отрезок соединения.
- Выберите команду "Вид, Параметры объединения" (View, Join Properties). Команда активна только тогда, когда выделено соединение. Кроме того, можно вывести диалоговое окно "Параметры объединения" (Join Properties), дважды щелкнув по тонкому отрезку соединения. При двойном щелчке по любому из двух толстых отрезков линий по краям соединительной линии выводится бланк свойств запроса. В диалоговом окне "Параметры объединения" переключатель "1" задает обычное внутреннее соединение, переключатель "2" — левое внешнее соединение и переключатель "3" — правое внешнее соединение.
- Задайте левое внешнее соединение, выбрав переключатель "2". Нажмите кнопку ОК для закрытия диалогового окна.
- Нажмите на панели инструментов кнопку "Запуск" для вывода результирующего множества запроса с левым внешним соединением. Три сотрудника не имеют записей в таблице "Персональные мероприятия" (рис. 31). Результаты отличаются, если ввести дополнительные данные. Если для всех сотрудников имеется план мероприятий, то откроите таблицу "Персональные мероприятия" и удалите его для нескольких служащих, а затем повторно выполните запрос.
Если бы не было задано условия на значение, то можно было бы добавить план мероприятии для несуществующего сотрудника и в результирующем множестве запроса с правым внешним соединением выводились бы персональные мероприятия с отсутствующими именами служащих.
3.2 Создание рекурсивного соединения
Рекурсивные соединения связывают поля одной таблицы. При этом требуется добавить в запрос копию таблицы, а затем задать соединение. В качестве примера рассмотрим запрос, в котором выводятся сотрудники, утвердившие мероприятия, за которые они ответственны, что запрещено внутренним распорядком компании "Борей".
Для создания рекурсивного соединения в таблице "Персональные мероприятия":
- Создайте новый запрос и добавьте в него таблицу "Персональные мероприятия".
- Добавьте в запрос копию таблицы "Персональные мероприятия", повторно нажав кнопку "Добавить". Access присваивает копии имя "Персональные мероприятия". Закройте диалоговое окно "Добавление таблицы".
- Перетащите поле "КодОтветствснного" исходной таблицы на поле копии "КодНачальника". Между таблицами возникает соединение (рис. 32).
- Перетащите поля "КодСотрудника" и "КодОтветственного" исходной таблицы, а также поля "КодНачальника" и "ТипМероприятия" таблицы "Персональные мероприятия_1" соответственно в первые четыре столбца бланка запроса.
- При использовании рекурсивных соединений требуется задать вывод только уникальных значений. Нажмите на панели инструментов кнопку "Свойства" либо дважды щелкните по свободной области верхней части окна запроса, а затем присвойте в бланке свойств запроса значение да свойству "Уникальные значения". Повторно нажмите кнопку "Свойства" для скрытия бланка свойств запроса
- Нажмите кнопку "Запуск" для вывода записей, в которых сотрудник, ответственный и утвердивший план мероприятий, является одним и тем же лицом (рис. 33). В данном случае служащий, у которого поле "КодСотрудника" равно 2. является вице-президентом и может не подчиняться общим правилам. Результаты могут отличаться, если ввести дополнительные данные.
В рассматриваемом задании можно добавить в запрос таблицу "Сотрудники", чтобы вывести имя сотрудника. При этом необходимо создать дополнительное соединение между полем "КодСотрудника" исходной таблицы "Персональные мероприятия" и полем "КодСотрудника" таблицы "Сотрудники", а затем перетащить поле "Фамилия" в пятый столбец бланка запроса. Так как в соединении участвует поле первичного ключа, то в инструкцию SQL по умолчанию добавляется ключевое слово distinctrow
которое исключает отображение повторяющихся записей. На всякий случай присвойте значение Да свойству "Уникальные значения", нажав на панели инструментов кнопку "Свойства", либо дважды щелкнув по свободной области верхней части окна запроса, а затем выполните запрос.
Рекурсивные соединения используются весьма редко в полностью автоматических базах данных, т. к. заданные условия на значения и обеспечиваемая целостность данных сводят на нет необходимость применения таких связей.
3.3 Создание соединения по отношению
Большинство соединений основаны на полях с одинаковыми значениями. Однако в некоторых случаях требуется создать связь неравных полей. В режиме конструктора запросов отображаются только внутренние и внешние соединения. Связь, эквивалентную соединению по отношению, можно задать с помощью условия отбора, которое указывается для одного из двух полей, участвующих в соединении.
Как уже отмечалось выше, поиск клиентов, которые имеют разный официальный адрес и адрес доставки, является примером, в котором требуется использовать соединение по отношению. Для создания связи, эквивалентной данному соединению:
- Создайте новый запрос и добавьте в него таблицы "Клиенты" и "Заказы".
- Перетащите поля "Название" и "Адрес" таблицы "Клиенты", а также поле "Адрес Получателя" таблицы "Заказы" соответственно в первые три столбца бланка запроса.
- Введите в строке "Условие отбора" (Criteria) столбца поля "Адрес Получателя" выражение о Клиенты. Адрес. Access автоматически заключает имена таблицы и поля в квадратные скобки в независимости от того, включают ли они пробелы или знаки препинания (рис. 34). В инструкцию select добавляется предложение where Заказы.АдресПолучателя о [Клиенты].[Адрес] (рис. 35).
Если ввести в столбце поля "Адрес" выражение <> Заказы. АдресПолучателя, то будет получен идентичный результат. В инструкцию select добавляется предложение where Клиенты. Адрес <>[Заказы].[АдресПолучателя].
- Нажмите на панели инструментов кнопку "Свойства", либо дважды щелкните по свободной области верхней части окна запроса, а затем присвойте в бланке свойств запроса значение да свойству "Уникальные значения".
- Выполните запрос. Результирующее множество содержит только те записи. в которых различаются адрес клиента и адрес доставки (рис. 36).
- Нажмите кнопку "Закрыть" и, если требуется, сохраните запроc.
4. Использование запросов для обновления таблиц
Большинство запросов, свойство "Уникальные записи" которых имеет значение Да, могут использоваться для обновления данных, потому что в предложении SQL, связанном с ними, участвует ключевое слово distinctrow. Эти запросы создают объекты Recordset типа Dynaset, допускающими изменения. Результирующее множество можно обновить, только если в его конце имеется пустая запись (со звездочкой на кнопке выделения). Запросы, свойство "Уникальные значения" которых имеет значение Да, создают объекты Recordset типа Snapshot путем замены ключевого слова ANSI SQL distinct. Такие объекты не допускают ни обновления, ни редактирования имеющихся в них записей, ни добавления новых. Ниже описывается, как обновить запись таблицы, включенной в динамический набор запроса, а также как форматировать данные, используя бланк свойств поля запроса.
4.1 Требования к запросам на обновление
Добавление новых записей к таблицам или обновление данных таблиц, включенных в запрос, оказывается в некоторых случаях весьма удобным. Особенно полезной является возможность исправления ошибок с помощью запроса. К сожалению, большинство запросов не позволяют добавлять или изменять записи. Нельзя добавлять или изменять записи в запросах в следующих случаях:
· Свойство запроса "Уникальные значения" имеет значение Да.
· В запросе используются рекурсивные соединения.
· В запросе применяются статистические функции SQL, например Sum (). Следует отметить, что статистические функции SQL используются во всех перекрестных запросах.
· Если две таблицы связаны отношением "один-ко-многим", то в таблице "один" не задано полей первичного ключа, повторения значений которых не допускается.
При разработке запроса, используемого в качестве основы формы для ввода или редактирования данных, убедитесь, что ни одно из перечисленных условий нс выполняется.
Если ни одно из перечисленных условий нс выполняется для запроса или для всех таблиц, входящих в него, то можно добавить или обновить данные в следующих объектах:
· В таблице, которая является единственной в запросе
· В обеих таблицах, связанных отношением "один-к-одному"
· В таблице "многие", связанной с другой таблицей отношением
"один-ко-многим"
· В таблице "один", связанной с другой таблицей отношением "один-ко-многим", если ни одно поле таблицы "многие" не участвует в запросе
Обновление таблицы "один", связанной с другой таблицей отношением "один-ко-многим", требует особого рассмотрения. Для изменения данных в такой таблице:
- Добавьте в запрос поле или поля первичного ключа таблицы "один", а также обновляемые поля.
- Добавьте в таблицу "многие" поле или поля, которые соответствуют ключевому полю или полям таблицы "один". Это необходимо для выбора обновляемых записей.
- Добавьте условие отбора записей для обновления полей, заданных на
шаге 2.
4. Сбросьте флажок "Вывод на экран" (Show) поля или полей таблицы "многие", так чтобы они не выводились в динамическом наборе записей запроса.
Выполнив эти действия, можно редактировать данные не ключевых полей таблицы "один". Однако нельзя изменять значения ключевых полей, которые связывают записи таблицы "один" с записями таблицы "многие". Это приведет к нарушению целостности данных. Кроме того, нельзя обновить значение вычисляемого поля запроса — таблицы не могут включать вычисляемые величины.
4.2 Определение формата вывода данных
Формат вывода данных запроса копируется из формата данных таблиц, на основе которых создается запрос. Чтобы изменить его, используется функция Format [Имя Поля, Формат}, которая создает вычисляемые поля .
5. Добавление в запрос всех полей таблицы
В большинстве запросов участвуют только отдельно выбранные поля. Чтобы добавить их в бланк запроса, можно либо ввести с клавиатуры их имя, либо выбрать из списка в ячейке строки "Поле", либо перетащить их из списка полей. Однако имеется возможность включить в запрос все поля таблицы. Для этого в Access имеются три способа.
5.1 Использование заголовка окна списка полей для добавления всех полей в запрос
Для включения всех полей таблицы в запрос используется заголовок окна списка полей или строка этого списка, помеченная звездочкой. Чтобы добавить в бланк запроса все поля вместе с их полными именами:
- Создайте новый запрос и добавьте в него требуемые таблицы.
- Дважды щелкните по заголовку окна списка полей таблицы, все поля которой включаются в запрос. Это приводит к выделению всего списка.
- Перетащите поля на ячейку строки "Поле" бланка запроса, начиная с которой необходимо добавить имена (рис. 39).
При этом предложение SQL, связанное с запросом, имеет следующий вид:
SELECT DISTINCTROW ИмяТаблицы.ПервоеПоле, ИмяТаблицы. ВтороеПоле, ... ИмяТаблицы.ПоследнееПоле FROM ИмяТаблицы;
5.2 Использование строки, помеченной звездочкой, для добавления всех полей в запрос
Для включения всех полей таблицы в запрос без отображения их имен перетащите из списка полей звездочку на ячейку строки "Поле", начиная с которой необходимо вывести данные таблицы. Столбец со звездочкой эквивалентен инструкции SQL select DISTINCTROW * FROM ИмяТаблицы;.
Для столбца со звездочкой нельзя задать порядок сортировки и условие отбора. Однако, чтобы все-таки упорядочить записи и указать критерий отбора:
- После добавления звездочки перетащите на бланк запроса поле, по значению которого требуется сортировать записи.
- Задайте порядок сортировки в ячейке "Сортировка" или "Условие отбора" столбца выбранного поля.
- Сбросьте флажок "Вывод на экран", чтобы не выводить поле дважды в результирующем множестве запроса (рис. 40).
В запросе может участвовать неограниченное число столбцов со звездочкой. Поле, по значению которого упорядочиваются записи, задается в инструкции SQL предложением order by Имя Таблицы. ИмяПоля,а условие отбора предложением where УсловиеОтбора
5.3 Использование окна свойств для добавления всех полей в запрос
Обычно для обновления в формах или отчетах доступны только те поля, чьи имена указаны в бланке запроса. Все другие поля исключены из результирующего множества. Однако, чтобы изменять в форме или отчете данные всех полей таблицы, используемой в запросе, и не включать их имена в запрос, требуется присвоить значение Да свойству запроса "Вывод всех полей" (Output All Fields). Для задания значения свойства "Вывод всех полей":
- Создайте новый запрос и добавьте в него требуемые таблицы.
- Нажмите на панели инструментов кнопку "Свойства" либо дважды щелкните по свободной области верхней части окна запроса.
- Установите курсор в поле свойства "Вывод всех полей" и измените значение на Да.
- Если свойство "Уникальные значения" имеет значение Да, то обновление полей не допускается.
- Закройте бланк свойств запроса, нажав кнопку "Свойства".
Если в диалоговом окне "Параметры" сбросить флажок "Вывод всех полей" (Restrict Available Fields), то в инструкцию SQL после списка выбранных полей будет добавлена звездочка, которая определяет вывод всех полей заданных таблиц:
SELECT DISTINCTROW Клиенты.Название, Типы.Категория, * FROM-Клиенты, Типы;
Следует отметить, что при выводе всех полей таблицы выполнение запроса происходит медленнее, особенно если в результирующем множестве содержится большое количество записей.
6. Вычисления на подмножестве записей
Access позволяет практически мгновенно обобщить информацию, содержащуюся в записях таблиц. Эта информация является входными данными для большинства информационных систем. В этих системах можно получить ответы на типичные вопросы, такие, например, как "Какова сумма продаж на заданный день?" или "Как изменилась сумма продаж за текущий месяц по сравнению с суммой продаж за тот же месяц прошлого года?". Чтобы выполнить расчеты на основе информации таблиц, необходимо создать запрос, в котором для вычислений используются статистические функции Access SQL.
6.1 Использование статистических функций языка SQL
При обобщении данных, которые содержатся в полях таблиц, включенных в запрос, используются статистические функции SQL (табл. 1). Эти функции называются статистическими, потому что они выполняют сбор статистики об информации в записях. Имеющихся статистических функций SQL обычно достаточно для решения большинства задач. Однако можно самостоятельно написать функцию Access VBA, которая выполняла бы более сложные статистические, научные или инженерные расчеты над данными. Статистические функции SQL можно задать в ячейке строки "Групповая операция" (Total) бланка запроса. Эта строка выводится, если нажать на панели инструментов кнопку "Групповые операции" (Sum) либо выбрать команду "Вид, Групповые операции" (View, Totals). Если вы знакомы с принципами статистической обработки данных, то вам известны отличия одной функции от другой.
6.2 Вычисления на всем диапазоне записей таблицы
Менеджерам, особенно специализирующимся на продажах и маркетинге, обычно требуется информация о заказах и поставках, размещенных и исполненных за определенный промежуток времени, финансовым директорам — обобщенные величины, такие, например, как общая сумма неоплаченных счетов и среднее число дней, прошедших со времени выставления счета до момента оплаты. В некоторых случаях необходимо выполнить расчеты над всеми записями таблицы, например, вычислить за весь период работы фирмы среднюю сумму выставленных счетов. Для выбора данных, которые требуется обобщить, в запросе используется условие отбора. Статистические функции SQL указываются в строке "Групповая операция", вывести которую можно, используя кнопку "Групповые операции" (с греческой буквой сигма (å), обозначающей суммирование), которая находится на панели инструментов.
Таблица "Заказы" базы данных "Борей" не содержит поля "Итог", которое содержало бы сумму заказа без стоимости перевозки. Для создания запроса, в котором используются статистические функции SQL для вывода данных о заказах, общего количества, общей, а также средней, наименьшей и наибольшей суммы, требуется добавить поле, которое бы содержало обобщенную информацию о каждом заказе. Чтобы создать новую таблицу, которая имеет дополнительное поле, содержащее рассчитанное значение поля "Итог":
- Создайте новый запрос и добавьте в него таблицы "Заказы" и "Заказано".
- Перетащите поле "КодЗаказа" таблицы "Заказы", а затем поле "ДатаРазмещения".
- Введите в ячейке строки "Поле" третьего (пустого) столбца бланка запроса выражение Итог: Sum([Количество]*[Цена]*(1-[Скидка])), которое вычисляет чистую сумму товаров, включенных в поставку. Установив курсор в столбец "Итог", нажмите на панели инструментов кнопку "Свойства" и присвойте значение "Денежный" (Currency) свойству поля "Формат поля".
- Нажмите на панели инструментов кнопку "Групповые операции". В бланк запроса добавляется строка "Групповая операция", содержащая по умолчанию в каждой ячейке операцию "Группировка" (Group By). Назначение операции "Группировка" обсуждается ниже.
- Выберите из списка статистических функций SQL в третьей ячейке строки "Групповая операция" элемент "Выражение" (Expression) (рис. 41)..
- Нажмите кнопку "Запуск" на панели инструментов, чтобы проверить запрос. Результирующее множество показано на рис. 42.
- Закройте запрос, сохранив его под именем"Итоги по заказам".
Чтобы использовать статистические функции SQL для поля "Итог" запроса "Итоги по заказам":
- Создайте новый запрос и добавьте в него запрос "Итоги по заказам". Перетащите поле "КодЗаказа" на первый столбец, а затем четыре раза поле "Итог".
- Выберите команду "Вид, Групповые операции" либо щелкните правой кнопкой мыши по бланку запроса и выберите из контекстного меню команду "Групповые операции" для добавления на бланк запроса строки "Групповая операция".
- Установите курсор в столбец "КодЗаказа" строки "Групповая операция" и выберите из списка функцию Count () (рис. 43).
- Установите курсор в первый столбец "Итог" строки "Групповая операция" и выберите из списка функцию Sum() (рис. 43), для второго столбца "Итог" задайте функцию Avg(), для третьего — функцию Min (), длячетвертого — функцию Мах ().
- Установите курсор в столбец "КодЗаказа" и нажмите на панели инструментов кнопку "Свойства" либо щелкните по столбцу правой кнопкой мыши и выберите из контекстного меню команду "Свойства". Присвойте значение "Счетчик" свойству "Подпись".
- Повторите шаг 5 для каждого из четырех столбцов "Итог", присвоив значение "Денежный" свойству "Формат поля", а свойству "Подпись" значения "Суммарная выручка", "В среднем за заказ", "Минимум", "Максимум" соответственно. Значение свойства "Формат поля" задавать не требуется, если в запросе "Итоги по заказам" использована функция ccur ().
- Нажмите кнопку "Запуск" для вывода результирующего множества. Условие отбора не было указано, поэтому выведенные данные относятся ко всей таблице (рис. 44).
- Сохраните запрос под именем"Статистика по заказам". Этот запрос будет использоваться в следующих разделах.
6.3 Вычисления на выделенных записях таблицы
Часто требуется выполнить вычисления над определенным набором данных и, основываясь на них, составить статистику. Чтобы произвести расчеты только над теми заказами, которые были размещены в марте 1995 года:
- Нажмите на панели инструментов кнопку "Конструктор" для возврата в режим конструктора, чтобы задать условие отбора группы записей.
- Перетащите поле "ДатаРазмешения" на столбец "КодЗаказа", чтобы столбец "ДатаРазмещения" стал первым в бланке запроса. Теперь необходимо определить условие отбора заказов на основе даты.
- Замените элемент "Группировка" в столбце "ДатаРазмещения" на элемент "Условие" (Where). Access сбрасывает флажок "Вывод на экран" столбца "ДатаРазмещения". Если установить этот флажок, то при выполнении запроса будет выведено сообщение об ошибке: этого нельзя делать в столбце, который явно связан с предложением SQL where.
- Введите выражение <=#31.03.95#And>=#01.03.95# в ячейку "Условие отбора" столбца "ДатаРазмещения" для отбора заказов, размещенных в марте 1995 года (рис. 45).
- Выберите команду "Вид, Режим SQL" для вывода предложения SQL. В него добавлена операция where, в данном случае where ( ( ([.Итоги по заказам] .ДатаРазмещения) <=#31.03.95#And>=#01.03.95#) ), которая отбирает требуемые записи. Если не задать в ячейке "Групповая операция" элемент "Условие", то выводятся обобщенные данные для каждого дня марта 1995 года, а не для всего месяца.
- Нажмите на панели инструментов кнопку "Запуск" для вывода результата: количество, общую и среднюю сумму заказов, размещенных в марте 1995 года (рис. 46).
Данные можно обобщить более удобным образом с помощью функции Format (), например, чтобы получить итоговые данные по месяцам (или по кварталам). Для вывода информации о продажах по каждому месяцу 1994 года, последнему году, для которого в базе "Борей" имеются данные по всем месяцам:
- Нажмите на панели инструментов кнопку "Конструктор", а затем щелкните по области выделения столбца "ДатаРазмещения", чтобы выделить его. Нажмите клавишу <Insert> для добавления в запрос пустого столбца..
- Введите выражение Месяц: Format ([ДатаРазмещения] ; "уу-mm") в первый (пустой) столбец строки "Поле". Формат "уу-mm" используется для упорядочивания записей по дате. Кроме того, можно применять формат "m" или "mm", но не "mmm", потому что в последнем случае столбцы упорядочиваются в алфавитном порядке, начиная с августа.
- Введите выражение <=#31.12.94#And>=#01.01.94# в ячейку "Условие отбора" столбца "ДатаРазмещения" (рис. 47).
- Нажмите на панели инструментов кнопку "Запуск" для вывода результирующего множества (рис. 48).
- Выберите команду "Вид, Режим SQL" для вывода предложения SQL. Инструкция SQL, показанная на рис. 49, была для ясности отформатирована: добавление пробелов и концов строк не влияет на ее смысл.
6. Выберите команду "Файл, Сохранить как" (File, Save As) для сохранения запроса под другим именем, таким, например, как "Ежемесячная статистика по заказам".
7. Создание запросов с параметрами
Если запрос требуется выполнять многократно, постоянно изменяя условие отбора, то можно преобразовать его в запрос с параметрами. Запросы с параметрами, позволяют задать критерий отбора с помощью диалогового окна "Введите значение параметра". Например, в запросе "Ежемесячная статистика по заказам", создание которого рассмотрено в предыдущем разделе, может изменяться только один аргумент: период времени, за который требуется подвести итоги по продажам. Ниже описывается, как добавить в запрос параметр, а также как указать тип данных аргумента.
7.1 Добавление параметра в запрос "Ежемесячная статистика по заказам".
Чтобы преобразовать запрос "Ежемесячная статистика по заказам" в запрос с параметрами, сначала необходимо задать текст приглашения, которое выводится в диалоговом окне "Введите значение параметра". При создании запроса с параметрами требуется заменить фактические значения подсказкой, заключив ее текст в квадратные скобки. Для этого:
- Откроите в режиме конструктора запрос "Ежемесячная статистика по заказам".
- Установите курсор в ячейку "Поле" столбца "Месяц", нажмите клавишу <F2> для выделения содержимого ячейки, а затем комбинацию клавиш <Ctrl> + <C>. чтобы поместить выделенное выражение в Буфер обмена.
- Установите курсор в ячейку "Поле" столбца "ДатаРазмещения", нажмите клавишу <F2> для выделения содержимого ячейки, а затем комбинацию клавиш <Ctrl>+<V>, чтобы заменить его на выражение, содержащееся в Буфере обмена.
- Установите курсор в ячейку "Условие отбора" столбца "ДатаРазмещения" и замените выражение <=#31.12.94#And>=#01.01.94# строкой [Введите месяц и год в формате ММ-ГГ:] (рис. 50).
- Нажмите на панели инструментов кнопку "Запуск". В появляющемся диалоговом окне "Введите значение параметра" выводится надпись, которая задана на шаге 4 в качестве критерия отбора.
Введите строку 03-94, чтобы вывести данные для марта 1995 года (рис. 10.51).
Нажмите кнопку ОК для вывода результирующего множества запроса(рис.52).
7.2 Определение типа данных параметра
Все параметры по умолчанию имеют текстовый тип. Если условие отбора относится к данным с типом "Дата/Время" (Data/Time) или "Числовой" (Number), то необходимо определить тип данных для каждого такого аргумента, используя диалоговое окно "Параметры запроса" (Query Parameters).
Для задания типа данных параметра:
- С помощью мыши выделите строку Введите месяц и год в формате мм-гг: в ячейке "Условие отбора" столбца "ДатаРазмещения" (не включайте квадратные скобки) и скопируйте текст в Буфер обмена, нажав комбинацию клавиш <Ctrl>+<0.
- Выберите команду "Запрос, Параметры" (Query, Parameters) для вывода диалогового окна "Параметры запроса".
- Вставьте текст приглашения в столбец "Параметр" (Parameter), установив в него курсор и нажав комбинацию клавиш <Ctrl>+<V>. Строки в столбце "Параметр" и в ячейке "Условие отбора" должны совпадать в точности, что обеспечивается использованием Буфера обмена. Не указывайте в столбце "Параметр" квадратные скобки!
- Нажмите клавишу <Таb> для перемещения к столбцу "Тип данных" (Data Type) и выберите из списка элемент "Текстовый" (Text) (рис. 53). Нажмите
кнопку ОК для закрытия диалогового окна.
Возможность преобразования в запрос с параметрами имеется у всех типов запросов, которые включают одно или несколько условий отбора. Преимущество использования аргументов заключается в том, что в запросе можно указать интервал интересующих значений, например указать определенный квартал или финансовый год.
8. Создание перекрестных запросов
Перекрестные запросы — это запросы, в которых вычисляются статистические данные и определяется их внешний вид. Информация выводится в привычной форме: в виде таблицы. В предложениях Assess SQL для обозначения перекрестного запроса используется ключевое слово transform . С помощью рассматриваемого типа запросов можно:
· Указать поле, которое является заголовком строки, используя операцию "Группировка".
· Задать поле или поля, которые являются заголовками столбцов, а
также условие, определяющее значения в столбцах.
· Указать место размещения данных в таблице. Перекрестные запросы обладают следующими достоинствами:
· Возможностью вывода значительного объема данных в компактном и
привычном виде.
· Итоговые данные представляется в формате, который идеально подходит для автоматического создания графиков и диаграмм с помощью Мастера диаграмм Access.
· Простота и скорость разработки запросов с несколькими уровнями детализации. Высоко структурированные запросы позволяют, например, посмотреть продажи для конкретного товара, нажав кнопку "Промежуточные итоги".
При использовании перекрестных запросов имеется только одно ограничение: нельзя сортировать таблицу результатов по значениям, содержащимся в столбцах. Таким образом, например, невозможно создать запрос. в котором товары упорядочены в строках по объему продаж: в подавляющем большинстве случаев одновременное упорядочивание данных в столбцах по всем строкам невозможно. Однако можно задать сортировку по возрастанию или по убыванию либо отсутствие упорядочивания для заголовков строк. Мастер перекрестного запроса помогает в создании перекрестных запросов. Однако он имеет одно ограничение: мастер создает перекрестный запрос только на основе одной таблицы или одного запроса. Для большинства баз данных потребуется использовать по крайней мере две таблицы. В этом случае для применения Мастера перекрестного запроса необходимо построить запрос, включающий все требуемые таблицы. Таким образом, в следующих разделах описывается создание перекрестного запроса без помощи мастера.
8.1 Создание перекрестного запроса "ежемесячная выручка от продаж"
Для создания перекрестного запроса, в котором в строках выводятся товары, а в столбцах соответствующие им месячные объемы продаж:
- Создайте новый запрос и добавьте в него таблицы "Товары". "Заказано" и "Заказы".
- Перетащите поля "КодТовара" и "Марка" таблицы "Товары", а затем поле "ДатаРазмещения" таблицы "Заказы".
- Выберите команду "Запрос, Перекрестный" (Query, Crosstab). Заголовок окна запроса Запрос1: на выборку (Select Query: Query1) изменится на Запрос1: перекрестный запрос (Crosstab Query:
Query1). Кроме того, в бланк запроса будет добавлена строка "Перекрестная таблица" (Crosstab).
- Выберите в списке ячейки "Перекрестная таблица" столбца "КодТовара" значение "Заголовки строк" (Row Heading). Выполните то же самое для столбца "Марка". Эти столбцы являются требуемыми заголовками строк перекрестной таблицы.
- Выберите в списке ячейки "Групповая операция" столбца "КодТовара" значение "Условие". В ячейке "Условие отбора" этого столбца введите выражение <=#31.12 . 94#And>=#01. 01. 94# для вывода в перекрестной таблице данных за 1994 год.
- Установите курсор в ячейку "Поле" следующего (пустого) столбца и введите следующее выражение:
Объем продаж: Sum([Заказано].[Количество]*
[Заказано].[Цена])
Выберите в ячейке "Групповая операция" того же столбца значение "Выражение", а затем в ячейке "Перекрестная таблица" значение "Значение" (Value). В столбце "Объем продаж" вычисляется общий объем заказов на каждый товар, который подставляется в ячейки перекрестной таблицы. Ссылка на таблицу "Заказано" обязательна, иначе будет выдано сообщение об ошибке "Неоднозначная ссылка на поле" (Ambiguous field reference).
- Установите курсор в ячейку "Поле" следующего (пустого) столбца и введитe выражение Format([ДатаРазмещения]; "mmm"). Access добавляет имя поля "Выражение1" (Expr1), применяемое по умолчанию. Не меняйте его, т. к. функция Format () создает при запуске запроса имена столбцов, состоящие из трех первых букв названия месяца (формат "mmm"). Выберите из списка в ячейке "Перекрестная таблица" значение "Заголовки столбцов" (Column Heading) (рис. 54).
- Нажмите кнопку "Запуск". Через несколько секунд появляется результирующее множество (рис. 55).
Обратите внимание на то, что выведенная перекрестная таблица обладает одним немаловажным недостатком: расположение столбцов определяется алфавитным порядком их заголовков, а не хронологической очередностью. В этом случае требуется использовать фиксированные заголовки столбцов.
8.2 Использование фиксированных заголовков столбцов в перекрестных запросах
В Access можно задать порядок сортировки заголовков строк или столбцов перекрестной таблицы, таким образом определив ее внешний вид. При использовании названии месяцев столбцы упорядочиваются в алфавитном порядке, а не в хронологической последовательности. Чтобы все-таки вывести столбцы в порядке календарной очередности месяцев, необходимо задать фиксированные заголовки. Для соответствующего изменения перекрестного запроса:
- Переключитесь в режим конструктора и выведите свойства запроса. В бланке свойств выводится свойство "Заголовки столбцов" (Column Headings), которое имеется только у перекрестных запросов.
- Введите краткие названия каждого из месяцев года в поле свойства "Заголовки столбцов" (рис. 56). Необходимо воспроизвести названия без ошибок, в противном случае данные для неправильно заданных месяцев не отображаются. Сокращения требуется заключить в кавычки и разделять названия точками с запятыми. Пробелы не нужны. По завершении ввода всех 12 месяцев закройте бланк свойств.
- Нажмите кнопку "Запуск". Теперь заголовки столбцов перекрестной таблицы соответствуют календарной последовательности (рис. 57). Если результирующее множество выглядит не так, как на рисунке, проверьте правильность фиксированных заголовков столбцов, заданных в поле свойства "Заголовки столбцов". Если заголовок столбца введен неправильно, то столбец отсутствует в перекрестной таблице.
4. Выберите команду "Файл, Сохранить как" и сохраните запрос под именем "Ежемесячная выручка от продаж".Чтобы напечатать перекрестную таблицу, нажмите кнопку "Печать" (Print), а для предварительного просмотра данных перед печатью щелкните по кнопке "Предварительный просмотр" (Print Preview).
8.3 Понижение уровня детализации в перекрестных запросах
Созданный перекрестный запрос выводит данные по всем товарам, по которым были размещены заказы в одном из месяцев 1994 года. Однако управленческому аппарату более высокого уровня обычно требуется информация в виде графика или диаграммы, с помощью которых можно проследить некоторую тенденцию. Таким образом, необходимо сократить число строк и столбцов, так чтобы создать на основе значений перекрестной таблицы удобный для анализа график.
Для создания перекрестного запроса, который выводит ежеквартальную выручку от продаж по типам товаров (а не значению поля "КодЗаказа"):
- Выберите команду "Файл, Сохранить как" и сохраните запрос под именем "Ежеквартальная выручка от продаж товаров по типам".
- Добавьте в запрос таблицу "Типы".
- Для того чтобы четко представлять себе связи между таблицами, щелкните по заголовку каждого списка полей и перетащите их, чтобы они заняли места, показанные на рис. 58.
- Перетащите поля "КодТипа" и "Категория" таблицы "Типы" на столбец "КолТовара". В ячейках "Перекрестная таблица" обоих столбцов выберите значение "Заголовки строк".
- Удалите столбцы "КодТовара" и "Марка", щелкнув по области выделения столбца, а затем нажав клавишу <Delete> (рис. 58).
- Введите в столбец "Выражение!" выражение Format ([ДатаРазмацания] ;
"""Квартал ""q"), которое определяет заголовки столбцов "Квартал 1", ..., "Квартал 4". Несколько кавычек требуется для указания того, что слово Квартал и пробел рассматриваются как строка, а q — как формат.
- Откройте бланк свойств и очистите содержимое свойства "Заголовки столбцов", а затем закройте бланк. Если не удалить фиксированные заголовки, то в перекрестной таблице не будет данных. Чтобы ускорить работу запроса, можно задать в поле свойства "Заголовки столбцов" четыре фиксированных названия: "Квартал 1", ..., "Квартал 4".
- Нажмите кнопку "Запуск" (рис. 59).
- Выберите команду "Вид, Режим SQL" для вывода инструкции SQL (рис. 60). Инструкция содержит операцию transform, в которой определяются данные, содержащиеся в таблице. В операции pivot задаются заголовки столбцов. После ключевого слова in указываются, если есть, фиксированные названия столбцов. Однако слова transform и pivot не являются зарезервированными словами ANSI SQL. Более того, в Access и ANSI SQL ключевое слово in интерпретируется по-разному.
- Сохраните запрос, выбрав команду "Файл, Сохранить" (File, Save). Запрос "Ежеквартальная выручка от продаж товаров по типам" используется ниже при рассмотрении многих вопросов.
Перекрестные таблицы, содержащие итоги, которые подводятся через определенные промежутки времени, например через каждый месяц или квартал, обычно используются в качестве данных для графиков.
Дата добавления: 2014-12-12; просмотров: 6201;