ПРАКТИЧЕСКАЯ РАБОТА №4 СОЗДАНИЕ СЛОЖНЫХ ЗАПРОСОВ, СОЗДАНИЕ МОДИФИЦИРУЮЩИХ ЗАПРОСОВ В СРЕДЕ MS ACCESS

ПРАКТИЧЕСКАЯ РАБОТА №4

СОЗДАНИЕ СЛОЖНЫХ ЗАПРОСОВ, СОЗДАНИЕ МОДИФИЦИРУЮЩИХ ЗАПРОСОВ В СРЕДЕ MS ACCESS

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

Запросы с выражениями

1.                Откройте базу данных «Шоколад».

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

3.                Создайте запрос, отображающий кондитерские изделия с названием, начинающимся на «К», используя оператор LIKE в поле «Название».

4.                Создайте запрос, отображающий стоимость заказов на каждое изделие, используя выражение: СуммаЗаказа: [Стоимость]*[Заказ]. Включите в запрос все поля таблицы «Изделия». Поля «Стоимость» и «Заказ» на экран не выводите ( снимите галочку «Вывод на экран» в бланке запроса). Назовите запрос «Общая сумма заказов».

5.                Создайте запрос с параметром, отображающий конфеты по вводимому при открытии запроса сорту начинки, используя в условии отбора выражение LIKE [Какой сорт начинки] & ”*”. Откройте запрос в режиме таблицы. Обратите внимание, что для ввода параметра можно набрать только начальные буквы.

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

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

 Запросы с использованием статистических функций

Первый запрос, который мы предлагаем построить, должен ответить на вопрос: "Конфеты с каким шоколадом пользуются большим спросом?". Воспользуемся уже существующим в базе «Шоколад» запросом «Общая сумма заказов»

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

2.                 В открывшемся окне «Добавление таблицы» перейдите на вкладку «Запросы» и выберите запрос «Общая сумма заказов».

Рис.4.1. Диалоговое окно «Добавление таблицы»

3.                 Перетащите в бланк заказа поля «Шоколад_Код» и «СуммаЗаказа».

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

5.                 Теперь нужно в строке Групповая операция задать необходимые статистические функции. Для поля « Шоколад_Код» оставьте значение Группировка, для поля «СуммаЗаказа»выберите из списка функцию Sum (рис. 4.2).

Рис.4.2. Использование статистических функций в запросе

6.                 Нажмите кнопку Запуск на панели инструментов, чтобы посмотреть результаты запроса. Вы получите таблицу, которая содержит список сортов шоколада, и для каждого сорта указывается общая сумма заказов на изделия с этим сортом шоколада (рис. 4.3).Из таблицы видно, что самая большая сумма заказов - на изделия с молочным шоколадом.

Рис.4.3. Результат выполнения запроса

7.                 Закройте запрос, сохранив eго под именем "Итоги по сорту шоколада".

8.                 Создайте аналогичный запрос и определите среднюю  и минимальную стоимость конфет по сорту шоколада .

9.                 Создайте аналогичный запрос "Итоги по сорту начинки".

10.            Создайте новый запрос на основе таблицы «Изделия»

11.            Включите в бланк запроса два раза поле «Начинка_Код». Для одного поля оставьте значение Группировка, для другого поля выберите из списка функцию Count .

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

Рис.4.4. Результат выполнения запроса с  использованием функции  Count

13.            Создайте аналогичный запрос с группировкой по сорту ореха.

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

Модифицирующие запросы

Создание таблиц с помощью запроса

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

1.                 Создать запрос на выборку.

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

3.                 Выполнить запрос на изменение, тем самым поместив отобранные записи в новую таблицу.

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

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

2.                 Удалите в бланке запроса критерий выборки: <100, в столбце «Заказ» добавьте условие <5.

Преобразование запроса на выборку в запрос на изменение

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

1.                 Выберите команду меню Запрос, Создание таблицы  [Меню Запрос доступно только в режиме Конструктора запроса.] или щелкните по стрелке на кнопке Тип запроса   на панели инструментов и выберите из списка тип Создание таблицы (рис. 4.5).

Рис. 4.5. Меню  Тип запроса

Диалоговое окно Создание таблицы показано на рис. 4.6.

2.                 В текстовом поле имя таблицы введите описательное имя для новой таблицы, например: «Изделия_мал_спрос».

Нет  рисунка

Рис. 4.6. Диалоговое окно Создание таблицы

Внимание

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

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

Выполнение запроса на изменение

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

Для выполнения запроса:

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

Внимание

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

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

Рис. 4.7. Предупреждающее сообщение о числе новых записей

3.                 Поскольку запрос запускается впервые, будет создана новая таблица "«Изделия_мал_спрос».

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

5.                 Можно запустить запрос двойным щелчком мышки на его имени в окне базы данных.

6.                 Щелкните по ярлыку Таблицы в окне базы данных и убедитесь, что в. списке появилось новое название таблицы.

7.                 Дважды щелкните мышью по значку этой таблицы, чтобы ее открыть. Она содержит те же данные, что и запрос на создание таблицы в режиме таблицы (рис. 4.8.).

Рис. 4.8. Таблица, созданная при помощи запроса

Внимание

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

Создание запросов на добавление данных

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

1.                 Раскройте список таблиц в окне базы данных и выделите таблицу "Изделия".

2.                 Скопируйте таблицу в буфер обмена, нажав комбинацию клавиш <Ctrl>+<C> или команду «Копировать» контекстного меню.

3.                 Вставьте таблицу из буфера обмена, нажав комбинацию клавиш <Ctrl>+<V> или команду «Вставить» контекстного меню. Появляется диалоговое окно Вставка таблицы.

Рис. 4.9. диалоговое окно Вставка таблицы

4.                 В группе Параметры вставки выберите переключатель «Только структура».

5.                 В поле имя таблицы введите имя, например «Изделия(архив)». Нажмите кнопку ОК или клавишу <Enter>.

В списке появляется новая таблица, пока пустая, т. к. мы скопировали только структуру таблицы .Теперь создадим запрос на добавление, который позволит перенести в новую таблицу данные из таблицы "Изделия".

1.                 Раскройте список таблиц в окне базы данных и выделите в нем таблицу "Изделия".

2.                 Сначала создайте запрос на выборку нужных записей. Перетащите из списка полей таблицы " Изделия " все поля в строку бланка запроса Поле, а из таблицы «Начинка» поле «Сорт начинки»

3.                 В строке Условие отбора столбца «Сорт начинки» введите условие отбора "Кофейный крем"

4.                 Запустите запрос на выборку, чтобы проверить, правильно ли отобраны записи. Оказались отобранными 5 записей.

5.                 Выберите команду Запрос, Добавление или щелкните левой кнопкой мыши по стрелке на кнопке Тип запроса и выберите из списка элемент Добавление. Появляется диалоговое окно Добавление, аналогичное диалоговому окну Создание таблицы  (рис. 4.10).

6.                 В поле имя таблицы необходимо выбрать из списка имя таблицы, в которую будут добавляться записи. В данном случае по умолчанию в нем указана таблица "Изделия (архив)", которую мы только что создали. Нажмите кнопку ОК. (Если бы таблица, в которую нужно добавить записи, находилась в другой базе данных, необходимо было бы выбрать переключатель в другой базе данных и затем в поле имя файла указать имя файла MDB, который содержит требуемую таблицу.)

Рис. 4.10. Диалоговое окно Добавление

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

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

10.            Сохраните запрос, дав ему имя: «Добавление».

Замечание

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

Создание запросов на обновление таблиц

Запросы на обновление используются главным образом для того, чтобы внести изменения сразу в большое количество записей с помощью одного запроса. Классическим примером такого запроса является изменение цены в таблице "Изделия" на некоторый постоянный коэффициент, например можно увеличить цену на изделия на 20%. Чтобы создать такой запрос:

1.                 Сначала создайте запрос на выборку. Перетащите из списка полей таблицы " Изделия " все поля в строку бланка запроса Поле.

2.                 Теперь изменим запрос, превратив его в запрос на обновление. Для этого выполните команду меню Запрос, Обновление или выберите при помощи кнопки Тип запроса. Изменяется заголовок запроса и появляется дополнительная строка Обновление. При этом исчезают строки Сортировка и Вывод на экран (рис. 4.11).

3.                 Теперь нужно в строку Обновление ввести выражение, по которому будет вычисляться новая цена: [Стоимость]*0,2. В других случаях можно вводить константу, например, если нужно поменять дату во многих записях на текущую.

4.                 Теперь можно выполнить запрос. Для этого нажмите кнопку Запуск на панели инструментов. Так же, как и при добавлении записей в таблицу, Microsoft Access выдаёт сообщение о количестве обновляемых записей и запрашивает подтверждение на обновление. Вы можете подтвердить обновление записей или отвергнуть.

5.                 Сохраните запрос, дав ему имя: «Обновление».

Рис. 4.11. Диалоговое окно Обновление

Совет

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

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

Создание запросов на удаления записей

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

Если две таблицы связаны отношением "один-ко-многим", нельзя удалять записи из таблицы "один" если в таблице "многие" присутствуют соответствующие им записи. Сначала должны быть удалены записи в таблице "многие" и только потом — соответствующие им записи в таблице "один". Для того чтобы упростить этот процесс, MS Access позволяет при определении связей между таблицами установить флажок каскадное удаление связанных записей.

Для примера создадим запрос на удаления изделий с темным шоколадом.

1.                 Создайте сначала запрос на выборку на основании двух таблиц: «» и, включите все поля таблицы «Изделия» с помощью * и поле «Сорт шоколада» таблицы «Шоколад».

2.                 Преобразуйте запрос на выборку в запрос на удаление записей любым способом. В бланке запроса появится строка Удаление(рис. 4.12).. В первом столбце строки Удаление показывается значение Из, которое указывает, что будут удаляться записи из таблицы " Изделия ". Во втором столбце строки Удаление  вы видите значение Условие что указывает на использование этого столбца для определения критерия отбора удаляемых записей. Введите в графу «Условия отбора» значение «темный».

Рис. 4.12. Бланк запроса на Удаление

3.                 Откройте запрос в режиме таблицы (кнопка «Вид») и убедитесь, что будут удалены именно записи с темным шоколадом

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

5.                 Сохраните запрос, дав ему имя: «Удаление».

Внимание

Такое каскадное удаление записей возможно, если в схеме данных установлен флажок - каскадное удаление связанных записей. Если удаления не произойдет, откройте схему данных и измените параметры связи.

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

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

1.                 Создайте новый запрос в режиме Конструктора и добавьте в него таблицы «Изделия», «Шоколад» и «Орех».

2.                 Перетащите поля «Сорт шоколада» из таблицы «Шоколад», затем «Сорт ореха» из таблицы «Орех» и поле «Заказы» из таблицы «Изделия» в первые три столбца бланка запроса.

3.                 Выберите команду меню Запрос, Перекрестный или выберите Перекрестный при помощи кнопки Тип запроса. Заголовок окна запроса «Запрос1:на выборку» изменится на «Запрос1 перекрестный запрос» .Кроме того, в бланк запроса будут добавлены строки Перекрестная таблица и Групповая операция, в которую во всех столбцах автоматически вводится операция Группировка (рис. 4.13).

4.                 Выберите в списке ячейки Перекрестная таблица столбца «Сорт ореха» значение Заголовки строк.

5.                 Выберите в списке ячейки Перекрестная таблица столбца «Сорт шоколада» значение Заголовки столбцов..

6.                 Выберите в списке ячейки Групповая операция столбца «Заказы» операцию Sum, ), а затем в ячейке Перекрестная таблица — значение Значение .

Рис. 4.13. Бланк перекрестного запроса

7.                 Нажмите кнопку Запуск или откройте запрос в режиме таблицы.

8.                 Сохраните запрос.

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

Рис. 4.14. Диалоговое окно Новый запрос

9.                 Создайте аналогичный запрос на основе запроса «Общая сумма заказов», используя режим мастера. В качестве заголовков строк используйте поле «Сорт ореха», заголовков столбцов «Сорт шоколада», в качестве Итога – сумму по полю «Стоимость»

Рис. 4.15. Окно Мастера перекрестных таблиц

10.            Просмотрите результат - рис 4.16. Обратите внимание на столбец «Итоговое».

Рис. 4.16. Перекрестная таблица

Скачано с www.znanio.ru