Создание запросов в режиме Конструктора

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

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

В нижней части окна находится Бланк построения запроса QBE (Query by Example), в котором каждая строка выполняет определенную функцию:

Поле – указывает имена полей, которые участвуют в запросе. Выбираются двойным щелчком по имени поля в верхней части окна, либо из раскрывающегося списка строке «Поле».

Имя таблицы – имя таблицы, из которой выбрано поле, заполняется автоматически при выборе полей

Сортировка – представляет записи в определенном порядке. Тип сортировки (по возрастанию или убыванию) выбирается из списка.

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

Условия отбора - задаются критерии поиска.

Или – задаются дополнительные критерии отбора.

Рисунок 2.3. Окно конструктора запросов

 

Условия отбора - это ограничения, которые пользователь задает для определения записей, которые нужно включить в запрос. Для числовых полей и полей сос значениями Дата-время условия отбора задаются с помощью знаков отношений (>, <, <>, >=, <=), например, >5 (рис.2.3). Для текстовых полей используют шаблоны (в поле ФИО в строке условие задать фамилию, например, Иванов) либо подстановочные знаки:

* - соответствует любому количеству любых знаков. (Условие отбора: И* - означает выбор фамилии на букву «И». То же самое – Like «И»);

? - соответствует одиночному символу.

Для задания сложных условий используются логические функции AND, OR, NOT. Например, для выбора строк, в которых значение какого-либо поля находится в пределах от 5 до 10, в строке условие отбора в этом поле необходимо ввести: >=5 AND <=10 либо BETWEEN 5 AND 10. Критерии на одной строке связываются по правилу И (одновременное выполнение нескольких условий), на разных строках – по правилу ИЛИ (достаточно, чтобы выполнялось хотя бы одно из условий).

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

В запросах с параметром критерий отбора может задавать пользователь при запуске запроса в специальном диалоговом окне. Для этого в Бланке запроса в строке Условие нужно набрать в квадратных скобках [] текст и при необходимости соответствующую операцию отношению (рис.2.4).

 

 

Рисунок 2.4. Запрос с параметром

 

Итоговые запросы, как и запросы на выборку, создаются в режиме конструктора и используются для подведения итогов. В этом случае требуется добавить на бланк запросов еще одну строку – Групповые операции. На ленте на временной вкладке Конструктор в группе Показать или скрыть нажать кнопку Итоги , либо в области бланка вызвать контекстное меню нажатием правой кнопки и выбрать строку Итоги. Все поля, отобранные для запроса, получают в этой строке значение Группировка. Для поля, по которому производится группировка записей (поле должно содержать одинаковые значения), в строке Групповые операции остается значение Группировка. Для поля, для значений которого вычисляются итоги, выполнить щелчок в этой строке – появится кнопка раскрывающего списка, из которого можно выбрать итоговую функцию для расчёта значений в данном поле (Рис 2.5).

В состав итоговых функций входят следующие: Sum – сумма, Avg – среднее значение, Count - количество, Min – минимальное значение, Max – максимальное значение, Last – последнее значение в группе, First – первое значение в группе.

 

Рисунок 2.5. Подведение итогов

 

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

К таким запросам относятся: запросы на создание таблиц; запросы на удаление; запросы на добавление; запросы на обновление. Запросы-действия создаются на основе запросов-выборок. Результаты выполнения таких запросов наблюдают в режиме Таблицы.

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

Запрос на удаление п озволяет удалять записи, основываясь на заданных критериях. Для удаления из автономной таблицы создать запрос на выборку. На ленте в группе Тип запроса нажать кнопку Удаление. Перетащить поля, по которым строятся критерии для удаления записей в бланк запроса. Нажать кнопку Выполнить и подтвердить удаление записей.

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

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

 

Построитель выражений

Ввод выражений возможен в среде Access не только вручную, но и с помощью удобного инструмента, называемого Построитель выражений. Для вызова Построителя из режима Конструктора запросов нужно поместить указатель мыши в ячейку бланка запроса (в строке Условие или в строке Поле) и нажать кнопку Построитель в группе Настройка запроса на вкладке Конструктор либо воспользоваться контекстным меню. После этого откроется окно Построитель выражений (рис. 2.7)

 

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

 

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

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

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

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

Построитель используется для создания сложных условий (в этом случае построитель вызывается из строки условие) либо для создания нового вычисляемого поля (указатель мыши помещается на первую незаполненную ячейку в строке Поле). Вычисляемое поле создается при необходимости выполнять операции над данными в нескольких полях таблицы. Например, во многих таблицах имена и фамилии хранятся в разных полях. Чтобы объединить значения этих полей и отобразить их в одном поле, следует создать вычисляемое поле в запросе. Выражение для вычисляемого поля, объединяющего имя и фамилию, выглядит следующим образом: [Имя] & " " & [Фамилия]. В этом случае амперсанды объединяют значения в полях Имя и Фамилия с пробелом — в выражении пробел заключен в двойные прямые кавычки. Значения нового поля может являться результатом арифметических действий над значениями других полей. Предположим, необходимо создать в запросе новое поле для вычисления суммы продаж на основании существующих в таблицах полей, содержащих информацию о цене и количестве товара.. Для этого используется следующее выражение: =[Количество]*[Цена], которое конструируется в полебланка запросв.

 

Задание на выполнение

1) Создать в режиме Мастера для всех вариантов:

- простой запрос (из каждой таблицы включить в запрос по два поля);

- перекрестный запрос (на основе Таблицы 2);

- запрос для поиска повторяющихся записей (на основе Таблицы 2).

2) Создать в режиме Конструктора: запросы на выборку, запрос с параметром, итоговый запрос, запрос на обновление, запрос с вычисляемым полем в соответствии с вариантом (таблица 2.1)

Содержание отчета

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

Варианты заданий

Таблица 2.1. Задания для выполнения лабораторной работы

Вариант 1 - Кафедра

1) Получить список всех доцентов; 2) Получить список сотрудников моложе 35 лет; 3) Получить список всех пенсионеров; 4) Получить список сотрудников с нагрузкой от 800 до 1000 часов; 5) Получить сотрудников, работающих в группах с количеством студентов больше 22; 7) Вывести список дисциплин по каждому сотруднику (запрос с параметром); 8) Подсчитать количество пенсионеров 9) Получить возраст (в полных годах) всех сотрудников с использованием построителя. 10) Уменьшить нагрузку профессорам на 50 часов (запрос на обновление)

Вариант 2 - Деканат

1) Получить список всех групп кафедры Экономика; 2) Получить список групп с количеством студентов, меньшим 23; 3) Получить список кафедр на букву М; 4) Получить список групп со средним баллом от 87 до 95; 5) Получить кафедр, с количеством студентов в группе больше 25; 7) Вывести список старост по каждой кафедре (запрос с параметром); 8) Подсчитать количество кафедр, где есть магистратура 9) Получить общий балл (произведение среднего балла на число студентов) использованием построителя. 10) Изменить ФИО заведующего на кафедре Финансы (запрос на обновление)

Вариант 3 - Бухгалтерия

1) Получить список всех инженеров; 2) Получить список сотрудников моложе 35 лет; 3) Получить список всех пенсионеров; 4) Получить список сотрудников с окладом от 15000 до 20000 руб; 5) Вывести даты рождения преподавателей; 7) Получить сотрудников по каждой кафедре (запрос с параметром); 8) Подсчитать количество инженеров 9) Получить возраст (в полных годах) всех сотрудников с использованием построителя. 10) Увеличить оклады преподавателей на 1000 руб (запрос на обновление).

Вариант 4 – Оптовая фирма

1) Получить список всех покупателей из Ялты; 2) Получить список товаров с ценой меньше 200 руб; 3) Получить список всех покупателей, купивших количество товара меньше 100; 4) Получить список товаров со стоимостью от 1500 до 10000 руб; 5) Получить номера накладной для каждого товара 7) Получить список товаров на определенную дату (запрос с параметром); 8) Подсчитать среднюю цену товара 9) Подсчитать для каждого товара НДС (18% от стоимости) с использованием построителя. 10) Изменить имя покупателя в накладной 5 (запрос на обновление).

Вариант 5 – Автомагазин

1) Получить список покупателей, оставивших заявки в мае 2016; 2) Получить список автомобилей с ценой больше 200 000 руб; 3) Получить список всех покупателей, оставивших заявки на автомобиль белого цвета; 4) Получить список автомобилей, выпущенных от 5 до 10 лет назад; 5) Получить номера заявки для каждого автомобиля 7) Получить список заявок на определенную дату (запрос с параметром); 8) Подсчитать среднюю цену автомобиля 9) Подсчитать для каждого автомобиля НДС (18% от цены) с использованием построителя. 10) Изменить имя покупателя в заявке 3 (запрос на обновление).

Вариант 6 – Аптека

1) Получить наименования препаратов со срок годности 8 месяцев; 2) Получить препаратов с ценой от 500 до 1000 руб; 3) Получить список всех продавцов, продавших ампулы; 4) Получить наименования препаратов, отпущенных по рецепту; 5) Получить номера чеков для каждого препарата 7) Получить список препаратов, проданных на определенную дату (запрос с параметром); 8) Подсчитать среднюю цену препарата 9) Подсчитать  общее количество проданных препаратов с использованием построителя. 10) Изменить срок годности препарата в заявке 4 (запрос на обновление).

Вариант 7 – Таксопарк

1) Получить марки автомобилей белого цвета; 2) Получить список автомобилей с датой ремонта в 2015 году; 3) Получить список всех водителей автомобилей, выпущенных от 5 до 10 лет назад; 4) Получить список водителей-пенсионеров; 5) Получить марки автомобилей для каждого водителя 7) Получить список водителей для автомобиля с заданным госномером (запрос с параметром); 8) Подсчитать средний стаж водителей для каждого автомобиля 9) Подсчитать премию для каждого водителя (20% от оклада) с использованием построителя. 10) Увеличить оклад водителей на 500 руб для автомобиля 1 (запрос на обновление).

Вариант 8 – Библиотека

1) Получить список всех книг художественного отдела; 2) Получить книг в наличие; 3) Получить список отделов с числом сотрудников меньше 10; 4) Получить список отделов с фондом от 1500 до 10000 книг; 5) Вывести книги, у которых фамилия автора начинается на буквы А или Б; 7) Получить информацию о книгах в определенном отделе (запрос с параметром); 8) Подсчитать количество книг на каждую дату возврата 9) Получить количество книг на 1 сотрудника в каждом отделе с использованием построителя. 10) Изменить ФИО заведующего в читальном зале (запрос на обновление).

Вариант 9 – Аэропорт

1) Получить список рейсов, с временем в пути от 3 до 5 часов; 2) Получить список заказов с количеством билетов, больше 3; 3) Получить список типов самолетов, с числом свободным мест меньше 10; 4) Получить список маршрутов, с временем вылета от 9 до 12 часов; 5) Получить даты вылета для каждого типа самолета 7) Получить число свободных мест для определенного типа самолета (запрос с параметром); 8) Подсчитать общее число свободных мест для определенного маршрута 9) Подсчитать для каждого рейса число занятых мест с использованием построителя. 10) Изменить дату вылета в заказе 3 (запрос на обновление).

Вариант 10 – Поликлиника

1) Получить диагнозы, поставленные в апреле 2016г; 2) Получить поликлиники с числом врачей от 21 до 45; 3) Получить список всех врачей, госпитализировавших пациентов; 4) Получить ФИО заведующих на букву К или С; 5) Получить номера телефонов для каждого посещения 7) Получить список диагнозов в определенной поликлинике (запрос с параметром); 8) Подсчитать количество госпитализированных пациентов 9) Подсчитать общее количество врачей и медсестер в поликлиниках с использованием построителя. 10) Изменить ФИО врача в посещении 2 (запрос на обновление).

 

ЗАДАНИЕ №3

СОЗДАНИЕ ФОРМ В СУБД ACCESS

 

Цель – изучение приемов создания форм в СУБД Access, получение практических навыков в построении и использовании форм различных типов

Теоретические сведения

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

– ввод и редактирование данных – формы обеспечивают вывод на экран данных в удобном для пользователя виде, контроль над вводом данных, а так же автоматизацию процесса ввода;

– управление ходом выполнения приложения – в этом случае формы используются для запуска макросов

– вывод сообщений – с помощью форм можно вывести на экран информацию, предупреждение или сообщение об ошибках;

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

– отображение графических данных, которые при работе с таблицей не видны

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

Для создания форм используются инструменты группы Форма вкладки Создание (рис. 3.1).

 

Рисунок 3.1. Инструменты создания форм

Инструмент «Форма»

Используется для быстрого создания формы. В этом случае в области объектов в левой части окна необходимо выделить таблицу, для которой будет создана форма. Затем перейти на вкладку Создание и щелкнуть на пиктограмме Форма. Построенная форма используется для отображения одной записи таблицы (рис 3.2).

Окно формы содержит (рис.3.2): имена полей; поля ввода; кнопки навигации по таблице. Перемещение на позицию после последней записи представляет пустую запись, в поля которой можно внести новое значение. Пустую запись можно так же выбрать, щелкнув по кнопке со «*».

 

Рисунок 3.2. Пример формы

 

Access не сохраняет новые формы автоматически. Поэтому, чтобы сохранить созданную форму, нужно щелкнуть на кнопке Сохранить панели быстрого дос­тупа, указать в появившемся диалоговом окне Сохранение имя формы и щелк­нуть на кнопке ОК.

 Если Access обнаруживает таблицу, связанную отношением «один-ко-многим» с таблицей или запросом, который использовался для создания формы, Access добавляет таблицу данных в форму, основанную на связанной таблице или запросе. Если таблица данных в форме не нужна, ее можно удалить (рис. 3.3).

Рисунок 3.3. Быстрое создание форм