Лабораторная работа № 2. Создание базы данных средствами MS Access.

Построение запросов

Цель работы: научиться создавать различные виды запросов средствами MS Access.

Материал для работы: файлы Кадры.accdb.

Результат работы: файл Кадры.accdb с построенными запросами.

Выполнение работы

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

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

 откройте файл Кадры.accdb. Перейдите на вкладку Создание. В области

Запросы выберите пиктограмму Мастер запросов;

 в окне диалога Новый запрос выберите Простой запрос и нажмите кнопку ОК. Открывается окно Создание простого запроса;

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

 перенесите поля Фамилия, Имя, Отчество и Дата рождения в область

Выбранные поля, нажимая кнопку >. Нажмите кнопку Далее;

 наберите имя запроса Список сотрудников и установите переключатель Изменить макет запроса (необходимо установить вывод фамилий по алфавиту) и нажмите Готово. Построенный запрос откроется в режиме Конструктора.

Режим имеет собственное пиктографическое меню (вкладка Конструктор). Рабочая область разделена на две части. В верхней части размещаются выбранные таблицы или запросы, на базе которых строится запрос. В нижней части расположен бланк построения запроса QBE (Query by example, запрос по образцу). При помощи мыши можно перемещать требуемые поля из таблицы и задавать способы сортировки и условия отбора из таблицы.

 в столбце поля Фамилия в строке Сортировка установите параметр по возрастанию;

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

Получите список сотрудников, фамилии которых начинаются с буквы Л.  создайте простой запрос (Создание/Запросы/Мастер запросов/Простой

запрос).  Для   получения  списка   укажите   только  поля   Фамилия,  Имя   и

Отчество. Нажмите Далее. Запрос назовите Фамилии_Л;

 установите   переключатель   Изменить   макет   запроса  (необходимо задать условие отбора фамилий)/Готово;

 в режиме Конструктора в столбце поля Фамилия в строке Условие отбора наберите Л*;

 просмотрите             полученный             список              (Конструктор/ Результаты/Режим/Режим таблицы). Закройте запрос.

Получите список сотрудников отделов кадров и ДОУ.

 создайте простой запрос. Выберите необходимые поля. Назовите запрос Сотрудники отделов. Перейдите в режим Конструктора;

 в столбце поля Отдел в строке Условие отбора наберите отдел кадров.

В строке или – отдел ДОУ;

 перейдите в Режим таблицы и просмотрите полученные сведения.

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

Создайте запрос для получения списка сотрудников с окладом менее 400 рублей. Для получения информации необходимо использовать поля из двух таблиц: Сотрудники и Должности.

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

 в списке Таблицы и запросы укажите таблицу Должности и выберите поле Оклад/Далее;

назовите запрос Оклады менее 400. Перейдите в режим Конструктора; в столбце поля Оклад в строке Условие отбора наберите <400; перейдите в Режим таблицы и просмотрите полученные сведения.

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

Групповые операции в запросах. Группируемые данные – это данные поля, которые имеют одинаковые значения. Для полученных групп записей

возможно вычислять итоговые данные как по группируемому поля, так и по другим полям. Запрос позволяет подсчитывать количество, сумму, среднее, определять максимальное и минимальное значение и т.д. Для группировки данных используют команду Итоги вкладки Конструктор в режиме Конструктора запросов.

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

 выберите Создание/Запросы/Конструктор запросов. Откроется окно

Добавление таблицы. Выберите таблицу Сотрудники/Добавить/Закрыть;     из таблицы Сотрудники в верхней части перетащите поле Отдел в

первый столбец бланка построения запроса. Или в строке Поле выберите

Отдел;

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

 на вкладке Конструктор нажмите в области Показать или скрыть

пиктограмму Итоги. Появится еще она строка Групповая операция;

 в первом столбце оставьте параметр Группировка, т.к. в столбце будут объединяться записи с одинаковы значениями поля Отдел. Во втором столбце в списке Группировка выберите Count, т.к. в данном столбце будет определяться сколько раз встречается одинаковое значение поля (определяться количество сотрудников в отделе);

 перейдите   в   Режим   таблицы.   Просмотрите   полученные   данные.

Закройте запрос, сохранив запрос с именем Количество сотрудников.

Создание параметрического запроса. Параметрический запрос – это запрос, при выполнении которого (получении информации) задаѐтся переменный параметр. Для создания параметрического запроса необходимо сначала создать простой запрос для выбора требуемых полей (в том числе и полей, по которым будут вводиться параметры). Чтобы определить параметр, необходимо в поле, для которого задаѐтся переменное значение в строке Условие отбора набрать фразу, заключенную в квадратные скобки.

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

 создайте простой запрос. Выберите необходимые поля. Запрос назовите Поиск сотрудника Перейдите в режим Конструктора;

 в столбце поля Фамилия в строке Условие отбора в квадратных скобках наберите [Введите фамилию] (сообщение, которое будет выводиться на экран при выполнении запроса);

выберите Конструктор/Показать или скрыть/Параметры;

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

в столбце Тип данных выберите тип Текстовый. Нажать ОК; закройте запрос с сохранением макета;

найдите сотрудника по фамилии Дубинина, используя построенный запрос. Для этого откройте запрос;

 в появившемся на экране окне Введите значение параметра наберите фамилию Дубинина/ОК. Появится таблица с данными о выбранном сотруднике. Закройте запрос.

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

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

Определите возраст сотрудников.

 создайте простой запрос. Выберите поля Фамилия, Имя и Отчество.

Запрос назовите Возраст сотрудников. Перейдите в режим Конструктора;

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

Вычисляемое поле создается путем вычитания от «текущей даты» «даты рождения сотрудника». Для решения задачи используйте функции Year() и Date():

Year(Date())-Year(Сотрудники![Дата рождения]).

в области ввода данных наберите Возраст:;

для    набора   выражения   используйте   инструменты    Построителя выражений. Наберите первую функцию Year();

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

Рисунок 26 – Окно Построителя выражений с установленными параметрами.

 задайте аргумент для функции Year. Аргументом будет функция Date(), задающая текущую дату. В области Элементы выражений выберите Встроенные функции, в области Категории выражений – Дата и время, в Значения выражений – Date();

 с клавиатуры наберите знак «минус» -. Далее опять выберите Year. Для задания аргументов функции в области Элементы выражений выберите Кадры/Таблицы/Сотрудники, последовательно раскрывая список +. В области Категории выражений найдите поле Дата рождения;

 проверьте корректность набора выражения. Нажмите ОК для перехода в режим Конструктора. Выражение появится в строке Поле;

 перейдите   в   Режим   таблицы,   просмотрите   построенный   запрос.

Сохраните изменения макета запроса.

Задания для самостоятельной работы

1.   Получите список сотрудников:

в алфавитном порядке фамилий с указанием занимаемой должности; по возрастанию окладов;

с указанием семейного положения.

2.   Получите список сотрудников: имеющих высшее образование;

занимающих должность инженер-программист; родившихся в Бресте и Минске;

женщин с высшим образованием;

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

занимающих должность начальника отдела; чей оклад более 300 и менее 700 рублей;

с фамилией, которая начинается на букву К;

отдела бухгалтерии, имеющих высшее образование; принятых на работу с января 2000 г. по декабрь 2018 г.

3.     Определите количество:

мужчин и женщин в организации;

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

4.     Определите:

средний оклад по каждому подразделению; средний оклад мужчин и женщин;

средний оклад по уровню образования; минимальный оклад в каждом подразделении.

5.   Постройте запрос для поиска информации о сотрудниках (фамилия, имя,

отчество,   оклад,   дата   рождения,   должность),   занимающих   конкретную должность.

6.   ¶ Создайте запрос для вывода информации о сотруднике и количестве членов его семьи.

7.   ¶ Определите количество сотрудников, проработавших в организации одно и тоже время.