Перекрестный запросЛабораторная работа № 7 Цель работы: Сформировать умения для создания перекрестных запросов.
Перекрестные запросы предназначены для группирования данных и представления их в компактном виде, напоминающем электронную таблицу. Перекрестный запрос позволяет представить большой объем данных в виде, удобном для восприятия, анализа и сравнения. Более того, перекрестный запрос удобно использовать в качестве базового при создании отчета. Следует иметь в виду, что в результате выполнения перекрестного запроса получается не динамический, а статический набор записей, то есть такой набор записей, который нельзя обновлять. Проще всего создать перекрестный запрос с помощью мастера по разработке перекрестных запросов. При необходимости перекрестный запрос можно создать без помощи мастера. Рассмотрим два этих способа создания перекрестного запроса. Создание перекрестного запроса с помощью мастера выполняют следующим образом: – Находясь в окне базы данных, выбирают корешок Запрос и нажимают кнопку Создать. – В диалоговом окне Создание запроса нажимают кнопку Мастера по разработке запросов. – В первом окне Мастера по разработке запросов выбирают пункт Перекрестный запрос. – Далее выполняют инструкции, появляющиеся в диалоговых окнах. В последнем диалоговом окне нажимают кнопку Готово. Для создания перекрестного запроса без помощи мастера надо выполнить следующую последовательность действий: 1. В окне базы данных выберите корешок Запрос и нажмите кнопку Создать. В появившемся диалоговом окне Создание запроса нажмите кнопку Новый запрос. 2. Выберите таблицы или запросы, содержащие поля, которые следует включить в запрос. Перенесите нужные поля в строку Поле бланка QBE и задайте условия отбора. 3. Выберите команду Перекрестный в меню Запрос или нажмите кнопку Перекрестный на панели инструментов. В бланке QBE появятся строки Групповые операции и перекрестная таблица. По умолчанию ячейки Групповые операции, соответствующие каждому полю, включенному в запрос, будут содержать надпись Группировка. 4. Установите указатель в ячейку Перекрестная таблица, соответствующую полю, которое содержит заголовки строк, и нажмите кнопку мыши, а затем нажмите кнопку раскрытия списка и выберите строку Заголовки строк. Можно указать несколько полей с заголовком строк. В ячейке Групповые операции, соответствующей по крайней мере одному из этих полей, должна содержаться надпись Группировка. 5. Установите указатель в ячейку Перекрестная таблица, соответствующую полю, которое содержит заголовки столбцов, и нажмите кнопку мыши, а затем нажмите кнопку раскрытия списка и выберите строку Заголовки столбцов. Только одно поле в перекрестном запросе может содержать заголовки столбцов. Ячейка Групповые операции, соответствующая этому полю, должна содержать надпись Группировка. 6. Установите указатель в ячейку Перекрестная таблица, соответствующую полю, которое содержит значения для вычислений, и нажмите кнопку мыши, а затем нажмите кнопку раскрытия списка и выберите строку Значения. Только одно поле в перекрестном запросе может содержать значения для вычислений. Если поле следует использовать для группирования, сортировки или размещения условий отбора, но не следует включать в результирующий набор записей, то нажмите кнопку раскрытия списка в ячейке Перекрестная таблица, соответствующей этому полю, и выберите строку не выводить. 7. Установите указатель в ячейку Групповые операции, соответствующую полю, которое содержит значения для вычислений, и нажмите кнопку мыши, а затем нажмите кнопку раскрытия списка и выберите тип групповой операции (например, Sum, Max или Count). Поле, которое содержит значения для вычислений, нельзя использовать для группирования; оно должно содержать результат выполнения групповой операции. 8. Для вывода на экран полученного набора записей выберите команду Таблица в меню Вид или нажмите кнопку Режим таблицы на панели инструментов. Следует иметь в виду, что перекрестный запрос может содержать несколько полей с заголовками строк, но только одно поле с заголовками столбцов. Если в перекрестный запрос следует включить несколько полей с заголовками столбцов, но только одно поле с заголовками строк, поменяйте местами заголовки строк и столбцов.
Рис. 1. Окно в режиме конструктора для запроса Заказы книг по годам. Рассмотрим следующий пример. Требуется создать список фамилий читателей, в котором для каждого читателя будет указано общее количество заказанных книг за весь период пользования услугами библиотеки и количество книг, заказанных в каждом году данного периода. Для решения этого примера нам понадобятся две таблицы из базы данных Библиотека: Читатели и Выдача книг. Разместите списки этих таблиц в верхней части окна запроса, как это сделано на рис. 1. В бланке QBE окна запроса надо заполнить четыре столбца. Переместите нужные поля из верхней части окна запроса в строку Поле бланка QBE. Первые два столбца должны определять заголовки строк перекрестной таблицы: Фамилия и Итого. Для того чтобы появились в бланке QBE строки Групповая операция и Перекрестная таблица, надо выполнить команду Перекрестная в меню Запрос или нажать кнопку Перекрестный на панели инструментов. После этого заполните строки Групповая операция и Перекрестная таблица. Для первого столбца укажите групповую операцию Группировка, а для второго – Count. Третий столбец должен определять заголовки столбцов перекрестной таблицы. В нашем примере в качестве заголовков столбцов должны быть выбраны годы из поля Дата заказа таблицы Выдача книг. Для того чтобы выбрать элемент год из значений Дата заказа можно использовать функцию Format. В четвертом столбце бланка QBE надо указать, какие данные будут выведены в перекрестной таблице под заголовками столбцов. С этой целью указано вычисляемое значение, которое определяется функцией Count. Набор данных, созданный в результате выполнения перекрестного запроса, приведен на рис. 2. Сохраните его под именем Заказы книг по годам.
Рис. 2. Результат выполнения запроса Заказы книг по годам.
Рассмотрим еще один пример перекрестного запроса, иллюстрирующий использование постоянных заголовков столбцов. Требуется создать запрос, который будет выводить информацию о количестве выдач каждой книги на протяжении всего периода работы библиотеки, а также суммарное количество выдач каждой книги по месяцам данного периода. Вид бланка QBE для решения примера приведен на рис. 3. Первые два столбца этого бланка определяют заголовки строк для результирующей таблицы запроса. Третий столбец используется для задания заголовков столбцов. Как и в предыдущем примере для выбора месяца из поля Дата заказа используется функция Format. Поскольку данный столбец используется для группирования записей, в строке Групповая операция бланка QBE указана операция Группировка. Последний столбец указывает, что для определения количества выдач книг с конкретным кодом книги используется групповая операция Count и в требуемой таблице в качестве значений ячеек, расположенных на пересечении строки с кодом книги и столбца с названием месяца, будет располагаться значение этой операции.
Рис. 3. Вид бланка QBE для запроса Выдача книг по месяцам.
Поскольку мы желаем получить результирующую таблицу перекрестного запроса, содержащую все названия месяцев, даже если некоторые из них отсутствуют в записях базы данных, надо определить эти названия при задании свойств запроса. Заголовки столбцов перекрестного запроса, определенные как свойства запроса, называются постоянными. Постоянные заголовки столбцов имеют одно важное преимущество по сравнению с обычными заголовками столбцов, рассмотренными в предыдущем примере, использование их повышает скорость выполнения перекрестного запроса. Другое преимущество состоит в следующем: пользователь сам может задать очередность вывода заголовков столбцов, кроме того, он может уменьшить или увеличить количество заголовков столбцов в наборе записей независимо от наличия значений для поля, которое используется при выборе значений для заголовков столбцов. Определим постоянные заголовки столбцов для нашего примера. Будем считать, что к настоящему времени бланк QBE имеет такой вид, как на рис. 3. Выделите поле Выражение1 (напомним, что оно используется для задания заголовков столбцов) и нажмите кнопку Свойства. После этого на экране появится бланк свойства запроса. В ячейку Заголовки столбцов введите названия месяцев, которые надо использовать в качестве заголовков столбцов (см. рис. 4). Введенные заголовки должны полностью совпадать с соответствующими значениями в полях базы данных. Разделяются заголовки столбцов точкой с запятой или другим разделителем, который установлен на Панели управления Windows. При этом кавычки можно не вводить – они появятся после нажатия клавиши Enter или при перемещении курсора в другую ячейку.
Рис. 4. Вид свойства запроса для постоянных заголовков столбцов. Для вывода на экран полученного набора записей надо выполнить команду Таблица в меню Вид или нажать кнопку Режим таблицы на панели инструментов. Выполните одно из указанных действий, и вы увидите на экране набор записей примерно в таком виде, в каком он приведен на рис. 5. Сохраните этот запрос под именем Выдача книг по месяцам.
Рис. 5. Результат выполнения запроса с постоянными заголовками столбцов.
Задание
1. Измените решение последнего примера так, чтобы перекрестная таблица содержала информацию о выдаче книг не за весь период работы библиотеки, а только за 2007 год. Полученный запрос назовите Заказы книг в 2007 году. 2. Создайте в электронной таблице MS Excel таблицу телефонных звонков, приведенную ниже.
Сделайте экспорт данной таблицы в систему управления базами данных MS Access. В MS Access создайте запрос, который для всех абонентов телефонной сети определяет количество звонков в каждый город и суммарную продолжительность звонков.
|
