Конспект по информатике на тему "ЗАПРОС С ПАРАМЕТРОМ"

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. Бланк запроса на обновление