Лабораторная работа №4: Создание и использование запросов

Придумайте выражения в фильтрах (расширенный фильтр) с использованием логических операторов, сохраните каждый фильтр (не менее 5).

Цели работы:

1. закрепить навыки по редактированию таблиц;

2. познакомиться с основными видами запросов;

3. научиться создавать запросы на выборку различными способами.

Ход работы

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

Задание 1. Создайте запрос на выборку с именем Товары с помощью мастера запросов

Для создания запроса с помощью мастера необходимо выполнить следующие действия:

1. Открыть БД.

2. Перейти на вкладку Создание (Рис. 16).

Рис. 16 Вкладка Создание

3. Нажать кнопку Мастер запросов в группе Другие. Откроется первое окно мастера запросов (Рис. 17).

Рис. 17 Первое окно мастера запросов – создание простого запроса

4. В появившемся окне предложено четыре варианта создания запросов. Выберите Простой запрос и нажмите кнопку ОК. откроется следующее окно мастера запросов (рис. 18).

Рис. 18 Окно мастера для выбора таблицы

5. Для создания запроса в раскрывающемся списке Таблицы и запросы выбрать необходимую таблицу.

6. Из списка Доступные поля переместите нужные поля в список Выбранные поля, активизировав их и нажав на кнопку (Рис. 19). Нажать кнопку Далее. Если мы создаем запрос на товары, то нам необходимы поля Фамилия, имя, отчество – из таблицы декларант, а из таблицы информация о товаре – наименование товара.

Рис. 19 Выбранные поля таблицы

7. В поле Задайте имя запроса введите Товары. В этом же окне можно посмотреть результаты выполнения запроса, установив переключатель в положение Открыть запрос для просмотра данных. Нажать кнопку Готово.

8. Должно получиться следующее (Рис.20):

Рис. 20 Запрос Товары

Самостоятельное задание. С помощью мастера запросов создайте простой запрос с именем Личные данные декларанта. В запросе должны быть поля: Фамилия, имя, отчество, тип документа, серия №, место и дата рождения.

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

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

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

1. Открыть БД и перейти на вкладку Создание.

2. Нажать кнопку Конструктор запросов в группе Другие. Откроется окно Добавление таблицы (Рис. 21).

Таблица запроса

Бланк запроса

Рис. 21 Окно Добавление таблицы

3. Добавьте таблицы Информация о товаре и Декларант. Нажмите закрыть.

4. Добавьте поля в таблицу: фамилия, имя, отчество, наименование товара по ТНВЭД, цена за единицу товара. Размещение полей в Бланке запроса можно осуществить разными способами: перетащить поле из верхней части окна запроса на Бланк запроса в строку Поле; в нижней части окна в раскрывающемся списке Поле выбрать необходимое поле таблицы. (Рис. 22)

5. Для того чтобы поле не выводилось на экран можно снять флажок в поле Вывод на экран.

Рис. 22 Добавление полей в таблицу

Замечание. Условие отбора можно включать аналогично включению фильтра. Например, если необходимо узнать что провозили декларанты, фамилии которых начинаются на букву "П", то данное условие может быть получено с помощью включения условия Like "П*" (рис. 23).

Рис. 23 Бланк запроса на выборку

6. Получите этот запрос. Щелкните по кнопке выполнить (!) на ленте для представления запроса.

7. Сохраните этот запрос с именем Выборка по П. Теперь в меню базы данных в окне Запросы будет показано два запроса.

Самостоятельное задание.

1. Составьте запрос на страну проживания Россия, используя поля: страна проживания, ФИО, дата рождения. Сохраните запрос с именем Страна проживания.

2. Составьте запрос на страну проживания, фамилия которых начинается на букву "М". Сохраните запрос с именем Выборка по М.

Задание 3. Составьте запрос с использованием логических операций в условии отбора

Предположим, что вам нужно составить Таможенную ведомость для декларантов, которые провозили товары в определенном количестве, например равному 2 или 3.

1. Выберите вкладку Создание – Конструктор запросов.

2. Добавьте нужные таблицы (Информация о товаре и Декларант).

3. Выберите поля Фамилия, Имя и Отчество из таблицы Декларант и поле количество – из таблицы Информация о товаре. В строке Условие отбора под полями Количество поставьте 5 Or 15(рис. 24).

Рис. 24 Бланк запроса Количество 1

4. Щелкните по кнопке! для представления запроса.

5. Сохраните запрос с именем Количество 1, щелкнув по кнопке Сохранить.

Самостоятельное задание.

1. Составьте аналогичный запрос, в котором количество товара 5 по цене 3000 руб.; сохраните запрос с именем Количество_цена.

2. Выберите тех декларантов количество товаров у которых больше 10, но меньше 20. Сохраните запрос под именем 10<x<20.

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

1. Выберите вкладку Создание – Запросы – Конструктор запросов.

2. Выберите таблицы Информация о товаре и Декларант.

3. Выберите поля фамилия, имя, отчество, год рождения, наименование товара.

4. Поставьте курсор в ячейку Условие отбора в столбц е Фамилия.

5. Щелкните по кнопке – Построить. Появитсяокно, вкотором можно строить сложные запросы (Рис. 25). Щелкните по кнопке Not, это слово появится в верхнем поле, Фамилию Молодецкий в кавычках наберите вручную. Щелкните по кнопке ОК. В поле условия отбора появиться данное выражение.

Рис. 25 Окно построителя выражений

6. Для представления запроса щелкните по кнопке!.

7. Закройте запрос, сохранив его с именем не Молодецкий.

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

1. Выберите вкладку Создание – Запросы – Конструктор запросов.

2. Добавьте нужные таблицы (Декларант и Информация о товаре).

3. Выберите поля Фамилия, Имя, Отчество из таблицы Декларант и поля Цена и Количество – из таблицы Информация о товаре.

4. Поставьте курсор на ячейку правее Количество (на линии Поле). Щелкните по кнопке – Построить.

5. В появившемся окне напечатайте вручную выражение, представленное на рис. 26, и щелкните по кнопке ОК.

6. Это выражение подставится в новое поле. Нажмите клавишу Enter.

Рис. 26. Окно Построитель выражения

7. Сохраните запрос с именем Стоимость.

8. Щелкните по кнопке! – для представления запроса. Новое поле будет иметь имя таможенная стоимость

9. Закройте Запрос.

Задание 6. Создайте запрос с параметрами

1. Выберите вкладку Создание – Запросы – Конструктор запросов.

2. Добавьте нужные таблицы (Декларант и Реквизиты декларанта).

3. Выберите поля серия № и гражданство из таблицы Реквизиты декларанта и Фамилия, Имя, Отчество из таблицы Декларант.

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

Рис. 27 Бланк параметрического запроса Введите гражданство

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

6. Закройте Запрос.

Задание 7. Создайте параметрический запрос Справка по полю Фамилия

В запросе должны быть поля: номер декларанта, Фамилия, Имя, Отчество, наименование товара и количество.

Задание 8. Анализ данных с помощью запроса

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

Ø Сумма – суммирует элементы в столбце. Работает только с числовыми или денежными данными.

Ø Среднее – вычисляет для столбца среднее значение. Столбец должен содержать числовые или денежные единицы, значения даты или времени. Функция игнорирует пустые значения.

Ø Число – подсчитывает количество элементов в столбце.

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

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

Ø Стандартное отклонение – показывает, насколько значения отклоняются от среднего.

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

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

Например, необходимо сгруппировать декларантов по гражданству и подсчитать их количество.

Для создания итогового запроса нужно:

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

2. Выберите таблицы Декларант и Реквизиты декларанта.

3. Переместите поля: Гражданство, фамилия.

4. На вкладке Конструктор в группе Показать или скрыть нажать кнопку Итоги. В бланке запроса появится строка Групповая операция.

5. В строке Групповая операция выбрать из раскрывающегося списка значение, например Count (Рис. 28).

Рис. 28 Результирующий бланк итогового запроса с условием

6. Перейти на вкладку Конструктор и выберите команду Выполнить.

7. Сохраните запрос с именем Анализ данных.

Задание 9. Определите количество заявленных товаров для каждого декларанта. В запросе должны быть поля фамилия, имя, отчество, количество.

Предъявите преподавателю запросы: Личные данные декларанта, Товары, Выборка по М, Страна проживания, Количество_цена, 10<x<20, не Молодецкий, Стоимость, Введите гражданство, Фамилия, Анализ данных.

Дата добавления: 2015-05-09