Урок "MS Access задание 2"

Задание №2. Создание базы данных «Университет».

Часть 1. Проектирование структуры таблицы

1.     

Создадим с помощью конструктора таблицу «Сотрудник» со структурой, представленной на рисунке

Для того чтобы обеспечить возможность хранения в базе данных разнообразной информации, Access предлагает большой набор типов данных, некоторые из которых мы использовали при создании таблицы:

ü  Текстовый – текст длиной до 255 символов;

ü  Счетчик – счетчик, который автоматически увеличивается на единицу с добавлением каждой новой записи;

ü  Числовой – числа различных форматов;

ü  Дата/время – Дата и /или время;

ü  Денежный – денежные значения различных форматов;

ü  Логический – величины, способные принимать только два значения: да/нет или 1/0.

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

Размер поля. В поле нельзя ввести больше символов, чем это определено в данном свойстве. Для текстовых полей это ограничение составляет изначально 50 символов. Изменим размер для поля «ФИО» на 60 символов.

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

Условия на значения. Условия вводятся как выражения, которые могут быть как простыми, так и сложными. Допускается также создание диапазонов. При задании этого ограничения ввод значения в поле будет обязательным, даже если в свойстве поля «Обязательное поле» зафиксировано значение «нет». Зададим условие на значение для поля «Пол». Для этого в соответствующую строку  поместим следующее выражение ”м” or “ж”.

Значение по умолчанию задает содержимое поля, которое записывается в новую запись в том случае, если пользователь не ввел в это поле никаких данных. Для поля «Пол» это значение поставим «м».

Сообщение об ошибке Access выводит в случае, если при вводе значения пользователь нарушил «Условие на значение». Для поля «Пол» в этой строке обозначим: поле «ПОЛ» может иметь значение только «м» или «ж».

2.      Создадим таблицу «Кафедры» с полями:

«Код_кафедры» - ключевое поле типа «Счетчик»;

«Наименование_кафедры_краткое» - текстовое поле, длина – 8 символов;

«Наименование_кафедры_полное» - текстовое поле длиной 50 символов.

3.      Создадим таблицу «Владение иностранными языками» с полями:

«Код_сотрудника» - ключевое поле типа «Счетчик»

«Название_языка» - текстовое, 20 символов;

«Степень_владения» - текстовое, 20 символов. Организуем для этого поля подстановку из фиксированного набора значений, выбрав в поле «Тип данных» «Мастер подстановки». Список значений: «свободно», «со словарем», «читаю и могу объясняться»

4.                  Так как таблица «Сотрудник» должна быть связана с таблицей «Кафедра»,  то необходимо в режиме конструктора перейти в поле «Код_Кафедры» и выбрать в типе данных «Мастер подстановки». Далее необходимо определить поле таблицы-источника «Кафедры», значения которого будут подставляться в описываемую колонку. Таким полем является «Код_кафедры». Но так как пользователь вряд ли помнит коды, то кроме этого поля перенесем в список выбранных полей еще и поле «Наименование_кафедры_краткое». Зададим подпись «Кафедра» для столбца подстановки. Аналогично задайте связь и для поля «Владение_ин_яз», включив в список выбранных полей все поля соответствующей таблицы.

5.                  Теперь необходимо скорректировать созданные связи, выбрав команду меню Сервис►Схема данных. Дважды щелкните на линии связи между таблицами «Кафедры» и «Сотрудник», и в появившемся окне поставьте галочку в поле «Обеспечение целостности данных». Это необходимо для того, чтобы в таблице «Сотрудник» не появлялись коды кафедр, отсутствующие в соответствующей таблице. Аналогично скорректируйте параметры связи для таблицы «Владение иностранными языками.

6.                  Заполним созданные таблицы следующими данными:

Рисунок 2. Таблица "Владение иностранными языками"

Рисунок 3. Таблица "Кафедры"

Часть 2. Создание запросов.

!

 При создании простого запроса условие отбора записывается в соответствующий столбец в строке «Условие отбора» бланка запроса. Причем допускается записывать не только значение атрибута, но и знаки операторов сравнения (=, >,<), а также диапазон значений. Если необходимо задать более одного условия, используются операторы And (требует, чтобы были выполнены все условия, связанные этим оператором) и Or (извлекает записи, которые удовлетворяют любому из условий, связанных этим оператором). Если используется оператор «И»(And), то операнды располагаются в одной строке «Условие отбора». Если используется оператор «ИЛИ»(Or), то операнды располагаются в разных строках («Условие отбора» и «ИЛИ»).

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

Для этого в режиме конструктора создайте запрос по следующей структуре:

Рисунок 5. Запрос с условием отбора

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

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

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

Рисунок 8. Результат выполнения запроса

!

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

3.      Создадим запрос, включающий вычисляемое поле для выплаты премии сотрудникам в размере 20% от оклада. Структура запроса представлена на рисунке. Выполните запрос и убедитесь, что результат содержит два поля, отсутствующие в реальной таблице и созданные путем вычислений.

Рисунок 9. Запрос с вычисляемым полем

!

Если приходится часто выполнять однотипный запрос на выборку, в котором условия меняются незначительно, можно использовать запрос с параметрами. Запрос с параметрами не требует каждый раз вносить изменения в бланк запроса; вместо этого вводится приглашение пользователю ввести условие отбора. Для каждого поля, которое предполагается использовать как параметр, в конструкторе запросов надо ввести в ячейку строки «Условие отбора» текст приглашения, заключенный в квадратные скобки. Это приглашение будет выводиться при запуске запроса. Текст подсказки должен отличаться от имени поля, но может включать его.

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

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

Часть 3. Создание форм.

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

1. На закладке Формы выберите Создать форму в режиме конструктора. В появившемся окне присутствуют пустая форма с разметочной сеткой, вертикальная и горизонтальная линейки, помогающие позиционировать объекты, а также Панель элементов, служащая для добавления элементов формы. Новую форму необходимо связать с источником данных – таблицей «Сотрудник».
2. Для этого в контекстном меню, вызванном в пустой области конструктора, выберите команду Свойства►Данные►Источник записей, в раскрывающемся списке которого необходимо выбрать пункт Сотрудник. После закрытия окна свойств в конструкторе форм появится небольшое окно со списком полей источника записей. Его можно скрывать или выводить на экран  с помощью кнопки Список полей.
3. Перетащите в область формы поля ФИО, Дата_рождения, Дата_приема_на_работу, Кафедра, Оклад и Должность. Access создаст элемент управления типа Поле, а также подпись.

!

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

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

Рисунок 11.Внешний вид формы в режиме конструктора

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


6. В подобных ленточных формах удобно пользоваться дополнительными разделами – заголовком и примечанием формы, которые остаются на месте при прокручивании ленточной формы. Чтобы добавить эти разделы воспользуйтесь контекстным меню, вызванным в области формы. С помощью кнопки Надпись на панели элементов растяните мышью прямоугольник в разделе Заголовок формы и введите текст «Список сотрудников». Аналогично оформите текст примечания. Изменение внешнего вида экранных элементов возможно с помощью панели Форматирование, особенности использования которой ничем не отличаются от использования во всех остальных программах пакета Microsoft Office. Измените размер шрифта для всех элементов на 14.  

!

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

7. Добавим на форму две кнопки, одна из которых удаляет выбранную запись, а другая закрывает форму. Выделите  элемент Кнопка на панели элементов, щелкнув на нем левой кнопкой мыши. Перейдите в область формы, и нажмите левую кнопку мыши там, где предполагается расположить элемент. В появившемся окне мастера выберите категорию Обработка записей и действие Удалить запись. В следующем окне мастера оставьте без изменений переключатель Рисунок и выберите любой понравившийся. В последнем окне мастера оставьте без изменений имя, предложенное программой для кнопки. После завершения работы мастера кнопка готова к работе и может быть размещена в любом удобном месте.
8. Добавьте на форму еще одну кнопку, выбрав для нее в списке категорий  Работа с формой и действие Закрыть форму. Выберите рисунок или текст на свое усмотрение. Окончательный вариант созданной формы представлен на рисунке

Рисунок 12. Внешний вид готовой формы

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

Часть 4. Создание отчетов.

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

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

Сотрудник: ФИО, оклад, должность;

Кафедры: Наименование_кафедры_полное;

Информацию сгруппируем по кафедрам, уровни группировки добавлять не будем, внутри кафедры записи упорядочим по алфавиту по полю ФИО. Воспользуемся кнопкой «Итоги», при  нажатии на которую выводятся все поля числового типа, включенные в отчет (в нашем случае только одно – «Оклад»). Выберем по этому полю получение суммарных итогов (Sum). Выберем вид макета Ступенчатый и Сжатый стиль. Оставим без изменения предложенное для отчета имя.

2.      Полученный в результате отчет нуждается в корректировке. Прежде всего следует изменить название отчета, а также подписи для итоговых показателей. Для этого перейдем в режим конструктора с помощью кнопки  панели инструментов. Выделите мышью заголовок отчета «Кафедры» и введите новое название: «Ведомость на выплату зарплаты»

!

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

3. Выражение "Итоги для " & "'Код_кафедры' = " & " " & [Код_кафедры] & " (" & Count(*) & " " & IIf(Count(*)=1;"запись";"записей") & ")", записанное в зоне «Примечание для группы» удалим, отказавшись тем самым от подсчета числа записей в группе.
4. Изменим в зоне «Примечание группы «Код_кафедры» подпись Sum на выражение «Итого по кафедре». Для этого вызовите на данное поле контекстное меню Свойства и в поле «Подпись» введите обновленный текст.

!

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

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

=[Оклад]*0,13

Имя для этого поля удалим. Вместо него в зоне верхнего колонтитула с помощью элемента «Надпись»  создадим название «Подоходный налог». Сравните получившийся отчет с представленным на рисунке и при необходимости откорректируйте его.

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

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