Сборник практических работ Access. Сборник Access. Урок Создание таблиц базы данных Рассмотрим последовательность необходимых действий при создании файла и таблиц базы данных. База данных Учебный процесс будет состоять из семи таблиц, свойства полей которых приведены в конце этой главы в приложении.
 Скачать 2.9 Mb.
|
|
Главная|Форматирование текста|Выровнять. В область примечаний формы введем инструкцию пользователю, требующую обязательного ввода данных в поля, идентифицирующие занятие код предмета КП, номер преподавателя — ТАБН и вид занятия — ВИДЗ. Без этого не может быть создана запись в таблице ИЗУЧЕНИЕ. 58 Защита справочных данных от изменений Защитим поля НП, ЧАСЫ, ЛЕК, таблицы ПРЕДМЕТ и поля ФИО, СТ, В таблицы ПРЕПОДАВАТЕЛЬ от случайных изменений при работе с формой. Для зашиты поля выделим рамку поля и с помощью контекстно-зависимого меню вызовем свойства поля. В окне свойств на вкладке Данные в строке Блокировка выберем Да. После установки этого свойства поле доступно только для чтения. Для визуального контроля правильности ввода идентификаторов занятия КП и ТАБН можно использовать Поле со списком. Процесс создания такого поля рассматривается ниже. Создание полей со списком При вводе идентификационных данных через форму в Access имеется возможность получить справочную информациюиз ранее загруженных таблиц, что позволяет выбрать уже имеющиеся значения в базе и тем самым повысить достоверность вводимой информации. Отображение данныхиз справочных таблиц при вводе идентификатора свидетельствует о наличии в базе данных главных записей для загружаемой подчиненной записи, что необходимо для успешного завершения ввода при установленном параметре целостности в схеме данных. Поле со списком объединяет поле формы, в которое нужно ввести данные, и список. Список содержит записииз связанной главной таблицы. В списке можно выбрать из соответствующего поля нужное значение и ввести его в поле формы (см, примерна рис. 6.8). Рис. 6.8. Отображение списка 59 Создание поля со списком с помощью мастера Создадим поле со списком для ввода значений кода предмета – КП в таблицу ИЗУЧЕНИЕ. Это позволит просматривать и вводить значения, которые уже имеются в главной таблице ПРЕДМЕТа также проверять соответствие кода и наименования предмета, имеющихся в документе-источнике загрузки План занятий. Выберем кнопку Поле со списком в группе Элементы управления на вкладке Конструктор, установим курсор мыши в нужное место, нажмем кнопку мыши и, не отпуская ее, вычертим рамку элемента. После отпускания кнопки мыши запустится мастери откроет диалоговое окно Создание полей со списком В этом окне определим способ, которым список поля получает свои значения. Для формирования списка из связанной таблицы выберем вариант Таблица или запрос содержат значения, которые использует поле со списком рис 6.9). Рис. 6.9. Выбор способа форматирования списка В следующем окне выберем таблицу ПРЕДМЕТ, которая будет поставлять значения в список поля (рис 6.10). 60 Рис. 6.10. Выбор источника данных для формирования списка Затем выберем поле КП, а также поле НП для расшифровки кода КП (рис. 6.11). Эти поля образуют записи списка. Рис. 6.11. Выбор полей для формирования записи списка Далее в появившейся таблице определим ширину столбцов списка в соответствии с размером значений. Для этого курсор мыши установим на линию, разделяющую имена столбцов, и переместим ее в нужное место (рис. 6.12). 61 Рис. 6.12. Окно для оформления столбцов списка Далее выберем поле списка КП, являющееся ключом связанной таблицы ПРЕДМЕТ. Из этого поля будет выбираться значение для ввода в поле формы рис. 6.13). Рис. 6.13. Окно для выбора поля источника значений для поля формы В следующем окне отметим переключатель Сохранить в поле и выберем поле формы КП (поле таблицы ИЗУЧЕНИЕ ), в которое будут вводиться значения из списка (рис. 6.14). 62 Рис. 6.14. Выбор поля формы, в которое вводится значение из списка Далее введем подпись поля со списком – Код предмета (рис. 6.15). Рис. 6.15. Определение подписи поля со списком Нажмем кнопку Готово. В результате получим поле КП со списком, которое содержится в окончательной форме, представленной на рис. 6.18. Использование поля со списком предметов возможно в режиме формы. Для удобства поиска нужного значенияв списке можно воспользоваться 63 операциями поиска и сортировки. Доступ к этим операциям возможен при помощи кнопок Найти (группа Найти, Сортировка по возрастанию, Сортировка по убыванию группы Сортировка и фильтр. Создание поля со списком без использования мастера Создадим поле со списком для ввода значений идентификатора преподавателя ТАБН. Это позволит просматривать и вводить значения, которые уже имеются в главной таблице ПРЕПОДАВАТЕЛЬа также проверять соответствие номера и фамилии преподавателя, имеющихся в документе-источнике загрузки. Нажмем на вкладке Конструктор в группе Элементы управления кнопку Поле со списком. Установим курсор мыши на появившийся элемент Свободный (рис. 6.16). Рис. 6.16. Элемент формы ПолеСоСписком Нажмем правую кнопку мыши, чтобы вызвать контексно-зависимое меню. Выберем пункт Свойства, затем – вкладку Данные. В строке Данные выберем поле ТАБН, которое необходимо заполнять через форму в таблице ИЗУЧЕНИЕ (рис. 6.17). В строке Тип источника строк выберем элемент Таблица|Запрос, а в строке Источник строк – таблицу ПРЕПОДАВАТЕЛЬ. Поля, включаемые в список, и их порядок определяются в построителе, который вызывается в этой же строке нажатием кнопки . Рис. 6.17. Окно определения свойств данных поля со списком 64 Построитель выводит бланк запросов, в который перетащим из таблицы ПРЕПОДАВАТЕЛЬ поля ТАБН и ФИО. Для того чтобы в списке выводились два поляна вкладке Макет необходимо в строке Число столбцов указать «2». Для настройки ширины столбцов спмска в строках Ширина списка и Ширина столбцов зададим подходящие значения. Закроем окно свойств Поле со списком. В результате получим поле ТАБН со списком, которое содержится в окончательной форме, представленной на рис. 6.18. Рис. 6.18. Форма ПЛАН ЗАНЯТИЙ в режиме конструктора Замечание Если необходимо преобразовать обычное поле в поле со списком , надо в контекстно-зависимом меню поля выбрать Преобразовать элемент в и далее выбрать строку Поле со списком При включенной кнопке Мастера элементов элемент будет преобразован мастером. При выключенной кнопке необходимо для получения поля со списком установить свойства этого поля, как описано выше. Загрузка данных в подчиненную таблицу через форму Для загрузки данных в подчиненную таблицу ИЗУЧЕНИЕ через форму ПЛАН ЗАНЯТИЙ можно сразу перейти из режима конструктора в Режим формы 65 Для этого на вкладке Главная в списке кнопки Режим выбирается Режим формы. Если форма была закрыта, необходимо в окне Область навигации выбрать форму ПЛАН ЗАНЯТИЙ. На рис. 6.19 приводится форма ПЛАН ЗАНЯТИЙ в режиме формы, обеспечивающая загрузку и корректировку данных о занятиях в таблице ИЗУЧЕНИЕ, подчиненной таблицам ГРУППА, ПРЕДМЕТ, ПРЕПОДАВАТЕЛЬ, данные из которых только отображаются в форме. Рис. 6.19. Форма ПЛАН ЗАНЯТИЙ в режиме формы Загрузка подчиненной таблицы Для загрузки записи нового занятия в таблицу ИЗУЧЕНИЕ через вызванную форму необходимо сделать текущим номер группы, для которой вводятся данные о занятиях из документа План занятий. Это можно сделать путем просмотра записей групп при помощи кнопок Предыдущая группа и Следующая группа (рис. 6.19). При большом числе групп целесообразно использовать функцию поиска нужной записи. Чтобы осуществить поиск, установим курсор в поле номера группы, нажмем на вкладке Главная в группе Найти кнопку Найти ив открывшемся окне Поиски замена (рис. 6.20) зададим в качестве образца поиска номер нужной группы. После нажатия в этом окне кнопки Найти далее в форме отобразятся данные занятиях заданной группы. Рис. 6.20. Диалоговое окно поиска записи Для ввода новых записей должен быть обеспечен переход в режим добавления новой записи в подчиненной форме. Для этого может быть создана специальная кнопка перехода к пустой записи Добавить запись (см. рис. 6.19). Для формирования такой кнопки используем мастер кнопок, в диалоговом окне которого Создание кнопок (рис. 6.21) надо выбрать соответствующую категорию Обработка записей и действие Добавить запись, которые обеспечат формирование нужной процедуры обработки события. Рис. 6.21. Выбор параметров при создании кнопки для добавления записи Установка свойств Разрешить добавление, Разрешить удаление, Разрешить изменение, обеспечивающих возможность добавления, удаления и изменения записей при загрузке и корректировке записей таблицы ИЗУЧЕНИЕ – источника записей, показана в окне свойств подчиненной формы (рис. 6.22). 67 Рис. 6.22. Установка свойств подчиненной формы ИЗУЧЕНИЕ Упражнение. Создайте многотабличную форму, для загрузки результатов сдачи экзаменов в таблицу УСПЕВАЕМОСТЬ и их просмотра. При создании формы произведите действия, аналогичные рассмотренным для таблицы ИЗУЧЕНИЕ. В соответствии с технологией загрузки базы данных, рассмотренной вначале настоящей главы, осуществите проектирование формы для загрузки данных в подчиненную таблицу УСПЕВАЕМОСТЬ из документа-источника Экзаменационная ведомость. Определите общую структуру составной формы для ввода (просмотра) данных об оценках студентов группы по предмету в соответствии с подсхемой данных для составной формы, приведенной на рис. 6.23. Рис. 6.23. Подсхема для составной формы ВЕДОМОСТЬ УСПЕВАЕМОСТИ В результате загрузки в БД данных об оценках студентов группы по предмету в БД должны формироваться только записи таблицы УСПЕВАЕМОСТЬ (объект загрузки. Загрузку и просмотр этих данных удобно производить по каждому проведенному в группе занятию в отдельности из соответствующей Экзаменационной ведомости. Поэтому в подсхему для формы ввода включается таблица ИЗУЧЕНИЕ, которую по этой причине целесообразно выбрать в качестве источника основной части составной формы. Кроме того, в форме предусмотрен вывод (отображение) данных о предмете и преподавателе, проводящем занятие, а в списке студентов необходимо предусмотреть отображение его фамилии. Для этого в подсхему включены таблицы СТУДЕНТ, ПРЕДМЕТ и ПРЕПОДАВАТЕЛЬ. Спланируйте размещение реквизитов в макете формы так, чтобы обеспечить удобный ввод данных в таблицу УСПЕВАЕМОСТЬ из документа Экзаменационная ведомость, а также отображение справочной информации о студенте, предмете и преподавателе, в списке студентов предусмотрите вывод фамилии студента. Выполните конструирование экранной формы (рис. 6.24), через которую будет осуществляться ввод, добавление и изменение записей таблицы базы данных УСПЕВАЕМОСТЬ. Рис. 6.24. Форма ввода и просмотра данных об успеваемости студентов по теории и практике 69 Загрузите через построенную форму данные из документа Экзаменационная ведомость в таблицу УСПЕВАЕМОСТЬ. Используйте значения данных, приведенные в Приложении B второй главы. Урок 7. Запросы В этом разделена конкретных примерах рассматривается технология конструирования запросов различного вида. Дано описание конкретных действий пользователя в процессе конструирования запросов. Подробно описан процесс конструирования однотабличного и многотаблич- ного запроса, показано формирование вычисляемых полей, использование групповых операций и параметров запроса. Рассмотрено обновление таблиц с помощью запроса. Кроме того, в конце раздела приведен пример решения задачи на основе нескольких запросов. Конструирование однотабличного запроса на выборку Рассмотрим процесс конструирования однотабличного запроса на выборку на примере получения информации из таблицы ПРЕДМЕТ базы данных Учебный процесс. Использование логических операций в условии отбора Пусть надо выбрать предметы, по которым общее число изучения не более 100, и есть лекции, а также выбрать предметы, по которым общее число часов больше 150 и число семестров изучения не более двух. Результат должен содержать наименование предмета (НП), общее число часов по предмету (ЧАСЫ, количество лекционных часов (ЛЕК) и число семестров (ЧС). Для создания запроса в режиме конструктора выберем вкладку Создание на панели быстрого доступа и нажмем кнопку Конструктор запросов. После нажатия кнопки появляется окно запроса на выборку в режиме конструктора Запрос рис. 7.1) и диалоговое окно Добавление таблицы. В диалоговом окне выберем таблицу ПРЕДМЕТ и нажмем кнопку Добавить. Выбранная таблица будет отображена в области схемы данных запроса. Закроем окно Добавление таблицы. В окне конструктора (рис. 7.1) перетащим из списка полей таблицы ПРЕДМЕТ поля НП, ЧАСЫ, ЛЕК и ЧС в столбцы бланка запроса в строку Поле. Рис. 7.1. Окно конструктора запроса на выборку с логическими операциями в условии отбора. Сформулированные в задаче условия требуют формирования следующего логического выражения ЧАСЫ AND ЛЕК<>0) OR (ЧАСЫ AND ЧС<3) Здесь ЛЕК<>0 (число лекций неравно нулю, соответствует заданному в задаче условию выбрать предметы, в которых есть лекции. Условия из первых скобок запишем в соответствующих полях ЧАСЫ и ЛЕК первой строки Условия отбора. Между условиями в разных полях одной строки выполняется логическая операция . Условия из вторых скобок запишем в соответствующих полях ЧАСЫ и ЧС второй строки Условие отбора. Между условиями, записанными в разных строках, выполняется логическая операция . Выполним запрос, нажав на панели конструктора запросов кнопку Выполнить. На экране появится окно запроса в режиме таблицы с записями из таблицы ПРЕДМЕТ, отвечающими заданным условиям отбора. Сохраним запрос, нажав кнопку Сохранить на вкладке Файл и задав нужное имя запроса. Закроем текущий запрос нажав кнопку окна запроса Закрыть. Сохраненный запрос можно выполнить, выделив запрос в окне Все объекты Access, нажатием правой кнопки мыши и выбрав кнопку Открыть. 72 Использование в условии отбора выражений с именами полей В предыдущем примере в условии отбора в качестве операндов использовались только значения для отбора по конкретным полям. Создадим запрос, в условии отбора которого сравниваются значения в разных полях. Пусть необходимо проверить правильность задания общих часов в таблице ПРЕДМЕТ. По запросу должны отбираться только те записи, в которых значение в поле ЧАСЫ неравно значению, получаемому при сложении значений полей ПР и ЛЕК. Такое условие записывается в бланке запроса в столбце ЧАСЫ ив нем используются имена полей ПР и [ЛЕК], как показано в бланке запроса на рис. Рис. Окно запроса на выборку из таблицы ПРЕДМЕТ записей, в которых количество часовне равно сумме часов лекций и практики Конструирование многотабличного запроса на выборку Рассмотрим технологию конструирования многотабличного запроса на выборку на примере получения информации об успеваемости студентов из БД Учебный процесс. Запрос на основе нескольких взаимосвязанных таблиц Пусть необходимо получить информацию об оценках полученных студентами по всем предметам. Результат должен содержать фамилию студента, наименования сданных предметов и оценки. Для создания запроса на панели быстрого доступа выберем вкладку Создание и нажмем кнопку Конструктор запросов. 73 Формирование схемы данных запроса В окне Добавление таблицы выберем таблицы y СТУДЕНТ- для выборки фамилия студента из поля ФИО y УСПЕВАЕМОСТЬ- для определения кодов предметов (поле КП), по которым студент сдал экзамены, выборки оценок по предмету (из поля ОЦЕНКА. y ПРЕДМЕТ- для выборки наименования предмета (из поля НП), представленного кодом КП в таблице УСПЕВАЕМОСТЬ. Закроем окно Добавление таблицы. В окне конструктора запросов (рис. 7.3) представлена схема данных запроса, содержащая выбранные таблицы. Между таблицами автоматически установлены необходимые связи y Одно-многозначная связь между таблицами СТУДЕНТ и УСПЕВАЕМОСТЬ по составному ключу НГ+НС в соответствии с построенной ранее схемой данных. y Связь-объединение между УСПЕВАЕМОСТЬ и ПРЕДМЕТ поскольку эти таблицы имеют поля с одинаковым именем КП и одинаковым типом данных. Рис. 7.3. окно запроса об успеваемости студента с созданной схемой данных. Подготовка бланка запроса Поскольку в запросе используется несколько таблиц, в бланке запроса удобно видеть имя таблицы наряду с именем поля. Для отображения имен таблиц в бланке запроса (рис. 7.4) нажмем кнопку Имена таблиц на панели инструментов конструктора или нажмем соответствующую кнопку в контекстном меню, который вызовем правой кнопкой мыши. Рис. 7.4. Запрос на получение информации о сдаче экзаменов студентками Боярской Н.П. и Маковой. Перетащим с помощью мыши поля, включаемые в результат выполнения запроса, в строку бланка запроса Поле y ФИО- из таблицы СТУДЕНТ y НП- из таблицы ПРЕДМЕТ y ОЦЕНКА- из таблицы УСПЕВАЕМОСТЬ Ввод значений в условия отбора записей Пусть необходимо получить информацию об успеваемости конкретных студентов Боярской Н.П. и Маковой. Зададим в строке Условие отбора их фамилии. Запишем фамилии студентов в разных строках бланка запроса, поскольку необходимо выбрать записи со значением в поле ФИО- Боярская или Макова. Поскольку инициалы студентки Маковой неизвестны, ее фамилию зададим с использованием символа шаблона. Заметим, что фамилия с инициалами содержит точки, поэтому ее надо брать в кавычки. После ввода фамилии с символом шаблона система сама вставляет оператор Like, определяющий поиск по образцу. Заполненный бланк запроса представлен на рис. 7.4. Выполним запрос, нажав на панели конструктора запросов кнопку Выполнить. Замечание. Записи о заданном студенте появятся в результирующей таблице запроса только в том случае, если запись об этом студенте содержится в таблице СТУДЕНТа в таблице УСПЕВАЕМОСТЬ имеются записи, связанные с записью о студенте. 75 Формирование записей результата при выполнении запроса По заданной фамилии студента- Боярская Н.П.- в таблице СТУДЕНТ отыскивается запись. По значению ключа связи НГ+НС осуществляется выборка подчиненных записей из таблицы УСПЕВАЕМОСТЬ с оценками данного студента по разным предметам (в поле ОЦЕНКА. Для каждой из этих записей по значению ключа связи КП выбирается одна запись с наименованием предмета (НП) из таблицы ПРЕДМЕТ. Таким образом, таблица с результатом запроса будет содержать по одной записи о каждом предмете, сданном студентом. Аналогично формируются записи для второго заданного в запросе студента- Маковой. Ввод параметров в запрос В предыдущем примере для задания фамилии конкретного студента необходимо было корректировать бланк запроса. Чтобы избежать этого, целесообразно использовать в запросе параметры. При этом Access перед выполнением запроса через диалоговое окно будет запрашивать у пользователя конкретные значения параметров и введет их в условия отбора. Пусть необходимо получить информацию об оценке студента по заданному предмету. В условие отбора поля ФИО вместо конкретной фамилии введем название параметра, по которому будет запрашиваться фамилия при выполнении запроса. Название параметра введем как текст, заключенный в квадратные скобки Фамилия и инициалы студента Этот текст Access воспринимает как имя параметра (рис. 7.5). В условие отбора поля НП введем второй параметр запроса Наименование предмета Рис. 7.5. Бланк запроса с параметрами для ввода ФИО и НП. 76 Рис. 7.6. Диалоговые окна для ввода параметров запроса. При выполнении запроса Access выведет диалоговые окна, представленные на рис. 7.6, в которые пользователь сможет ввести нужные значения параметров. Использование имен полей различных таблиц в условии отбора Пусть необходимо выбрать записи из таблицы ИЗУЧЕНИЕ, в которых часы практических занятий по информатике не соответствуют равномерному распределению по семестрам всех часов практики. Для решения этой задачи необходимо использовать таблицы y ИЗУЧЕНИЕ, в которой содержатся сведения о плановых занятиях в группах (в текущем семестре, в том числе о продолжительности (поле ЧАСЫ) каждого вида занятия (поле ВИД. y ПРЕДМЕТ, в которой содержатся сведения о наименовании (поле НП), общей продолжительности изучения предмета (поле ЧАСЫ, числа часов практики (ПР) и числе семестров изучения (ЧС). Для отбора записей о практических занятиях по информатике из таблицы ИЗУЧЕНИЕ надо в строке Условие отбора для поля НП (ТАБЛИЦА ПРЕДМЕТ) задать значение Информатика, а для поля ВИД (таблицы ИЗУЧЕНИЕ) задать значение пр (практическое занятие. При равномерном распределении практики по семестрам общее число часов практических занятий по предмету (ПР) должно равняться произведению часов практики (ЧАСЫ) из таблицы ИЗУЧЕНИЕ на число семестров (ЧС) из таблицы ПРЕДМЕТ. Для решения рассматриваемой задачи надо включить в результат только те записи, для которых число часовне соответствует этому произведению. Для этого запишем в Условие отбора поля ПР (таблицы ПРЕДМЕТ) выражение ИЗУЧЕНИЕ ! [ЧАСЫ]*[ЧС] Замечание. Указывать таблицу ИЗУЧЕНИЕ для поля ЧАСЫ обязательно, потому что поле с таким же именем имеется ив таблице ПРЕДМЕТ. Запрос на выборку с условиями отбора записей, удовлетворяющих сформулированной задаче, приведен на рис. 7.7. Результаты выполнения запроса приведены на рис. 7.8. 77 Замечание. Если результат выполнения запроса не содержит записей, то это означает, что для заданного предмета в каждой из студенческих групп часы практических занятий соответствуют равномерному распределению по семестрам всех часов практики. Рис. 7.7. Запрос с использованием в условиях отбора имен полей из разных таблиц. Рис. |