Использование запроса в качестве источника записей для формы или отчета. Запрос в аксессе
Создание запроса на создание таблицы
В этой статье объясняется, как создать и выполнить запрос таблицы в Access. Вы можете воспользоваться запросом на создание таблицы, чтобы скопировать данные в таблицу, архивировать их или сохранить результаты запроса в виде таблицы.
Чтобы изменить или обновить часть данных в существующем наборе записей, например одно или несколько полей, вы можете использовать запрос на обновление. Дополнительные сведения о запросах на обновление см. в статье Создание и выполнение запроса на обновление.
Чтобы добавить записи (строки) в существующую таблицу, вы можете использовать запрос на добавление. Дополнительные сведения о запросах на добавление см. в статье Добавление записей в таблицу с помощью запроса на добавление.
В этой статье
Общие сведения о запросах на создание таблиц
Создание запроса на создание таблицы
Дополнительные сведения об условиях запроса и выражениях
Предотвращение блокировки запроса режимом отключения
Общие сведения о запросах на создание таблиц
Запрос на создание таблицы извлекает данные из одной или нескольких таблиц, а затем загружает результирующий набор в новую таблицу. Такая новая таблица может храниться в открытой базе данных. Кроме того, вы можете создать ее в другой базе данных.
Как правило, запросы на создание таблицы используются, если нужно скопировать или архивировать данные. Например, предположим, что у вас есть таблица (или несколько таблиц) с данными о продажах, которые используются в отчетах. Суммы продаж не меняются, так как транзакции выполнялись по крайней мере один день назад, а постоянное выполнение запроса для извлечения данных может занимать время, особенно если вы применяете сложный запрос к крупному хранилищу данных. Чтобы снизить рабочую нагрузку и получить удобный архив, вы можете загрузить данные в отдельную таблицу и использовать ее в качестве источника. При этом не забывайте, что данные в новой таблице — это моментальный снимок: они не связаны с исходными таблицами и не подключены к ним.
Создание запроса на создание таблице включает следующие основные этапы:
-
Включите базу данных, если она не подписана или не находится в надежном расположении. В противном случае не удастся выполнять запросы на изменение (добавление, обновление и создание таблиц).
-
В конструкторе запросов создайте запрос на выборку и настройте его так, чтобы он возвращал нужные записи. Вы можете выбрать данные из нескольких таблиц данных и выполнить денормализацию данных. Например, можно поместить данные клиентов, грузоотправителей и поставщиков в одну таблицу, что вы никогда не стали бы делать в рабочей базе данных с нормализованными таблицами. Вы также можете использовать условия в запросе для дальнейшей настройки или ограничения результирующего набора.
Дополнительные сведения о нормализации данных см. в статье Основные сведения о создании баз данных.
-
Преобразуйте запрос на выборку в запрос на создание таблицы, выберите расположение для новой таблицы и выполните запрос.
Не путайте запрос на создание таблицы с запросом на обновление или на добавление. Используйте запрос на обновление, когда вам нужно добавить или изменить данные в отдельных полях. Запрос на добавление нужен, чтобы добавлять новые записи (строки) в существующий набор записей в существующей таблице.
К началу страницы
Создание запроса на создание таблицы
При составлении запроса на создание таблицы следует сначала создать запрос на выборку, а затем преобразовать его в запрос на создание таблицы. В запросе на выборку можно использовать вычисляемые поля и выражения, чтобы он возвращал нужные данные. Ниже описано, как создать и преобразовать запрос. Если нужный запрос на выборку уже создан, переходите непосредственно к инструкциям по преобразованию запроса на выборку и выполнению запроса на создание таблицы.
Создание запроса на выборку
Примечание: Если запрос на выборку, возвращающий нужные данные, уже создан, перейдите к следующему действию.
-
На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов. Если вы используете Access 2007, на вкладке Создание в группе Другие нажмите кнопку Конструктор запросов.
-
В диалоговом окне Добавление таблицы дважды щелкните таблицы, из которых нужно получить данные. Каждая таблица отображается в верхней части конструктора запросов. Завершив добавление таблиц, нажмите кнопку Закрыть.
-
В каждой таблице дважды щелкните поля, которые вы хотите использовать в запросе. Каждое поле появится в пустой ячейке в строке Поле на бланке. На рисунке показан бланк с несколькими добавленными полями таблицы.
-
При необходимости добавьте выражения в строку Поле.
-
Вы также можете добавить любые условия в строку Условие отбора на бланке.
-
Чтобы выполнить запрос и отобразить результаты в режиме таблицы, нажмите кнопку Выполнить .
-
Настраивайте поля, выражения или условия и повторно выполняйте запрос, пока он не будет возвращать данные, которые вы хотите поместить в новую таблицу.
Преобразование запроса на выборку
-
Откройте запрос на выборку в конструкторе или перейдите в конструктор одним из указанных ниже способов.
-
Если запрос открыт в режиме таблицы, щелкните правой кнопкой мыши вкладку документа запроса и выберите пункт Конструктор.
-
Если запрос закрыт, щелкните его правой кнопкой мыши в области навигации и выберите в контекстном меню пункт Конструктор.
-
-
На вкладке Конструктор в группе Тип запроса нажмите кнопку Создание таблицы.
Откроется диалоговое окно Создание таблицы.
-
В поле Имя таблицы введите имя новой таблицы.
или
Щелкните стрелку вниз и выберите имя существующей таблицы.
-
Выполните одно из указанных ниже действий.
К началу страницы
Дополнительные сведения об условиях запроса и выражениях
Ранее в статье упоминались условия запроса и выражения. Условие запроса — это правило, служащее для определения записей, которые должен возвращать запрос. Условия используются, если вам нужны не все записи в наборе данных. Например, условие >25 AND <50 возвращает значения, которые больше 25 и меньше 50, а условие "Воронеж" OR "Рязань" OR "Москва" — только записи для этих городов.
Дополнительные сведения об использовании условий см. в статье Примеры условий запроса.
Выражение — это сочетание математических или логических операторов, констант, функций и имен полей, элементов управления и свойств, результатом вычисления которого является одно значение. Выражения используются для получения данных, которые не хранятся непосредственно в таблице. Например, выражение [ЦенаЗаЕдиницу]*[Количество] умножает значение в поле "ЦенаЗаЕдиницу" на значение в поле "Количество". Выражения можно использовать различными способами, и процесс их создания и работы с ними может быть довольно сложным.
К началу страницы
Дополнительные сведения о создании и использовании выражений см. в статье Создание выражений.
Предотвращение блокировки запроса режимом отключения
По умолчанию при открытии базы данных, которая не сохранена в надежном расположении или не является доверенной, Access не позволяет выполнять никакие запросы на изменение (запросы на добавление, обновление, удаление и создание таблиц).
Если при попытке выполнения запроса на изменение ничего не происходит, проверьте, не появляется ли в строке состояния Access следующее сообщение:
Данное действие или событие заблокировано в режиме отключения.
Если выводится это сообщение, сделайте следующее:
-
На панели сообщений нажмите Параметры.
Откроется диалоговое окно Параметры безопасности Microsoft Office.
-
Нажмите кнопку Включить это содержимое, а затем кнопку ОК.
-
Выполните запрос еще раз.
К началу страницы
support.office.com
Запросы на изменение Access - Базы данных Access
В этой статье поговорим про запросы на изменение Access. К запросам на изменение относятся запросы на обновление данных в записях таблицы базы, на добавление и удаление записей из таблицы, а также запросы на создание таблицы из записей, сформированных в нем.Чтобы создать запросы на изменение Access, используется конструктор. Процесс создания любого запроса на изменение начинается с создания запроса на выборку, который после добавления в него необходимых таблиц преобразуется в нужный запрос на изменение.По умолчанию Access 2010 в целях обеспечения безопасности, как правило, блокирует выполнение всех запросов на изменение. Если при выполнении запроса на изменение ничего не происходит, проверьте, не появляется ли в строке состояния Access сообщение: «Данное действие или событие заблокировано в режиме отключения».Если отображается это сообщение и панель сообщений (Message Bar) (рис. 4.39), для включения заблокированных запросов можно нажать на ней кнопку Включить содержимое (Enable content). После этого выполнение запроса будет доступным.Если панель сообщений была закрыта и больше не отображается, перейдите на вкладку Файл (File) и на открытой странице Сведения (Info) в блоке Предупреждение системы безопасности (Security Warning) нажмите кнопку Включить содержимое (Enable Content) (рис. 4.40).
ВНИМАНИЕ!Единое средство вывода предупреждений системы безопасности — панель сообщений — по умолчанию появляется при открытии базы данных Access 2010 вне доверенного расположения. Если точно известно, что можно доверять содержимому базы данных, включите все отключенные потенциально опасные активные компоненты — запросы на изменение, макросы, элементы управления ActiveX, некоторые выражения и программы на VBA — при открытии базы данных, содержащей один или несколько этих компонентов.
Для закрепления смотрим видеоурок:Далее узнаем про запрос на создание таблицы Access.
accesshelp.ru
Использование запроса в качестве источника записей для формы или отчета
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).
С помощью запроса можно передать данные в форму или отчет. При создании формы или отчета можно использовать запрос, а также изменять существующую форму или отчет, задавая свойство источника записей. При задании свойства Источник записей можно либо указать существующий запрос, либо создать новый запрос для использования.
Если вы используете запрос в качестве источника записей, возможно, вы не сможете изменить данные. Прежде чем использовать запрос в качестве источника записей, следует решить, нужно ли изменять данные.
В этой статье объясняется, как задать свойство "источник записей" для существующей формы или отчета, а также сведения о том, когда можно и не изменять данные запроса.
В этой статье объясняется, как создать форму или отчет. Дополнительные сведения о создании форм и отчетов см. в статье Создание формы и Создание простого отчетас помощью плана.
В этой статье
Использование существующего запроса в качестве источника записей для формы или отчета
Создание запроса в качестве источника записей для формы или отчета
Изменение данных в запросе
Использование существующего запроса в качестве источника записей для формы или отчета
В режиме конструктора задайте для свойства Источник записей существующий запрос, который вы хотите использовать.
-
Откройте форму или отчет в конструкторе.
Если окно свойств не открыто, нажмите клавишу F4, чтобы открыть его.
-
В окне свойств на вкладке данные щелкните поле свойства Источник записей.
-
Выполните одно из указанных ниже действий.
-
Начните вводить имя запроса, который вы хотите использовать.
Access автоматически заполняет имя объекта по мере ввода.
- или -
-
Щелкните стрелку, а затем выберите запрос, который вы хотите использовать.
-
К началу страницы
Создание запроса в качестве источника записей для формы или отчета
В режиме конструктора используйте кнопку " построить " (_з0з_) в поле свойства Источник записей, чтобы создать новый запрос, который будет использоваться в качестве источника записей.
-
Откройте форму или отчет в конструкторе.
Если окно свойств не открыто, нажмите клавишу F4, чтобы открыть его.
-
В окне свойств на вкладке данные щелкните поле свойства Источник записей.
-
Нажмите кнопку _з0з_.
Новый запрос откроется в режиме конструктора.
-
Создайте запрос, а затем сохраните и закройте его.
Инструкции по проектированию запросов см. в статье Создание простого запросана выборку.
К началу страницы
Изменение данных в запросе
Одним из основных причин использования форм является ввод и изменение данных. Некоторые запросы не поддерживают редактирование данных. Прежде чем использовать запрос в качестве источника записей для формы, следует подумать о том, можно ли изменять данные запроса.
Когда можно редактировать данные из запроса?
Данные из запроса можно редактировать в указанных ниже случаях.
-
Запрос основан только на одной таблице.
-
Запрос основан на двух таблицах с _з0з_ между ними.
-
Свойство RecordsetType формы имеет значение динамического набора (непоследовательные обновления), и ни одно из условий, приведенных в следующем разделе, не применяется.
Примечание: Даже если вы можете редактировать данные в запросе, некоторые его поля могут быть недоступны для редактирования. Такие случаи перечислены в следующем разделе.
Когда можно не изменять данные из запроса?
Вы не можете редактировать данные из запроса в указанных ниже случаях.
-
Запрос является _з0з_.
-
Запрос является _з0з_.
-
Поле, которое вы пытаетесь изменить, является _з0з_. В этом случае вы можете изменить данные из других полей запроса.
-
Запрос содержит предложение GROUP BY.
К началу страницы
support.office.com
Access 2007_5 ЗАПРОС С ПАРАМЕТРОМ
Access №5
Запрос с параметром
Запрос с параметром. Специальный «интерактивный» тип запроса: прежде чем запрос будет выполнен, на экран выводится диалоговое окно с приглашением ввести один или ряд параметров, например условие отбора записей по определенному полю. Запрос, таким образом, допускает некоторую модификацию при каждом своем запуске, что очень удобно, например при генерировании месячных или квартальных отчетов о доходах предприятия. Запросы с параметрами могут быть отнесены к отдельному типу условно, так как ввод параметра можно организовать для запросов на выборку, запросов на изменение и перекрестных запросов.
В формулах, используемых для нахождения данных вычисляемых полей, могут присутствовать не только значения существующих или других вычисляемых полей или некоторые константы, но и переменные величины, которые можно ввести в процессе выполнения запроса с помощью специального диалогового окна. Такие запросы, как уже упоминалось выше, называются параметрическими.
Рассмотрим создание параметрического запроса на простом примере. В таблице Товары для каждого из товаров указана его фиксированная цена (поле ЦенаПродажи). Предположим, что в рамках предпраздничных акций на все товары объявляется некоторая скидка, величина которой может изменяться. Чтобы вычислить новые цены на товары с учетом той или иной скидки, можно создать запрос с параметром, в роли которого и будет выступать величина скидки.
Бланк запроса приведен на рис.1. Как видно, в бланке запроса создается новое вычисляемое поле, Цена со скидкой, в ячейку Поле которого вводится следующая формула: Цена со скидкой: [ЦенаПродажи]*(1-[Скидка в процентах]/100).
Рис. 1. Бланк запроса с одним параметром
При выполнении запроса программа Access открывает диалоговое окно Введите значение параметра, куда необходимо ввести нужную для вычислений величину (рис.2).
Рис. 2. Диалоговое окно Введите значение параметра
После ввода параметра и щелчка на кнопке ОК значения вычисляемого поля рассчитываются с учетом указанной величины скидки. Результирующая таблица показана на рис.3.
Рис. 3. Результаты выполнения запроса
Параметры могут использоваться также для ввода критерия отбора записей. Такой подход целесообразен, если заранее предполагается, что запрос будет запускаться неоднократно, но одно или несколько условий придется изменять. Например, в запросе к таблице Клиенты, отображающем список всех клиентов с указанием их данных, можно запрашивать наименование фирмы клиента. Тогда при каждом запуске запроса название фирмы, введенное пользователем в диалоговое окноВведите значение параметра, будет преобразовано в условие отбора, и в результирующую таблицу программа Access включит только те записи, которые соответствуют этому критерию. На рис.4 приведен бланк и диалоговое окно ввода такого запроса, в котором для поляФирмав строкеУсловие отборавместо конкретного значения указана подсказка для ввода параметра – [Введите название фирмы].
Рис. 4. Запрос с параметром, где параметром является условие отбора для поля Фирма
Перекрестные запросы
Более сложная категория запросов на выборку, в которых также используется группировка записей, но уже "двухмерная" (по строкам и по столбцам), — это перекрестные запросы. Они группируются по двум наборам данных: первый выводится в столбце слева (это заголовки строк, например наименование товара), а второй – в верхней строке (заголовки столбцов, например порядковые номера кварталов). Итоговая таблица может, таким образом, отображать поквартальные итоги продаж определенных товаров.
Для создания перекрестного запроса требуется как минимум три элемента: поле для определения заголовков строк, поле, определяющее заголовки столбцов, и поле для выбора значений, с которыми будут непосредственно производиться вычисления.
В программе Access предусмотрен специальный мастер, с помощью которого можно построить перекрестный запрос.
Естественно, при создании перекрестного запроса под руководством мастера приходится мириться с некоторыми ограничениями. Во-первых, мастер позволяет работать с полями только одной таблицы или запроса, поэтому, чтобы включить в перекрестный запрос данные из нескольких таблиц, необходимо составить вспомогательный запрос и использовать его в качестве источника записей. Во-вторых, в процессе создания запроса с помощью мастера нет возможности указать дополнительные условия отбора, поэтому созданный мастером запрос необходимо дорабатывать уже в режиме конструктора. Кроме того, мастер не позволяет изменить заголовки столбцов.
Чтобы построить перекрестный запрос под руководством мастера, выполните следующее.
1. Щелкните на кнопке Мастер запросовв группеДругие(вкладкаСоздание). На экране появится диалоговое окноНовый запрос(рис. 5).
2. Выберите из списка доступных методов создания нового запроса элемент Перекрестный запроси щелкните на кнопкеОК.
Рис. 5. Диалоговое окно Новый запрос
3. В первом диалоговом окне мастера укажите источник данных для перекрестного запроса. Для этого выберите элемент Запросыв группе опцийПоказать, а затем из расположенного выше списка — запросСуммаПродажи(рис. 6). Щелкните на кнопкеДалее.
Рис. 6. Диалоговое окно выбора источника данных для перекрестного запроса
4. Выберите из списка Доступные поляте поля, значения которых будут использоваться как заголовки строк. Чтобы сделать это, щелкните на имени нужного поля, а затем на кнопке со значком ">" (или просто дважды щелкните на имени поля). Выделенное поле переместится в списокВыбранные поля. В данном случае в списокВыбранные поляследует перенести поле КодТ (рис. 7). Щелкните на кнопкеДалее.
5. Выберите поле, значения которого будут использованы в качестве заголовков столбцов. Таким полем в данном примере является поле ДатаОформления (рис. 8). Выберите данное поле и щелкните на кнопке Далее.
Рис. 7.
6. Выберите интервал группировки дат для столбцов, например Квартал.
Рис. 8.
7. Выберите итоговую операцию, которую необходимо применить для обработки данных поля Всего. Для данного примера такой функцией будет суммирование (Sum). Если это необходимо, оставьте установленным флажокДа, чтобы вычислить итоговое значение для каждой строки (рис. 9). Затем щелкните на кнопкеДалее.
8. Заключительное окно мастера предлагает указать имя запроса. Введите нужный заголовок (например, Квартальные объемы продаж за 2008) и щелкните на кнопке Готово, чтобы просмотреть результаты выполнения перекрестного запроса (рис. 10).
Рис. 9.
Рис. 10. Результат выполнения перекрестного запроса
Модифицирующие запросы
До настоящего момента любые правки в таблицы вносились в режиме просмотра таблицы. Однако понятно, что при обслуживании реальных баз данных должны быть предусмотрены иные механизмы, автоматизирующие модификацию большого числа записей в таблицах. Таким механизмом в Microsoft Access 2007 являются модифицирующие запросы.
Существует четыре типа модифицирующих запросов:
• Запрос на создания таблицы;
• Запрос на удаление;
• Запрос на добавление записей;
• Запрос на обновление.
Основой для любого запроса на изменение служит обычный запрос на выборку, определяющий ту группу записей, над которыми будут выполнены некоторые действия. Поэтому при создании запроса на изменение сначала необходимо построить (в режиме конструктора или с помощью мастера) запрос на выборку, а затем изменить в режиме конструктора его тип. Сделать это можно, выбрав подходящий тип запроса с помощью команд группы Тип запроса на ленте (вкладка Конструктор).
Чтобы обезопасить в равной степени и себя, и непосредственно данные, которые подлежат изменению, при работе с запросами на изменение не забывайте делать резервные копии исходных таблиц.
Чтобы предотвратить потерю важных данных, перед запуском любого запроса такого типа воспользуйтесь командой Режим таблицы (одна из команд кнопки Вид группы Результаты), чтобы просмотреть те изменения, которые ожидают таблицу в результате выполнения запроса.
Запрос на создание таблицы
Запрос такого типа выполняется в два этапа: сначала осуществляется выборка необходимых записей, а затем на основе данных, полученных в результате этого запроса, создается новая таблица для текущей или какой-либо другой базы данных Microsoft Access. Одно из применений таких запросов — выделение некоторого подмножества записей, удовлетворяющих определенным условиям отбора, из одной или нескольких таблиц для их последующего анализа или обработки.
Предположим, что владелец склада собирается премировать 3 своих наиболее активных зарегистрированных покупателей, т.е. тех клиентов, которые за весь период обслуживания оформили заказы на наибольшую сумму. Чтобы удобнее было работать с этими покупателями (например, сообщать им о предоставленных компанией определенных скидках), сведения о них должны быть размещены в отдельной таблице. Поэтому логично решить такую задачу, обратившись к запросу на создание таблицы. В запросе на выборку, который послужит основой для создания новой таблицы, рассчитываетсяобщая сумма, на которую покупателями были сделаны заказы, затем результирующие записи сортируются, и в итоговую таблицу помещаются три первых записи. Последовательность действий при построении запроса на создание таблицы будет следующей.
1. Создайте новый запрос. В данном примере в запросе участвуют поля трех связанных таблиц: Товар, Выдача товара и Клиенты. Вычисляемое (оно же итоговое) поле — СуммаЗаказа — рассчитывается по формуле: СуммаЗаказа: Sum([ЦенаПродажи]*[Количество товара]).
2. Чтобы в результирующую таблицу были включены только 3 первых записи, щелкните в списке Возврат (в группе Настройка запроса) и введите значение 3 в поле ввода. Бланк запроса будет выглядеть аналогично представленному на рис. 11.
Рис. 11. Бланк исходного запроса на выборку
3. Для выбора типа запроса (на создание таблицы) воспользуйтесь кнопкой Создание таблицы группыТип запроса. На экране появится диалоговое окно Создание таблицы (рис. 12).
Рис. 12.Диалоговое окно создания таблицы
4. Введите в поле имя таблицы: название новой таблицы, примите предложенную по умолчанию опцию в текущей базе данных и щелкните на кнопке ОК.
5. Чтобы убедиться, что результирующая таблица включает именно те данные, которые необходимы для новой таблицы, воспользуйтесь для предварительного просмотра режимом таблицы.
6. Вернитесь в режим конструктора запросов и щелкните на кнопке Выполнить группы Результаты. В небольшом диалоговом окне программа Access сообщит о том, сколько записей копируется в новую таблицу, и запросит подтверждение на создание таблицы (рис. 13). Щелкните на кнопке Да, чтобы завершить выполнение запроса.
Рис. 13. Диалоговое окно для подтверждения создания новой таблицы
7. Сохраните запрос на создание таблицы, указав имя «СозданиеТаблицы».
8. Теперь, чтобы проверить полученные результаты, обратитесь к категории Таблицы области переходов и откройте новую таблицу ТроеКлиентов (рис. 14). Обратите внимание на то, что данные новой таблицы не наследуют какие-либо свойства полей базовой таблицы.
Важно помнить о том, что запрос на создание таблицы позволяет сохранить в структуре новой таблицы такие же типы данных и размеры полей, что и в исходной таблице запроса. Однако любые другие свойства полей (в том числе и настройки ключевых полей) необходимо определять отдельно, открыв вновь созданную таблицу в режиме конструктора.
Рис. 14. Новая таблица
Запрос на добавление записей
Схема построения запроса на добавление записей имеет много общего с разработкой запросов на создание таблицы. Записи из результирующего набора могут быть добавлены как в таблицу уже открытой базы данных, так и в какую-либо другую базу данных Access. Безусловно, структура запроса должна соответствовать структуре таблицы-получателя. Чтобы продемонстрировать, каким образом создается запрос на добавление записей в таблицу, создадим в базе данных таблицу, в которой указаны товары, которые должны подвести на склад (рис.15).
Рис. 15.
Чтобы построить запрос на добавление записей, выполните следующее.
1. Создайте новый запрос. В нашем примере бланк запроса включает три поля таблицы ОжидаемыеТовары (рис. 16).
Рис. 16.
2. Чтобы преобразовать запрос на выборку в запрос на добавление, щелкните на кнопке Добавление группыТип запроса. На экране появится диалоговое окно Добавление.
3. Выберите из раскрывающегося списка поля имя таблицы название той таблицы, в которую необходимо добавить записи новой таблицы (для нашего примера это таблица Товары, как показано на рис. 17), затем примите предложенную по умолчанию опцию в текущей базе данных и щелкните на кнопке ОК.
Рис. 17.
В бланке запроса теперь появилась новая строка — Добавление (рис. 18).
При условии, что имена полей исходной таблицы, указанные в бланке запроса, совпадают с именами тех полей, куда должны быть добавлены данные, программа Access по умолчанию вносит эти имена в ячейки строки Добавление.
4. Щелкните на кнопке Выполнить группы Результаты. Программа Access сообщит о том, какое число записей будет добавлено в таблицу. Щелкните на кнопке Да, чтобы завершить выполнение запроса.
5. Если необходимо, сохраните запрос.
6. Чтобы проверить полученные результаты, обратитесь к категории Таблицы области переходов и откройте целевую таблицу Товары.
Запросы на удаление
Как и следует из названия, главное и единственное назначение запросов такого типа — удалять из таблиц (или нескольких таблиц) записи, соответствующие определенному критерию. Именно записи, а не значения отдельных полей, для удаления которых можно воспользоваться запросом на обновление. Применение запросов на удаление обязывает быть предельно внимательным, поскольку удаление записей выполняется без возможности их последующего восстановления.
Порядок действий при создании запроса на удаление аналогичен приводимым выше процедурам для других запросов на изменение.
1. Создайте запрос на выборку, результирующими записями которого должны стать строки исходной таблицы, подлежащие удалению.
2. Перейдя в режим таблицы, убедитесь, что в результирующей таблице отображены именно те данные, которые следует удалить.
3. Вернитесь в режим конструктора запросов и воспользуйтесь для выбора типа запроса (на удаление записей) кнопкой Удаление группы Тип запроса. Запрос на выборку будет преобразован в запрос на удаление, а в бланке запроса вместо строкиСортировка появится новая строка — Удаление. Простой пример бланка запроса на удаление приведен на рис. 18.
Рис. 18.
4. Щелкните на кнопке Выполнить группы Результаты. Программа Access сообщит о том, сколько записей будет удалено и запросит подтверждение операции. Щелкните на кнопке Да, если вы готовы расстаться с указанными записями.
Таков сценарий удаления записей из одной, не связанной с другими таблицы. Однако на практике таблицы базы данных, как правило, связаны между собой, и наличие этих связей в схеме базы данных обязательно учитывается программой Access при попытке удалить записи одной из связанных таблиц. Напомним, что если между таблицами определено отношение "один ко многим" и в диалоговом окне Изменение связей установлен флажок опции каскадное удаление связанных записей, при удалении записи в главной таблице автоматически будут удалены все связанные записи подчиненной таблицы, даже если ее поля не фигурировали в бланке запроса на удаление.
Если флажок каскадного удаления не установлен, но для данного отношения между таблицами опция Обеспечение целостности данных активизирована, удалить записи из родительской таблицы не удастся до тех пор, пока не будут удалены записи из подчиненной таблицы. При попытке удаления записей программа Access выдает соответствующее предупреждение о наличии связанных записей в подчиненной таблице.
Запрос на обновление
Такой запрос позволяет указать критерий отбора записей, подлежащих обновлению, а также новые значения для одного или нескольких полей исходной таблицы. Значения указанных полей будут изменены в тех записях исходной таблицы, которые соответствуют записям результирующего набора. Запросы на обновление позволяют с помощью одного выражения модифицировать сразу множество записей. Например, можно снизить или повысить стоимость доставки всех или некоторой группы товаров на определенный процент.
Прежде чем выполнить запрос, обновляющий данные, убедитесь в том, что изменения затронут только требуемые записи. Для этого достаточно, указав все критерии отбора, выполнить запрос на выборку и просмотреть в режиме таблицы результирующие записи.
Чтобы проиллюстрировать, как может быть использован запрос на обновление, обратимся к простому примеру. Предположим, что необходимо снизить стоимость всех товаров на 5%.
Чтобы внести изменения в записи таблицы, выполните следующее.
1. Создайте новый запрос на выборку и добавьте в него таблицу Товары. Добавьте в бланк запроса поле ЦенаПродажи.
2. Чтобы преобразовать запрос на выборку в запрос на обновление, воспользуйтесь для выбора типа запроса (на обновление записей) кнопкой Обновление группы Тип запроса.
Теперь запрос является запросом на обновление. Кроме того, в бланк запроса добавлена новая строка Обновление.
3. Введите новое значение для обновляемого поля. Так, в ячейку строки Обновление, соответствующую столбцу ЦенаПродажи, следует ввести выражение [ЦенаПродажи]*0,95 (рис. 19).
Если в бланке запроса на обновление не указан критерий отбора записей (строка Условие отбора остается пустой), при выполнении запроса будут модифицированы значения во всех записях таблицы.
4. Щелкните на кнопке Выполнить группы Результаты. Программа Access сообщит о том, какое число записей будет обновлено в таблице. Щелкните на кнопке Да, чтобы завершить выполнение запроса. Чтобы отменить операцию обновления, щелкните на кнопке Нет.
Как и в случае запросов на удаление, при выполнении запросов на обновление записей связанных таблиц программа Access следит за тем, чтобы не были нарушены условия целостности данных.
Рис. 19. Бланк запроса на обновление
10
studfiles.net
Подсчет данных при помощи запроса
В этой статье описано, как подсчитать данные, возвращаемые запросом. Например, в форме или отчете можно подсчитать количество элементов в одном или нескольких полях таблицы или элементах управления. Вы также можете вычислять средние значения, находить наибольшее и наименьшее значения, самую давнюю и самую последнюю дату. Кроме того, в Access предусмотрено средство, называемое строкой итогов, с помощью которого можно подсчитывать данные в таблице, не изменяя структуру запроса.
Выберите нужное действие
Способы подсчета данных
Подсчет данных с помощью строки итогов
Подсчет данных с помощью итогового запроса
Справочные сведения об агрегатных функциях
Способы подсчета данных
Подсчитать количество элементов в поле (столбце значений) можно с помощью функции Число. Функция Число принадлежит к ряду функций, называемых агрегатными. Агрегатные функции выполняют вычисления со столбцами данных и возвращают единственное значение. Кроме функции Число, в Access есть следующие агрегатные функции:
-
Сумма для суммирования столбцов чисел;
-
Среднее для вычисления среднего значения в столбце чисел;
-
Максимум для нахождения наибольшего значения в поле;
-
Минимум для нахождения наименьшего значения в поле;
-
Стандартное отклонение для оценки разброса значений относительно среднего значения;
-
Дисперсия для вычисления статистической дисперсии всех значений в столбце.
В Access предусмотрено два способа добавления функции Count и других агрегатных функций в запрос. Вы можете:
-
Открыть запрос в режиме таблицы и добавить строку итогов. Строка итогов позволяет использовать агрегатные функции в одном или нескольких столбцах в результатах запроса без необходимости изменять его структуру.
-
Создать итоговый запрос. В итоговом запросе вычисляются промежуточные итоги по группам записей. Например, если вы хотите вычислить промежуточную сумму всех продаж по городам или по кварталам, следует использовать итоговый запрос для группировки записей по нужной категории, а затем просуммировать все объемы продаж. С другой стороны, с помощью строки итогов можно вычислить общий итог для одного или нескольких столбцов (полей) данных.
Примечание: Ниже в разделах этой статьи подробно описано применение функции Сумма, однако следует помнить, что вы можете использовать другие агрегатные функции в строках итогов и запросах. Дополнительные сведения об использовании других агрегатных функций см. ниже в разделе Справочные сведения об агрегатных функциях.
Дополнительные сведения о способах использования других агрегатных функций см. в разделе Отображение итогов по столбцу в таблице.
В следующих разделах описаны шаги, которые следует выполнить, чтобы добавить строку итогов, а также описывается использование итогового запроса для подсчета данных. Следует обратить внимание на то, что функция Число работает с большим числом типов данных, чем другие агрегатные функции. Функцию Число можно использовать для любого типа полей, кроме тех, которые содержат сложные повторяющиеся скалярные данные, например поле с многозначными списками.
С другой стороны, многие агрегатные функции работают только с данными в полях, имеющих определенный тип данных. Например, функция Сумма работает только с типами данных "Число", "Действительное" и "Денежный". Дополнительные сведения о типах данных, требуемых для каждой функции, см. ниже в разделе Справочные сведения об агрегатных функциях.
Общие сведения о типах данных см. в статье Изменение типа данных для поля.
К началу страницы
Подсчет данных с помощью строки итогов
Чтобы добавить в запрос строку итогов, откройте его в режиме таблицы, добавьте строку, а затем выберите функцию Число или другую агрегатную функцию, например Сумма, Минимум, Максимум или Среднее. В этом разделе объясняется, как создать простой запрос на выборку и добавить строку итогов.
Создание простого запроса на выборку
-
На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.
-
В диалоговом окне Добавление таблицы дважды щелкните таблицу или таблицы, которые вы хотите использовать в запросе, а затем нажмите кнопку Закрыть.
Выбранные таблицы отображаются в виде окон в верхней части конструктора запросов. На рисунке показана типичная таблица в конструкторе запросов.
-
Дважды щелкните поля таблицы, которые вы хотите использовать в запросе.
Вы можете включить поля, содержащие описательные данные, например имена и описания, но следует обязательно добавить поле, содержащее подсчитываемые значения.
Каждое поле отображается в столбце в бланке запроса.
-
На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.
Результаты запроса отображаются в режиме таблицы.
-
При необходимости переключитесь в Конструктор и скорректируйте запрос. Для этого щелкните правой кнопкой мыши вкладку документа для запроса и выберите команду Конструктор. После этого можно изменить запрос, добавив или удалив поля таблицы. Чтобы удалить поле, выберите столбец в бланке запроса и нажмите клавишу DELETE.
-
При необходимости вы можете сохранить запрос.
Добавление строки итогов
-
Откройте запрос в режиме таблицы. Если база данных имеет формат ACCDB, щелкните правой кнопкой мыши вкладку документа для запроса и выберите команду Режим таблицы.
-или-
Если используется база данных в формате MDB, созданная в более ранней версии Access, на вкладке Главная в группе Режимы щелкните стрелку под кнопкой Режим и выберите значение Режим таблицы.
-или-
Дважды щелкните запрос в области навигации. Запрос будет выполнен, а его результаты будут загружены в таблицу.
-
На вкладке Главная в группе Записи нажмите кнопку Итоги.
Под последней строкой данных в таблице появится новая строка Итог.
-
В строке Итог щелкните поле, по которому вы хотите выполнить подсчет, и выберите в списке функцию Count.
Скрытие строки итогов
Дополнительные сведения об использовании строки итогов см. в разделе Отображение итогов по столбцу в таблице.
К началу страницы
Подсчет данных с помощью итогового запроса
Когда нужно подсчитать некоторые или все записи, возвращаемые запросом, то вместо строки итогов можно воспользоваться итоговым запросом. Например, вы можете подсчитать общее число сделок или число сделок в отдельном городе.
Как правило, итоговый запрос применяется вместо строки итогов тогда, когда требуется использовать значение результата в другой части базы данных, например в отчете.
Подсчет всех записей в запросе
-
На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.
-
В диалоговом окне Добавление таблицы дважды щелкните таблицу, которую вы хотите использовать в запросе, а затем нажмите кнопку Закрыть.
Таблица появится в окне в верхней части конструктора запросов.
-
Дважды щелкните поля, которые вы хотите использоваться в запросе. Обязательно включите поле, по которому будет выполняться подсчет. Подсчитывать можно поля любого типа, кроме тех, которые содержат сложные повторяющиеся скалярные данные, например многозначные списки.
-
На вкладке Конструктор в группе Показать или скрыть нажмите кнопку Итоги.
В бланке появится строка Итог, а в строке для каждого поля запроса будет указано Группировка.
-
В строке Итог щелкните поле, по которому вы хотите выполнить подсчет, и выберите в списке функцию Count.
-
На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.
Результаты запроса отображаются в режиме таблицы.
-
При необходимости вы можете сохранить запрос.
Подсчет записей в группе или категории
-
На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.
-
В диалоговом окне Добавление таблицы дважды щелкните таблицу или таблицы, которые вы хотите использовать в запросе, а затем нажмите кнопку Закрыть.
Таблица (или таблицы) появится в окне в верхней части конструктора запросов.
-
Дважды щелкните поле, содержащее данные категории, а также поле, значения в котором вы хотите подсчитать. Запрос не может содержать других описательных полей.
-
На вкладке Конструктор в группе Показать или скрыть нажмите кнопку Итоги.
В бланке появится строка Итог, а в строке для каждого поля запроса будет указано Группировка.
-
В строке Итог щелкните поле, по которому вы хотите выполнить подсчет, и выберите в списке функцию Count.
-
На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.
Результаты запроса отображаются в режиме таблицы.
-
При необходимости вы можете сохранить запрос.
К началу страницы
Справочные сведения об агрегатных функциях
В следующей таблице перечислены и отписаны агрегатные функции Access, которые можно использовать в строке итогов и в запросах. Помните, что в Access предусмотрено больше агрегатных функций для запросов, чем для строки итогов. Кроме того, при работе с проектом Access (внешней базой данных Access, которая подключается к базе данных Microsoft SQL Server) можно использовать расширенный набор агрегатных функций, предоставляемый SQL Server. Дополнительные сведения о них см. в электронной документации Microsoft SQL Server.
|
Функция |
Описание |
Поддерживаемые типы данных |
|
Сумма |
Суммирует элементы в столбце. Подходит только для числовых и денежных данных. |
"Число", "Действительное", "Денежный" |
|
Среднее |
Вычисляет среднее значение для столбца. Столбец должен содержать числовые или денежные величины или значения даты или времени. Функция игнорирует пустые значения. |
"Число", "Действительное", "Денежный", "Дата/время" |
|
Число |
Подсчитывает число элементов в столбце. |
Все типы данных, за исключением сложных повторяющихся скалярных данных, таких как столбец многозначных списков. Дополнительные сведения о таких списках см. в статьях Руководство по полям, одновременно допускающим несколько значений и Добавление и изменение поля подстановок, одновременно допускающего несколько значений. |
|
Максимум |
Возвращает элемент, имеющий наибольшее значение. Для текстовых данных наибольшим будет последнее по алфавиту значение, причем Access не учитывает регистр. Функция игнорирует пустые значения. |
"Число", "Действительное", "Денежный", "Дата/время" |
|
Минимум |
Возвращает элемент, имеющий наименьшее значение. Для текстовых данных наименьшим будет первое по алфавиту значение, причем Access не учитывает регистр. Функция игнорирует пустые значения. |
"Число", "Действительное", "Денежный", "Дата/время" |
|
Стандартное отклонение |
Показывает, насколько значения отклоняются от среднего. Дополнительные сведения об этой функции см. в статье Отображение итогов по столбцу в таблице. |
"Число", "Действительное", "Денежный" |
|
Дисперсия |
Вычисляет статистическую дисперсию для всех значений в столбце. Подходит только для числовых и денежных данных. Если таблица содержит менее двух строк, Access возвращает пустое значение. Дополнительные сведения о функциях для расчета дисперсии см. в разделе Отображение итогов по столбцу в таблице. |
"Число", "Действительное", "Денежный" |
К началу страницы
support.office.com
Запросы и отчеты в Access
3
Основы СУБДAccess4. Работа с запросами и отчетами
4.1. Создание и фильтрация запросов
Для отбора информации из БД можно использовать запросы, при работе с которыми можно устанавливать фильтры для отбора данных. Создадим запрос, позволяющий отбирать информацию о заказах на основании различных критериев, определяемых с помощью фильтра.
Задание 1. Создайте запрос, выполнив следующие операции:
В окне БД переключиться на вкладку «Запросы».
Щелкнуть кнопку Создать.
В диалоговом окне «Новый запрос» выбрать тип запроса (выберем наиболее простой вариант – «Простой запрос») и щелкнуть кнопку ОК.
В списке «Таблицы/Запросы» выберите строку «Таблица: Заказы». С помощью кнопки > выберите из списка доступных в таблице полей поля «НомерЗаказа», «ДатаПриема», «ДатаИсполнения» и «Оплачен» для включения их в запрос.
В списке «Таблицы/Запросы» выберите строку «Таблица: Клиенты». С помощью кнопки > выберите из списка доступных в таблице полей поле «ФИО» для включения их в запрос.
В списке «Таблицы/Запросы» выберите строку «Таблица: Услуги». С помощью кнопки > выберите из списка доступных в таблице полей поля «Наименование» и «Стоимость» для включения их в запрос.
Щелкните кнопку Далее.
В следующем диалоговом окне установите переключатель «Подробный…» и щелкните кнопку Далее.
Задайте имя запроса «Оплата заказов», установите переключатель «Открытие результатов…» и щелкните кнопку Готово.
Р
езультаты созданного запроса будут представлены на экране в виде таблицы. К этой таблице можно применить фильтр для выделения части информации, нужной пользователю.
Задание 2. С помощью фильтра выведите информацию только об оплаченных заказах. Для этого необходимо:
Выполнить команду Записи Фильтр Изменить фильтр.
Установить с помощью мыши флажок в поле «Оплачен».
Выполнить команду Записи Применить фильтр.
В результате отбора информации с помощью фильтра в таблице остались только записи об оплаченных заказах.
Работать с такой информацией не очень удобно. Поэтому можно для работы с запросами создать формы, как и для работы с таблицами.
Вопросы для самоконтроля:
Как создать запрос к БД?
Создайте запрос, выводящий информацию и неоплаченных заказах.
4.2. Создание и просмотр отчетов
Для вывода результатов выполнения запроса можно использовать отчеты.
Задание 3. Для создания отчета выполните следующие действия:
Переключитесь на вкладку «Отчеты» диалогового окна базы данных заказов.
Щелкните кнопку «Создать».
В диалоговом окне «Новый отчет» выберите строку «Автоотчет: ленточный», а в качестве источника – запрос «Оплата заказов».
Щелкните кнопку ОК и сохраните созданный отчет под именем «Оплата заказов».
Обычно отчет создается с помощью Мастера отчетовилиАвтоотчета. Если отчет, предоставленный компьютером, пользователя не устраивает, то такой отчет можно доработать с помощьюКонструктора.
Задание 4. Внесите в отчет итоговые данные по оплаченным и неоплаченным заказам. Для модификации отчета выполните следующие шаги:
Выделите значок отчета и щелкните кнопку Конструктор.
В окне конструктора щелкните правой кнопкой мыши по вертикальной линейке (слева) и в контекстном меню выберите команду «Сортировка и группировка».
В диалоговом окне команды выберите в столбце (списке) «Поле/выражение» поле «ФИО» и выберите порядок группировки «По возрастанию».
В свойствах группы «включите» заголовок и примечание, установив в соответствующих строках значение «Да».
(Данные в отчете будут сгруппированы по именам клиентов, причем для каждого клиента можно посчитать итоги).
Закройте диалоговое окно.
Сместите нижний колонтитул так, чтобы после примечания группы можно было вставить два вычисляемых поля.
На панели элементов выберите элемент «Поле» и разместите его ниже полосы «Примечание группы ФИО».
Измените текст надписи (подписи) к полю, введя строку «Всего заказов».
Правой кнопкой мыши щелкните по самому полю и выберите в контекстном меню строку «Свойства».
В диалоговом окне свойств поля выберите вкладку «Данные» и в строке «Данные» щелкните кнопку … для вызова построителя выражений.
Двойным щелчком по значку + раскройте папку «Функции» и вложенную в нее папку «Встроенные функции».
Выберите в расположенном справа окне категорию функций «Статистические» и в данной категории выберите функцию Count, вставьте ее в выражение двойным щелчком или щелчком по кнопке «Вставить» после выбора (в окне построителя выражений должно появиться выражение = Count («expr»)).
Выражение в скобках нужно заменить на название поля, количество которых будет подсчитываться. Для этого дважды щелкните мышью по строке «expr» (она будет выделена) и раскройте в расположенном ниже окне папку «Оплата заказов». Для отчета с таким именем в окне справа будут выведены все включенные в него элементы. Найдите элемент «Стоимость» и дважды щелкните по нему. Построитель выражений сформирует строку «=Count([Стоимость])».
Закройте окно построителя выражений.
Закройте диалоговое окно свойств поля.
Аналогичным образом разместите рядом вычисляемое поле «Общая стоимость», задав для вычисления данных выражение «=Sum([Стоимость])» (его можно ввести с клавиатуры или построить с помощью построителя выражений).
Закройте окно конструктора отчетов.
Итоговые данные по группе (по каждому клиенту) внесены в отчет. Просмотрите его с помощью соответствующей кнопки.
Задание 5. Внесте в отчет итоговые данные по всем заказам. Для этого снова откройте конструктор отчетов и выполните следующие шаги:
Расширьте в окне конструктора область примечания отчета.
Выделите в области примечания группы «ФИО» размещенные в этой области вычисляемые поля и скопируйте их с помощью команды копировать.
Выделите область примечания отчета в выполните вставку из буфера полей.
Переименуйте первое поле, введя в надпись строку «Итого заказов» вместо «Всего заказов».
Разместите новое поле, присвоив ему имя «Оплачено» и задав для вычисления данных выражение
=DSum("[Стоимость]";"[Оплата заказов]";"[Оплата заказов]![Оплачен]")
(суммируется стоимость оплаченных заказов, данные для суммирования выбираются из поля «Стоимость» отчета «Оплата заказов» по полю «Оплачен»).
Скопируйте это поле и вставьте его ниже, изменив надпись на «Не оплачено» и выражение для вычисления значения соответственно на
=DSum("[Стоимость]";"[Оплата заказов]";" not [Оплата заказов]![Оплачен]")
Отчет готов. Его можно дополнительно отформатировать, просмотреть и выполнить для обработки в WordилиExcelс помощью командыСвязь с Office из менюСервис. Файл отчета вWordприлагается.
studfiles.net
Видео: использование условий в запросе в Access 2013
В этом видео рассмотрены основы добавления условий в запросы и приведены советы по предотвращению проблем.
Другие видеоролики в этом курсе
Этот видеоролик входит в обучающий курс Использование условий в запросах Access 2013. Вы можете загрузить весь курс или просмотреть видео в Интернете.
-
Видео: использование условий в запросе в Access 2013. (Вы находитесь здесь!) В этом видео рассмотрены основы добавления условий в запросы и приведены советы по предотвращению проблем.
-
Видео: использование функций AND и OR с несколькими условиями. В этом ролике рассказано о применении логики AND и OR в условиях.
-
Видео: использование операторов NOT, IN, LIKE и BETWEEN. Используйте логические операторы для включения и исключения данных в набор, а также для поиска диапазонов данных.
-
Видео: использование подстановочных знаков в условиях запроса. Используйте подстановочные знаки для при поиске данных, если нужные значения точно не известны.
Готовы продолжить обучение? См. полный список обучающих курсов по Access 2013.
support.office.com
