Закройте таблицу Сотрудники.

Контрольные вопросы

1. Какая команда используются для поиска записей?

2. Возможна ли сортировка по нескольким полям;

3. Какие типы фильтров можно использовать для отбора записей?

4. Можно ли с помощью фильтра по выделенному задать несколько значений полей для отбора записей?

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

6. Какая команда применяется для удаления сохраненного фильтра?

Лабораторная работа №3. Создание запросов на выборку данных

Цель работы:

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

• уметь создавать запросы с параметром;

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

• уметь задавать групповые операции и вычисляемые поля в запросах;

• уметь создавать запросы на основе других запросов.

Задание

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

1. Вывести список военнообязанных сотрудников, в должности менеджера, имеющих оклад более 15000. Вывести фамилию, имя, отчество, размер оклада.

Порядок выполнения запроса:

1. Выбрать объект Запросы>Создание запроса в режиме конструктора.

2. В окне – Добавление таблицы выбрать таблицу Сотрудники.

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

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

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

6. Бланк запроса должен иметь следующий вид:

g) Закрыть конструктор, сохранить запрос под именем Менеджеры, запустить запрос на выполнение (двойным нажатием кнопки мыши или выбрать пункт Открыть)

2. Вывести список сотрудников, работающих в отделе, код которого запрашивается с клавиатуры (указать ФИО, название отдела, должность, оклад). Это запрос на основе двух таблиц, т.к. поле название отдела находиться во второй таблице.

Данный запрос-запрос с параметром. В бланке запроса под полем код отдела необходимо поставить квадратные скобки, и в них написать текст, который должен появляться в окне при выполнении запроса, например [введите код отдела]

3. Вывести список военнообязанных сотрудников, поступивших на работу в текущем месяце.

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

В появившемся окне – в самом левом окошке выбрать функции>встроенные функции, в среднем – дата/время, в правом – функцию month (месяц), получите - Month («number») , надо заменить аргумент, удалите «number». В самом левом окошке выберете Таблицы > Сотрудники > поле Data. Получили -Month ( [Сотрудники]![data] ). Нажали Ок.

Надпись Выражение1 замените на Месяц принятия на работу.

Установите курсор в строку условие отбора, зайдите в построитель, наберите =month(Date()), данная функция вычисляет месяц от текущей даты.

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

Здесь необходимо создать два вычисляемых поля – Стаж и Премия. См. предыдущий запрос.

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

Для вычисления премии необходимо оклад*1,5

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

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

Установите курсор в строку Поле, нажмите правую кнопку мыши, выберете Групповые операции:

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

Лабораторная работа №4. Запросы удаления и замены.

1. Запросы удаления. Удаление записей вручную с помощью команд Правка/Удалить занимает много времени, а также сопровождается часто ошибками. Для автоматического удаления ненужных записей составляется запрос - выбор на удаление.

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

1.1. Прежде всего, введите записи, которые не жалко будет удалить, например, добавьте еще один отдел и одного сотрудника из этого отдела.

1.2. В режиме конструктора запроса выбрать таблицу, в которой будет производиться удаление (таблица Сотрудник), в бланке запроса установить критерии (условия) отбора записей для удаления (например, otd=5). Затем просмотрите результат выполнения запроса, отображаемый в режиме таблицы. Лишь в том случае, если в результате запроса присутствуют только подлежащие удалению записи, следует преобразовать запрос выбора в запрос удаления.

1.3. Выбрать Запрос/Удаление. После этого Access выключает в бланке запроса строки “Сортировка” и “Вывод на экран”. Для запросов удаления эти строки не нужны. Кроме того, для всех колонок бланка запроса в новой строке “Удаление” отображается текст “Условие”. Однако критерии при этом не меняются.

1.4. Если выполнить запрос выбором кнопки Выполнить, то Access не отобразит на экране результат выполнения запроса. Вместо него появиться сообщение о том, сколько записей будет удалено в исходной таблице. Можно прервать процесс удаления нажатием кнопки Отмена. Нажатие кнопки ОК приводит к безвозвратному удалению записей, удовлетворяющих критериям запроса.

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

2.1 Сначала создайте запрос выбора и введите критерии для отбора изменяемых записей (например, otd=2). Только в том случае, если результат запроса отвечает заданным требованиям, следует преобразовать его в запрос замены.

2.2. Выберите Запрос/Обновление. При этом будут выключены строки “Вывод на экран” и “Сортировка” в бланке запроса. Обе строки не нужны для запроса замены. Вместо них включается новая строка “Обновление”. В эту строку следует ввести новые выражения для заменяемых записей (например otd=4). Обратите внимание, что тип данных выражения должен совпадать с типом данных поля исходной таблицы. Замена выполняется аналогично запросу - удаления, сначала выдается предупреждение, что имеется некоторое количество записей, удовлетворяющих условию отбора, при дальнейшем выборе кнопки ОК замены будет выполнены, при выборе отмены, выполнение запроса будет прекращено.

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

Лабораторная работа №5. Создание форм на основе запросов и таблиц

1) Создать форму на основе таблицы Отдел:

a) Выполнить Вставка, Форма. Выбрать Мастер форм.

b) В списке Таблицы/запросы выбрать таблицу Отдел

c) В форму добавить все поля из таблицы, щелкнув кнопку >>

d) выбрать внешний вид формы по своему представлению, щелкнуть кнопку Далее

e) выбрать стиль представления по своему усмотрению, щелкнуть кнопку Далее

f) задать имя формы Отдел и дальнейшие действия - открыть форму для просмотра и ввода данных, щелкнуть кнопку Готово

2) Создать форму на основе таблицы Сотрудники

a) Выполнить Вставка, Форма. Выбрать Мастер форм.

b) В списке Таблицы/запросы выбрать таблицу Сотрудники

c) В форму добавить все поля из таблицы, щелкнув кнопку >>

d) выбрать внешний вид формы по своему представлению, щелкнуть кнопку Далее

e) выбрать стиль представления по своему усмотрению, щелкнуть кнопку Далее

f) задать имя формы Сотрудники и дальнейшие действия - открыть форму для просмотра и ввода данных, щелкнуть кнопку Готово.

3) Открыть форму Сотрудники в режиме конструктора

a) добавить заголовок, изменить цвет и размер шрифта для полей

b) создать 2 кнопки управления: переход по записям (к следующей и предыдущей).

c) Для поля код отдела использовать мастер подстановок. (На панели элементов выбрать – Поле со списком , разместить элемент на форме, на первом шаге выбрать - Использовать данные таблицы, далее выбираем таблицу отдел, поле номер код отдела, следовать указаниям мастера, дать полю соответствующую подпись).

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

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

a) Выполнить Вставка, Форма. Выбрать Мастер форм.

b) В списке Таблицы/запросы выбрать таблицу Отдела

c) В форму добавить нужные поля главной таблицы, щелкну кнопку >

d) В этом же окне выбрать из списка – Таблицы подчиненную таблицу – Сотрудники, выбрать все поля, кроме кода отдела, кнопка Далее.

e) выбрать вариант отображения подчиненной формы (переключатель: подчиненная форма), Далее.

f) выбрать внешний вид формы по своему представлению, щелкнуть кнопку Далее, выбрать стиль представления по своему усмотрению, щелкнуть кнопку Далее

g) задать имя формы Распределение сотрудников по отделам и дальнейшие действия - открыть форму для просмотра и ввода данных, щелкнуть кнопку Готово

Лабораторная работа №6. Создание отчетов на основе запросов и таблиц

1) Создать отчет на основе таблицы: Отдел

a) Выполнить Вставка, Отчет. Выбрать Мастер отчетов

b) В списке Таблицы/запросы выбрать таблицу Отдел

c) В отчет добавить все поля из таблицы, щелкнув кнопку >>

d) выбрать сортировку по названиям от А до Я: в поле уровня 1 выбрать код отдела,

e) выбрать макет по своему усмотрению, щелкнуть кнопку Далее

f) выбрать стиль по своему усмотрению, щелкнуть кнопку Далее

g) задать имя отчета Отдел и дальнейшие действия - просмотр отчета, щелкнуть кнопку Готово

2) Создать отчет на основе таблицы Сотрудники:

a) Выполнить Вставка, Отчет. Выбрать Мастер отчетов

b) В списке Таблицы/запросы выбрать таблицу Сотрудники

c) В отчет добавить все поля из таблицы, щелкнув кнопку >>

d) добавить уровни группировки по отделу.

e) выбрать сортировку по названиям от А до Я: в поле уровня 1 выбрать Фамилия

f) Нажать кнопку Итоги, посчитать средний оклад по отделу.

g) выбрать макет по своему усмотрению, щелкнуть кнопку Далее

h) выбрать стиль по своему усмотрению, щелкнуть кнопку Далее

i) задать имя отчета Сотрудники и дальнейшие действия - просмотр отчета, щелкнуть кнопку Готово

3) Доработать отчета в режиме конструктора.

a) Открыть отчет Сотрудники в режиме конструктора

b) Вставить новый заголовок – Список сотрудников, изменить подписи полей и шрифт, рядом с заголовком добавить рисунок.

c) Добавить по каждому отделу итоги – минимальный и максимальный оклад (установить курсор в области примечания отчета, кнопку Поле поместить в нужное место, правой кнопкой мыши вызвать команду Свойства, открыть вкладку Данные, в строке Данные набрать выражение: =Min([Сотрудники]![OK]),

затем соответственно =Max([Сотрудники]![OK]). Изменить подписи Поле1 и Поле2 переименовать на – Минимальный и максимальный оклад.

4) Создать отчет, в который включить информацию только по тем сотрудникам, кто проработал в организации более 10 лет (на основе соответствующего запроса),

- группировку провести по коду отдела;

- отсортировать данные по фамилии;

- посчитать минимальный и максимальный, и средний оклад по отделу.

- выбрать альбомную ориентацию отчета.

Лабораторная работа №7. Диспетчер кнопочных форм.

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

Создаем кнопочную форму, которая открывает формы «Сотрудники», «Отдел», «Распределение сотрудников по отделу» в режиме добавления записей.

Разобьём процесс на этапы:

1. Команда Сервис\Надстройки\Диспетчер кнопочных форм (либо Сервис\Служебные программы\Диспетчер кнопочных форм);

2. Программа не найдёт ни одной кнопочной формы в базе данных и выдаст соответствующее сообщение. Подтвердим, что мы хотим создать новую кнопочную форму;

3. на экране появится окно «Диспетчер кнопочных форм». Нажмём в нём кнопку «Изменить». Откроется окно «Изменение страницы кнопочной формы»;

4. Теперь можно определить элементы кнопочной формы. Нажмём кнопку «Создать», Откроется окно «Изменение элемента кнопочной формы». Здесь надо указать команду и объект базы данных - форму или отчёт. Выберем команду «Открытие формы в режиме редактирования» и объект - форму «Сотрудники». Изменим подпись на более понятную и соответствующую выполняемой команде - «Открытие формы «Сотрудники»»;

5. аналогично определим элементы кнопочной формы для открытия формы «Отдел», «Распределение сотрудников по отделу»

6. определим следующие элементы кнопочной формы. «Открыть отчет» и объект – отчеты «Отдел», затем «Сотрудники».

7. откроем кнопочную форму в режиме запроса и изменим подпись в её заголовке на более подходящую («Список сотрудников»);

8. закроем форму.

Чтобы форма запускалась автоматически при открытии базы данных, выберите в меню «Сервис» команду «Параметры запуска», В списке «Форма» найдите имя созданной формы.