Лабораторная работа Создание запросов.

Лабораторная работа № 17

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

Задание. Выполните задание, используя следующие возможности работы с запросами в MS Access:

а) для создания запроса на выборку с условиями, необходимо выбрать команду Запрос-Создать–Конструктор; добавить таблицы, поля которых будут отображаться в запросе; выбрать поля для запроса (дважды щелкнув по нужному полю таблицы мышкой или выбрать в графе «Поле» из предложенного списка); записать в строке «Условие отбора» необходимые условия по заданию.

Обратите внимание, что условия, записанные в одной строке («Условие отбора» или «или») будут связаны логической операцией «и» (and), а в разных строках - логической операцией «или» (or).

При записи условий можно использовать знаки операций: «», «=», «and» («и»), «or» («или»), «not» («не»).

Например, запрос №1, может быть оформлен следующим образом:

б) для создания запроса с вычисляемыми полями необходимо создать запрос (пункт а); далее в свободном столбце в строке «Поле» создать вычисляемое поле, выбрав команду Построить… в контекстном меню.

При этом откроется окно «Построитель выражения», в котором в верхней части вводится вычисляемое выражение. Для построения выражения используются встроенные функции (для их переноса в выражение необходимо в первой колонке нижней части выбрать: Функции-Встроенные функции, в средней колонке выбрать категорию, в третьей колонке выбрать функцию, дважды щелкнув левой кнопкой мыши по названию функции). Аргументы функций задаются щелчком левой кнопки мыши по соответствующему аргументу, а затем выбором нужного аргумента из таблиц (в левой колонке выбрать команду Таблицы и имя нужной таблицы, в средней колонке выбрать имя нужного поля двойным щелчком мыши). Название нового поля пишется перед выражением (формулой) со знаком «:».

В данном задании будут используются следующие функции:

- Left («stringexpr»; «n») -возвращает n -первых символов строки (категория: Текстовые).

Например, для создания запроса, который формирует новое поле, состоящее из фамилии и инициалов работника на основе имеющихся полей Фамилия, Имя, Отчество, необходимо записать следующую формулу: ФИО:[Работники]![Фамилия]+"_"+Left([Работники]![Имя];1)+"."+Left ([Работники]![Отчество];1)+"."

- Month(«дата») – номер месяца из даты (категория: Дата и время).

- Date ( ) - текущая системная дата (категория: Дата и время).

- Year(«дата») – год из даты (категория: Дата и время).

- InStr («start»; «stringexpr1»; «stringexpr2»; «compare») - позиция первого вхождения «stringexpr2» в «stringexpr1»; поиск начинается с позиции «start»; в «compare» указывается цифра «1», если регистры не учитываются (категория: Текстовые).

- IIf («expr»; «truepart»; «falsepart») - возвращает одно из двух значений в зависимости от истинности указанного выражения (категория: Управление).

в) для создания запроса с параметром необходимо создать запрос (пункт а); в столбце (поле), для которого необходимо организовать ввод параметра, в строке «Условие отбора» вводится приглашение на ввод в квадратных скобках.

Например, в поле «Семейное положение» в строке «Условие отбора»: [Введите семейное положение].

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

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

д) для создания запроса на обновление необходимо создать запрос (пункт а); выбрать пункт меню Запрос-Обновление; задать при необходимости условие отбора для обновляемого поля (полей); в строке «Обновление» ввести выражение, по которому вычисляется новое значение поля.

Например, в примере в строке «Обновление» с помощью построителя выражений необходимо ввести следующее выражение:

I if ([Отделы]![Оклад]

II if([Отделы]![Оклад]

2000; [Отделы]![Оклад]*1,05; [Отделы]![Оклад]*1,1))

е) для создания запроса на удаление необходимо создать запрос (пункт а); выбрать пункт меню Запрос-Удаление; задать при необходимости условие отбора для удаляемого поля (полей); в свободном столбце в строку «Поле» перенести значок «*» из той таблицы, в которой производится удаление записей.

ж) для создания запроса на создание таблицы необходимо создать запрос (пункт а); выбрать поля, которые будут содержаться в новой таблице; в пиктографическом меню «Конструктор запросов» выбрать команду «Тип запроса» , пункт «Создание таблицы…»; ввести название новой таблицы в появившемся диалоговом окне.

Текст задания.

Создайте следующие запросы.

1. На выборку.

   1. С условиями.

а) Выдать информацию: Фамилия, Имя, Отчество, Семейное положение, Название отдела, Должность, Дети, Оклад по следующим условиям: для холостых работников без детей с окладом больше 3000 руб. (запрос назвать «Холостые без детей»).

б) Выдать информацию: Фамилия, Имя, Отчество, Название отдела, Должность, Дети, Оклад по следующим условиям: для работников с детьми и окладом от 2000 до 5000 руб. (запрос назвать «Оклад»).

в) Выдать информацию: Фамилия, Имя, Отчество, Название отдела, Должность, Дети, Оклад по следующим условиям: для работников, имеющих 1 или 2 детей (запрос назвать «1 или 2 детей»).

2. Вычисляемые поля.

а) Создать запрос, в котором формируется новое поле «ФИО»: фамилия и инициалы на основе имеющихся полей: Фамилия, Имя, Отчество (Пр. Иванов П.А.). Для создания поля использовать в выражении встроенную функцию Left и «+» (запрос назвать «ФИО»);

б) Создать запрос (назвать его «Начисления») с информацией: Фамилия, Имя, Отчество, Название отдела, Должность, Оклад. Организуйте вычисляемые поля:

1) Район коэф: 15% от Оклада;

2) Начислено: Оклад + Район коэф;

3) Подоход налог: 13% от Начислено;

4) Пенс фонд: 3% от Начислено;

5) На руки: Начислено – Подоход налог – Пенс фонд.

3. С параметрами

а) Вывести информацию о работниках: Фамилия, Имя, Название отдела, Должность, Семейное положение с заданным семейным положением (запрос назвать «Семейное положение»).

б) Вывести информацию о работниках: Фамилия, Имя, Отчество, Дата рождения и родившихся в заданном номере месяца. Для этого создать вычисляемое поле «Номер месяца», используя встроенную функцию Month, и организовать ввод параметра-номера месяца (запрос назвать «Месяц»).

в) Вывести информацию о работниках: Фамилия, Имя, Отчество, Дата рождения и имеющих возраст из заданного диапазона. Для этого создать вычисляемое поле Возраст, используя встроенные функции Date(), Year, и организовать ввод начального и конечного возраста для задания диапазон (запрос назвать «Возраст»).

г) Вывести информацию о работниках: Фамилия, Имя, Отчество, Название отдела, Должность; в фамилии которых содержится заданный фрагмент, используя встроенную функцию InStr (запрос назвать «Фамилия»).

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

Создайте перекрестные запросы следующего вида:

а) б)

III. На обновление.

Создайте запрос на обновление, предусматривающий увеличение оклада на 15% тем работникам, у которых «Оклад»Iif).

IV. На удаление.

V. На добавление.

Создайте запрос на создание таблицы с полями: Фамилия, Имя, Название отдела, Должность. Таблицу назвать «Общая информация».