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

Лабораторная работа №7

Запросы-действия

Цель занятия

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

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

MS Access позволяет с помощью специальных запросов изменять содержимое таблиц БД. Эти запросы называются модифицирующими запросами (или запросами-действиями).

MS Access реализует четыре типа модифицирующих запросов:

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

· запросы на удаление;

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

· запросы на обновление.

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

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

Для решения этой задачи потребуется создать два запроса:

1. Запрос на создание таблицы, с помощью которого можно будет перенести из таблицы «Заказы» записи за предыдущие годы в новую таблицу.

2. Запрос на удаление записей за предыдущие годы из таблицы «Заказы».

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

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

Для выполнения поставленной задачи надо:

1. Перейти на вкладку Создание ленты инструментов и выбрать команду Конструктор запросов из группы Другие.

2. Добавить в список полей таблицу «Заказы».

3. Теперь необходимо внести в бланк QBE поля, данные из которых нужно будет поместить в новую таблицу. В нашем случае это все поля таблицы «Заказы». Для этого надо дважды щелкнуть мышью по строке со звездочкой (*) в верхней части списка полей таблицы «Заказы» (рис. 7.1).

рис. 7.1. Добавление всей таблицы в бланк запроса QBE

В строку Поле MS Access поместил название таблицы и звездочку, обозначающую шаблон Все поля указанной таблицы. Поэтому в запрос будет помещен полный набор полей таблицы «Заказы».

4. Теперь определим, какие записи таблицы «Заказы» будут отобраны для копирования в новую таблицу. Пусть это будут заказы, исполненные в 2006 году. Для этого создадим вычисляемое поле, возвращающее значение года из поля Дата размещения и зададим для него условие отбора. В свободном столбце бланка QBE в строке Поле запишем:

Год: Year([Дата размещения])

А в строке Условие отбора запишем нужный год - 2006 (рис 7.2).

рис. 7.2. Задание условия отбора для переноса данных в новую таблицу

Чтобы определить созданный запрос как запрос на добавление новой таблицы нужно в группе Тип запроса контекстной вкладки Работа с запросами – Конструктор ленты инструментов выбрать кнопку Создание таблицы . В группе Тип запроса контекстной вкладки Работа с запросами – Конструктор ленты инструментов также присутствуют значки других модифицирующих запросов:

- запрос на добавление;

- запрос на обновление;

- запрос на удаление.

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

5. В результате выбора Запроса на создание таблицы MS Access выведет диалоговое окно, в котором попросит задать имя создаваемой с помощью этого запроса таблицы. Можно назвать новую таблицу «Заказы – архив» и нажать кнопку ОК (рис. 7.3).

рис. 7.3. Окно диалога для создания новой таблицы с помощью запроса

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

Выполнение запроса приведет к созданию новой таблицы в БД. Для выполнения запроса-выборки можно нажать одну из двух кнопок: Вид или Выполнить . В случае модифицирующего запроса нажатие кнопки Вид не приведет к его выполнению, а лишь только приведет к отображению в режиме таблицы данных, которые будут модифицированы при выполнении запроса. Поэтому целесообразно использовать кнопку Вид для предварительного просмотра отобранных данных, подлежащих модификации. Нажатие кнопки Выполнить приведет к выполнению запроса (в нашем случае - созданию новой таблицы «Заказы – архив» и копированию в нее всех записей о заказах за 2006 год).

Отмена выполнения запроса-действия невозможна!

7. Для выполнения запроса необходимо нажать кнопку Выполнить. При этом MS Access выдаст предупреждение о создании новой таблицы и о невозможности отмены операции. Подтвердить выполнение запроса можно нажатием кнопки Да (рис. 7.4).

рис. 7.4. Окно предупреждения при выполнении запроса на создание таблицы

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

Запрос на удаление

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

Создать его можно следующим образом:

1. Перейти на вкладку Создание ленты инструментов и выбрать команду Конструктор запросов из группы Другие.

2. Добавить в список полей таблицу «Заказы».

По умолчанию MS Access предлагает создание запроса - выборки. С помощью кнопки в группе Тип запроса установить Запрос на удаление. При этом изменится вид Бланка QBE - вместо строк бланка Сортировка и Вывод на экран появилась новая строка Удаление.

В поле Удаление может быть одно из двух значений:

· Из;

· Условие.

Значение Из устанавливает удаление данных из выбранного поля. Значение Условие определяет условие отбора для данных, которые следует удалить.

3. Занести в Бланк QBE все поля этой таблицы. В поле Удаление должно автоматически установиться значение Из (рис. 7.5).

рис. 7.5. Пример запроса на удаление с условием отбора

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

Год: Year([Дата размещения])

Зададим условие отбора для этого поля. Чтобы запрос мог удалять записи за любой задаваемый год, запишем в строке Условия отбора:

[Введите год]

В строке Удаление должно быть установлено значение Условие (рис. 7.5).

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

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

5. Выполнить запрос, нажав кнопку Выполнить.

6. Подтвердить выполнение запроса.

В дальнейшем для пополнения архивной таблицы потребуется еще один запрос - Запрос на добавление.

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

Так как требуется добавлять записи в таблицу «Заказы – архив», созданную запросом «Создание архива», то можно не создавать новый запрос, а воспользоваться этим запросом, слегка его изменив.

Для этого надо выполнить следующие операции:

1. Открыть запрос «Создание архива» в режиме Конструктора.

2. Изменить тип запроса с помощью кнопки Добавление в группе Тип запроса контекстной вкладки Работа с запросами - Конструктор ленты инструментов. MS Access попросит ввести имя таблицы, в которую будут добавляться данные с помощью создаваемого запроса. Так как используется уже готовый запрос, который ранее был настроен на создание нужной таблицы, то менять что-либо в диалоговом окне не нужно.

3. Нажав кнопку ОК, перейти к следующему этапу создания запроса. Собственно говоря, запрос почти готов. В Бланк QBE внесены все поля и задано условие отбора для добавляемых данных.

Бланк QBE снова изменился. В отличие от Бланка QBE запроса-выборки вместо строки Вывод на экран отображается строка Добавление, которая показывает, какие записи будут добавлены в текущее поле указанной таблицы (в данном случае - все поля таблицы «Заказы» за указанный год).

4. Чтобы с помощью этого запроса можно было добавлять записи за любой год, не изменяя при этом самого запроса, заменить в строке Условие запроса ранее заданное условие (2006) на новое:

[Введите год]

5. Сохранить запрос в текущей БД под именем «Дополнение архива», выполнив команду Сохранить как... из меню кнопки Office.

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

6. Выполнить запрос одним из описанных выше способов. MS Access предупредит об изменении данных в таблице и о количестве добавляемых записей. На запрос Года размещения добавляемых записей ввести 2006.

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

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

Это можно сделать с помощью Запроса на обновление.

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

2. Добавить в список полей нужную таблицу.

3. Изменить тип запроса на Обновление.

4. Внести в строку Поле Бланка QBE поле, в котором надо сделать изменения.

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

6. В строке Условие отбора надо задать, какие значения должны изменяться (рис. 7.7).

рис. 7.7. Пример запроса на обновление

7. Выполнение запроса приведет к указанным изменениям.

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

- в строке Обновление заменить введенное значение на

[Новое значение];

- в строке Условие отбора заменить введенное значение на

[Старое значение].

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

10. Выполнить запрос.

Задания

1. Создайте с помощью запроса таблицу «Архив», в которой будет храниться информация о товарах, поступивших на склад в 2007 году. Запрос сохраните под именем «СозданиеАрхива».

(Замечания:

а. В таблице «Архив» должны присутствовать все поля таблицы «Товар».

б. Условие на год записать с помощью функции Year.

в. Поле Год в результирующую таблицу не включать!)

2. С помощью запроса добавьте в таблицу «Архив» записи, соответствующие товарам, поступившим на склад в 2008 г. Запрос сохраните под именем «ДобавлениеВАрхив».

(Замечание: Условие на год записать с помощью предиката Like.)

3. Создайте копию таблицы «Архив» и сохраните ее под именем «АрхивКопия».

4. На товары, попавшие в «АрхивКопия» за 2007 г., сделайте с помощью запроса скидку в размере 50% от розничной и оптовой цен. Запрос сохраните под именем «Скидка2007».

5. На товары, попавшие в «АрхивКопия» за 2008 г., сделайте с помощью запроса скидку в размере 30% от розничной и оптовой цен. Запрос сохраните под именем «Скидка2008».

6. (*) Создайте параметрический запрос, позволяющий делать скидку с указанием ее размера для оптовой и розничной цен на товары в «Архиве» за указанный год поступления. Запрос сохраните под именем «Скидка».

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

7. Откройте таблицу «Архив» в режиме Конструктора и сделайте поле КодТовара ключевым.

8. Удалите с помощью запроса из таблицы «Товар» записи, соответствующие товарам, перемещенным в «Архив». Запрос сохраните под именем «УдалениеЗаписей».

(Замечание: Добавьте таблицы «Товар» и «Архив» и установите связь между ними.)

9. (*) Получите с помощью SQL-запроса общую таблицу, содержащую всю информацию о товарах, на основе таблиц «Товар» и «Архив» и включающую все поля таблиц «Товар» и «Архив». Запрос сохраните под именем «ОбщийСписокТоваров».

Обратите внимание на значки и размещение созданных запросов в области переходов!

Контрольные вопросы

1. Какие запросы называются модифицирующими?

2. Какие типы модифицирующих запросов Вы знаете?

3. Каким образом создаются модифицирующие запросы?