Запросы на выборку и их использование

Наиболее часто используемым запросом является запрос на выборку. После выполнения запроса на выборку Access создает новый набор записей, содержащий отобранные данные. Такой набор физически не существует в БД, но работать с ним можно как с обычной таблицей.

Запросы можно строить с помощью Мастера запросов или самостоятельно в режиме Конструктора.

Для создания запроса на выборку без использования Мастера надо в окне БД выбрать объект Запросы, нажать кнопку Создать и в диалоговом окне Новый запрос выбрать команду Конструктор. В открывшемся окне Конструктора запросов можно создавать запрос на выборку из одной или нескольких связанных таблиц. В качестве источника данных для запроса можно использовать не только таблицу, но и другой ранее составленный запрос.

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

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

Если запрос строится на нескольких таблицах или запросах, то они должны быть связаны. Если таблицы не связаны, то Access рассматривает каждую таблицу как независимый объект и позволяет составлять любую возможную комбинацию из полей в окне Конструктора.

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

Условие отбора позволяет определить, какие именно записи следует отобрать с помощью запроса. Обычно условие отбора представляет собой конкретное значение того или иного поля. Это значение указывается в строке Условие отбора бланка запроса. Символьные значения заключаются в кавычки, Access сам добавляет кавычки к введенному тексту. Календарные даты и время заключаются в символы #. При определении условий отбора можно использовать операторы сравнения =, <, >, <=, >=, <> и логические операторы OR и AND. Значения, введенные в разных строчках бланка запроса, рассматриваются как операнды операции ИЛИ. Значения, введенные в разных столбцах, рассматриваются как операнды логического И.

При определении условия отбора необходимо использовать те значения поля, которые хранятся в таблице, но не подстановочные значения. Пусть, например, необходимо отобрать записи о покупках, сделанных клиентом РГРТУ, и в качестве источника данных используется таблица ПОКУПКИ. Тогда в условии отбора для поля КодКлиента необходимо указать код РГРТА, т. е. цифру 4. Если этот же запрос создать на двух таблицах - ПОКУПКИ и КЛИЕНТЫ, то в качестве условия отбора для поля ИмяКлиента надо ввести слово РГРТУ. В том случае, когда условие отбора содержит значки подстановки ? или *, условие (даже число) надо заключать в апострофы.

В запрос можно добавлять вычисляемые поля, содержимое которых является результатом арифметических операций над определенными полями таблиц. Для создания вычисляемого поля надо в свободном столбце бланка запроса в строке Поле ввести имена полей, являющихся операндами, и указать оператор. Здесь же нужно задать имя вычисляемого поля. Например, можно составить запрос, формирующий список покупателей, названий купленных ими книг, количество купленных экземпляров каждой из книг, цену книги и стоимость каждой покупки. В бланке такого запроса надо указать поля КодКлиента (таблица ПОКУПКИ), КодКниги (таблица ПОКУПКИ), Количество (таблица ПОКУПКИ), ЦенаПокупки (таблица КНИГИ) и вычисляемое поле

Сумма:[ЦенаПокупки]*[Количество]

Выражение для вычисляемого поля удобнее вводить в Область ввода. Для ее вызова надо после щелчка в свободной ячейке нажать Shift+F2.

Если в бланк этого запроса добавить поле КодПокупки, установить для него порядок сортировки По возрастанию и снять флажок вывода на экран, то отобранные записи будут выводиться в том же порядке, что и в таблице ПОКУПКИ.

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

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

Существует девять функций для выполнения групповых операций. В их числе:

Sum - возвращает сумму всех значений данного поля в каждой группе;

Avg – возвращает среднее арифметическое значений полей в группе;

Min, Max – возвращает наименьшее или наибольшее значение в каждой группе,

Count - возвращает число записей в группе, для которых значения данного поля отличны от Null.

Групповые операции можно выполнять и над вычисляемыми полями.

Так, например, для подведения итогов по объемам покупок, сделанных каждым из клиентов, можно составить итоговый запрос на таблицах ПОКУПКИ и КНИГИ В качестве источника данных для итогового запроса можно использовать также рассмотренный выше запрос с вычисляемым полем. В бланк итогового запроса надо включить поля КодКлиента, Количество из таблицы ПОКУПКИ и создать вычисляемое поле Сумма так, как это описано выше. Для первого из полей надо оставить установку Группировка, а для двух других полей эту установку следует заменить итоговой функцией Sum. В результате выполнения запроса сформируется список, содержащий по одной записи для каждого клиента с итоговыми значениями – общим количеством купленных этим клиентом книг и общей стоимостью всех сделанных им покупок.

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

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

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

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

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

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

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

в ответ на запрос, имена клиентов будут располагаться слева сверху вниз (по строкам), а названия разделов - вверху слева направо (по столбцам). Кроме того, в таблице появится столбец, содержащий суммарное количество покупок для каждого клиента. Для создания такого запроса придется объединить данные из нескольких таблиц, поэтому вначале придется создать запрос, объединяющий данные, а потом создать перекрестный запрос, основанный на объединяющем запросе. Объединяющий запрос должен сформировать таблицу с полями КодКлиента (таблица ПОКУПКИ), Раздел (таблица РАЗДЕЛЫ), Количество (таблица ПОКУПКИ).

Перекрестный запрос проще всего создать при помощи Мастера запросов. Для вызова Мастера надо в окне БД для объекта Запросы нажать кнопку Создать и выбрать из списка строку Перекрестный запрос. На первом шаге работы Мастера надо установить флажок Запросы и выбрать имя объединяющего запроса. На следующих шагах следует выбрать в качестве заголовков строк поле КодКлиента, для заголовков столбцов поле Разделы, в списке функций для поля Количество выбрать Сумма и определить имя запроса. Готовый запрос можно просмотреть в режиме таблицы.