Создание запросов в СУБД Access

Контрольная работа

Создание запросов в СУБД Access

Содержание

1. Определение

2. Создание запросов

2.1 Простой запрос

2.2 Запрос с параметром

2.3 Запрос с группировкой

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

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

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

Литература

1. Определение

Запросы - объекты СУБД Access, предназначенные для отбора данных, которые удовлетворяют заданным условиям.

2. Создание запросов

2.1 Простой запрос

Задание 1: создать запрос, который будет выводить на экран информацию о дисках, поставляемых ОАО "Компьютерным миром" или ООО "Лирой".

Порядок работы:

1. В Окне навигации выбирается Категория объекта - Тип объекта, а в разделе Фильтр по группам устанавливается переключатель Запросы. На вкладке Создание в группе Запросы нажимается кнопка Конструктор Запросов.

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

Рис. 1. Добавление таблиц в запрос

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

Рис. 2. Окно создания запроса

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

Рис. 3. Определение полей запроса

. В строке Условие отбора записываются условия (рис. 4). Символы, используемые в запросах описаны в табл. 1.

Примечание: кавычки и слово Like в условии отбора писать не нужно, так как они будут поставлены автоматически.

Рис. Задание условий отбора

6. Для просмотра результата на вкладке Конструктор в разделе Результаты нажимается кнопка .

Результат запроса отобразится в виде временной (динамической) таблицы (рис. 5).

Рис. 5. Просмотр результатов запроса

Таблица 1. Операторы, используемые в запросах

Таблица 1. Окончание

Задание 2: найти товары, поступившие в количестве от 10 до 50 или с ценой более 1000.

Порядок работы:

1. В Окне навигации выбирается Категория объекта - Тип объекта, а в разделе Фильтр по группам устанавливается переключатель Запросы. На вкладке Создание в группе Запросы нажимается кнопка Конструктор Запросов.

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

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

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

. В строке Условие отбора записываются условия запроса (рис. 6). Операторы, используемые в запросах, описаны в таблице 1.

Рис. 6. Условие отбора

6. Для просмотра результата на вкладке Конструктор нажимается кнопка . Результат запроса отобразится в виде временной (динамической) таблицы.

Обратите внимание, так как условия отбора связаны логической функцией ИЛИ, то в результате выполнения запроса на экране появятся записи, удовлетворяющие как 1 и 2 условию по отдельности, так и двум условиям вместе, например, товар с артикулом 4, проданный по 105 накладной.

Цифрой (1) обозначены записи, удовлетворяющие первому условию - цена более 1000, цифрой (2) - записи, удовлетворяющие второму условию - количество между 10 и 50 (рис. 7).

Рис. 7. Результат запроса

2.2 Запрос с параметром

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

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

[Введите <имя поля>] (рис.8).

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

Порядок работы:

1. В Окне навигации выбирается Категория объекта - Тип объекта, а в разделе Фильтр по группам устанавливается переключатель Запросы. На вкладке Создание в группе Запросы нажимается кнопка Конструктор Запросов.

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

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

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

. В строке Условие отбора, под полем Товар, в квадратных скобках записывается параметр: [Введите Товар] (рис. 8).

Рис. 8. Запрос с параметром

6. Для просмотра результата на вкладке Конструктор нажимается кнопка . При выполнении запроса на экране появляется окно Введите Товар с полем для ввода данных (рис. 9 а). Результат запроса отобразится в виде динамической таблицы (рис. 9 б).

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

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

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

Порядок работы:

1. В Окне навигации выбирается Категория объекта - Тип объекта, а в разделе Фильтр по группам устанавливается переключатель Запросы. На вкладке Создание в группе Запросы нажимается кнопка Конструктор Запросов.

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

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

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

. При создании запроса в строке Условие отбора под полем Дата в квадратных скобках записываются два параметра (рис. 10).

Рис. 10. Запрос с двумя параметрами

. Для просмотра результата на вкладке Конструктор нажимается кнопка . При вызове запроса на экране последовательно появятся два окна для ввода начальной и конечной даты диапазона (рис. 11).

Рис. 11. Окна для ввода значений параметров

Результат запроса отобразится в виде динамической таблицы (рис. 12).

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

2.3 Запрос с группировкой

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

Задание: создать запрос, который будет подсчитывать общее количество наименований и суммарный объем товаров, поступивших по каждой накладной.

Рассмотрим данные таблицы "Поступление товаров" (рис.13). В таблице одинаковые значения столбца Номер накладной могут повторяться многократно (в зависимости от того, сколько товаров поступило по накладной). Необходимо создать запрос, который будет для накладных с одинаковым номером суммировать количество товара.

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

Рис. 13. Таблица "Поступление товаров"

Порядок работы:

1. В Окне навигации выбирается Категория объекта - Тип объекта, а в разделе Фильтр по группам устанавливается переключатель Запросы. На ленте в разделе Создание в группе Запросы нажимается кнопка Конструктор Запросов.

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

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

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

. Для реализации запроса в режиме Конструктора добавляется дополнительная строка Групповые операции (рис.14) нажатием на панели инструментов кнопки .

Под полем № накладной в строке Групповые операции выбирается команда Группировка (накладные с одинаковым номером группируются), под полем Код товара в строке Групповые операции выбирается команда Count (количество), а под полем Количество - команда Sum (количество суммируется). Операции, используемые в запросе на группировку, описаны в табл.2.

Результат выполнения запроса представлен на рис. 15. (сравните результат с данными рис. 13).

Таблица 2. Групповые операции

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

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

Синтаксис вычисляемого поля:

Имя вычисляемого поля: формула

Примечание: в формуле заголовки столбцов таблицы записываются в квадратных скобках. Например, Скидка: [Цена] *0,02.

Задание: создать запрос, который позволяет рассчитать стоимость товаров по каждой накладной (стоимость=цена*количество).

Порядок работы:

1. В Окне навигации выбирается Категория объекта - Тип объекта, а в разделе Фильтр по группам устанавливается переключатель Запросы. На вкладке Создание в группе Запросы нажимается кнопка Конструктор Запросов.

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

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

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

. Вычисляемое поле создается в свободном столбце (рис. 16).

Рис. 16. Создание вычисляемого поля

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

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

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

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

Рис. 18. Макет перекрестного запроса

Порядок работы:

1. В Окне навигации выбирается Категория объекта - Тип объекта, а в разделе Фильтр по группам устанавливается переключатель Запросы. На вкладке Создание в группе Запросы нажимается кнопка Конструктор Запросов.

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

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

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

. В запрос добавляются поля:

№ накладной - заголовки строк, групповая операция - группировка,

Товар - заголовки столбцов, групповая операция - группировка,

Стоимость (вычисляемое поле) - значение на пересечении строки и столбца, групповая операция - (Sum) сумма,

Поступившее количество (дополнительный столбец с общим количеством поступившего товара) - заголовки строк, групповая операция - сумма (рис. 19).

Рис. 19. Перекрестный запрос в режиме конструктора

Результат запроса представлен на рис. 20.

Пояснение: например, товар диск DVD-R (Lpro) поступил по накладным №101 на сумму 851 р., №103 на сумму 666 р. и №105 на сумму 185 р. в общем количестве 46 единиц.

Рис. 20. Перекрестный запрос

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

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

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

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

Порядок работы:

1. В Окне навигации выбирается Категория объекта - Тип объекта, а в разделе Фильтр по группам устанавливается переключатель Запросы. На вкладке Создание в группе Запросы нажимается кнопка Конструктор Запросов.

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

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

При создании запроса на обновление необходимо указать тип запроса. Для этого на вкладке Конструктор в разделе Тип запроса нажимается кнопка Обновление. В таблице появится новая строка Обновление (рис. 21).

. В строку Поле, добавляются поля Товар и Цена.

Под полем Товар в строке Условие отбора записывается параметр [Введите наименование товара], а под полем Цена, в строке Обновление, записывается параметр [Введите новую цену] (рис. 21).

Рис. 21. Запрос на обновление в режиме конструктора

6. При выполнении запроса на экране появятся два окна. В первом окне необходимо ввести наименование товара, цену которого требуется изменить (например, Диск CD-D (Mirex)), а во втором окне - новую цену товара (рис.22). После нажатия на кнопку OK появится окно с сообщение о том количестве записей, которое будет обновлено (рис. 23).

Выполнения запроса приведет к тому, что исходные данные в таблице "Справочник товаров" изменятся (рис. 24).

Рис. 22. Ввод параметров: а) Наименование товара; б) Цена

Рис. 23. Информационное сообщение о количестве обновленных записей

Рис. 2 Таблица "Справочник товаров": а) данные до обновления;

б) данные после обновления

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

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

Порядок работы:

1. В Окне навигации выбирается Категория объекта - Тип объекта, а в разделе Фильтр по группам устанавливается переключатель Запросы. На вкладке Создание в группе Запросы нажимается кнопка Конструктор Запросов.

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

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

При создании запроса на удаление необходимо указать тип запроса. Для этого на вкладке Конструктор в разделе Тип запроса нажимается кнопка Удаление. На экране в окне запроса добавляется новая строка Удаление (рис. 25).

. В строку Поле добавляется поле Код поставщика. Под ним в строке Условие отбора записывается условие [Введите код поставщика] (рис. 25).

Рис. 25. Запрос на удаление в режиме конструктора

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

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

Задание: создать запрос, позволяющий формировать новую таблицу в базе данных, содержащую поля № накладной, Товар, Поставщик, Количество. В новую таблицу перенести записи только для поставщиков с кодом 1 или 2.

Порядок работы:

1. В Окне навигации выбирается Категория объекта - Тип объекта, а в разделе Фильтр по группам устанавливается переключатель Запросы. На вкладке Создание в группе Запросы нажимается кнопка Конструктор Запросов.

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

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

На экране появится диалоговое окно, в котором записывается имя новой таблицы (рис. 26).

Рис. 26. Определение имени таблицы

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

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

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

Рис. 28. Таблица "Список"

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

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

Порядок работы:

1. В Окне навигации выбирается Категория объекта - Тип объекта, а в разделе Фильтр по группам устанавливается переключатель Запросы. На вкладке Создание в группе Запросы нажимается кнопка Конструктор Запросов.

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

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

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

При создании запроса указывается его тип. Для этого на вкладке Конструктор в разделе Тип запроса нажимается кнопка Добавление. В диалоговом окне Добавление выбирается таблица, в которую будут добавлены записи (рис. 29).

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

5. В строке Условие отбора записывается ограничение на значения поля Код поставщика (рис. 30).

Рис. 30. Запрос на добавление в режиме конструктора

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

Литература

1.      Гребенюк Е.И.: Технические средства информатизации. - М.: Академия, 2011

2.      Михеева Е.В.: Информационные технологии в профессиональной деятельности. - М.: Академия, 2011

.        Голенищев Э.П.: Информационное обеспечение систем управления. - Ростов н/Д: Феникс, 2010

.        Кузовкин А.В.: Управление данными. - М.: Академия, 2010

.        Михеева Е.В.: Информационные технологии в профессиональной деятельности. - М.: Академия, 2010

.        под ред. С.В. Симоновича; рец.: Каф. Московского гос. технического ун-та им. Н.Э. Баумана, С.В. Калин: Информатика. - СПб.: Питер, 2010

.        Уткин В.Б.: Информационные системы в экономике. - М.: Академия, 2010

.        [отв. ред. И.В. Коробко]: Программы для ЭВМ. - М.: ФГУ ФИПС, 2009

.        [отв. ред. И.В. Коробко]: Программы для ЭВМ. - М.: ФГУ ФИПС, 2009

.        Квинт И.: Видеосамоучитель. Sound Forge 9. - СПб.: Питер, 2009

.        Могилев А.В.: Информатика. - М.: Академия, 2009

.        Моосмюллер Г.: Маркетинговые исследования с SPSS. - М.: ИНФРА-М, 2009

.        под ред. С.В. Симоновича; рец.: Кафедра САПР Московского гос. технического ун-та им. Н.Э. Баумана, С.В. Калин: Информатика. - СПб.: Питер, 2009

.        Филимонова Е.В.: Информационные технологии в профессиональной деятельности. - Ростов н/Д: Феникс, 2009

.        Харуто А.В.: Музыкальная информатика. - М.: ЛКИ, 2009