Глава 4. Запросы

Глава 4. Запросы

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

·         выбирать данные из таблиц по нужным критериям;

·         группировать записи и получать итоговые значения полей по группам, например, Sum, Max, Min и пр.;

·         получать данные из нескольких таблиц одновременно.

Прежде чем, строить дальше ИС "Заказы", разберем порядок и средства построения  запросов в Access. Запросы конструируют в закладке Запросы главного окна базы данных. В качестве примера составим запрос, выдающий полную информацию о клиентах в заказах. Для этого сформируем запрос к двум таблицам – Заказы и Клиенты. Проделайте следующие действия:

1. Выберите категорию Запросы, находясь в окне базы данных Access, нажмите кнопку Создать и выберите Конструктор. Откроется окно построителя запросов.

2. Добавьте в запрос таблицы Заказы и Клиенты (в случае конструирования существующего запроса, окно добавления вызывается кнопкой Добавление таблиц на Панели инструментов). Отметим, что здесь базовой таблицей для запроса является таблица Заказы.

3. Последовательно перетащите вниз, в строку с надписью Поле, показанные на рис. 4.1, поля из этих таблиц – эти поля и будут содержаться в итоговой таблице.

4. Закройте окно построителя запросов, сохраните запрос с именем ЗаказыКлиентов - в окне базы данных Access появится новый запрос. Откройте его - Вы увидите данные из двух таблиц в виде единой таблицы с выбранными полями – рис. 4.2.

Рис. 4.1 Окно построителя запросов

Рис. 4.2. Результат выборки запросом - итоговая таблица

Как Вы уже догадались, можно установить сортировку поля (здесь поля ОбщаяСумма). Можно также установить условие отбора (здесь >600). После таких установок, в режиме просмотра Вы увидите только записи с ОбщаяСумма>600, отсортированные по возрастанию. Отметим, что перейти из режима просмотра в режим построителя запросов и наоборот можно по кнопке с всплывающей подсказкой Вид - самой левой кнопке на Панели инструментов.

Как видно, с помощью простых манипуляций можно строить сложные запросы. Сам текст запроса в Access формируется автоматически. Его можно просмотреть, выбрав из кнопки-списка Вид пункт Режим SQL. Он записан на структурированном языке запросов Structured Query Language (SQL) и выглядит так:

SELECT Заказы.КодЗаказа, Заказы.ДатаЗаказа, Заказы.ОбщаяСумма, Клиенты.ФИО, Клиенты.[Тел/факс]

FROM Клиенты INNER JOIN Заказы ON Клиенты.КодКлиента = Заказы.КодКлиента

WHERE (((Заказы.ОбщаяСумма)>600))

ORDER BY Заказы.ОбщаяСумма;

Запрос можно записать непосредственно на языке SQL, но без крайней необходимости делать это не следует, поскольку Access корректно переводит запросы из оконного представления на язык SQL. Для большего понимания смысла и сложности правильного построения запросов попробуем перевести наш запрос на русский язык:

ВЫБРАТЬ ПОЛЯ Заказы.КодЗаказа, Заказы.ДатаЗаказа, Заказы.ОбщаяСумма, Клиенты.ФИО, Клиенты.[Тел/факс]

ИЗ Клиенты ОБЪЕДИНИВ С Заказы ПО ПОЛЯМ Клиенты.КодКлиента = Заказы.КодКлиента

ПО УСЛОВИЮ (ГДЕ) (Заказы.ОбщаяСумма)>600

ОТСОРТИРОВАВ ПО ВОЗРАСТАНИЮ ПОЛЯ Заказы.ОбщаяСумма;

Статистические расчеты в запросах

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

-         Sum – вычисляет значения сумм в группах;

-         Avg – среднее арифметическое;

-         Max – максимальное значение;

-         Min – минимальное значение;

-         Count – число записей в группе;

-         First – значение первой записи в группе;

-         Last – значение последней записи в группе;

-         StDev – среднеквадратичное отклонение (величина отклонения значений от среднего арифметического);

-         Var – дисперсия (квадрат значения среднеквадратичного отклонения);

-         Группировка – указывает, что для поля задана группировка по одинаковым значениям;

-         Выражение – задает более сложное выражение для групп;

-         Условие – используется для задания условия отбора записей в группе.

Пример: Необходимо узнать суммы продаж по дням. Для этого создайте запрос из одной таблицы Заказы и отберите в строку Поле 2 поля – ДатаЗаказа и ОбщаяСумма – рис. 4.3. Сгруппируйте записи, нажав на Панели инструментов кнопку с изображением суммы å, а для поля ОбщаяСумма параметр Группировка замените на функцию Sum. В результате при просмотре запроса суммы продаж будут вычислены по датам.

Рис. 4.3. Расчет сумм по датам в запросе

Запросы с параметрами

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

Пример: Требуется просмотреть заказы в некотором, заранее неизвестном диапазоне дат. Постройте следующий запрос – рис. 4.4:

Рис. 4.4. Запрос с двумя параметрами, задающими диапазон дат

В условие отбора поля ДатаЗаказа запишите оператор Between [Начальная дата] And [Конечная дата]. В переводе это означает: Между [Начальная дата] и [Конечная дата], где [Начальная дата] и [Конечная дата] есть параметры. Поскольку запрос заранее "не знает" их значений, он попросит Вас ввести значения этих параметров и выдаст записи в диапазоне введенных дат. Окно ввода значений параметров выглядит так:

Рассчитываемые поля

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

Пример: Проверить правильность расчета поля Сумма в таблице ЗаказаноТовара. Постройте запрос, как показано на рис. 4.5:

Здесь рассчитываемое поле называется НаСумму и содержит выражение [Количество]*[Цена]. При просмотре запроса значения, хранящиеся в поле Сумма, должны совпадать со значениями вычисленного поля НаСумму. Как видно, поле Сумма является избыточным в таблице ЗаказаноТовара, поскольку эти значения всегда могут быть рассчитаны с помощью запросов. Отметим, что здесь базовой для запроса является таблица ЗаказаноТовара.

Рис. 4.5. Запрос с рассчитываемым полем НаСумму

Пример: Рассчитать в запросе суммы продаж по датам (другой способ). Постройте запрос согласно рис. 4.6.

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

Пример: Рассчитать суммы продаж по клиентам. Для этого в запрос из предыдущего примера добавьте таблицу Клиенты, а в итоговой таблице поле ДатаЗаказа замените на поле ФИО из таблицы Клиенты.

Рис. 4.6. Запрос с группировкой и вычислением сумм групп

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

-         запросы на обновление изменяют записи в таблицах;

-         запросы на добавление добавляют записи в таблицы;

-         запросы на удаление удаляют записи из таблиц;

-         запросы на создание таблиц;

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

Рекомендуется всегда вначале создавать запросы на выборку и лишь после тщательного тестирования результатов выборки изменять тип запроса на другой. Для изменения типа запроса используется кнопка-список  ТипЗапроса на Панели инструментов.