Запросы с групповыми операциями

В СУБД Access существует возможность задать запросы с групповыми операциями, что позволяет находить интегральные показатели для групп записей в таблице. Каждая такая группа характеризуется одинаковым значением по какому-то полю, например, одинаковым названием отдела или семейным положением. Для перехода в данный режим запросов необходимо в панели инструментов нажать кнопку ГРУППОВЫЕ ОПЕРАЦИИ , что приведет к появлению в бланке запроса новой второй строки с одноименным названием. В ячейках данной строки указывается или режим группировки по некоторому полю (опция Группировка), или название групповой операции:

· Sum - сумма значений;

· Avg - среднее значение по данному полю для всей группы;

· Count - число записей в данной группе;

· Max -максимальное значение поля в каждой группе;

· Min -минимальное значение поля в каждой группе;

· First -первое значение данного поля в каждой группе;

· Last -последнее значение данного поля в каждой группе и др.

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

Задание

1. В таблице СЛУЖАЩИЙ своей базы данныхосуществить следующие виды фильтрации:

- выбор служащих одного отдела;

- выбор служащих, название должности которых заканчивается на букву "т";

- выбор служащих, фамилия которых начинается на букву "В";

- выбор всех служащих, кроме одного отдела;

- выбор служащих по двум должностям;

- выбор служащих-женщин одного отдела.

 

2. Для таблицы СЛУЖАЩИЙ своей базы данныхсформировать, проверить корректность и сохранить следующие запросы:

· Отображающий в выборке поля:

- ФИО;

- дата_рождения;

- отдел;

- оклад.

· Отображающий в выборке все поля таблицы для работников одного отдела. Сделать данный запрос параметризованным;

· Отображающий в выборке поля:

- ФИО;

- отдел;

- количество детей,

для семейных сотрудников, фамилии которых начинаются на буквы от "Г" до "Ш";

· Отображающий в выборке поля:

- ФИО;

- должность;

- оклад,

для холостых сотрудников с окладом больше 1000 руб.

· Рассчитывающий для каждого сотрудника налог в виде выражения (13% от оклада).

В выборке выводятся:

ФИО;

отдел;

оклад;

налог.

Предусмотреть суммирование полей оклад и налог для каждого отдела.

Лабораторная работа 3. Создание форм

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

Для перемещения по всему множеству записей таблицы можно использовать кнопки клавиатуры (PgUp -переход к следующей записи, PgDn -возврат к предыдущей записи, Home -переход к 1-йзаписи таблицы, End -переход к последней записи таблицы) или кнопки навигации в форме

Использование форм имеет следующие преимущества:

* возможность отображения содержимого таблиц в более естественном для человека виде;

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

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

* возможность разрешения на модификацию данных только у части полей.

Форму можно создать в режиме мастера или в режиме конструктора форм. Удобнее создать форму с помощью мастера, а последующую корректировку выполнить с помощью конструктора форм.

Конструктор форм

Конструктор оперирует формой как совокупностью объектов. Объекты, в свою очередь, характеризуются набором определенных свойств.

Окно конструктора содержит окно формы (Рис.5) и панель элементов.

 

Рис.5. Форма в режиме конструктора

 

Окно формы включает следующие области:

1. Область данных - является главной, содержит поля таблицы.

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

3. Верхний и нижний колонтитулы - размещаются на каждой странице многостраничной формы

4. Примечание формы - размещается в конце последней страницы.

Области заголовка и примечания могут быть удалены/восстановлены в бланке командами главного меню Вид - Заголовок/Примечание.

Размер каждой области может быть изменен путем установки мыши на ее границу, захвата границы левой клавишей мыши и перетаскивания её на новое место.

Размер самого бланка формы также может быть изменен мышью путем перетаскивания его границ.

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

На панели элементов расположены следующие кнопки:

 

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

Мастера – позволяет вызвать программу – мастер при создании объекта;

Надпись - используется для введения в бланк формы дополнительных надписей путем щелка мышью по полю бланка в месте размещения текста и дальнейшего его набора;

Линия - позволяет разместить на поле формы линию для отделения одной группы данных от другой. Создается путем протаскивания мышью;

Поле - позволяет разместить в форме поле из таблицы;

Поле со списком - позволяет разместить в форме поле со спускающимся списком;

Список – позволяет разместить в форме список;

Прямоугольник - позволяет разместить на поле формы прямоугольник для выделения в группу логически связанных объектов. Создается путем протаскивания мышью в требуемом месте по диагонали;

Кнопка - позволяет разместить в форме управляющую кнопку.

Подчинённая форма – используется для создания многотабличной формы;

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

Все объекты формы характеризуются свойствами, которые можно изменять, и событиями, которые можно связывать с объектами.

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

Свойства представлены на пяти вкладках:

1. Макет - содержит перечень свойств, связанных с оформлением объекта;

2. Данные - содержит перечень свойств, связанных с источником данных;

3. События -содержит перечень доступных для объекта событий;

4. Другие -содержит перечень свойств, не вошедших в три первых карточки;

5. Все -содержит перечень всех событий и свойств в алфавитном порядке.

Значения свойств можно изменять:

* путем ввода с клавиатуры новых значений;

* выбора из списка;

* настройкой в диалоговом окне.

Все объекты формы можно: перемещать, удалять, менять в размерах.

Одиночный объект выделяется однократным щелчком левой клавиши мыши, а группа объектов - через кнопку Выбор объектов.

Размер объекта можно менять путем:

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

2. Точной установкой размеров объекта в полях Ширина и Высота окна свойств.

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

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

Выделенный объект с бланка формы можно удалить.

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

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

Для эффективной организации работы с БД на поле формы можно размещать кнопки активизации требуемых действий: открытия и закрытия таблиц, запросов, других форм и т.п. Размещение кнопки начинается с выбора на панели данного инструмента, указания щелком мыши места его расположения, что приводит к раскрытию окна с двумя полями: "Категории" и "Действия". В первом из них выбирается группа операций, а во втором - сама операция работы с БД.

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

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

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

После этого нажимается кнопка Готово.

Оформление

СУБД позволяет выбрать один из уже готовых стилей оформления бланка формы. Для реализации данного подхода необходимо выполнить команды главного меню Формат- Автоформат или нажать клавишу панели инструментов Автоформат . Далее из появившегося списка выбирается готовый стиль оформления и нажимается клавиша ОК.

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

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

- по размеру данных;

- по узлам сетки;

- по самому высокому;

- по самому низкому;

- по самому широкому;

- по самому узкому.

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

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

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

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

Доступ к данным таблиц

Формы позволяют дифференцировать доступ к данным путем установки следующих режимов:

- запрет на изменение существующих данных;

- запрет на добавление новых данных;

- запрет на изменение значений некоторых полей в форме;

- отображение в поле формы только части полей исходной таблицы.

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

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

Задание

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

2. С помощью конструктора форм откорректировать форму Сотрудники:

- в заголовок формы ввести название "Лицевая карточка сотрудника";

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

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

- в первой области с названием ОБЩИЕ ДАННЫЕ разместить поля: ФИО, Дата рождения, - Пол;

- во второй области с названием ЛИЧНЫЕ ДАННЫЕ разместить поля: название_ отдела, название _должности, - Оклад, - Семейное положение, - Количество детей;

- установить любой рисунок в качестве фона для формы;

- устранить в форме кнопку изменения размеров окна.

3. Занести в таблицу Служащие 5 записей, используя созданную форму.

4. Для таблицы С лужащие создать форму с названием просмотр, предназначенную только для просмотра информации в таблице (полей ФИО, название_ отдела, Оклад). Дополнить её полем, в котором автоматически рассчитывается налог для каждого служащего по формуле налог = 0,13* Оклад

5. Для таблицы С лужащие создать форму, предназначенную для модификации переменной информации о служащем, т.е. полей: название_ отдела, название _должности, - Оклад, - Семейное положение, - Количество детей.

Разместить на этой форме кнопки: Следующий сотрудник, Предыдущий сотрудник, Выход.

Удалить с формы кнопки оконного меню и кнопки переходов.

6. Дополнить таблицу С лужащие полем логического типа, определяющим отношение служащего к военной повинности. В Последнюю форму добавить флажок, обеспечивающий работу с этим полем.

 

Лабораторная работа № 4. Создание отчетов

Отчет – это документированный результат анализа информации, хранящейся в БД.

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

Отчёт позволяет:

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

- ввести пояснительную графику (логотип фирмы, диаграммы и т.д.);

- разбить анализируемые данные по группам;

- производить вычисления по записям и итоговые (для всего отчета, отдельных групп, страниц и т.д.).

Существует несколько разновидностей отчетов:

- в столбец;

- ленточный;

- почтовые наклейки и др.

Отчёт может быть создан с помощью мастера отчётов или в режиме конструктора отчётов. Проще создать отчёт с помощью мастера отчётов, а затем доработать его в режиме конструктора отчётов. По своим возможностям и структуре он аналогичен конструктору форм, т.е. имеет бланк формируемого отчета (Рис.6) и панель элементов.

 

Рис.6. Отчет в режиме конструктора

Поле бланка разбито на несколько областей:

1. заголовок отчета;

2. верхний колонтитул;

3. область данных;

4. нижний колонтитул;

5. примечание отчета.

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

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

Содержание области данных в отчете отображается для каждой записи источника информации (таблицы или запроса).

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

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

· поле или выражение, по которому информация в отчете должна быть разбита по группам;

· порядок сортировки внутри группы (по возрастанию или убыванию);

· параметры группировки (например, необходимость шапки и/или примечания группы, интервал и т.п.).

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

· год;

· квартал;

· месяц;

· неделя;

· день;

· час;

· минута;

· каждое отдельное значение.

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

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

=[Количество_товара]*[Стоимость_1_товара]

Для расчета итоговых значений в примечаниях отчета и/или групп необходимо создать там новое поле и на вкладке Данные его свойств ввести выражение вида

=<Операция>(<Аргумент>)

Аргументом является выражение или название поля. Например, если суммируются все стоимости партий товара, то можно использовать выражение вида

=Sum([Количество_товара]*[Стоимость_1_товара])

или

=Sum(Поле23),

где Поле23 - название поля, в котором вычисляются стоимости каждой партии. Его можно увидеть на вкладке Другие свойств вычисляемого поля.

 

Задание

1. На основе данных таблицы Служащие сформировать отчёт следующего вида:

Ведомость на выдачу зарплаты за 10 месяц Дата расчёта 01.10.10

ФИО оклад налог К выдаче

Автоматизации

Романова 8 000,00р. 1 040,00р. 6 960,00р.

Сомов 10 000,00р. 1 300,00р. 8 700,00р.

Всего по отделу 18 000,00р. 2 340,00р. 15 660,00р.

Администрация

Храмов 30 000,00р. 3 900,00р. 26 100,00р.

Всего по отделу 30 000,00р. 3 900,00р. 26 100,00р.

Бухгалтерия

Фёдорова 20 000,00р. 2 600,00р. 17 400,00р.

Иванов 10 000,00р. 1 300,00р. 8 700,00р.

Всего по отделу 30 000,00р. 3 900,00р. 26 100,00р.

Продаж

Короедов 6 000,00р. 780,00р. 5 220,00р.

орлов 6 000,00р. 780,00р. 5 220,00р.

Артёмов 6 000,00р. 780,00р. 5 220,00р.

Всего по отделу 18 000,00р. 2 340,00р. 15 660,00р.

ИТОГО 96 000,00р. 12 480,00р. 83 520,00р.

 

2. При подготовке отчёта учесть, что зарплата начисляется первого числа месяца, следующего за расчётным. В качестве даты расчёта предусмотреть автоматическую подстановку текущей даты. Номер месяца в заголовке ведомости должен определяться автоматически на основании даты расчёта.