Запросы

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

 

Упражнение

1. Мастер запросов

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

 В окне базы данных щелкните на кнопке Запросы (Queries).  Дважды щелкните на значке Создание запроса с помощью мастера (Create Query By Using Wizard).  В раскрывающемся списке Таблицы и запросы (Tables/Queries) первого окна мастера выберите таблицу Список (рис. 17.1).  В списке Доступные поля (Available Fields) щелкните на строке Дата.  Щелкните на кнопке >, чтобы переместить выделенное поле в список Выбранные поля.  Повторяя шаги 3-5, добавьте в список Выбранные поля поле, Описание таблицы Список, а также ноле Фамилия таблицы Контакты.  Щелкните на кнопке Далее.  Введите имя запроса Перечень контактов и щелкните на кнопке Готово.

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

Рис. 17.1. Мастер запросов Примечание

Чтобы база данных выглядела интереснее, в таблицу Список было добавлено несколько новых записей.

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

Рис. 17.2. Результат выполнения запроса  Результат выполнения запроса показан на рис. 17.2.

 

Упражнение 2. Конструктор запросов

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

 Чтобы переключиться в режим конструктора, выберите команду Вид > Конструктор (View > Design View). Окно конструктора показано на рис. 17.3. В его верхней части отображаются списки полей таблиц, к которым обращается запрос, и связи между таблицами. Нижняя область содержит бланк выбора полей таблиц, условий отбора и режимов сортировки. Чтобы добавить в запрос еще одно поле, выполните следующие шаги.  Переместите указатель на пункт Имя таблицы Контакты.

Рис. 17.3. Конструктор запроса

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

Примечание

Чтобы добавить в запрос сразу все поля таблицы, перетаскивайте пункт *. Если нужная таблица отсутствует в верхней части окна запроса, щелкните на кнопке Отобразить таблицу (Show Table) панели инструментов, выделите нужную таблицу или запрос в открывшемся окне диалога, щелкните на кнопке Добавить, а затем на кнопке Закрыть.

5.  Назначьте тот же режим сортировки для поля Имя. Теперь записи результата запроса будут упорядочены по алфавиту фамилий и имен. Имеющийся вариант связи добавляет в результат запроса только те записи связанных таблиц, в которых значения полей Код_Контакты равны. Так как для некоторых людей из таблицы Контакты нет записей в таблице Список, то информация о таких людях не включается в результат запроса. Чтобы запрос возвращал данные даже о тех людях (включенных в таблицу Контакты), с которыми не было никаких контактов, нашедших отражение в таблице Список, следует изменить параметры объединения.
6.  Дважды щелкните на линии связи.
7. В открывшемся окне диалога Параметры объединения (Join Properties) выберите положение переключателя Объединение всех записей из "Контакты" и только тех записей из "Список", в которых связанные поля совпадают (Include All Records From "Контакты" And Only Those Records From "Список" Where The Joined Fields Are Equal).  Щелкните на кнопке ОК. На одном конце линии связи появится стрелка, указывающая на смену режима объединения.

Примечание

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

9. Щелчком на кнопке Вид панели инструментов запустите запрос повторно. Результат выполнения запроса с учетом сортировки и нового режима объединения будет таким, как показано на рис. 17.5.  Закройте окно запроса.  В ответ на вопрос о необходимости сохранения новой структуры запроса ответьте Да.

Упражнение 3. Условие отбора

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

 Выделите в окне базы данных (рис. 17.4) значок запроса Перечень контактов.

Рис. 17.4. Окно базы данных

2. Щелкните на кнопке Конструктор. В бланке запроса щелкните на ячейке Условие отбора (Criteria) первого столбца правой кнопкой мыши и выберите в контекстном меню команду Построить (Build). Откроется окно построителя выражений.  В левом списке построителя щелкните на папке Операторы (Operators). В среднем списке выберите категорию Сравнения (Comparison).  В правом списке дважды щелкните на пункте Between, чтобы добавить этот оператор в ноле формулы. Щелчком выделите в поле формулы первый местозаполнитель «Выражение» («Ехрг»).  В левом списке построителя выражений двойным щелчком откройте папку Функции. 9. Щелкните на папке Встроенные функции, содержащей стандартные функции Access. В среднем списке построителя выражений щелкните на пункте Дата/время (Data/Time).  В правом списке дважды щелкните на функции DateValue, чтобы заменить ею местозаполнитель «Выражение».  Нажмите два раза клавишу —>, выделив местозаполнитель «stringexpr».  Введите текст "1.12.99".  Повторяя шаги 7-13, замените второй местозаполнитель «Выражение» на выражение DateValue ("31.12.99").

У вас должна получиться формула Between DateValue ("1.12.99") And DateValue ("31.12.99"). Она проверяет условие нахождения даты в интервале от 1 до 31 декабря 1999 г., то есть отбирает те записи, значение поля Дата которых относится к декабрю 1999 г. Примечание

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

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

Рис. 17.5. Скрытие поля

17.  Щелчком на кнопке Запуск (Run) панели инструментов выполните запрос. 

Упражнение 4. Итоговый запрос

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

 В окне базы данных щелкните на кнопке Запросы.  Дважды щелкните на значке Создание запроса в режиме конструктора (Create Query In Design View).  В открывшемся окне диалога (рис. 17.6) выделите строку Контакты. 4. Щелчком на кнопке Добавить добавьте выбранную таблицу в верхнюю область конструктора запроса. 5. Выделите пункт Список и снова щелкните на кнопке Добавить. 6. Щелчком на кнопке Закрыть закройте окно диалога Добавление таблицы (Show Table). Списки полей двух таблиц, соединенные линией связи, появятся в окне конструктора. 7. Щелкните на кнопке Групповые операции (Totals) панели инструментов. В бланке запроса появится дополнительная строка Групповая операция: (Total), позволяющая выполнять статистические операции со значениями конкретных полей.

Рис. 17.6 . Добавление таблицы

8.  Перетащите поле Фамилия в ячейку Поле (Field) первого столбца конструктора.  В ту же ячейку второго столбца перетащите поле Имя таблицы Контакты.  В третий, четвертый и пятый столбцы бланка запроса перетащите поле Дата таблицы Список (рис. 17.7).

Рис. 17.7. Запрос с групповыми операциями

11.  В раскрывающемся списке ячейки Групповая операция (Total) третьего столб ца бланка запроса выберите пункт Min. В той же ячейке четвертого столбца выберите пункт Мах. В пятом столбце задайте групповую операцию Count. Групповые операции построенного запроса обработают все записи таблицы Список, соответствующие конкретному человеку из таблицы Контакты, и вместо самих данных таблицы Список выведут в соответствующее поле результата запроса только значение величины, вычисляемой по определенной формуле. Доступные групповые операции перечислены в табл. 17.1.

ТАБЛИЦА 17.1 . Групповые операции

 

 

 

                    Примечание

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

14. Щелкните на кнопке Вид, чтобы выполнить запрос. Появится таблица с пятью столбцами. Два первых столбца содержат фамилии и имена людей. По ним выполняется группировка, то есть расчет значений остальных полей запроса выполняется для записей таблицы Список, сопоставляемых с одним человеком. Как уже говорилось выше, соответствие контакта таблицы Список и человека из таблицы Контакты определяется полями Код_Контакты, с помощью которых осуществляется связь этих двух таблиц. Третий и четвертый столбцы запроса выводят соответственно дату первого (функция Min) и последнего (функция Мах) контакта с данным человеком. Пятый столбец содержит количество записей в таблице Список (функция Count), соответствующих. данному человеку, то есть число контактов с ним. Единственный недостаток построенного запроса — это непонятные имена столбцов. Давайте скорректируем их.  Щелчком на кнопке Вид вернитесь в конструктор запроса.  В ячейке Поле третьего столбца замените имя Дата на текст Дата первого контакта: Дата. Правая часть этого выражения, расположенная правее двоеточия, по-прежнему задает имя поля, а левая определяет название столбца результата запроса. Таким образом, любому столбцу запроса можно назначить произвольное имя.  В ячейке Поле четвертого столбца введите Дата последнего контакта: Дата.  В первой строке пятого столбца бланка'запроса введите Число контактов: Дата.

Примечание

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

19.  Снова щелкните на кнопке Вид. 
20. Закройте запрос.  Для сохранения изменений структуры щелкните на кнопке Да.  В окне диалога Сохранение (Save As) введите имя Итоговый запрос и щелкните на кнопке ОК.

Упражнение 5. Запрос действия

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

 В окне базы данных щелкните на кнопке Таблицы.  Выделите таблицу Список, данные которой нужно обновить.  В палитре кнопки Новый объект (New Object) выберите пункт Запрос (Query). Откроется окно диалога Новый запрос (New Query), показанное на рис. 17.8. Подобное окно открывается и при щелчке на кнопке Создать (New) окна базы данных. Оно позволяет выбрать наиболее удобный способ создания объекта.

Рис. 17.8. Создание запроса

4.  Дважды щелкните на строке Конструктор (Design View). Таблица Список, выделенная в окне базы данных на шаге 2, автоматически появится в окне конструктора запросов. Перетащите в бланк запроса поле Дата, значение которого нужно обновлять.

Примечание

Название поля, заключенное в квадратные скобки, является ссылкой на значение поля. Чтобы сослаться на поле другой таблицы, нужно в начале указать имя таблицы, а затем имя поля (оба имени в квадратных скобках) и разделить их восклицательным знаком. Например [Контакты]! [Фамилия].

6.  В палитре кнопки Тип запроса (Query Type) выберите пункт Обновление (Update Query). Описание всех возможных вариантов запросов, предлагаемых программой Access, приведено в табл. 17.2. Структура бланка запроса видоизменяется в соответствии с типом запроса. В бланке выбранного варианта запроса на обновление появляется поле Обновление (Update To), в которое нужно ввести новое значение поля. Чтобы изменить месяц даты с ноября на декабрь, достаточно прибавить к дате 30 дней.  Введите в ячейку Обновление (Update To) формулу [Дата] +30.

ТАБЛИЦА 17.2. Варианты запросов

Тип	Описание
Выборка (Select) ;	Выборка данных в таблицу результата запроса на основе указанных условий отбора
Перекрестный (Crosstab)	Результат запроса выводит статистические значения (сумму, количество или среднее) для одного из полей таблицы в зависимости от двух параметров других полей таблицы, задающих заголовки строк и столбцов результата перекрестного запроса
Создание таблицы (Make-Table)	Создание новой таблицы в текущей или в другой базе данных на базе информации из имеющихся таблиц
Обновление (Update)	Обновление данных таблицы
Добавление (Append)	Добавление набора записей в таблицу
Удаление (Delete)	Удаление записей таблицы в соответствии с указанным критерием

8. Чтобы обновлялись только даты, относящиеся к ноябрю 2000 года, введите в ячейку Условие отбора формулу Between DateValue ("1.11.99") And DateValue ("30.11.99"), которая подробно обсуждалась в третьем упражнении этого занятия (рис. 17.12). Закройте запрос, сохранив его под именем Обновление.  Щелкните на кнопке Таблицы окна базы данных и двойным щелчком на значке Список откройте эту таблицу. В ней есть четыре записи, относящиеся к ноябрю 2000 года.

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

11.  В окне базы данных щелкните на кнопке Запросы.  Дважды щелкните на значке Обновление.

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

Рис. 17.9. Запрос на обновление

13. Щелкните на кнопке Да и изучите изменения, произошедшие с данными таблицы Список.

Фильтры

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

 

Упражнение 6. Установка фильтра

Установка фильтра — простейший способ отбора части записей в таблице, запросе или форме. В этом упражнении с помощью фильтра будет выделено несколько записей таблицы Контакты.

 В окне базы данных щелкните на кнопке Таблицы, а затем дважды на значке Контакты.  В столбце Фамилия щелкните на ячейке с фамилией Петров. Щелкните на кнопке Фильтр по выделенному (Filter By Selection). В результате видимыми останутся только те записи, в поле Фамилия которых присутствует значение Петров (рис. 17.10). Чтобы отменить влияние фильтра, щелкните на кнопке Удалить фильтр (Remove Filter).

Примечание

Access запоминает последний применявшийся фильтр. Его можно снова назначить простым щелчком на кнопке Применение фильтра (Apply Filter). Это та же самая кнопка, с помощью которой отменяется действие фильтра, но теперь она будет иметь другое название.

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

Рис. 17.10. Фильтрация записей

5.  Выберите команду Записи > Фильтр > Расширенный фильтр (Records > Filter > Advanced Filter/Sort). Откроется окно, похожее на конструктор запроса. Критерий равенства поля Фамилия значению Петров уже будет присутствовать в бланке фильтра.  Перетащите поле Рабочий Телефон во второй столбец бланка.

Рис. 17.11. Окно расширенного фильтра 

7.  В ячейку Условие отбора введите формулу <>Null (рис. 17.11).

Значение любого пустого поля равно величине NuIL Условие < >NuII проверяет поле на неравенство величине Null, то есть отбирает все непустые поля.

8.  Выберите команду Фильтр > Применить фильтр (Filter > Apply Filter/Sort). Настроенный" фильтр будет применен к таблице Контакты. В результате в ней останутся только две записи.  С помощью команды Записи > Удалить фильтр (Records > Remove Filter/Sort) отмените фильтрацию.

Упражнение 7. Преобразование в запрос

К сожалению, назначение нового фильтра автоматически стирает все предыдущие, которые уже невозможно применить повторно. Если фильтр достаточно сложный, его многократная настройка может отнимать много времени, и вам, вероятно, захочется как-то спасти результат титанического труда. Access предлагает простой способ сохранения фильтров. Так как окно фильтра подобно конструктору запроса, было бы логично записать фильтр в виде запроса с теми же условиями отбора. Чтобы превратить фильтр в запрос, выполните следующие шаги.

Командой Записи > Фильтр > Расширенный фильтр (Records > Filter > Advanced Filter/Sort) активизируйте окно сложного фильтра, созданного в предыдущем упражнении.  Выполните команду Файл > Сохранить как запрос (File > Save As Query).  В открывшемся окне диалога введите имя Фил ьтр и щелкните на кнопке ОК. Закройте окно фильтра.  В окне базы данных появился новый запрос с названием Фильтр. Дважды щелкните на значке этого запроса.

Откроется окно результата выполнения запроса, записи которого будут повторять записи фильтрованной таблицы Контакты. Этим запросом можно пользоваться когда угодно, выводя на экран записи, отобранные по соответствующим критериям. Более того, любой запрос можно превратить в фильтр. Это делается следующим образом.

6.  Щелчком активизируйте окно таблицы Контакты (если таблица закрыта, откройте ее).  Чтобы назначить новый фильтр (и стереть предыдущий), щелкните на ячейке с фамилией Петров правой кнопкой мыши и выберите в контекстном меню команду Исключить выделенное (Filter Excluding Selection). В таблице останутся только те записи, в поле Фамилия которых нет слова Петров.  Щелчком на кнопке Удалить фильтр отмените фильтрацию данных. Теперь давайте вернем прежний сложный фильтр.  Выберите команду Записи > Фильтр > Расширенный фильтр.   Выберите команду Файл > Загрузить из запроса (File > Load From Query).  В открывшемся окне диалога выделите запрос Фильтр и щелкните на кнопке ОК.

Старые условия фильтрации появятся в бланке окна фильтра. Теперь для применения сохраненного фильтра достаточно щелкнуть на кнопке Применение фильтра панели инструментов.

 

Контрольное упражнение

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

 Запустите мастер запросов.  Добавьте в запрос поля Фамилия и Адрес таблицы Контакты, а затем поля Дата и Описание таблицы Список.  Настройте итоговый запрос, подсчитывающий количество записей.   Как с помощью мастера, создать итоговый запрос?
4.  Переключитесь в режим конструктора.  Скройте третье и четвертое поля запроса. Закройте запрос, сохранив его под именем Количество записей. Откройте таблицу Список. Включите фильтр, оставляющий только записи, относящиеся к фамилии Леонидов. Откройте окно сложного фильтра.  Измените условие фильтрации так, чтобы оставались также записи, касающиеся Петрова. Какое условие отбора необходимо добавить ?
11.  Назначьте обновленный фильтр.  Сохраните настроенный фильтр в виде запроса.  Откройте запрос Обновление в конструкторе запросов. Просмотрите его в режиме SQL.  Как включить режим просмотра SQL?
15.  Закройте базу данных.

Подведение итогов

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

 

---

---