Тема 14. Типы запросов

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

Запросы на выборку

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

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

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

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

Перекрестные запросы

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

Запросы на изменение

Запросом на изменение называют запрос, который за одну операцию изменяет или перемещает несколько записей. Существует четыре типа запросов на изменение.

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

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

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

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

Запросы SQL

Запрос SQL — это запрос, создаваемый при помощи инструкций SQL Язык SQL (Structured Query Language) используется при создании запросов, а также для обновления и управления реляционными базами данных, такими как базы данных Microsoft Access.

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

Ввод выражений в запросах и фильтрах

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

· бланк запроса

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

· инструкция SQL в запросе в режиме SQL. При создании запроса в режиме конструктора запросов Microsoft Access автоматически создает эквивалентную инструкцию SQL в режиме SQL;

· окно обычного фильтра

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

Чтобы увидеть, как Microsoft Access рассчитывает выражения в запросах, полезно переключиться в режим SQL и просмотреть базовые инструкции SQL запроса. Если запрос содержит связанные таблицы, то в значениях, указанных в условиях отбора для полей из связанных таблиц, учитывается регистр знаков. Они должны соответствовать регистру значений в базовой таблице.

В бланке запроса выражения вводятся в разных местах, в зависимости от выполняемой операции. После ввода выражения в бланк запроса и нажатия клавиши ENTER выражение отображается на экране с использованием синтаксиса, понятного Microsoft Access.

Если в выражение не был включен оператор, то подразумевается, что был использован оператор =. Например, если в качестве условия отбора в поле «Страна» будет введено Дания, то Microsoft Access отобразит на экране «Дания», а выражение интерпретирует как Страна="Дания".

Оператор - знак или символ, задающий тип вычисления в выражении. Существуют математические, логические операторы, операторы сравнения и ссылок.

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

Места ввода выражений показаны в таблице.

Создание запроса на выборку

Существует немало различных видов запросов, но самые простые из них и, к тому же, используемые наиболее часто — это запросы на выборку.

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

Как и другие объекты Ассеss, запросы можно создавать автоматически с помощью Мастера или вручную

Выбор базовых таблиц для запроса

1. Создание запроса к базе начинается с открытия вкладки Запросы диалогового окна База данных и щелчка на кнопке Создать.

2. В открывшемся диалоговом окне Новый запрос задают ручной режим создания запроса выбором пункта Конструктор.

3. Создание запроса в режиме Конструктора начинают с выбора тех таблиц базы, на которых будет основан запрос.

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

5 Выбранные таблицы заносят в верхнюю половину бланка запроса по образцу щелчком на кнопке Добавить.

6. В окне Добавление таблицы обратите внимание на наличие трех вкладок: Таблицы, Запросы, Запросы и таблицы Они говорят о том, что запрос не обязательно основывать только на таблицах. Если ранее уже был создан запрос, то новый запрос можно основывать и на нем.

Заполнение бланка запроса по образцу

1. Бланк запроса по образцу имеет две панели. На верхней панели расположены списки полей тех таблиц, на которых основывается запрос.

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

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

4. Строка Имя таблицы заполняется автоматически при перетаскивании поля.

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

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

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

В нашем примере назначено условие отбора: по дате заказа (от 1 декабря 99г. до 1 марта 2000г.).

8. Запуск запроса выполняют щелчком на кнопке Вид. При запуске образуется результирующая таблица.

9. Чтобы выйти из результирующей таблицы и вернуться к созданию запроса в бланке запроса по образцу, нужно еще раз щелкнуть на кнопке Вид.

Создание запроса с параметром

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

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

2. Для этой цели служит специальная команда языка SQL (язык запросов), которая выглядит так:

LIKE[...]

В квадратных скобках можно записать любой текст, обращенный к пользователю. Команду LIKE надо поместить в строке Условие отбора и в том поле, по которому производится выбор. После запуска запроса открывается диалоговое окно, в котором пользователю предлагается ввести параметр. Если в качестве параметра ввести слово синий, то выдается результирующая таблица, содержащая записи по продаже синих «Жигулей» шестой модели.

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

Создание перекрестного запроса

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

2.  В диалоговом окне Новый запрос щелкните строку Конструктор, а затем нажмите кнопку OK.

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

4.  Дважды щелкните объекты, которые нужно добавить в запрос, а затем нажмите кнопку Закрыть.

5.  Добавьте поля в строку Поле в бланке запроса и задайте условия отбора

6.  На панели инструментов нажмите кнопку Тип запроса и выберите Перекрестный.

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

Для таких полей нужно оставить в строке Групповая операция значение Группировка, установленное по умолчанию.

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

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

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

1.  Откройте перекрестный запрос в режиме конструктора.

2.  Щелкните фон окна режима конструктора запроса вне бланка запроса и списка полей. Нажмите на панели инструментов кнопку Свойства, чтобы отобразить окно свойств запроса.

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

Вводящиеся заголовки столбцов должны точно соответствовать заголовкам столбцов в запросе в режиме таблицы. Например, если в заголовке столбца в режиме таблицы выводится «USA», следует ввести именно «USA», а не «US». (После нажатия клавиши ENTER или перевода указателя в другую ячейку имя каждого заголовка заключается в кавычки).

4.  Чтобы просмотреть результаты запроса, нажмите кнопку Вид на панели инструментов.

Задание условия отбора, ограничивающего отбираемые записи до группировки заголовков строк и до заполнения перекрестной таблицы

1.  Добавьте поле, для которого необходимо установить условие отбора, в бланк запроса.

2.  Выберите значение Условие в ячейке строки Групповая операция.

3.  Оставьте ячейку в строке Перекрестная таблица пустой.

4.  Введите выражение в ячейку строки Условие отбора.

Поля, имеющие значение Условие в строке Групповая операция, не выводятся в результатах запроса. Чтобы просмотреть результаты запроса, нажмите кнопку Вид на панели инструментов. Если требуется прервать выполнение запроса после его запуска, нажмите клавиши CTRL+BREAK.

Вычисления в запросе

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

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

·  Встроенные вычисления, называемые «итоговыми», для расчета следующих значений по группам записей или по всем записям, отобранным в запросе: сумма, среднее, число значений, минимальное или максимальное значение, стандартное отклонение или дисперсия.

·  Пользовательские вычисления для выполнения расчетов с числовыми и строковыми значениями или значениями дат для каждой записи с использованием данных из одного или нескольких полей. Для ввода таких выражений необходимо создать новое вычисляемое поле (Поле, определенное в запросе для вывода результата расчета выражения, а не для сохранения данных. Значение пересчитывается при каждом изменении выражения.) непосредственно в бланке.

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

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

Для создания запроса, производящего вычисления, служит тот же самый бланк запроса по образцу. Разница только в том, что в одном из столбцов вместо имени поля записывают формулу. В формулу входят заключенные в квадратные скобки названия полей, участвующих в расчете, а также знаки математических операций, например так: Сумма: [Кол]*[Заводская цена]

2. В узкий столбец непросто записать длинную формулу, но если нажать комбинацию клавиш Shift+F2, то открывается вспомогательное диалоговое окно, которое называется Область ввода. В нем можно ввести длинную формулу, а потом щелчком на кнопке ОК перенести ее в бланк запроса по образцу.

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

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

Итоговые запросы

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

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

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

1. Рассмотрим работу салона, занимающегося продажей подержанных автомобилей. Результаты работы салона за последнюю неделю содержатся в таблице. В ней можно выделить несколько групп по разному признаку. Записи можно сгруппировать по моделям автомобилей (ВАЗ — отдельно и БМВ — отдельно) или по году выпуска (1989, 1993 и т. д.). Для каждой из групп можно провести итоговое вычисление по полю Цена.

2. Итоговые запросы создают на основе известного нам бланка запроса по образцу, только теперь в нем появляется дополнительная строка — Группировка.

3. Для введения этой строки в бланк надо щелкнуть на кнопке Групповые операции на панели инструментов программы Ассеss.

Далее все происходит очень просто.

4. В тех полях, по которым производится группировка, надо установить (или оставить) функцию Группировка.

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

6. Щелчок на кнопке Вид запускает запрос и выдает результирующую таблицу с необходимыми итоговыми данными.

7. В строке Группировка можно указать лишь одну итоговую функцию. А как быть, если надо найти и сумму, и среднее, и максимальное значение, и еще что-то? Решение простое: одно и то же поле можно включить в бланк запроса по образцу несколько раз.

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

Для определения вычисляемого поля можно использовать встроенные функции Microsoft Access или выражения, создаваемые пользователем. Встроенные вычисления, называемые статистическими функциями (Функции, используемые для расчетов итоговых значений.) или «итоговыми» используются для расчета значений по группам записей или по всем записям, отобранным в запросе: сумма, среднее, число значений, минимальное или максимальное значение, стандартное отклонение или дисперсия. Для каждого вычисляемого поля следует выбрать одно выражение.

Вычисления по всем записям

Вычисления по группе записей

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

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

·  Группировка выполняется до расчетов по группам.

·  Результаты рассчитываются после выполнения расчетов по группам.

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

Статистические функции

Следующие параметры в строке Групповая операция бланка запроса являются статистическими функциями:

Функции «First» и «Last»

Функции First и Last используются для возвращения первой или последней записи. Записи возвращаются в том порядке, в котором они были введены. Сортировка записей не влияет на выполнение этих функций.

Элементы «Группировка», «Выражение» и «Условие»

В следующей таблице перечислены операции, выполняемые этими элементами. 

Изменение формата числового поля.

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

Изменение формата числа:

1.  Поместите курсор в числовое поле, формат которого Вы хотите изменить, и щелкните кнопку Свойства панели инструментов Конструктор запросов .

2.  Щелкните строку Формат поля, затем – стрелку раскрывающегося списка и выберите нужный формат. Имя формата появится в левой части раскрывающего списка. Примеры представления чисел в этом формате приведены в правой части списка

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

4.  Щелкните кнопку «Закрыть».

Запросы на изменение

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

• создается запрос на выборку, который отбирает данные из разных таблиц или сам создает новые данные путем вычислений;

• после запуска запроса образуется временная результирующая таблица;

• данные из этой временной таблицы используют для создания новых таблиц или изменения существующих.

Существует несколько видов запросов на изменение. Самый простой и понятный — это запрос на создание таблицы.

Запрос на добавление записей

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

Создание запроса на добавление записей:

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

2.  В режиме Конструктор щелкните стрелку раскрывающегося списка кнопки Тип запроса и выберите команду Добавление.

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

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

5.  Щелкните кнопку ОК.

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

7.  Если ключевое поле не является полем счетчика, перетащите его в бланк запроса.

8.  Перетащите из списка полей все поля, значения которых Вы хотите использовать в условиях отбора.

9.  Введите критерии, чтобы определить присоединяемые записи.

10.  Для просмотра присоединяемых записей щелкните кнопку Вид на панели инструментов Конструктор запросов. Чтобы вернуться в режим Конструктор щелкните кнопку Вид на панели инструментов Таблица в режиме таблицы.

11.  Для добавления записей щелкните кнопку Запуск на панели инструментов Конструктор запросов.

12.  Щелкните Да,чтобы ещё раз подтвердить добавление.

Запрос на создание новой таблицы.

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

Разработка запроса на создание новой таблицы:

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

2.  В режиме Конструктор щелкните стрелку раскрывающегося списка кнопки Тип запроса панели инструментов Конструктор запросов и затем – команду Создание таблицы.

3.  Задайте имя новой таблице или выберите из списка.

4.  Чтобы оставить таблицу в текущей базе данных, щелкните переключатель В текущей Б. Д. Иначе В другой Б. Д..

5.  Щелкните ОК.

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

7.  При необходимости задайте критерии отбора полей. (например, >100).

8.  Для просмотра записей новой таблицы щелкните кнопку Вид на панели инструментов Конструктор запросов. Чтобы вернуться в режим Конструктор щелкните кнопку Вид на панели инструментов Таблица в режиме таблицы.

9.  Для создания таблицы щелкните кнопку Запуск на панели инструментов.

10.  Для подтверждения операции щелкните Да.