Практикум Access Учебное пособие Сергиев Посад 2014
 Скачать 2.45 Mb.
|
|
Рисунок 23. Таблица «Успеваемость». Использование поля со списком «№Зачетки». Поле со списком «Преподаватель» таблицы «Успеваемость» Связь между таблицами «Преподаватели» и «Успеваемость» организуем по полю «Код» таблицы «Преподаватели» и полю «Преподаватель» таблицы «Успеваемость». 1. Откройте таблицу «Успеваемость» в Конструкторе. Для поля «Преподаватель» выберите тип данных «Мастер подстановок». Появится окно «Создание подстановки». 2. Далее. Появляется второе окно Мастера подстановок. Выбираем таблицу «Преподаватели». 3. Далее. Появляется третье окно Мастера подстановок. Выбираем поля «Код» и «Преподаватель» из таблицы «Преподаватели». 4. Далее. Появляется четвертое окно Мастера подстановок. Задаем порядок сортировки: по полю «Преподаватель». 5. Далее. Появляется пятое окно Мастера подстановок. Задаем ширину столбцов и оставляем галочку «Скрыть ключевой столбец». 6. Далее. Появляется шестое окно Мастера подстановок. Задаем подпись Преподаватель. 7. Сохраняем таблицу «Успеваемость» перед созданием связи с таблицей «Преподаватели». Сравните свойства поля со списком «Преподаватель» таблицы «Успеваемость» с рис. 24. Рисунок 24. Вкладка Подстановка поля со списком Преподаватель таблицы Успеваемость. 25 Обратите внимание, что тип данных поля «Преподаватель» преобразован в Числовой, что ширина первого столбца равна нулю, поскольку мы скрыли ключевой столбец. Самостоятельно проверьте работу поля со списком «Преподаватель» таблицы «Успеваемость». Поле со списком «Семестр» таблицы «Успеваемость» Поле «Семестр» таблицы «Успеваемость» преобразуем в поле со списком, чтобы можно было выбирать номер семестра из заранее заготовленного списка значений. 1. Откройте таблицу «Успеваемость» в Конструкторе. Для поля «Семестр» выберите тип данных «Мастер подстановок». Появится окно «Создание подстановки». Выберите опцию «Будет введен фиксированный набор значений». 2. Далее. Появляется второе окно Мастера подстановок. Введите числа 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 в ячейки столбца. 3. Далее. Появляется третье окно Мастера подстановок. Задайте подпись Семестр. 4. Готово. Сравните свойства поля со списком «Семестр» таблицы «Успеваемость» с рис. 25. Рисунок 25. Вкладка Подстановка поля со списком «Семестр» таблицы «Успеваемость» Поля со списками «Дисциплины», «ФормаКонтроля», «Оценки» таблицы «Успеваемость» С помощью Мастера подстановок самостоятельно преобразуйте поля «Дисциплина», «ФормаКонтроля», «Оценка» таблицы «Успеваемость» в поля со списками и организуйте связи между таблицей «Успеваемость» и таблицами «Дисциплины», «ФормаКонтроля», «Оценки». 26 Ввод данных в таблицу «Успеваемость» Заполните таблицу Успеваемость данными, как показано. Данные нужно вводить построчно (запись за записью), как в реальной жизни. Не надо копировать данные, это может привести к ошибкам. Добавьте свои оценки по нескольким дисциплинам. Успеваемость №зачетки Семестр Дисциплина ФормаКонтроля Оценка Дата Преподаватель 2ю/2013 1 ИТ в юридической деятельности Зачет зачтено Ткаченко Л.А. 1ю/2013 1 ИТ в юридической деятельности Зачет зачтено 15.12.2013 Ткаченко Л.А. 1ю/2013 1 Правоохранительные органы Экзамен 2 Качаун Е.Я. 2ю/2013 1 Практикум по освоению ЭВМ Зачет зачтено Кабанов В.А. 1ю/2013 1 Практикум по освоению ЭВМ Зачет незачтено 20.12.2013 Кабанов В.А. Сортировка и фильтрация записей в таблицах Сортировка и фильтрация записей в таблицах Access производится так же как в Excel. Отсортируйте таблицу «Студенты» по полю «Фамилия», затем по полю «Имя», потом по полю «Отчество». Порядок сортировки от А до Я (рис. 26). Рисунок 26. Сортировка записей в таблице «Студенты» по полю «Фамилия». 27 Отфильтруйте записи таблицы «Студенты», показав все записи, у которых в №зачетки есть буква Э. Рисунок 27. Фильтрация записей в таблице «Студенты» по полю «№Зачетки». Вопросы по теме «Таблицы» 1. Что такое таблица базы данных? Способы создания таблиц. 2. Что такое поле таблицы? 3. Что такое тип данных поля? Какие существуют типы данных? 4. Что такое свойства поля таблицы? Какие существуют свойства поля таблицы? Зависят ли свойства поля от типа данных поля? 5. Что такое ключевое поле? Для чего оно используется? Допустимо ли использовать несколько полей таблицы в качестве ключевого поля? Особенности ключевого поля с типом данных Счетчик? 6. Что такое запись таблицы? 7. Что такое поле записи? 8. Что такое ячейка таблицы? 9. Что такое значение поля записи? 10. Отличие режима таблицы от режима конструктора? 11. Что такое область выделения записей в таблице? 12. Что такое поле со списком в таблице? В чем преимущество его использования? Какие свойства подстановки поля со списком Вы знаете? Для чего они используются? 13. Как создать поле со списком в таблице с помощью Мастера подстановок? Как преобразовать поле со списком в таблице в обычное поле? 14. Как осуществляется сортировка и фильтрация записей? 28 Схема данных Схема данных позволяет нам посмотреть на базу данных как бы сверху, увидеть какие у нас таблицы, какие поля есть в этих таблицах, есть ли ключевые поля, есть ли связи между таблицами и по каким полям произведено связывание, включено ли обеспечение целостности. Откроем окно Схема данных (Работа с базами данных/Схема данных). Добавим в Схему данных все наши таблицы (Конструктор/Отобразить таблицу) (рис. 28). Рисунок 28. Схема данных после создания полей со списками. В схеме данных каждая таблица представлена списком полей этой таблицы. Ключевые поля таблиц отмечены значком. Линии связи между полями таблиц уже созданы. Они были созданы тогда, когда мы делали поля со списком. Линия связи соединяет первичный ключ главной таблицы (например, ключевое поле «Группа» таблицы «Группы») с внешним ключом подчиненной таблицы (например, поле «Группа» таблицы «Студенты»). Можно прямо в схеме данных устанавливать связи или удалять связи, помните только, что можно связывать поля с одинаковой по смыслу информацией и с одинаковым типом данных. Например, нельзя связывать поле №зачетки с полем Группа, хотя тип данных у них одинаковый, но по смыслу информация в этих полях разная. Чтобы удалить связь надо щелкнуть правой кнопкой мыши по линии связи и дать команду Удалить. Удалите связь между таблицами «Группы» и «Студенты» (рис 29). Рисунок 29. Удаление связи по полю «Группа» между таблицами «Группы» и «Студенты» в схеме данных. 29 Чтобы создать связь надо навести указатель мыши на поле, нажать левую кнопку мыши и, удерживая её нажатой, протащить на соответствующее поле другой таблицы. Появится окно Изменение связей. Восстановите связь между таблицами «Группы» и «Студенты» (рис.30). Рисунок 30. Создание связи по полю «Группа» между таблицами «Группы» и «Студенты». В окне Изменение связей можно задавать Обеспечение целостности или отказываться от него, Кроме того, можно включать или отключать такие дополнительные возможности, как Каскадное обновление связанных полей и Каскадное удаление связанных записей. Вопросы по теме «Схема данных» 1. Что такое Схема данных? Для чего она используется? Что такое список полей в схеме данных? Как связываются таблицы? Можно ли связывать таблицы уже заполненные значениями? 2. Что такое межтабличная связь? Какие существуют типы межтабличных связей? Что такое линия связи в схеме данных? Каковы требования к связываемым полям? 3. Что такое первичный ключ и внешний ключ? 4. Что такое главная и подчиненная таблица? Может ли главная таблица одновременно быть и подчиненной таблицей? Какова очередность заполнения главной и подчиненной таблиц? Чем это вызвано? Целостность данных Обеспечение целостности данных нам нужно для того, чтобы одна и та же информация в связанных таблицах была представлена одинаково. Например, человек может при вводе ошибиться и ввести в одном случае код группы 1721, а в другом код группы 1721_. Это разные коды с точки зрения Access. Обеспечение целостности страхует нас от таких ошибок. 30 Зададим Обеспечение целостности . Для этого надо выделить линию связи, например, между полем «Группа» таблицы «Группы» и полем «Группа» таблицы «Студенты», и правой кнопкой мыши вызвать контекстное меню, в котором выбрать "Изменить связь". Появится окно «Изменение связей». Поставим галочку для обеспечения целостности (рис. 31). Рисунок 31. Окно «Изменение связей». Включено Обеспечение целостности. После обеспечения целостности для всех связей получим такую схему данных (рис. 32). Рисунок 32. Схема данных с обеспечением целостности. У концов линий связи появятся знаки "1" и "∞". Таблицы на стороне "1" называются главными, а на стороне "∞" – подчиненными или связанными. Например, таблица «Группы» – главная, а таблица «Студенты» – подчиненная. Тип связи между ними "один-ко-многим". Это обозначает, что в одной группе может быть много студентов, а может и ни одного. Бывают еще типы связи "один-к-одному" и "многие-ко-многим". Например, таблица «Успеваемость» обеспечивает связь "многие-ко-многим". Один студент сдает экзамены по многим дисциплинам, одну дисциплину сдают многие студенты. Правила целостности Правила целостности гарантируют нам, что первичный ключ (например, поле «Группа» таблицы «Группы») не будет содержать повторяющихся значений, а внешний ключ (в нашем примере поле 31 «Группа» таблицы «Студенты») будет содержать только те значения, которые ранее были введены в соответствующий первичный ключ. Самостоятельно проверьте, как работает Обеспечение целостности. Для этого, надо открыть какую- нибудь таблицу, например «Группы», и попробовать изменить или удалить код какой-нибудь группы. Если у этой группы есть подчиненные записи в таблице Студенты, то Access не позволит нам это сделать. Действительно, как же можно удалять группу, если в ней есть студенты. А вот если студентов в группе нет, тогда можно и удалить код группы, и изменить его. А теперь откроем подчиненную таблицу «Студенты» и попробуем изменить код группы у какого-либо студента, если такой группы нет в главной таблице Группы, то Access не позволит нам это сделать, в то же время перевести студента из одной группы в другую можно. Поэкспериментируйте. Каскадное обновление связанных полей Задайте у линии связи таблицы Группы Каскадное обновление связанных полей (рис. 33) Рисунок 33. Каскадное обновление связанных полей. А теперь откройте таблицу «Группы» и измените код какой-нибудь группы. Access позволит нам это сделать, при этом код группы в таблице «Студенты» также изменится. Каскадное удаление связанных записей Задайте у линии связи таблицы «Группы» Каскадное удаление связанных записей (рис. 34). Рисунок 34. Каскадное удаление связанных записей. 32 Если вы откроете таблицу «Группы» и попытаетесь удалить запись с кодом какой-либо группы, то Access позволит нам это сделать, но при этом будут удалены все студенты этой группы, так что пользоваться этой возможностью надо осторожно. Вопросы по теме «Целостность данных» 1. Что такое межтабличная связь? Какие существуют типы межтабличных связей? Что такое линия связи в схеме данных? Каковы требования к связываемым полям? 2. Что такое первичный ключ и внешний ключ? 3. Что такое главная и подчиненная таблица? Может ли главная таблица одновременно быть и подчиненной таблицей? Какова очередность заполнения главной и подчиненной таблиц? Чем это вызвано? 4. Что такое целостность данных? Для чего она используется? Проанализируйте схему данных. Как конкретно обеспечивается целостность данных для связанных таблиц? 5. Что такое Каскадное обновление связанных полей? 6. Что такое Каскадное удаление связанных записей? 7. Почему надо тщательно продумать и создать схему базы данных до ввода данных в таблицы? Запросы Запросы используются для выборки данных из таблиц или других запросов, для изменения данных, для вычислений. В меню "Создание" представлены два основных способа создания запросов: Мастер запросов и Конструктор запросов. При создании запроса появится меню Конструктор, в котором можно указать тип запроса. Существует несколько типов запросов: "Выборка" (тип запроса по умолчанию), "Создание таблицы", "Добавление", "Обновление", "Удаление" и др. Есть несколько режимов просмотра запроса, основными режимами являются "Режим таблицы" и "Конструктор". Запрос на выборку С помощью Мастера запросов (Простой запрос) создайте запрос на выборку. Включите в запрос две таблицы «Группы» и «Студенты», выберите в запрос поля Группа, №Зачетки, Фамилия, Имя (рис. 35). Сохраните запрос с именем "Список студентов". Проверьте запрос в работе. Рисунок 35. Запрос на выборку. Запрос с параметром С помощью Конструктора запросов создайте запрос с параметром на выборку студентов указываемой группы. Включите в запрос две таблицы «Группы» и «Студенты», выберите в запрос 33 поля Группа, №Зачетки, Фамилия, Имя. В условии отбора по полю «Группа» введите подходящий текст в квадратных скобках, например, [Введите группу] (рис. 36). Проверьте запрос в работе. Рисунок 36. Запрос с параметром. Запрос «без подчиненных» С помощью Мастера запросов (запрос "без подчиненных") узнайте, в каких группах нет студентов. У нас в группе с кодом 9999 нет студентов. Выполните запрос, он покажет группу, в которой нет студентов. Обратите внимание, что по полю «Группа» в условии отбора Мастер запросов поставил выражение Is Null (рис. 37). Рисунок 37. Запрос без подчиненных. Запрос «повторяющиеся записи» С помощью Мастера запросов (запрос "повторяющиеся записи") узнайте, есть ли повторяющиеся записи в таблице "Успеваемость". Для этого выберите в запрос все поля из таблицы "Успеваемость". Если у нас нет повторяющихся записей в таблице "Успеваемость", то запрос покажет их отсутствие. Для проверки работы запроса введите в таблицу запись, которая точно повторяет какую-либо запись. Обязательно должны быть заполнены поля «№зачетки» и «Преподаватель», другие поля могут быть и не заполнены. После этого повторите запрос, повторяющаяся запись будет найдена и показана. Это ошибочная запись, так как нет необходимости дважды фиксировать одни и те же результаты, это может нарушить отчетность. Самостоятельно ознакомьтесь со структурой запроса в конструкторе, он достаточно громоздкий. 34 Запрос на основе нескольких таблиц С помощью Мастера запросов (простой запрос) получите данные об успеваемости студентов на основе трех таблиц: "Группы", "Студенты", "Успеваемость". Включите в запрос поля: Группа, №Зачетки, Фамилия, Имя, Семестр, Дисциплина, ФормаКонтроля, Оценка, Дата, Преподаватель. Дайте запросу имя "Успеваемость Студентов" (рис. 38). Рисунок 38. Запрос на основе нескольких таблиц. Выполните запрос "Успеваемость Студентов". Этот запрос нами подготовлен для следующего задания по созданию перекрестного запроса, так как перекрестный запрос не может быть построен на основе нескольких таблиц, а только на основе одной таблицы или одного запроса. Перекрестный запрос С помощью Мастера запросов (перекрестный запрос), на основе предварительно специально нами подготовленного запроса "Успеваемость студентов", постройте перекрестную таблицу со сведениями об успеваемости студентов по учебным дисциплинам. Этот запрос должен показать нам в качестве заголовков строк - Дисциплины, в качестве заголовков столбцов - Фамилии, а на пересечении строк и столбцов - оценки. В конструкторе наш перекрестный запрос будет выглядеть примерно так (рис. 39). Рисунок 39. Перекрестный запрос в Конструкторе. Выполните запрос. Должно получиться примерно так (рис. 40). 35 Рисунок 40. Перекрестный запрос. Вопросы по теме «Запросы» 1. Что такое запрос на выборку? Способы создания запросов. 2. Типы запросов: запрос на выборку, запрос на обновление, запрос на добавление, запрос на удаление, запрос на создание таблицы, перекрестный запрос. 3. Режимы работы с запросом: Режим таблицы, Сводная таблица, Сводная диаграмма, Режим SQL, Конструктор. 4. Бланк запроса? Для чего он используется? Можно ли изменять межтабличные связи в бланке запроса? 5. Источник данных для запроса? Позволяет ли запрос использовать в качестве источника данных другие запросы, несколько таблиц или запросов? 6. Что такое условие отбора для поля в запросе? 7. Что такое запрос с параметром? 8. Что такое перекрестный запрос? 9. Какую информацию позволяет получить "Запрос без подчиненных"? 10. Какую информацию позволяет получить "Запрос Повторяющиеся записи"? Вычисления в запросах. Построитель выражений. Запрос с группировкой Создадим в Конструкторе запрос с группировкой для подсчета количества студентов в группах. Включим в запрос таблицы «Группы» и «Студенты». Из них выберем два поля «Группа» и «№Зачетки». Далее надо показать в запросе строку Групповая операция, которая обычно скрыта. Для этого можно в меню Конструктор нажать кнопку |