Acc2007_L3_запросы

MS Access

Запросы

ЗАПРОСЫ

Запрос – объект базы данных, предназначенный для обработки данных.

С помощью запросов можно:

отобрать записи, соответствующие заданным условиям;

объединить в виде одной таблицы данные из нескольких таблиц;

выполнить вычисления над значениями полей.

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

В Microsoft Access 2007 существует несколько видов запросов. При создании запроса с помощью Конструктора по умолчанию формируется запрос на выборку.

Запросы на выборку

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

1.На вкладке Создание в группе Другие щелкнуть по кнопке

Конструктор запросов.

2.На экране появится окно Конструктора, а также окно

Добавление таблицы.

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

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

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

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

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

перетащить мышью нужное поле из списка полей таблицы в верхней части окна Конструктора в строку Поле бланка запроса;

дважды щелкнуть мышью по имени нужного поля в списке полей таблицы;

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

1

MS Access

Запросы

7. Если нужно использовать все поля какой-либо таблицы или запроса, можно не выбирать каждое поле по отдельности, а использовать подстановочный знак «*» в списке полей этой таблицы или запроса.

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

8.Для полей, которые должны выводиться на экран, установить флажок в строке Вывод на экран.

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

10.Ввести условия отбора. Для ввода условий отбора используются строки Условие отбора и или. Условия, находящиеся в одной строке, связываются логическим оператором И; условия, находящиеся в разных строках – логическим оператором ИЛИ.

Условия отбора состоят из операторов сравнения (<, <=, >, >=, =, <>, Between, In, Like) и констант (например, >#1/1/90#, «Морозильник»). Имена полей в условиях отсутствуют, так как поле, на которое накладывается условие, определяется столбцом, в который это условие вводится.

11.Сохранить запрос, выбрав команду Сохранить в меню кнопки Office или щелкнув по кнопку Сохранить на панели быстрого доступа. В открывшемся диалоговом окне ввести имя запроса.

2

MS Access

Запросы

Перед выполнением запроса можно проверить правильность выборки. Для этого надо перейти в режим таблицы: на вкладке Работа с запросами – Конструктор в группе Результаты в меню кнопки Вид

выбрать команду Режим таблицы или в контекстном меню бланка запроса выбрать команду Режим таблицы.

Выполнить запрос можно одним из следующих способов;

В режиме Конструктора на вкладке Работа с запросами – Конструктор в группе Результаты щелкнуть по кнопке Выполнить;

в области переходов дважды щелкнуть по имени запроса;

в области переходов в контекстном меню запроса выбрать команду Открыть.

Параметрические запросы

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

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

Например, для создания параметрического запроса о заказах на заданный товар надо в строку Условие отбора для поля Наименование товара ввести текст: [Введите название товара]. После запуска запроса на экран будет выведено диалоговое окно, в которое вводится название товара.

Таких параметров может быть несколько.

Запросы с вычисляемыми полями

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

Им я по л я : в ыр а же н ие

Для построения выражений в Microsoft Access имеется утилита,

называемая Построитель выражений.

Чтобы вызвать Построитель выражений, надо поместить курсор в ту ячейку, в которую надо ввести выражение, и на вкладке Работа с запросами – Конструктор в группе Настройка запроса щелкнуть по кнопке Построитель или выбрать команду Построить в контекстном меню ячейки. На экран будет выведено окно Построителя выражений.

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

3

MS Access

Запросы

Чтобы вставить в выражение имя поля, надо:

1.В первом списке нижней части окна дважды щелкнуть по строке

сназванием нужного объекта (Таблицы или Запросы). Откроется список таблиц (или запросов) текущей базы данных.

2.Дважды щелкнуть мышью по имени нужного объекта. Во втором списке появятся имена полей этого объекта.

3.Во втором списке щелкнуть по имени нужного поля, а затем по кнопке Вставить (или дважды щелкнуть по имени нужного поля).

Чтобы вставить в выражение функцию, надо:

1.В первом списке нижней части окна дважды щелкнуть по строке

Функции, затем – по строке Встроенные функции.

2.Выбрать нужный тип функции во втором списке.

3.Щелкнуть по строке с именем нужной функции в третьем списке, затем – по кнопке Вставить (или дважды щелкнуть по имени нужной функции).

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

Итоговые запросы

Итоговые запросы используются для выполнения вычислений над группами записей.

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

вкоторой каждой группе соответствует одна строка.

4

MS Access

Запросы

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

вгруппах, надо:

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

2.На вкладке Работа с запросами – Конструктор в группе

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

В бланке запроса появится новая строка Групповая операция. По умолчанию для всех полей в этой строке задана установка Группировка.

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

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

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

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

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

Перекрестные запросы – особый вид итоговых запросов, представляющих данные в виде матрицы.

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

Для создания перекрестного запроса надо:

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

2.На вкладке Работа с запросами – Конструктор в группе Тип запроса щелкнуть по кнопке Перекрестный.

В бланке запроса появятся две дополнительные строки: Групповая операция и Перекрестная таблица.

5

MS Access

Запросы

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

выбрать значение Заголовки строк.

4.Для поля, значения которого должны быть представлены в запросе как заголовки столбцов, в строке Групповая операция оставить стандартное значение Группировка, а в строке Перекрестная таблица выбрать значение Заголовки столбцов.

5.Для поля, значения которого должны быть использованы для расчетов, в строке Перекрестная таблица выбрать из раскрывающегося списка строку Значения, а в строке Групповая операция – нужную статистическую функцию.

6.При необходимости задать условия отбора.

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

Запросы-изменения

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

Существует четыре типа запросов-изменений:

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

запрос на обновление записей изменяет данные в группе записей.

запрос на добавление записей добавляет группу записей из одной или нескольких таблиц в одну или несколько других таблиц;

запрос на удаление записей удаляет записи из одной или нескольких таблиц;

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

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

1.На панели сообщений щелкнуть по кнопке Параметры. Откроется диалоговое окно Параметры Microsoft Office.

2.Установить переключатель в положение Включить это содержимое.

6

MS Access

Запросы

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

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

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

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

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

3.На вкладке Работа с запросами – Конструктор в группе Тип запроса щелкнуть по кнопке Создание таблицы.

4.В открывшемся диалоговом окне ввести имя новой таблицы.

5.Включить содержимое базы данных.

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

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

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

Запрос на обновление нельзя использовать:

для добавления новых записей в таблицу;

для удаления записей целиком из таблицы;

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

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

для полей с типом данных «Счетчик»;

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

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

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

3.На вкладке Работа с запросами – Конструктор в группе Тип запроса щелкнуть по кнопке Обновление. В бланке запроса появится новая строка Обновление.

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

5.Включить содержимое базы данных.

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

7

MS Access

Запросы

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

Запрос на добавление записей добавляет записи из одной или нескольких исходных таблиц (или запросов) в одну или несколько результирующих таблиц. Чтобы создать запрос на добавление, надо:

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

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

3.На вкладке Работа с запросами – Конструктор в группе Тип запроса щелкнуть по кнопке Добавление.

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

Добавление.

5.В строку Добавление ввести имена полей в таблице-получателе,

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

6.Включить содержимое базы данных.

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

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

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

1.В бланк запроса из списка полей таблицы, из которой следует удалить записи, переместить символ «*», а также добавить поля, по которым следует задать условия отбора.

2.Задать условия отбора.

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

4.На вкладке Работа с запросами – Конструктор в группе Тип запроса щелкнуть по кнопке Удаление. В бланке запроса появится строка Удаление. В этой строке в столбце с символом «*» появится значение Из, а в столбцах, содержащих условия отбора – значение

Условие.

5.Включить содержимое базы данных.

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

8

MS Access

Запросы

Практическая работа 3

I. Запросы для базы данных Торговый дом

Запросы на выборку

1.Создать запрос о заказах на заданный товар. Запрос должен содержать поля: Номер заказа, Наименование товара, Клиент,

Продавец. Сортировка – по номерам заказов.

2.Создать запрос о заказах на товары с ценой выше заданной. Поля запроса: Номер заказа, Наименование товара, Розничная цена.

Сортировка – по возрастанию цены.

3.Создать запрос о заказах, выполненных одним из двух заданных

продавцов. Запрос должен содержать все поля таблицы Заказы. Сортировка – по номерам заказов.

4.Создать запрос о заказах на товары, производимые заданной фирмой. Поля запроса, выводимые на экран: Номер заказа,

Наименование товара, Клиент, Продавец. Поле Фирма-

производитель использовать для задания условия отбора. Сортировка – по номерам заказов.

5.Создать запрос о продавцах с датой рождения в заданном диапазоне. Создать запрос о заказах на товары заданной фирмы с розничной ценой в заданном диапазоне. Сортировка – по фирмам. Сортировка – по дате рождения.

Параметрические запросы

6.Создать параметрический запрос о заказах на заданный товар.

Поля, выводимые на экран: Номер заказа, Название фирмы, Клиент,

Продавец. Параметр – наименование товара. Сортировка – по номерам заказов.

7.Создать запрос с двумя параметрами о заказах, сделанных заданным клиентом на товар заданной фирмы. Поля запроса: Номер заказа, Наименование товара, Продавец. Параметры – клиент и фирма-

производитель. Сортировка – по наименованию товара.

Запросы с вычисляемыми полями

8.Создать запрос Расчет стоимости. Стоимость вычисляется как произведение цены товара на количество. Если количество товара меньше заданного (например, 10 шт.), берется розничная цена, иначе – оптовая. Для расчета цены использовать функцию Iif. Поля запроса: Номер заказа, Наименование товара, Количество, Цена, Стоимость заказа.

Сортировка записей – по номерам заказов.

9.Создать запрос о продавцах с полями Фамилия продавца, Возраст, Место рождения. Возраст - вычисляемое поле (рассчитывается как частное от целочисленного деления разности между текущей датой и датой рождения на 365). Сортировка – по возрасту.

9

MS Access

Запросы

Итоговые запросы

10.Создать запрос, вычисляющий суммарную стоимость заказов на товары каждой фирмы. Группировка – по полю Фирма-производитель. Суммирование – по полю Стоимость заказа. Сортировка – по названиям фирм.

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

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

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

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

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

14.Создать запрос на создание таблицы Стоимость заказов с

полями Номер заказа, Наименование товара, Название фирмы, Количество, Цена, Стоимость заказа, Клиент, Продавец. Выполнить запрос.

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

15.Создать запрос на обновление таблицы Товары: задать снижение розничных цен на товары заданного производителя на 5%, а оптовых – на 10%. Выполнить этот запрос. Для внесения соответствующих изменений

втаблицу Стоимость заказов повторно выполнить запрос на создание этой таблицы.

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

16.Cкопировать структуру таблицы Заказы под именем Архив заказов: в контекстном меню таблицы выбрать команду Копировать, затем в контекстном меню окна – команду Вставить, в открывшемся диалоговом окне выбрать параметр вставки только структуру.

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

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

18.С помощью запроса на удаление записей удалить из таблицы Заказы записи, помещенные в таблицу Архив заказов.

10