Запрос с подгруппировкой в режиме конструктора

Обновлено: 04.05.2024

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

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

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

Значения сгруппированных столбцов (столбцов, указанных в предложении GROUP BY). В приведенном выше примере столбец pub_id является сгруппированным.

Значения, созданные агрегатными функциями, такими как SUM( ) и AVG( ). В приведенном выше примере второй столбец создается путем применения функции AVG( ) к столбцу price .

Отобразить значения отдельных строк нельзя. Например, если группируются данные только по идентификаторам издателей, нельзя отобразить в запросе еще и отдельные названия книг. Таким образом, если столбцы добавляются в вывод запроса, конструктор запросов и представлений автоматически добавляет их в предложение GROUP BY инструкции на панели SQL. Если нужно, чтобы столбец содержал статистические данные, можете определить для него агрегатную функцию.

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

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

Итоговый вывод мог бы выглядеть следующим образом:

Группирование строк

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

Щелкните правой кнопкой мыши фон панели диаграммы и выберите из контекстного меню пункт Добавить Group By . Конструктор запросов и представлений добавляет столбец Группировать в сетку на панели критериев.

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

Конструктор запросов и представлений добавит в инструкцию, отображаемую на панели SQL, предложение GROUP BY. Например, инструкция SQL может иметь такой вид:

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

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

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

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

Термина «обобщенный» или «агрегирующий» оператор в Access нет. Есть просто понятие «встроенные функции Microsoft Access», а среди них – «статистические функции» и «статистические функции по подмножеству».

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

Рис. 2. 17. Использование групповых операций в запросах. Выбор агрегирующей функции

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

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

На рис. 2.17 приведен пример использования групповых операций в запросе (Count

– подсчет числа сотрудников, работающих на каждой кафедре).

Рис. 2.18. Запрос с вычисляемым полем, используемым в условии отбора. Групповые операции

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

Выражения, определяющие вычисляемые поля, создаются с помощью мастера простых запросов или вводятся пользователем в строку « Групповая операция » бланка запроса. В бланке запроса задают также условия отбора, с помощью которых определяются группы, для которых вычисляются итоговые значения, записи, включаемые в вычисления, или результаты, отображаемые после выполнения расчетов. На рис. 2.18 изображен запрос, в котором условия отбора применены к вычисляемому полю («Выдать список кафедр, на которых работает меньше 3 человек»).

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

1. При реализации запросов на связанных таблицах по ER-модели уточните характер связи между соответствующими объектами (это может повлиять на формулировку запроса).

2. Определите, какие должны быть заданы параметры объединения для данного запроса (в случае необходимости измените эти параметры).

3. Будьте внимательны при формулировании запроса: даже на первый взгляд однотипные запросы требуют разной реализации.

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

2.2.5. Запросы, содержащие вычисляемые поля

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

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

Так, для текстовых полей возможно использование оператора «+», который в этом случае воспринимается как конкатенация (соединение) строк.

Рис. 2.19. Создание таблицы «СОТРУДНИК1»

Создадим таблицу «Сотрудник1» (рис. 2.19), подобную таблице «СОТРУДНИК», только поле «ФИО» разобьем на три поля: «Фамилия», «Имя», «Отчество». Предположим, что вы хотите вывести все три поля в одном столбце. Для этого можно использовать выражение:

[Фамилия] + " " + [Имя] + " " +[Отчество]

Внимание. Если хотя бы одно из этих трех полей будет не заполнено (причем безразлично, будет это пустое поле, или там будут введены пробелы), то вся строка будет «пустой».

В связи с этим рекомендуется вместо операции «+», использовать операцию «&».

В этом случае выражение будет иметь вид:

[СОТРУДНИК1]![Фамилия] & " " & [СОТРУДНИК1]![Имя] & " " & [СОТРУДНИК1]![Отчество]

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

Рис. 2.20. Использование выражений с тестовыми полями

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

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

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

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

Результаты вычислений не обязательно должны отображаться в ответе. Их можно использовать в условиях отбора для определения записей, которые выбираются в запросе, или для определения записей, над которыми производятся какие-либо действия. Например, на рис . 2.21 изображен запрос: «Выдать список сотрудников, зарплата которых превышает 2000 рублей». Само вычисляемое поле только используется в условиях отбора, но в ответ не выводится.

Рис. 2.21. Запрос с вычисляемым полем, используемым в условии отбора

Предполагается, что зарплата состоит из оклада и фиксированной премии/надбавки. Для создания этого запроса скорректируйте таблицу «СОТРУДНИК», добавив в нее поле «ПРЕМИЯ» (рис. 2.22). Введите в это поле данные.

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

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

в других единицах измерения, то следует воспользоваться функцией DateDiff.

- от даты можно отнять/прибавить число; при этом получается дата, отстоящая от данной на заданное число дней.

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

Рис. 2.4. Запрос с группировкой

а). Операция Группировка предназначена для объединения записей, имеющих одинаковые значения в группируемых полях, в одну запись. Для полей, у которых указана статистическая функция (Sum, Count и т.д.), производятся соответствующие вычисления. В нашем примере операция группировка указана для поля Название. Это значит, что из всех записей таблицы Клиенты, удовлетворяющих условию по полю Дата Размещения, в результирующий набор данных войдет только одна запись для каждого клиента. При этом поле Количество заказов будет содержать количество записей, соответствующих конкретному клиенту в таблице Заказы. Например, если для какого-либо из клиентов в течение 1998 года было выписано 12 счетов, то в поле Количество заказов, соответствующих данному клиенту, будет отображено значение 12. Точно так же в полях Средняя стоимость и Полная стоимость — будут вычислены среднее значение и сумма стоимости всех заказов по каждому клиенту. Описание статистических функций SQL приведено в таблице 2.4.

б). В таблице 2.3 перечислены все возможные значения свойства групповые операции.

Таблица 2.3. Возможные значения свойства Групповые операции.

Функция	Комментарий
Группировка	Указывается для полей, по совпадающим значениям которых происходит объединение записей в одну.
Выражение	Указывается для вычисляемых полей.
Условие	Указывается для полей, которые не должны попасть в результирующий набор данных, но по которым проверяется условие. Условие для такого поля проверяется до выполнения операции группирования. Если для поля ввести условие и в поле. Групповые операции указать группировка, то условие будет проверяться уже после группировки. Для первого случая в операторе SQL используется предложение WHERE, a для второго — HAVING.

Таблица 2.4. Статистические функции.

Функция	Описание
Sum	Возвращает сумму значений, содержащихся в заданном поле запроса в записях, группируемых в одну. Синтаксис: Sит(выражение). Аргумент выражение может содержать либо название поля, либо выражение, выполняющее какие-либо вычисления. Выражение может включать имена полей, константы и функции. Функции могут быть определяемыми пользователем (в модуле), но не могут быть другими статистическими функциями. Функция Sum пропускает записи со значением Null в данном поле.
Avg	Вычисляет арифметическое среднее набора чисел, содержащихся в указанном поле запроса в записях, входящих в одну группировку. Синтаксис: Avg(выpaжeнue). Замечания относительно аргумента выражение и полей со значением Null те же, что и для функции Sum.
Min, Max	Возвращают соответственно минимальное и максимальное значения из набора значений, содержащихся в указанном поле запроса в пределах одной группировки. Синтаксис: Мin(выражение), Мах(выражение). Замечания — те же.
Count	Возвращает количество записей, объединяемых в одну при группировке. Синтаксис: Соиnt(выражение). Выражение может быть таким же, как и для выше описанных статистических функций. Функция Count, так же как и остальные функции, не подсчитывает записи, содержащие Null в полях, указанных в выражении. Поэтому, чтобы избежать глупых ошибок, нужно указывать либо обязательные для ввода поля (которые гарантированно будут содержать какое-либо значение — например, первичный ключ), либо подстановочный знак звездочки (например, Count()).*
StDev	Вычисляет величину смещенного стандартного отклонения по набору значений, содержащихся в указанном поле запроса для каждой группировки. Синтаксис: StDev(выраженue). Замечания по поводу выражения и полей со значением Null те же, что и для функции Sum. Кроме того, если группировка содержит меньше двух записей, то функция возвращает значение Null, что означает невозможность вычисления стандартного отклонения. Стандартное отклонение (среднеквадратичное отклонение) — параметр, который указывает величину разброса функции распределения около среднего значения. Он равен квадратному корню из момента для квадрата отклонений от среднего.
Var	Возвращает значение смещенной дисперсии, вычисляемой по набору значений, содержащихся в указанном поле запроса для каждой группировки. Синтаксис: Var(выражение). Замечания по поводу выражения и полей со значением Null те же, что и для функции Sum. Если группировка содержит меньше двух записей, функция возвращает значение Null, что означает невозможность вычисления дисперсии. Дисперсия — квадрат значения среднеквадратичного отклонения, мера отличия значений в группе от среднего.
First, Last	Возвращают значение поля соответственно из первой и последней записи набора записей в пределах каждой группировки. Синтаксис: First(выражение), Last(выражение). Выражение — такое же, как и для остальных статистических функций. Поскольку записи обычно возвращаются без какого-либо специального порядка (кроме случаев, когда запрос содержит предложение ORDER BY), эти функции возвращают случайные данные

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

г). При вычислении полей Средняя стоимость и Полная стоимость использовалась функция ССиr, являющаяся одной из функций преобразования типов. Хотя чаще эти функции используются при программировании на Visual Basic, в запросах тоже иногда приходится их использовать. Чаще всего эти функции используются в вычисляемых полях запросов. Например, если из таблицы в запрос добавить поля Количество и Стоимость, а потом добавить вычисляемое поле Цена:[Стоимость]/[Количество], то в результирующем наборе данных в этом поле появятся значения, содержащие огромное количество знаков дробной части.

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

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

Бланк простого запроса содержит шесть строк:

o вывод на экран (указывает, будет ли поле присутствовать в динамическом наборе данных);

o условие отбора (содержит первое условие, ограничивающее набор данных);

o или (содержит другие условия ограничения данных).
Разработка простого запроса выполняется в несколько этапов:

· выбор полей (добавление полей в запрос);

· установление критериев отбора;

· задание порядка расположения записей (сортировка).

Пример такого запроса в режиме конструктора приведен выше на Рис 1.1

Запрос, показывающий список студентов по номерам зачетной книжки и Фамилиям групп 661 и 662.

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

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

Рис. 1.2. Вид диалогового окна для ввода параметра

Чтобы создать запрос с параметром, необходимо в строку «Условия отбора» для заданного поля ввести текст приглашения для ввода данного, заключив его в прямоугольные скобки: [Введите номер группы] (рис. 1.3). Можно задать параметры для нескольких полей или для одного поля определить несколько параметров для отбора, используя запись условия в несколько строк совместно с логической операцией «ИЛИ».

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

Рис. 1.3. Вид запроса с параметром в режиме конструктора

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

Sum - сумма значений некоторого поля для группы;
Avg - среднее значение некоторого поля для группы;
Max, Min - максимальное или минимальное значение поля для группы;

Count - число значений поля в группе (пустые значения поля не учитываются);

StDev - среднеквадратическое отклонение от среднего;

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

First, Last - значение поля из первой или последней записи.

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

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

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

2. Групповые операции для некоторых групп записей. Функция «ГРУППИРОВКА» указывается для полей, по которым будут группироваться данные. В полях, по которым будут выполняться вычисления, должны быть выбраны функции, соответствующие вычислениям.

3. Группировка записей, которые соответствуют условию отбора. Для этого необходимо указать условие отбора для поля, где выбрана функция «ГРУППИРОВКА».

4. Вывод только тех результатов, которые удовлетворяют условию отбора. Для этого условие отбора задается для полей, по которым в строке «Групповая операция» выбрана итоговая функция (например, вывести группы, средний балл студентов которых больше 4,5).

Рис. 1.4. Вид запроса на выборку с группировкой

Например: вычислить количество студентов на специальностях (специальности по наименованию)

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

Например: рассчитать количество студентов на специальностях (специальности вывести по наименованию) по возрастам:

Рис. 1.5. Вид перекрестного запроса в режиме конструктора

Перекрестный запрос - это специальный тип группового запроса. Строка «Групповая операция» должна быть включена. В запросе обязательно должны быть установлены как минимум три параметра - поле заголовка строк, поле заголовка столбцов и поле для выбора значений. Поля, используемые в качестве строк и столбцов, должны содержать функцию «ГРУППИРОВКА» в строке «Групповая операция». Для создания запроса необходимо:

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

• изменить тип ЗАПРОСА на Перекрестный;

• в строке «Перекрестная таблица» указать, какое поле используется в качестве заголовка строк, какое в качестве заголовков столбцов и какое для выполнения вычислений в соответствии с выбранной групповой операцией;

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

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

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

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

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

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

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

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

Выражения могут содержать следующие элементы: константы, литералы, функции, имена объектов.

Константы- это величины, которые не могут быть изменены:
Yes, No, Null, False, True.

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

Формула, помещаемая в вычисляемое поле, всегда начинается со знака равенства (=).

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

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

В этой статье

Общие сведения

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

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

Преимущества запросов

Запрос позволяет выполнять перечисленные ниже задачи.

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

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

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

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

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

Основные этапы создания запроса на выборку

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

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

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

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

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

Создание запроса на выборку с помощью мастера запросов

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

Подготовка

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

Дополнительную информацию о создании отношений между таблицами можно найти в статье Руководство по связям между таблицами.

Использование мастера запросов

На вкладке Создание в группе Запросы нажмите кнопку Мастер запросов.

В диалоговом окне Новый запрос выберите пункт Простой запрос и нажмите кнопку ОК.

Теперь добавьте поля. Вы можете добавить до 255 полей из 32 таблиц или запросов.

Для каждого поля выполните два указанных ниже действия.

В разделе Таблицы и запросы щелкните таблицу или запрос, содержащие поле.

В разделе Доступные поля дважды щелкните поле, чтобы добавить его в список Выбранные поля. Если вы хотите добавить в запрос все поля, нажмите кнопку с двумя стрелками вправо (>>).

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

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

Выполните одно из указанных ниже действий.

Если вы хотите просмотреть отдельные записи, выберите пункт подробный и нажмите кнопку Далее. Перейдите к действию 9.

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

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

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

Sum — запрос вернет сумму всех значений, указанных в поле.

Avg — запрос вернет среднее значение поля.

Min — запрос вернет минимальное значение, указанное в поле.

Max — запрос вернет максимальное значение, указанное в поле.

Если вы хотите, чтобы в результатах запроса отобразилось число записей в источнике данных, установите соответствующий флажок Подсчет числа записей в (название источника данных).

Нажмите ОК, чтобы закрыть диалоговое окно Итоги.

Если вы не добавили в запрос ни одного поля даты и времени, перейдите к действию 9. Если вы добавили в запрос поля даты и времени, мастер запросов предложит вам выбрать способ группировки значений даты. Предположим, вы добавили в запрос числовое поле ("Цена") и поле даты и времени ("Время_транзакции"), а затем в диалоговом окне Итоги указали, что хотите отобразить среднее значение по числовому полю "Цена". Поскольку вы добавили поле даты и времени, вы можете подсчитать итоговые величины для каждого уникального значения даты и времени, например для каждого месяца, квартала или года.

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

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

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

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

Создание запроса в режиме конструктора

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

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

Действие 1. Добавьте источники данных

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

На вкладке Создание в группе Другое нажмите кнопку Конструктор запросов.

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

Автоматическое соединение

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

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

Если приложение Access при добавлении источников данных автоматически создало соединения правильных типов, вы можете перейти к действию 3 (добавление выводимых полей).

Повторное использование одного источника данных

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

При добавлении источника данных во второй раз Access присвоит имени второго экземпляра окончание "_1". Например, при повторном добавлении таблицы "Сотрудники" ее второй экземпляр будет называться "Сотрудники_1".

Действие 2. Соедините связанные источники данных

Если у источников данных, которые вы добавляете в запрос, уже есть связи, Access автоматически создает внутреннее присоединение для каждой связи. Если целостность данных, access также отображает "1" над линией связи, чтобы показать, какая таблица находится на стороне "один" отношение "один-ко-многим", и символ бесконечности(∞),чтобы показать, какая таблица находится на стороне "многие".

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

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

Добавление соединения

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

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

Изменение соединения

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

Откроется диалоговое окно Параметры соединения.

Ознакомьтесь с тремя вариантами в диалоговом окне Параметры соединения.

Выберите нужный вариант и нажмите кнопку ОК.

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

Действие 3. Добавьте выводимые поля

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

Для этого перетащите поле из источника в верхней области окна конструктора запросов вниз в строку Поле бланка запроса (в нижней части окна конструктора).

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

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

Использование выражения в качестве выводимого поля

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

В пустом столбце таблицы запроса щелкните строку Поле правой кнопкой мыши и выберите в контекстном меню пункт Масштаб.

В поле Масштаб введите или вставьте необходимое выражение. Перед выражением введите имя, которое хотите использовать для результата выражения, а после него — двоеточие. Например, чтобы обозначить результат выражения как "Последнее обновление", введите перед ним фразу Последнее обновление:.

Примечание: С помощью выражений можно выполнять самые разные задачи. Их подробное рассмотрение выходит за рамки этой статьи. Дополнительные сведения о создании выражений см. в статье Создание выражений.

Действие 4. Укажите условия

Этот этап является необязательным.

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

Определение условий для выводимого поля

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

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

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

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

Условия для нескольких полей

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

Настройка условий на основе поля, которое не включается в вывод

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

Добавьте поле в таблицу запроса.

Снимите для него флажок в строке Показывать.

Задайте условия, как для выводимого поля.

Действие 5. Рассчитайте итоговые значения

Этот этап является необязательным.

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

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

Когда запрос открыт в конструкторе, на вкладке "Конструктор" в группе "Показать или скрыть" нажмите кнопку Итоги.

Access отобразит строку Итого на бланке запроса.

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

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

Действие 6. Просмотрите результаты

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

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

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

Читайте также: