Создание и использование запроса

Для просмотра, изменения и анализа данных используются запросы. Запросы также можно использовать в качестве источников записей для форм, отчетов и страниц доступа к данным. В общем случае запрос - это вопрос о данных. В Microsoft Access есть несколько типов запросов (на выборку, запрос с параметрами, перекрестные запросы, запрос на изменение таблицы, запросы SQL).

В спроектированной нами таблице Учащиеся содержится вся информация, необходимая для решения поставленной нами задачи. Но как этой информацией пользоваться? Как узнать, например, сколько человек учится в 4А классе или у скольких учащихся день рождения в апреле? Не сидеть же перед компьютером с калькулятором, ручкой и бумагой! Решить эту проблему поможет запрос.

Запрос-выборка в MS Access. Простейший из запросов — запрос-выборка — это производная таблица, которая содержит те же структурные элементы, что и обычная таблица (столбцы-поля и строки), и формируется на основе фактических данных базы данных. Запрос на выборку отбирает данные из одной или более таблиц по заданным условиям, а затем отображает их в нужном порядке. Запрос можно создать с помощью мастера или самостоятельно, в режиме конструктора, выбрав таблицы или запросы, содержащие нужные данные, и заполнив бланк запроса.

При создании макета запроса (т. е. производной таблицы) в общем случае нам необходимо выполнить следующие базовые операции:

· указать системе, какие поля и из каких таблиц мы хотим включить в запрос;

· описать вычисляемые поля, т.е. поля, значения которых являются функциями значений существующих полей (например, средняя успеваемость - это среднее арифметическое значение всех оценок);

· описать групповые операции над записями исходных таблиц (например, нужно ли объединить группу записей и указать условие отбора (мальчики из 5Б класса) в одну и просуммировать значение их роста для расчета среднего значения роста учащихся класса).

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

Создание запроса-выборки. В общем случае для создания произвольного запроса используется универсальный язык SQL (Structured Query Language — Структурный язык запросов). В предложении этого языка (Select - Выбрать) можно описать все базовые операции: какие поля и откуда выбрать, какие вычислить, как их сгруппировать (просуммировать, пересчитать, найти среднее и т.п.) и при каких условиях включить записи в выборку. Однако в реальности пользоваться этим языком могут только специалисты (или очень грамотные пользователи), а для обычных людей разработчики придумали упрощенный механизм создания запроса, называемый QBE (Query By Example – запрос по образцу). Вам предлагают бланк QBE - некую модель, заготовку запроса, и на этом бланке, пользуясь определенными правилами, вы сообщаете системе о своих планах: помечаете поля, вводите выражения, значения и т.п. На основе заполненного вами бланка система сама создает соответствующее предложение Select и сама выполняет его.

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

Для создания запроса выберите в окне базы данных Группа вкладку Запросы в списке Объекты и нажмите кнопку «Создать на панели инструментов». В диалоговом окне Новый запрос выберите мастер Простой запрос и нажмите кнопку «ОК».

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

Рис. 17. Выбор полей таблицы для формирования запроса

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

Следуя инструкциям Мастера, выберите вариант Подробный отчет и щелкните кнопку «Далее». В последнем диалоговом окне задайте имя запроса Запрос1 и запустите полученный запрос, выбрав вариант Открыть запрос для просмотра данных и щелкнув кнопку «Готово».

Выбрав команду Режим SQL в меню Вид, мы можем увидеть текст созданного запроса на языке SQL:

SELECT [Учащиеся].[Фамилия], [Учащиеся].[Имя], [Учащиеся]. [Отчество], [Учащиеся].[Дата рождения], [Учащиеся].[Пол], [Учащиеся].[Домашний адрес], [Учащиеся].[Класс] FROM Учащиеся;

Как видно из текста, в запросе на языке SQL записана команда выбора из таблицы Учащиеся и описан порядок размещения полей таблицы в таблице-результате действия запроса.

Примечание. Для получения подробной справки о создании запроса вызовите справочную систему Access и на вкладке Содержание изучите справочную информацию в разделе Работа с запросами, тема Создание запроса.

Если получился не тот запрос, который был нужен, можно снова запустить мастер или изменить этот запрос в режиме конструктора. Для изменения запроса в режиме конструктора, открыв окно запроса, щелкните кнопку «Конструктор» на панели инструментов базы данных. После этого откроется окно Конструктор запроса, показанное на рис. 18.

Рис. 18. Изменение запроса в режиме Конструктора

В верхней части этого окна показана схема данных выбранных таблиц с указанием связей и имен всех полей, в данном случае - таблица Учащиеся. В нижней части окна размещается бланк QBE, который представляет собой макет некоей таблицы. Столбцы этой таблицы соответствуют полям создаваемого запроса, а число строк переменно и зависит от состояния флажков «Имена таблиц» и «Групповую операции» (на панели инструментов или в пункте Вид). В строке Поле: указываются имена столбцов (полей) создаваемого запроса. Существующее имя можно выбрать из раскрывающегося списка (щелкнув мышью на поле) или просто перенести в ячейку Поле: методом «Drag-and-Drop» из таблицы Учащиеся в верхней части окна запроса. В первом столбце разместите поле Фамилия, во втором - Имя и т.д. В ячейке Имя таблицы: (она появляется в бланке, если установлен флажок «Имена таблиц») отображается имя таблицы, которая содержит выбранное поле (таблица Учащиеся).

Если бы в строке Поле: размещалось вычисляемое поле, тогда в ячейке этой строки нужно было бы ввести формулу: <Имя поля>: <Выражение>

Например: Стоимость: [Количество]* [Цена]

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

Если бы мы собирались проводить в запросе групповые операции (т.е. объединять записи в группы), нужно было бы включить флажок «Групповые операции» - появляется строка Групповая операция: После щелчка на любом поле этой строки появится список типов групповых операций: Группировка, Sum, Avg, Count, Выражение и др. Например, если в качестве типа групповой операции в столбце указать Count (Сосчитать), то мы получим количество записей, соответствующих запросу в этой группе.

В строках Условие отбора: и или: можно указать условные или логические выражения, которые позволят нам отобрать для запроса только записи, удовлетворяющие заданному условию. Обратите внимание на следующие соглашения, предусмотренные в Access.

1. Условные выражения, набранные в разных столбцах строки Условие отбора: по умолчанию соединяются между собой знаком AND. Например, если соседние столбцы имеют имена Пол и Класс и вы набрали в них =Тrue и =5Б, то тем самым вы сформулировали логическое выражение: Пол =True и Класс =5Б.

2. Условные выражения, набранные в соседних строках одного итого же столбца, соединяются между собой знаком OR. Например, если столбец имеет имя Класс и мы набрали в строке Условие отбора: =4А, а в строке или: =5А, то тем самым мы сформулировали логическое выражение: [Класс]= 4А OR [Класс]= 5А.

Измените запрос, добавив условие отбора только тех учащихся, которые не имеют мужской пол. Для этого, установив курсор в столбце Пол на строке Условие отбора и щелкнув правую кнопку мыши, откройте контекстное меню и выберите в нем команду Построить. В списке папок построителя выражений, щелкнув на папке Запрос1, раскройте список полей. Дважды щелкнув поле Пол, включите это поле в область выражений, затем, щелкнув оператор «=», включите его в выражение. Выбрав папку Константы, в списке констант дважды щелкните на значении «Ложь», как показано на рис.19.

Рис. 19. Создание логического выражения в окне Построитель выражений

Щелкнув кнопку «ОК», завершите построение логического выражения.

Выбрав в меню Вид команду Режим SQL, можно просмотреть запись созданного запроса на языке SQL:

SELECT Учащиеся.Фамилия, Учащиеся.Имя, Учащиеся.Отчество, Учащиеся.[Дата рождения], Учащиеся.Пол, Учащиеся.[Домашний адрес), Учащиеся.Класс

FROM Учащиеся

WHERE ((|Пол]=false))

ORDER BY Учащиеся.Фамилия;

Как видно из текста, в описание запроса добавлено выражение WHERE (([Пол]=false)), которое отбирает в таблице те записи об учащихся, в которых поле Пол не является мужским. После просмотра записи выберите в меню Вид команду Режим таблицы и просмотрите таблицу - результат запроса.

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

Для каждого поля, которое предполагается использовать как параметр, следует ввести в ячейку строки Условие отбора текст приглашения, заключенный в квадратные скобки. Это приглашение будет выводиться при запуске запроса. Текст приглашения должен отличаться от имени поля, но может включать его. Для поля, которое выводит даты, можно ввести приглашения следующего вида «Введите начальную дату:» и «Введите конечную дату:», чтобы задать границы диапазона значений.

Откройте созданный ранее запрос на выборку Запрос1 в режиме конструктора и удалите условие отбора [Пол] = Ложь. Выключите флажок выдачи столбца Класс на экран, а в поле Условие отбора: (в этом столбце!) наберите в квадратных скобках текст приглашения: [Какой класс]. Для просмотра результатов нажмите кнопку Вид на панели инструментов. После этого на экран будет выведено окно с приглашением ввести параметр. Введите параметр 4В и щелкните «ОК» для выполнения выборки записей из таблицы в соответствии с заданным параметром-условием.

После этого таблица-результат будет выглядеть, как показано на рис.20.

Рис.20. Результат отбора записей по условию [Класс]=«4В», заданному как параметр

Чтобы вернуться в режим конструктора запроса, снова нажмем кнопку «Вид» на панели инструментов. Отмените запрос с параметром и измените условие отбора данных, чтобы в запросе отображались данные об учащихся, родившихся летом 1988 г. Для этого восстановите флажок выдачи столбца Класс на экран, а в полеУсловие отбора: (в этом столбце!) удалите текст приглашения. В ячейку строки Условие отбора поля Дата рождения введите текст условия:

[Дата рождения]>#01.06.88# AND [Дата рождения]<#01.09.88#.

Для проверки действия созданного запроса выберите команду Режим таблицы в меню Вид. Таблица-результат запроса выглядит следующим образом (рис.21).