Файл: 2 Кафедра Информационные технологии и методика преподавания информатики Дисциплина Компьютерные науки направление 010100. 62, Информационные технологии направление 031300. 62, Информационные системы направление 034700.pdf
Добавлен: 10.11.2023
Просмотров: 36
Скачиваний: 1
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ федеральное государственное бюджетное образовательное учреждение высшего профессионального образования Курганский государственный университет Кафедра информационных технологий и методики преподавания информатики ОСНОВЫ РАБОТЫ С СУБД MICROSOFT ACCESS Методические рекомендации для студентов очной и заочной форм обучения направлений 010100.62, 031300.62, 050400.62, 230700.62, 034700.62 Курган 2015
2 Кафедра Информационные технологии и методика преподавания информатики Дисциплина Компьютерные науки направление 010100.62), Информационные технологии направление 031300.62), Информационные системы направление 034700.62), Информатика и программирование направление 230700.62), Информационные системы и базы данных по психолого- педагогическим наукам направление 050400.62). Составитель старший преподаватель С.Г. Тетюшева. Утверждены на заседании кафедры «26» сентября 2014 г. Рекомендованы методическим советом университета «20» декабря 2013 г.
3
1 Проектирование базы данных Деканат Базы данных (БД) – это любой набор собранной информации. Примерами баз данных являются записные книжки, телефонные справочники, расписание электричек и т.д. Для управления, редактирования, поиска, подготовки отчетов по базам данных служат специальные программы – системы управления базами данных СУБД. Одной из систем управления базами данных является Microsoft Access. Современные системы управления базами данных основываются на использовании моделей данных, позволяющих описывать объекты предметных областей и взаимосвязи между ними. Существуют три основных модели данных и их комбинации, на которых основываются системы управления базами данных реляционная модель данных, сетевая модель данных, иерархическая модель данных. На ПК в основном используют системы управления базами данных, поддерживающие реляционную модель данных, в соответствии с которой база данных представляется в виде совокупности таблиц, связанных между собой. Создадим базу данных, которая будет содержать сведения о студентах, успеваемости студентов по предметами преподавателях, ведущих эти предметы.
1.1 Создание инфологической модели базы данных Деканат Одной из целей создания хорошей структуры базы данных является устранение избыточности данных (повторяющихся данных. Для этого нужно распределить данные по нескольким отдельным, тематически организованным таблицам, чтобы каждый факт был представлен один раз. Например, если в базе хранятся сведения о студентах, каждого из них следует один раз внести в таблицу, которая предназначена исключительно для хранения данных о студентах. Данные о дисциплинах будут храниться в отдельной таблице, а данные о преподавателях в другой. Эта процедура называется нормализацией. Перед разработкой информационно-логической модели реляционной базы данных рассмотрим, из каких информационных объектов должна состоять эта база данных. Можно выделить три объекта, которые не будут обладать избыточностью Студенты, Дисциплины и Преподаватели Представим состав атрибутов этих объектов в виде название объекта (перечень атрибутов
•
Студенты (код студента, фамилия, имя, отчество, номер группы, пол, дата рождения, телефон, стипендия, оценки
•
Дисциплины (код дисциплины, название дисциплины
•
Преподаватели (код преподавателя, фамилия, имя, отчество, дата рождения, телефон, должность, кафедра. Рассмотрим связь между объектами Студенты и Дисциплины Студент изучает несколько дисциплин, что соответствует многозначной связи и отражено на рисунке 1 двойной стрелкой. Каждая дисциплина изучается множеством студентов. Это тоже многозначная связь. Таким образом, связь между
4 объектами Студенты и «Дисциплины»имеет тип«многие-ко-многим» (М. Рассмотрим связь между объектами Преподаватели и Дисциплины Преподаватель преподает несколько дисциплин, что соответствует многозначной связи и отражено на рисунке 1 двойной стрелкой. С другой стороны, каждая дисциплина может преподаваться несколькими преподавателями (например, у разных групп студентов и на различных факультетах дисциплину с одними тем же названием преподают различные преподаватели. Таким образом, связь между объектами Преподаватели и Дисциплины – «многие-ко- многим (М. Рисунок 1 − Типы связей между объектами базы данных Деканат Множественные связи усложняют управление базой данных, например, в СУБД Microsoft Access при множественных связях нельзя использовать механизм каскадного обновления. Поэтому использовать такие связи нежелательно и нужно строить реляционную модель, не содержащую связей типа «многие-ко- многим В Microsoft Access для контроля целостности данных с возможностью каскадного обновления и удаления данных необходимо создать вспомогательный объект связи, который состоит из ключевых атрибутов связываемых объектов и который может быть дополнен описательными атрибутами. В нашем случае таким новым объектом для связи служит объект Оценки атрибутами которого являются код студента, код дисциплины, код преподавателя и оценки. Каждый студент имеет оценки по нескольким дисциплинам, поэтому связь между объектами Студенты и Оценки будет «один-ко-многим»(1:М). Каждую дисциплину сдает множество студентов, поэтому связь между объектами Дисциплины и Оценки также будет «один-ко-многим» (МВ результате получаем информационно-логическую модель базы данных, приведенную на рисунке 2. Рисунок 2 − Информационно-логическая модель реляционной базы данных
1.2 Создание логической модели базы данных Деканат В реляционной базе данных в качестве объектов рассматриваются отношения, которые можно представить в виде таблиц. Таблицы между собой связываются посредством общих полей, одинаковых по форматами, как правило, по названию, имеющихся в обеих таблицах. Рассмотрим, какие общие поля надо ввести в таблицы для обеспечения связности данных. В таблицах Студенты и Оценки Студенты Дисциплины Преподаватели ММ М Студенты
M:N Дисциплины
M:N Преподаватели
5 Оценки таким полем будет Код студента, в таблицах Дисциплины и Оценки – Код дисциплины, в таблицах Преподаватели и Оценки – Код преподавателя. В соответствии с этим логическая модель базы данных представлена на рисунке 3. Студенты Оценки Дисциплины Преподаватели Код студента Код студента Код дисциплины Код преподавателя Фамилия Код дисциплины Имя Название дисциплины Фамилия Отчество Код преподавателя Имя Номер группы Отчество Пол Оценка Дата рождения Дата рождения Телефон Стипендия
Должность
Телефон Кафедра Рисунок 3 − Логическая модель базы данных При создании таблиц в среде Microsoft Access для каждого поля, включаемого в запись таблицы, необходимо указать тип данных. Тип данных определяет вид информации, которая будет храниться в данном поле. Описание полей таблиц для разрабатываемой базы данных Деканат представлено в таблицах
1-4. Таблица 1 − Описание полей таблицы Дисциплины Имя поля Тип данных Свойства поля Код дисциплины Счетчик Длинное целое, первичный ключ Название Текстовый Длина 50, обязательное Таблица 2 − Описание полей таблицы Студенты Имя поля Тип данных Свойства поля Код студента Счетчик Длинное целое, первичный ключ Фамилия Текстовый Длина 20, индекс с повторениями, обязательное Имя Текстовый Длина 15, обязательное Отчество Текстовый Длина 20, обязательное Номер группы Текстовый Длина 6, обязательное Пол Мастер подстановок Длина 1, список (М Ж, обязательное ММ М
6 Продолжение таблицы 2 Дата рождения
Дата/время Краткий формат даты, маска ввода, по умолчанию
01.01.85, обязательное Стипендия Логический По умолчанию Нет, необязательное Телефон Текстовый Длина 10, маска ввода, необязательное Таблица 3 − Описание полей таблицы Преподаватели Имя поля Тип данных Свойства поля Код преподавателя Счетчик Длинное целое, первичный ключ Фамилия Текстовый Длина 20, обязательное Имя Текстовый Длина 15, обязательное Отчество Текстовый Длина 20, обязательное Должность Мастер подстановок Длина 21, список (ассистент старший преподаватель доцент профессор, обязательное Кафедра Текстовый Длина 50, обязательное Дата рождения
Дата/время Краткий формат даты, маска ввода, по умолчанию, необязательное Телефон Текстовый Длина 10, маска ввода, необязательное Таблица 4 − Описание полей таблицы Оценки Имя поля Тип данных Свойства поля Код студента Мастер подстановок Подстановка из поля Код студента таблицы Студенты, обязательное Код дисциплины Мастер подстановок Подстановка из поля Код дисциплины таблицы Дисциплины, обязательное Код преподавателя Мастер подстановок Подстановка из поля Код преподавателя таблицы Преподаватели, обязательное Оценка Мастер подстановок Список фиксированных значений (5; 4; 3; 2), обязательное Создание реляционной базы данных База данных Microsoft Access – это совокупность данных и объектов (те. таблиц, форм, отчетов и т.п.), относящихся к определенной задаче и представляющих законченную систему. Базу данных Access составляют таблицы, запросы, формы, отчеты, страницы доступа, макросы и модули. Вся совокупность данных и объектов базы данных хранится водном файле.
7 Создание новой базы данных
В среде Microsoft Access 2003
1 Открыть Access, вменю Файл выбрать команду Создать. В появившейся панели Создание файла выбрать пункт Новая база данных.
2 В окне Файл новой базы данных выбрать папку, в которой будет находиться база данных.
3 В поле Имя файла задать имя для базы данных.
4 Нажать кнопку Создать. Будет создана новая база данных.
В среде Microsoft Access 2007
1 На странице Приступая к работе св разделе Новая пустая база данных выбрать команду Новая база данных.
2 В области Новая база данных в поле Имя файла ввести имя файла. Если имя файла указано без расширения, расширение будет добавлено автоматически Чтобы сохранить файл в другой папке, отличной от используемой по умолчанию, нажать кнопку Открыть рядом с полем Имя файла, перейти к нужной папке и нажать кнопку ОК.
4 Нажать кнопку Создать. Будет создана новая база данных и открыта новая таблица в режиме таблицы.
2.1 Создание таблиц базы данных В Microsoft Access используются три способа создания таблиц путем ввода данных, с помощью Конструктора таблиц и с помощью Мастера создания таблиц. Обычно создание таблиц путем ввода данных используют тогда, когда структура таблицы очень проста, например справочник. Тогда Access сама догадывается о том, как установить свойства полей. Использование Мастера таблиц оправдано, если нужно создать какую-то типовую таблицу, пользуясь шаблонами, которые он предложит. Наиболее часто применяется режим Конструктора, он может также потребоваться для просмотра или изменения структуры таблицы. Создание таблицы путем ввода данных
1 а В среде Microsoft Access 2003: раскрыть список таблиц, щелкнув по ярлыку Таблицы на панели объектов окна базы данных, и ажать кнопку Создать. В окне Новая таблица щелкнуть строку Режим таблица затем кнопку
OK
. Откроется пустая таблица б в среде Microsoft Access 2007: в панели инструментов на вкладке Создание в группе Таблицы щелкнуть Таблица. Access добавит новую пустую таблицу и откроет ее в режиме таблицы.
2 В режиме таблицы дважды щелкнуть заголовок первого столбца, ввести новое имя и нажать клавишу Enter.
3 Щелкнуть следующее поле или использовать клавиши со стрелками, чтобы перейти к нему, и ввести имя поля. Повторять эту процедуру до тех пор, пока всем полям таблицы не будут заданы имена.
4 Ввести данные в таблицу. В соответствии с введенными данными полю присваивается тип данных.
5 Нажать кнопку Сохранить на панели инструментов.
6 В окне Сохранение в поле Имя таблицы ввести имя новой таблицы и нажать кнопку ОК.
7 При сохранении таблицы выводится приглашение создать первичный ключ. Целесообразнее нажать кнопку Нет, а затем указать поле в качестве первичного ключа в режиме конструктора. Создание таблицы в режиме конструктора
1 а в среде Microsoft Access 2003: раскрыть список таблиц, щелкнув по ярлыку Таблицы на панели объектов окна базы данных, и затем нажать кнопку Создать. В окне Новая таблица щелкнуть строку Режим конструктора, а затем кнопку OK; б в среде Microsoft Access 2007: в панели инструментов на вкладке Создание в группе Таблицы щелкнуть Конструктор таблиц. Access добавит новую пустую таблицу и откроет ее в режиме конструктора.
2 Щелкнуть ячейку в столбце Имя поля и ввести уникальное имя поля.
3 В столбце Тип данных выбрать из раскрывающегося списка тип данных.
4 В столбце Описание ввести описание данных, которые будет содержать это поле. Текст описания будет выводиться в строке состояния при добавлении данных в поле, а также будет включен в описание объекта таблицы. Вводить описание необязательно Повторить действия 2-4 до тех пор, пока не будут заданы все поля таблицы.
6 Для определения первичного ключа выделить одно или несколько полей, которые требуется определить как поля первичного ключа, и нажать на панели инструментов кнопку Ключевое поле.
7 Нажать кнопку Сохранить на панели инструментов, ввести имя новой таблицы и нажать кнопку ОК.
При создании таблиц в режиме конструктора приходится задавать имена таблиц и полей в таблицах. При этом необходимо придерживаться ряда правил.
•
Имена полей в таблице должны быть уникальными.
•
Имена полей могут содержать не более 64 символов, включая пробелы.
•
Желательно избегать употребления имен полей, совпадающих с именами встроенных функций или свойств Microsoft Access.
•
Имя поляне должно начинаться с пробела.
•
Имена полей могут содержать любые символы, включая буквы, цифры, пробелы, специальные символы, за исключением точки (.), восклицательного знака (!), апострофа (') и квадратных скобок ([ ]). Эти же правила действительны и для имен таблица также других объектов Создание таблицы при помощи мастера таблиц
В среде Microsoft Access 2003
1 Раскрыть список таблиц, щелкнув по ярлыку Таблицы на панели объектов окна базы данных, и нажать кнопку Создать. В окне Новая таблица щелкнуть строку Мастер таблица затем кнопку OK.
2 В окне Создание таблиц выбрать категорию (деловые или личные) и образец таблиц, затем из списка Образцы полей с помощью кнопок > ивы- брать поля для новой таблицы. При необходимости можно переименовать выбранное поле, выделив его название в списке Поля новой таблицы и нажать кнопку Переименовать поле.
3 Нажать кнопку Далее.
4 В следующем окне задать имя для новой таблицы и выбрать опцию Пользователь определяет ключ самостоятельно. Нажать кнопку Далее.
5 Из выпадающего списка вверху окна выбрать поле, которое будет ключевыми выбрать тип данных для этого поля. Нажать кнопку Далее.
6 Если в базе данных уже существуют таблицы, тов следующем окне нужно указать связь создаваемой таблицы с уже существующими таблицами и нажать кнопку Далее.
7 Затем нужно выбрать действие после создания таблицы изменить структуру таблицы, ввести данные непосредственно в таблицу или же ввести данные с помощью автоматически создаваемой формы. Нажать кнопку Готово В среде Microsoft Access 2007: в панели инструментов на вкладке Создание в группе Таблицы щелкнуть Шаблоны таблицы и выбрать из списка один из доступных шаблонов. Будет вставлена новая таблица на основе выбранного шаблона таблицы.
2.2 Типы данных и свойства полей В Microsoft Access имеются следующие типы данных
•
текстовый – символьные или числовые данные, не требующие вычислений (до 255 символов. Размер текстового поля задается с помощью свойства Размер поля, в котором указывается максимальное количество символов, которые могут быть введены в данное поле
•
поле MEMO – предназначено для ввода текстовой информации, по объему превышающей 255 символов (может содержать до 65 535 символов. Поле этого типа не может быть ключевым или проиндексированным
•
числовой – применяется для хранения числовых данных, используемых в математических расчетах. От выбора подтипа (размера) данных числового типа зависит точность вычислений. Для установки подтипа числовых данных служит свойство Размер поля. Обычно по умолчанию используется подтип Длинное целое
•
дата/время – тип для представления даты и времени. Размер поля – 8 байтов предоставляет большой выбор форматов отображения даты и времени
10
•
денежный – предназначен для хранения данных, точность представления которых колеблется от 1 до 4 десятичных знаков. Целая часть данного типа может содержать до 15 десятичных знаков
•
счетчик – поле содержит 4-байтный уникальный номер, определяемый
Microsoft Access автоматически для каждой новой записи либо случайным образом, либо путем увеличения предыдущего значения на 1. Значения полей типа счетчика обновлять нельзя
•
логический – логическое поле, которое может содержать только два значения, интерпретируемых как Да/Нет, Истина/Ложь, Включено/Выключено.
Access использует величину –1 для представления значения Истина и величину 0 – для значения Ложь
•
гиперссылка – дает возможность хранить в поле ссылку, с помощью которой можно ссылаться на произвольный фрагмент данных внутри файла или страницы на том же компьютере или в Интернете
•
поле объекта OLE – содержит ссылку на объект (лист Microsoft
Excel, документ Microsoft Word, звук, рисунок и т.п.);
•
мастер подстановок – запускает мастер подстановок, создающий поле подстановок. Поле подстановок позволяет выбирать значение поля из списка, содержащего набор постоянных значений или значений из другой таблицы. Как только вы выбираете тип данных для поля, так на нижней панели окна отображается список свойств этого поля. Данный список зависит от типа поля. Для того чтобы просмотреть или изменить свойства конкретного поля таблицы необходимо
1) выделить нужное поле таблицы в окне конструктора (то есть установить курсор в строку с именем поля
2) раскрыть вкладку Общие для просмотра общих свойств поля или вкладку Подстановка для просмотра параметров подстановки. Рассмотрим основные свойства полей для каждого типа данных
•
подпись − позволяет задать названия полей таблицы, которые выводятся в различных режимах (в надписях, присоединенных к элементам управления формы, в заголовке столбца в режиме таблицы и т.п);
•
обязательное поле определяет необходимость ввода данных в это поле
•
индексированное поле − определяет, является ли данное поле индексированными если является, тов каком режиме. Существуют два режима индексирования Совпадения допускаются и Совпадения не допускаются значение по умолчанию − содержит значение, автоматически добавляемое в поле для каждой новой записи, если это значение не введено пользователем условие назначение определяет условие (ограничение, накладываемое на вводимые в это поле данные. При несоответствии вводимых данных указанному условию выдается сообщение об ошибке
11
•
сообщение об ошибке определяет то сообщение, которое будет выдаваться пользователю, если при вводе данных не соблюдается условие, указанное в свойстве Условие назначение (если это свойство не указано, будет выдано стандартное сообщение об ошибке
•
формат поля − позволяет указать форматы вывода текста, чисел, дат и значений времени на экран и на печать. Свойство имеет различные значения для разных типов данных, выбираемые из раскрывающегося списка маска ввода задает маску, позволяющую упрощать процесс ввода, атак- же автоматизировать проверку ввода символов в поле. Например, для полям, хранящего номер телефона, маска ввода обеспечивает возможность вводить только цифры номера, а остальные символы (скобки вокруг кода города, дефис между цифрами номера) будут добавляться автоматически. Задавать маску ввода можно вручную (с помощью символов, приведенных в таблице 6) или с помощью мастера. Маска ввода состоит из трех частей
- символьной строки маски
- символа 0. Это означает, что введенные в поле данные будут сохраняться вместе со специальными символами. Можно заменить 0 на 1 – тогда специальные символы маски не будут запоминаться в поле
- символа заполнителя. Примеры масок приведены в таблице 7. Таблица 6 − Символы для создания масок ввода Символ маски Описание
1 2 3 4 5 6 7
0 Должна быть введена цифра. Знаки плюс и минус не допускаются
9 Должна быть введена цифра или пробел. Знаки плюс и минус не допускаются Должна быть введена цифра, пробел, знаки плюс или минус
; L Должна быть введена буква
? Может быть введена буква или пробел А Должна быть введена буква или цифра а Должна быть введена буква, цифра или пробел
& Должен быть введен произвольный символ или пробел С Может быть введен произвольный символ или пробел. Если пользователь ничего не введет, Access не занесет в эту позицию никаких данных или , Десятичный разделитель (зависит от региональных установок в окне Языки стандарты Панели управления Windows)
: – / Разделители в значениях даты и времени (зависят от региональных установок в окне Языки стандарты Панели управления Windows)
< Преобразует все символы справа к нижнему регистру
12 Продолжение таблицы 6
> Преобразует все символы справа к верхнему регистру
! Указывает, что маску нужно заполнять справа налево. Этот символ следует использовать в том случае, когда символы в левой части маски являются необязательными. Его можно помещать в любой позиции маски
\ Указывает, что следующий символ необходимо рассматривать в качестве постоянного символа, даже если он является специальным символом маски. Например, А будет выводить в маске букву А Таблица 7 − Примеры использования масок ввода Маска ввода Обеспечивает ввод в виде Примечания
(000) 000-0000
(206) 555-0199 При вводе номера телефона пользователи должны вводить код города, т.к. в соответствующем разделе маски используется заполнитель В этом случае код города необязателен. Кроме того, восклицательный знак (!) обусловливает заполнение маски слева направо Обязательный почтовый индекс и необязательная область четырехзначного расширения Мария Вадим Имя или фамилия, первая буква которой автоматически вводится заглавной
2.3 Создание межтабличных связей После создания таблицы для каждой темы в базе данных нужно использовать средства, с помощью которых можно будет вновь объединять сведения при необходимости. Это делается путем помещения общих полей в связанные таблицы и определения связей между таблицами. После этого можно создавать запросы, формы и отчеты, одновременно отображающие сведения из нескольких таблиц. При создании связей между таблицами следует позаботиться об обеспечении целостности данных. Примером нарушения целостности данных может служить следующая ситуация. Если в таблице Студенты кто-то удалит запись для одного из студентов, ноне сделает этого в таблице Оценки, то получится, что согласно таблице Оценки некто, имеющий только абстрактный код, сдал экзамены. Узнать по коду, кто этот студент, будет невозможно – произошло нарушение целостности данных. Установка связи между таблицами позволяет
13
•
либо исключить возможность удаления или изменения данных в ключевом поле главной таблицы, если с этим полем связаны какие-либо поля других таблиц
•
либо сделать так, что при удалении (или изменении) данных в ключевом поле главной таблицы автоматически произойдет удаление или изменение соответствующих данных в полях связанных таблиц. Создание связи между таблицами
1 а В среде Microsoft Access 2003: вменю Сервис выбрать команду Схема данных б в среде Microsoft Access 2007: в панели инструментов на вкладке Работа с базами данных в группе Показать или скрыть выбрать пункт Схема данных.
2 а В среде Microsoft Access 2003: если ни одной связи еще не определено, откроется диалоговое окно Добавить таблицу. Если окно не открылось, вменю Связи выбрать команду Отобразить таблицу б в среде Microsoft Access 2007: если ни одной связи еще не определено, автоматически откроется диалоговое окно Добавить таблицу. Если окно не открылось, в панели инструментов на вкладке Конструктор в группе Связи нажать кнопку Отобразить таблицу.
3 Выбрать одну или несколько таблиц и нажать кнопку Добавить. После добавления всех таблиц нажать кнопку Закрыть.
4 Перетащите поле (как правило, поле первичного ключа) из одной таблицы на общее поле (поле внешнего ключа) в другой таблице. Откроется диалоговое окно Изменение связей (рисунок 4), в котором показаны названия связанных таблиц и имена полей, участвующих в связи, а также приведены элементы управления для обеспечения условий целостности данных. Рисунок 4 − Создание связи между таблицами базы данных
5 Для поддержания целостности данных для создаваемой связи установить флажок Обеспечение целостности данных рисунок 4).
6 Нажать кнопку Создать. Access проведет линию связи между двумя таблицами с указанием ее типа. При этом одна из таблиц считается главной, а другая связанной. Главная таблица участвует в связи своим ключевым полем название этого поляна схеме данных отображается жирным шрифтом).
Замечание. Если установлен только флажок Обеспечение целостности данных, то удалять данные из ключевого поля главной таблицы нельзя. Если вместе с ним включены флажки Каскадное обновление связанных полей и Каскадное удаление связанных записей, то операции редактирования и удаления данных в ключевом поле главной таблицы разрешены, но сопровождаются автоматическими изменениями в связанной таблице. Чтобы изменить связь между таблицами, следует
1) в окне Схема данных установить указатель на линии связи и щелкнуть линию, чтобы выделить ее (при выделении линия связи становится толще
2) дважды щелкнуть выделенную линию связи
3) внести необходимые изменения в открывшемся окне Изменение связей.
2.4 Использование списков значений Для удобства ввода данных в поле таблицы в Microsoft Access предусмотрена возможность создания списков подстановок, из которых можно выбирать значения для ввода в данное поле. Списки подстановок могут быть двух типов
•
список подстановок, в котором выводятся значения из существующей таблицы или запроса
•
список постоянных значений, которые вводятся при создании поля. Когда таблица открыта в режиме таблицы, поле со списком ничем не отличается от обычного текстового поля. Но когда курсор попадает в это поле, справа появляется кнопка со стрелкой. Если нажать эту кнопку, откроется список, из которого выбирается нужное значение. Создание поля подстановок, отображающего значения из таблицы
1 Открыть в режиме конструктора таблицу, в которую нужно добавить поле с подстановкой значений.
2 Выбрать строку поля, которое будет использоваться как внешний ключ для поля подстановок.
3 В столбце Тип данных выбрать Мастер подстановок.
4 В окне мастера подстановок выбрать переключатель для создания столбца подстановок, использующего значения из таблицы. Нажать кнопку Далее.
5 Выбрать таблицу со значениями, которые будет содержать столбец подстановки. Нажать кнопку Далее.
6 Выбрать поля со значениями, которые следует включить в столбец подстановки. Нажать кнопку Далее.
7 При необходимости выбрать порядок сортировки списка. Нажать кнопку Далее.
8 Задать ширину столбца подстановки. Нажать кнопку Далее.
9 Задать подпись для столбца подстановки. Нажать кнопку Готово.
Создание поля со списком фиксированных значений
1 Открыть в режиме конструктора таблицу, в которую нужно добавить поле с подстановкой значений.
15
2 Выбрать строку поля, которое будет использоваться как внешний ключ для поля подстановок.
3 В столбце Тип данных выбрать Мастер подстановок.
4 В окне мастера подстановок выбрать переключатель, определяющий ввод фиксированных значений. Нажать кнопку Далее.
5 Ввести значения, которые будет содержать столбец подстановки. Нажать кнопку Далее.
6 Задать подпись для столбца подстановки. Нажать кнопку Готово.
2.5 Практическая работа № 1
1 Создайте в личной папке базу данных Деканат.
2 Создайте таблицы базы данных Деканат согласно таблицам 1-4.
3 Создайте связи между таблицами, установив для каждой связи опции Обеспечение целостности данных, Каскадное обновление связанных полей и Каскадное удаление связанных записей.
4 Введите в таблицу Студенты данные согласно рисунку 5. Рисунок 5 − Содержимое таблицы Студенты
5 Введите в таблицу Дисциплины данные согласно рисунку 6. Рисунок 6 − Содержимое таблицы Дисциплины
6 Введите в таблицу Преподаватели данные согласно рисунку 7.
16 Рисунок 7 − Содержимое таблицы Преподаватели
7 Введите в таблицу Оценки данные таким образом, чтобы у каждого студента были оценки по каждой дисциплине.
3 Фильтрация данных Для просмотра и корректировки записей БД, удовлетворяющих указанным пользователем условиям отбора, можно использовать фильтрацию таблицы. Фильтр – это набор условий, применяемый для отбора подмножества записей из таблицы, формы или запроса. Можно рассматривать фильтр как условие или правило, задаваемое для поля. Условие определяет, какие значения поля требуется отобразить. После применения фильтра отображаются только теза- писи, которые содержат указанные значения. Остальные записи будут скрыты до тех пор, пока фильтр не будет удален. Простейшим способом задания условия отбора записей является выделение в таблице или форме некоторого значения поля или его части. Применение фильтра по выделенному
1 Открыть таблицу в режиме таблицы.
2 В поле, по которому надо сделать фильтр, выделить
- все содержимое поля для поиска записей, в которых все содержимое поля совпадает с выбранным значением
- часть значения, начиная с первого знака в поле, для поиска записей, поля которых содержат значения, начинающиеся с выделенных знаков
- часть значения, начиная нес первого знака в поле, для поиска записей, в которых все значение данного поля или его часть содержат выбранные знаки.
3 а В среде Microsoft Access 2003: выбрать команду Записи / Фильтр / Фильтр по выделенному или на панели инструментов нажать кнопку Фильтр по выделенному, либо после выделения нажать правую кнопку мыши ив контекстном меню выбрать команду Фильтр по выделенному. Для отбора записей, поля которых не содержат выделенного значения, необходимо после выделения значения выбрать команду Записи / Фильтр / Исключить выделенное б в среде Microsoft Access 2007: в панели инструментов на вкладке Главная в группе Сортировка и фильтр нажать кнопку Выделение и выбрать нужную команду из списка (зависит от выделенной части значения, либо после выделения нажать правую кнопку мыши ив контекстном меню выбрать нужное условие для фильтра.
17 Если необходимо работать со всеми записями таблицы, то действие фильтра можно отменить. Удаление фильтра
в среде Microsoft Access 2003: выбрать команду Записи / Удалить фильтр
или на панели инструментов нажать кнопку Удалить фильтр
в среде Microsoft Access 2007: в панели инструментов на панели инструментов на вкладке Главная в группе Сортировка и фильтр отжать кнопку Применить фильтр. Кроме фильтра по выделенному можно использовать и другие обычный фильтр по форме) и расширенный. Применение обычного фильтра (фильтра по форме)
1 Открыть таблицу в режиме таблицы а В среде Microsoft Access 2003: выбрать команду Записи / Фильтр / Изменить фильтрили на панели инструментов нажать кнопку Изменить фильтр б в среде Microsoft Access 2007: в панели инструментов на вкладке Главная в группе Сортировка и фильтр нажать кнопку Дополнительно, а затем выбрать команду Изменить фильтр.
3 Ввести выражение в соответствующее поле или воспользоваться для его создания построителем выражений. Если задать значения в нескольких полях, фильтр будет возвращать только те записи, в которых каждое из этих полей содержит указанное значение.
4 Чтобы задать несколько значений, которые могут содержать результирующие записи, щелкнуть вкладку Или в левом нижнем углу окна таблицы и ввести дополнительные условия. Фильтр будет возвращать записи, соответствующие поля которых содержат все значения, указанные на вкладке Найти, или все значения, указанные на первой вкладке Или, или все значения, указанные на второй вкладке Или и т.д.
5 а В среде Microsoft Access 2003: на панели инструментов нажать кнопку Применить фильтр б в среде Microsoft Access 2007: в панели инструментов на вкладке Главная в группе Сортировка и фильтр нажать кнопку Применить фильтр. На рисунке 8 фильтрация осуществляется по значению М в поле Номер группы, а также по значению Истина в поле Стипендия. Значения могут вводиться с клавиатуры или выбираться из списка, как это показано для поля Номер группы. Рисунок 8 − Структура обычного фильтра для таблицы Студенты
18 Применение расширенного фильтра
1 Открыть таблицу в режиме таблицы.
2 а В среде Microsoft Access 2003: выбрать команду Записи / Фильтр / Расширенный фильтрили на панели инструментов нажать кнопку Изменить фильтр б в среде Microsoft Access 2007: в панели инструментов на вкладке Главная в группе Сортировка и фильтр нажать кнопку Дополнительно, а затем выбрать команду Расширенный фильтр.
3 Добавить в бланк поля, для которых будут заданы искомые значения или другие условия, используемые в фильтре для отбора записей.
4 Для указания порядка сортировки выбрать ячейку «Сортировка»нужного поля, щелкнуть стрелку и выбрать порядок сортировки. В Access записи сортируются сначала по самому левому полю, затем по полю, расположенному в следующем столбце справа и т.д.
5 В ячейке Условие отбора»полей, включенных в бланк, указать искомое значение или ввести выражение. Для указания альтернативных условий для отдельного поля ввести первое условие в строке Условия отбора, второе условие в строке или и т.д.
6 а В среде Microsoft Access 2003: на панели инструментов нажать кнопку Применить фильтр б в среде Microsoft Access 2007: в панели инструментов на вкладке Главная в группе Сортировка и фильтр нажать кнопку Применить фильтр. Пример использования расширенного фильтра приведен на рисунке 9. Рисунок 9 − Структура расширенного фильтра для таблицы Оценки Для сохранения подготовленного фильтра нужно щелкнуть правой кнопкой мыши по окну со структурой фильтра и выбрать в контекстном меню команду Сохранить как запрос. В открывшемся окне Сохранение в виде запроса необходимо ввести имя сохраняемого запроса. В дальнейшем, вместо того, чтобы создавать фильтр заново, можно использовать существующий фильтр. Для этого в режиме Изменить фильтр воспользоваться командой контекстного меню Загрузить из запроса.
19
3.1 Использование подстановочных знаков и выражений для поиска значений
Подстановочные знаки используются в качестве прототипов для других знаков при указании образца поискав запросах и выражениях для поиска значений полей в случаях, когда известна только часть значения или требуется найти значения, начинающиеся с конкретной буквы или соответствующие определенному шаблону.
Подстановочные знаки можно использовать с текстовыми типами данных, хотя возможно их использование и с другими типами данных, например с датами. В базах данных Microsoft Access для поиска значений могут использоваться следующие подстановочные знаки
•
* − соответствует любому количеству знаков. Например, wh* − поиск слови и т.п.
•
? - соответствует любому текстовому знаку. Например, B?ll − поиск слови и т.п.
•
[ ] − соответствует любому одному знаку из заключенных в скобки. Например, B[ae]ll − поиск слови, ноне и др.
•
! - соответствует любому одному знаку, кроме заключенных в скобки. Например, b[!ae]ll − поиск слови и других, ноне или ball.
•
– − соответствует любому знаку из диапазона (диапазон нужно указывать по возрастанию, например, от A до Z, ноне от Z до A). Например, b[a-c]d
− поиск слови соответствует любой цифре. Например, 1#3 − поиск значений 103, 113,
123 и т.п. Выражение в Microsoft Access − это сочетание математических и логических операторов, констант, функций, имен полей, элементов управления и свойств, в результате обработки которого получается единственное значение. Выражения могут выполнять вычисления, обрабатывать текст или проверять данные и используются при выполнении многих операций Microsoft Access, в том числе, при создании вычисляемых элементов управления, определении условий в запросах и фильтрах, значений по умолчанию, условий назначение и условий в макросах. Примеры условий отбора для извлечении записей из базы данных
1 Использование диапазона значений (операторы >, <, >=, <=, <> и
«Between...And»):
•
>234 − отбор записей, значение поля Количество которых больше 234;
•
Иванов" − отбор записей, содержащих фамилии, начиная с Иванов и до конца алфавита, в поле Фамилия
•
Between #02-фев-1999# And дек − отбор записей, содержащих в поле «ДатаРождения» даты в диапазоне от 2 февраля 1999 года до 1 декабря
1999 года.
20
2 Использование оператора «Not» (несовпадающие значения
•
Not "США" − отбор записей со сведениями о заказах на доставку, содержащих в поле Страна получателя любую страну за исключением США
•
Not 2 − отбор всех студентов, код которых, указанный в поле идентификации, неравен отбор в поле Имя студентов, имена которых не начинаются с буквы T.
3 Использование оператора «In» (значения в списке
•
In ("Канада","Великобритания") − отбор заказов на доставку, содержащих в поле Страна получателя значение Канада или США.
4 Использование текста, части текста или совпадающих значений
•
"Киров" − отбор заказов на поставку, содержащих в поле Город получателя значение Киров
•
"Киров" Or "Минск" − отбор заказов на поставку, содержащих в поле Город получателя значение Киров или Минск
•
Н" − отбор заказов на доставку товаров в фирмы, названия которых в поле Имя начинаются с букв, находящихся в диапазоне от Н до Я.
5 Использование оператора «Like» (часть значения поля
•
Like "С" − отбор информации о студентах, имена которых в поле Имя начинаются с буквы С
•
Like "Импорт" − отбор заказов на доставку товаров клиентам, названия которых в поле «НазваниеПолучателя» заканчиваются словом Импорт
•
Like "АД" − отбор заказов на доставку товаров клиентам, имена которых, указанные в поле «НазваниеПолучателя», находятся в диапазоне от А до Д
•
Like "*тр*" − отбор заказов на доставку товаров клиентам, названия которых в поле «НазваниеПолучателя» содержат сочетание букв «тр».
1 2 3 4 5 6 7
6 Даты
•
#2/2/2000# − выполненные заказы, в поле «ДатаИсполнения» которых указана дата 2 февраля 2000 года
•
Date( ) − заказы, в поле «ДатаНазначения» которых указана текущая дата
•
Between Date( ) And DateAdd("m",3,Date( )) − заказы, у которых значение в поле «ДатаНазначения» попадает в интервал между текущей датой и датой, отстоящей от нее натри месяца
•
Year ( [ДатаРазмещения] )=1999 − отбор по полю Дата заказа заказов, сделанных в 1999 году
•
DatePart ("q", [ДатаРазмещения] )=4 − отбор по полю Дата заказа заказов четвертого квартала
•
Year ([ДатаРазмещения])=Year (Now( )) And Month([ДатаРазмещения])=
Month(Now( ))
− отбор по полю «ДатаРазмещения» заказов текущего года и месяца Пустое значение поля (Null или строка нулевой длины
•
Is Null − отбор по полю «ОбластьПолучателя» заказов для клиентов, у которых поле «ОбластьПолучателя» содержит значение Null, те. является пустым отбор по полю «ОбластьПолучателя» заказов для клиентов, у которых поле «ОбластьПолучателя» содержит определенное значение
•
"" − отбор по полю Факс заказов для клиентов, у которых нет факсимильного аппарата, то есть для тех клиентов, у которых поле Факс содержит строку нулевой длины, а не значение «Null».
•
3.2 Практическая работа № 2
1 Отфильтровать таблицу Преподаватели по полям Должность и Кафедра с помощью фильтра по выделенному для отбора записей о преподавателях доцентах кафедры физики. Сохранить фильтр как запрос.
2 Отфильтровать таблицу Оценки по полям Код студента и Оценка с помощью обычного фильтра для отбора записей об оценках «4» и «5» студентов с кодами 1 и 2. Сохранить фильтр как запрос.
3 Отфильтровать таблицу Оценки по полям Код дисциплины и Оценка с помощью расширенного фильтра для отбора записей об оценках выше «3» по дисциплине с кодом 1 или записей об оценках ниже «4» по дисциплине с кодом. Сохранить фильтр как запрос.
4 Создать и сохранить как запрос фильтр, выбирающий из таблицы Студенты записи а) о девушках из групп Ми М б) о юношах по имени Сергей и девушках по имени Ольга во студентах с фамилией, начинающейся на букву Кг) о студентах группы М, родившихся, например, в 1996 году доданном студенте (фамилия студента − параметр фильтра.
5 Создать и сохранить как запрос фильтр, выбирающий из таблицы Преподаватели записи а) о сотрудниках кафедры информатики б) о преподавателях, имеющих телефон во преподавателях, фамилия которых начинается на данную букву (буква − параметр фильтра го преподавателях, родившихся в данном году (год − параметр фильтра.
3.3 Практическая работа № 3
1 Изменить структуру таблиц базы данных Деканат следующим образом а) для таблицы Преподаватели
● переименовать поле Телефон в Домашний телефон
● добавить новые поля и заполнить их осмысленными данными
- Адрес − тип данных Текстовый, размер 30;
22
- Рабочий телефон − тип данных Текстовый, размер 10, маска ввода
- Оклад − тип данных Денежный
● заполнить новые поля осмысленными данными, при этом учесть, что не все преподаватели имеют домашний/рабочий телефон б) для таблицы Студенты
● переименовать поле Номер группы в Группа
● удалить из таблицы поля Телефон и Стипендия
● добавить новое поле Курс (тип данных Числовой, размер Байт) и заполнить его осмысленными данными
● добавить в таблицу не менее 10 новых записей о студентах с других курсов и групп. Часть студентов заселить в общежитие в уже занятые комнаты чтобы в каждой комнате проживало не менее 2 человек в) для таблицы Оценки
● добавить новое поле Дата сдачи (тип данных Дата/время, краткий формат даты, маска ввода) и заполнить его осмысленными данными
● добавить в таблицу данные об успеваемости новых студентов по всем дисциплинам.
2 Добавить в базу данных Деканат новые таблицы а) таблицу Стипендия Имя поля Тип данных Размер поля Ключевое Код студента Числовой Целое Да Сентябрь Денежный Октябрь Денежный Ноябрь Денежный Декабрь Денежный Январь Денежный б) таблицу Общежитие Имя поля Тип данных Размер поля Ключевое Код студента Числовой Целое Да Общежитие Текстовый
1 Список (1, 2, 3, 4) Комната Текстовый
4 3 Заполнить новые таблицы осмысленными данными, при этом учитывать, что не все студенты получают стипендию и не все студенты проживают в общежитии, некоторые студенты должны проживать водной комнате.
4 Скорректировать схему данных, добавив связи с новыми таблицами.
4 Работа с запросами Запросы используются для просмотра, анализа и изменения данных водной или нескольких таблицах. Запросы могут служить источником данных для форм и отчетов. Особенность запросов состоит в том, что они извлекают данные из базовых таблиц и создают на их основе временную результирующую таблицу. При сохранении запроса сохраняется не результирующая таблица, а только алгоритм ее получения. Поэтому при следующем выполнении запроса
23 выдается другая результирующая таблица, отражающая новое состояние базы данных. В Access существует несколько видов запросов запросы на выборку, запросы с параметрами, перекрестные запросы, запросы на изменение и запросы
SQL.
4.1 Запросы на выборку Одним из наиболее часто применяемых типов запросов являются запросы на выборку. Запросы этого типа возвращают данные из одной или нескольких таблиц и отображают их в виде таблицы, записи в которой можно обновлять (с некоторыми ограничениями. Запросы на выборку можно также использовать для группировки записей и вычисления сумм, средних значений, подсчета записей и нахождения других типов итоговых значений. Запросы на выборку похожи на фильтры ив тех ив других производится извлечение подмножества записей из базовой таблицы или запроса. Однако между ними существуют различия, которые нужно понимать, чтобы правильно сделать выбор, в каком случае использовать запроса в каком − фильтр. Основные отличия запросов и фильтров заключаются в следующем
•
фильтры не позволяют водной строке отображать данные из нескольких таблиц, те. объединять таблицы
•
фильтры всегда отображают все поля базовой таблицы
•
фильтры не могут быть сохранены как отдельный объект в окне базы данных (они сохраняются только в виде запроса
•
фильтры не позволяют вычислять суммы, средние значения, подсчитывать количество записей и находить другие итоговые значения
•
запросы могут использоваться только с закрытой таблицей или запросом. Фильтры обычно применяются при работе в режиме формы или в режиме таблицы для просмотра или изменения подмножества записей. Запрос на выборку можно использовать
•
для просмотра подмножества записей таблицы без предварительного открытия этой таблицы
•
для объединения данных из нескольких таблиц в виде одной таблицы
•
для просмотра отдельных полей таблицы
•
для выполнения вычислений над значениями полей. Простые запросы на выборку можно создавать при использовании мастера и при работе в режиме конструктора. Мастер простого запроса на выборку создает запросы для получения данных из полей, выбранных водной или нескольких таблицах или запросах. С помощью мастера можно также вычислять суммы, число записей и средние значения для всех записей или определенных групп записей, а также находить максимальное и минимальное значение в поле. Создание простого запроса на выборку с помощью мастера запросов
1 а В среде Microsoft Access 2003: в окне базы данных нажать кнопку Запросы на панели Объекты, а затем нажать кнопку Создать на панели инструментов окна базы данных. В диалоговом окне Новый запрос выбрать в списке строку Простой запроси нажать кнопку OK; б в среде Microsoft Access 2007: в панели инструментов на вкладке Создание в группе Другие щелкнуть Мастер запросов. В диалоговом окне Новый запрос выбрать вариант Простой запроси нажать кнопку ОК.
2 В группе Таблицы и запросы выбрать таблицу, содержащую нужные данные. Обратите внимание на то, что в качестве источника данных можно использовать другой запрос.
3 В группе Доступные поля дважды щелкнуть нужные поля, чтобы добавить их в список Выбранные поля (или воспользоваться кнопками > и >> ).
4 Для добавления в структуру запроса полей из другой таблицы или запроса повторить шаги 2 и 3. После добавления всех полей нажать кнопку Далее.
5 Выбрать тип отчета подробный. Нажать кнопку Далее.
6 Присвоить запросу имя, а затем нажать кнопку Готово. В приложении Access результат выполнения запроса отображается в режиме таблицы. Создание простого запроса на выборку с помощью конструктора
1 а В среде Microsoft Access 2003: в окне базы данных нажать кнопку Запросы на панели Объекты, а затем нажать кнопку Создать на панели инструментов окна базы данных. В диалоговом окне Новый запрос выбрать в списке строку Конструктор и нажать кнопку OK; б в среде Microsoft Access 2007: в панели инструментов на вкладке Создание в группе Другие щелкнуть Конструктор запросов.
2 В диалоговом окне Добавление таблицы выбрать вкладку, содержащую объекты, данные из которых будут использованы в запросе.
3 Дважды щелкнуть объекты, которые нужно добавить в запроса затем нажать кнопку Закрыть.
4 Добавить поля в строку Поле в бланке запроса.
5 Если необходимо, в строке Условие отбора указать условия отбора значений полей, которые требуется включить в запрос. При необходимости указать альтернативные условия отбора в строке или.
6 Если необходимо, указать порядок сортировки в строке Сортировка.
7 а В среде Microsoft Access 2003: чтобы просмотреть результаты запроса, нажать кнопку Запускна панели инструментов или выбрать команду Запрос / Запуск, или переключиться в режим таблицы с помощью кнопки Видна панели инструментов б в среде Microsoft Access 2007: чтобы просмотреть результаты запроса, в панели инструментов на вкладке Конструктор в группе Результаты нажать кнопку Выполнить или переключиться в режим таблицы с помощью кнопки Режим в группе Результаты вкладки Конструктор. Замечание. Режим конструктора запросов используется не только для создания новых запросов, но и для изменения уже существующих запросов. Возможные действия по изменению структуры запроса
•
чтобы удалить базовую таблицу из запроса, необходимо выделить ее, щелкнув на любом месте в списке ее полей, и нажать клавишу
25
•
чтобы удалить поле из запроса, выделите нужный столбец в бланке запроса, а затем нажмите клавишу
•
если требуется изменить порядок следования полей, сначала выделите нужный столбец или несколько столбцов, а затем перетащите их мышью на новое место. При этом отпускать кнопку мыши нужно тогда, когда указатель мыши окажется перед тем столбцом, который должен быть справа от вставляемого столбца
•
чтобы переименовать поле, необходимо установить курсор в бланке запроса перед первой буквой имени поля и ввести новое имя и символ двоеточия. В базовой таблице при этом имя поляне изменяется, атак жене изменяется в формах и отчетах, которые были созданы на основе запроса до изменения имени поля. Замечание. При создании запросов можно указать условия отбора записей в строке Условие отбора»и в строке Или. Такими условиями могут быть логические выражения, например, (>30), (Иванов, (=10) и т.п. При этом
•
условия, находящиеся водной строке, нов разных столбцах бланка запроса, объединяются по логическому оператору And (И
•
условия, находящиеся в разных строках бланка запроса, объединяются по логическому оператору Or (ИЛИ.
•
4.2 Вычисления в запросах на выборку Результирующие таблицы запросов на выборку могут включать не только поля базовых таблиц, но и новые поля, значения в которых получаются как результат вычислений с использованием данных из базовых таблиц. Поле, содержимое которого получается как результат расчета по содержимому других полей, называется вычисляемым полем. Вычисляемые поля существуют только в результирующих таблицах запросов. В исходных таблицах такое поле создать нельзя. Для создания такого поля в строке «Поле»свободного столбца бланка запроса вместо имени поля записывается формула в следующем формате Имя поля выражение где выражение – формула для вычисления нужного значения. Выражение может содержать функции, имена полей, знаки действий, константы. При этом имена полей заключаются в квадратные скобки. Действия могут быть
•
арифметические + (сложение, − (вычитание, * (умножение, / (деление,
^ (возведение в степень
•
логические логическое умножение, Or (логическое сложение, логическое отрицание
26
•
сравнения > (больше, >= (больше либо равно, < (меньше, <= (меньше либо равно, = (равно, <> неравно соединения текстовых последовательностей
& Фамилия Имя ). Удобно пользоваться для ввода формул построителем выражений. Для этого нужно щелкнуть правой кнопкой в поле ввода формулы и выбрать в контекстном меню команду Построить или нажать кнопку Построить
на панели инструментов. После этого строится выражение формулы, при этом почти все составляющие элементы формулы (имена полей таблиц, знаки математических операций и т.д.) выбираются с помощью мыши в диалоговом окне по- строителя выражений. Это позволяет свести к минимуму ошибки ввода. На рисунке 10 показан пример запроса с вычисляемым полем Налог. При конструировании запроса в заголовке поля записывается формула
Налог:[Оклад]*0,13. В результирующей таблице появится поле с именем Налог, значения в котором будут получены как результат умножения соответствующих значений из поля Оклад на число 0,13. Чтобы вводить формулу было удобнее, можно нажать на клавиатуре комбинацию клавиш
27
•
Date ( ) − возвращает текущую дату
•
Month ( поле формата Дата/Время] ) − возвращает номер месяца из указанной даты
•
Year ( поле формата Дата/Время] ) − возвращает номер года из указанной даты
•
Day ( поле формата Дата/Время] ) − возвращает номер дня из указанной даты
•
DatePart (параметр поле формата Дата/Время] ) − возвращает указанную параметром часть значения столбца даты. Возможные значения параметра
«dd»
− день, «mm» – месяц, «w» – неделя, «q» – квартал или «yyyy» − год
•
Format ( поле формата Дата/Время]; параметр) – возвращает в виде текста указанную часть значения столбца даты. Формат отображения задается параметром номер дня «m», «mm» – номер месяца «mmm» – сокращенное название месяца («янв», «фев», «мар»); «mmmm» – полное название месяца «yy» – номер года из х цифр «yyyy» – номер года из х цифр «q» – номер квартала «ww» – номер недели
•
DateDiff (параметр НачальнаяДата; КонечнаяДата) − рассчитывает интервал между двумя датами. Вид результата задается параметром (значения параметра как для Format);
•
DateAdd (параметр интервал ИсходнаяДата) − возвращает дату, полученную как сумму исходной даты и интервала (целое число со знаком, добавленного к заданному параметру даты. Возможные значения параметра «d» − день, «m» – месяц, «yyyy» – год. Примеры выражений с датами
•
Between Date( ) And DateAdd ("m"; 3; Date ( )) − отбор записей, для которых значение в поле Дата попадает в интервал между текущей датой и датой, отстоящей от нее натри месяца
•
Year ( [ДатаПродажи] ) = Year (Now( )) And Month ( [ДатаПродажи]) =
Month (Now( ))
− отбор записей за текущий месяц
•
Year ( Дата рождения ) = 1992 − отбор по полю Дата рождения студентов, родившихся в 1992 году
•
DatePart ("q"; [ДатаРазмещения] ) = 4 − отбор по полю Дата заказа заказов четвертого квартала
•
DatePart ("m"; [ДатаРазмещения] ) − отображение номера месяца для указанной даты
•
DatePart ("yyyy"; [ДатаНайма] ) − отображение года найма каждого сотрудника Дата заказа Date( )) < 5 − отбор по полю Дата заказа заказов за последние 4 года
•
DateDiff ("yy"; [ДатаРождения]; Now( )) − вычисление возраста на основе даты рождения относительно текущей даты
28
•
DateDiff ("d"; [ДатаРазмещения]; [ДатаОтправки] ) − отображение количества дней между датами в полях «ДатаРазмещения» и «ДатаОтправки».
4.3 Итоговые запросы Итоговый запрос – это запрос, в котором выводятся результаты статистических расчетов по какой-либо группе записей из одной или нескольких таблиц. Можно находить сумму (функция Sum), среднее значение (функция Avg), наибольшее значение (функция Max) или наименьшее значение (функция Min), количество знаний в группе (функция Count). Процедура создания итогового запроса похожа на процедуру создания запроса на выборку. При выполнении такого запроса требуется группировать записи по совпадающим значениям в каком-либо поле таблицы. Для выполнения группировки записей нужно щелкнуть по кнопке Групповые операции на панели инструментов. В бланке запроса по образцу появляется дополнительная строка Групповая операция. В тех полях, по которым проводится группировка, надо установить в строке Групповая операция значение Группировка. В тех полях, где проводится итоговые операции, нужно в строке Групповая операция раскрыть список и выбрать одну из функций (Sum, Avg, Max, Min,
Count и т.д.) Например, можно создать запрос для определения средней, наибольшей и наименьшей оценки для каждого студента, как показано на рисунке 11. Рисунок 11 − Результат выполнения итогового запроса В этом случае следует задать группировку пополам Фамилия, Имя и Номер группы и выбрать соответствующие функции в поле Оценка, включив это поле в бланк запроса трижды (рисунок 12).
29 Рисунок 12 − Создание итогового запроса
1 2 3 4 5 6 7
4.4 Перекрестные запросы Перекрестные запросы используют для расчетов и представления данных в структуре, облегчающей их анализ. Перекрестный запрос подсчитывает сумму, среднее, число значений или выполняет другие статистические расчеты, после чего результаты группируются в виде таблицы по двум наборам данных, один из которых определяет заголовки столбцов, а другой заголовки строк. Для расчета итоговых вычислений используют стандартные итоговые функции, например
•
Sum − сумма значений некоторого поля для группы
•
Avg − среднее от всех значений поля в группе
•
Max, Min − максимальное, минимальное значение поля в группе
•
Count − число значений поля в группе без учета пустых значений. Создание перекрестного запроса с помощью мастера
1 а В среде Microsoft Access 2003: в окне базы данных нажать кнопку Запросы на панели Объекты, а затем нажать кнопку Создать на панели инструментов окна базы данных. В диалоговом окне Новый запрос выбрать в списке строку Перекрестный запроси нажать кнопку OK; б в среде Microsoft Access 2007: в панели инструментов на вкладке Создание в группе Другие щелкнуть Мастер запросов. В диалоговом окне Новый запрос выбрать вариант Перекрестный запроси нажать кнопку
ОК.
2 Выбрать таблицу или запрос, поля которых будут использоваться в перекрестном запросе. Нажать кнопку Далее.
3 Выбрать поля, значения которых будут использованы в качестве заголовков строк. Нажать кнопку Далее.
4 Выбрать поля, значения которых будут использованы в качестве заголовков столбцов. Нажать кнопку Далее.
5 Выбрать поле, значения которого будут выводиться на пересечении строки столбцов. Выбрать среди приведенных функций вид вычисления.
30
6 Если не требуется вычислять и выводить итоговое значение для каждой строки запроса, то следует снять флажок с соответствующей опции. Нажать кнопку Далее.
7 Ввести имя для запроса и нажать кнопку Готово. Если получился не тот запрос, который был нужен, можно снова создать запрос с помощью мастера или изменить этот запрос в режиме конструктора. Создание перекрестного запроса с помощью конструктора запросов
1 а В среде Microsoft Access 2003: в окне базы данных нажать кнопку Запросы на панели Объекты, а затем нажать кнопку Создать на панели инструментов окна базы данных. В диалоговом окне Новый запрос выбрать в списке строку Конструктор и нажать кнопку OK; б в среде Microsoft Access 2007: в панели инструментов на вкладке Создание в группе Другие щелкнуть Конструктор запросов.
2 В окне Добавление таблицы выбрать вкладку, содержащую объекты, данные из которых будут использованы в запросе.
3 Дважды щелкнуть объекты, которые нужно добавить в запроса затем нажать кнопку Закрыть.
4 Добить поля в строку Поле в бланке запроса и задать условия отбора.
5 а В среде Microsoft Access 2003: на панели инструментов нажать кнопку Тип запроса и выбрать Перекрестный б в среде Microsoft Access 2007: в панели инструментов на вкладке Конструктор в группе Тип запроса щелкнуть Перекрестный.
6 Для поля или полей, значения которых должны быть представлены в виде заголовков строк, щелкнуть ячейку строки Перекрестная таблица и выбрать значение Заголовки строк. Для таких полей нужно оставить в строке Групповая операция значение Группировка, установленное по умолчанию. Значение Заголовки столбцов можно задать только для одного поля.
7 Для поля, значения которого нужно использовать при создании перекрестной таблицы, щелкнуть ячейку строки Перекрестная таблица и выбрать пункт Значение. Пункт Значение можно выбрать только для одного поля.
8 В строке Групповая операция выбрать статистическую функцию, которая будет использована для заполнения перекрестной таблицы (например Sum,
Avg или Count).
9 Выполните одно из следующих действий а) для задания условия отбора, ограничивающего отбираемые заголовки строк до выполнения вычисления ввести выражение в строку Условие отбора поля, для которого в ячейке строки Перекрестная таблица выбрано значение Заголовки строк. Например, можно вывести итоговые суммы продаж для некоторых категорий товаров, таких как мясные или рыбные продукты. б) для задания условия отбора, ограничивающего отбираемые записи до группировки заголовков строки до заполнения перекрестной таблицы
•
добавить поле, для которого необходимо установить условие отбора, в бланк запроса
•
выбрать значение Условие в ячейке строки Групповая операция
31
•
оставить ячейку в строке Перекрестная таблица пустой. Ввести выражение в ячейку строки Условие отбора. Поля, имеющие значение Условие в строке Групповая операция, не выводятся в результатах запроса а В среде Microsoft Access 2003: чтобы просмотреть результаты запроса, нажать кнопку Запускна панели инструментов или выбрать команду Запрос Запуск, или переключиться в режим таблицы с помощью кнопки Видна панели инструментов б в среде Microsoft Access 2007: чтобы просмотреть результаты запроса, в панели инструментов на вкладке Конструктор в группе Результаты нажать кнопку Выполнить или переключиться в режим таблицы с помощью кнопки Режим в группе Результаты вкладки Конструктор. Рассмотрим пример создания перекрестного запроса к базе данных Деканат с помощью конструктора. Пусть нужно получить средние оценки по изучаемым дисциплинам для каждой из групп. Структура перекрестного запроса в режиме конструктора представлена на рисунке 13. Рисунок 13 – Структура перекрестного запроса Средние оценки по группам Результат выполнения перекрестного запроса представлен на рисунке 14. Рисунок 14 – Результат выполнения перекрестного запроса Если необходимо при вычислении среднего нужно учитывать только положительные оценки («3», «4» и «5»), тов структуру запроса нужно добавить условие отбора (рисунки 15 и 16).
32 Рисунок 15 – Измененная структура перекрестного запроса Рисунок 16 – Результат измененного перекрестного запроса
4.5 Запросы с параметрами Запрос с параметрами − это запрос, при выполнении отображающий в собственном диалоговом окне приглашение ввести данные, например условие для возвращения записей или значение, которое требуется вставить в поле. Запросы с параметрами удобно использовать в качестве основы для форм и отчетов. Например, на основе запроса с параметрами можно создать месячный отчет о доходах. При печати данного отчета Microsoft Access выводит на экран приглашение ввести месяц, доходы за который должны быть приведены в отчете. После ввода месяца Microsoft Access выполняет печать соответствующего отчета. Создание запроса с параметрами
1 Создать запрос на выборку или перекрестный запроси открыть его в режиме конструктора.
2 Для каждого поля, которое предполагается использовать как параметр, ввести в ячейку строки Условие отбора выражение с текстом приглашения, заключенным в квадратные скобки. Например, для поля, в котором отображаются фамилии студентов, ввести выражение Введите фамилию студента.
3 Для просмотра результатов перейти в режим таблицы и ввести значения параметров. Если качестве параметров отбора нужно в указать верхнюю и нижнюю границы какого-либо значения, то следует ввести условие отбора в виде
33
BETWEEN Введите нижнюю границу AND Введите верхнюю границу Например, для получения списка студентов, родившихся в определенный год, для поля, в котором отображаются даты рождения, можно ввести приглашения Введите начальную дату и Введите конечную дату, чтобы задать границы диапазона значений
Between Введите начальную дату And Введите конечную дату Структура запроса и результат его работы представлены на рисунках 17 и
18. Рисунок 17 – Структура параметрического запроса Список студентов Рисунок 18 – Результат выполнения параметрического запроса Чтобы запросить у пользователя один или несколько знаков для поиска записей, которые начинаются с этих знаков или содержат их, создается запрос с параметрами, использующий оператор LIKE и подстановочный знак «*» (пример запроса приведен на рисунке 19).
34 Рисунок 19 – Структура и результат выполнения параметрического запроса Например, следующее выражение выполняет поиск слов, начинающихся с указанной буквы LIKE Введите первый символ для поиска & "*"
4.6 Практическая работа № 4
1 На основе таблицы Преподаватели создать с помощью мастера простой запрос на выборку, в котором должны отображаться фамилии, имена, отчества преподавателей и их должность. Данные запроса отсортировать по должностям по убыванию. Сохранить запрос под именем Должности преподавателей.
2 Создать с помощью конструктора запрос Оценки студентов, в котором выводятся все оценки студентов по всем дисциплинам. Запрос должен содержать поля из таблицы Студенты поля Фамилия, Имя и Группа, из таблицы Дисциплины – поле Название дисциплины, из таблицы Оценки
– поле Оценка.
3 Изменить запрос Оценки студентов так, чтобы выводились оценки студентов группы М по дисциплине Физика. Сохранить запрос под именем Оценки студентов − физика.
4 Изменить запрос Оценки студентов так, чтобы выводились оценки «4» по дисциплине Физика или оценки «5» по дисциплине Экономика. Сохранить запрос под именем Оценки студентов − физика или экономика.
5 Создать запрос, выдающий список студентов, фамилия которых начинается с буквы С. Сохранить запрос под именем Студенты на С.
6 Создать параметрический запрос на выборку всех данных о студентах, чьи фамилии начинаются с первых букв (одной, двух, сколько нужно по контексту поиска, задаваемых в окне диалога. Сохранить запрос под именем Студенты − параметрический.
35 7 Создать запрос Студенты 1996 года, включив в него записи о студентах, родившихся в 1996 году.
8 Создать параметрический запрос Студенты заданного года, включив в него записи о студентах, родившихся в году, заданном в окне диалога.
9 Изменить запрос Оценки студентов, добавив группировки по полю Название дисциплины – по возрастанию, по полю Оценка – по убыванию. Сохранить запрос под именем Оценки студентов − сортировка.
10 Создать запрос Список доцентов. В запросе поле Должность должно быть включено только для того, чтобы указать условие отбора, оно не должно выводиться на экран.
11 Изменить запрос Оценки студентов таким образом, чтобы в запросе отображались сведения об успеваемости студентов заданной группы по заданному предмету (номер группы и название дисциплины – параметры запроса, задаваемые в диалоге. Сохранить запрос под именем Оценки студентов − параметрический Создать запрос, в результате которого создастся выборка, отражающая средний балл по дисциплинам в группах. Сохранить запрос под именем Средние оценки в группах.
13 Создать запрос, в результате которого для каждого студента подсчитывается количество сданных экзаменов и средний балл по результатам сдачи всех экзаменов. Сохранить запрос под именем Оценки студентов − итоги.
14 Сконструировать запрос Оценки студентов − группировка с несколькими групповыми функциями
- вычисление числа студентов в группе, присутствовавших на экзамене,
- среднего балла сессии по группе. Результаты запроса должны быть сгруппированы по полю Группа. Дисциплина вводится как параметр запроса.
15 Создать перекрестный запрос о среднем балле в группах по предметам рисунок 20). Сохранить запрос под именем Средние оценки групп. Рисунок 20 – Примерный результат выполнения перекрестного запроса
16 Создать запрос Оценки заданный месяц с полями Фамилия, Название дисциплины, Оценка и Дата сдачи. Для поля Дата сдачи в строке Условия отбора ввести выражение Month ( Дата сдачи) = Введите № месяца На основе таблиц Студенты и Общежитие построить запрос Информационная карта студента с полями «ФИО», Группа, Дата рождения, Адрес, Пол. Поле «ФИО» должно содержать фамилию, имя и отчество студента, поле Адрес − сведения об общежитии и комнате. Список отсортировать по фамилиям.
36
5 Работа с запросами. Запросы на изменение Запросом на изменение называют запрос, который за одну операцию изменяет или перемещает несколько записей. Существует четыре типа запросов на изменение на обновление, добавление, создание или удаление. Запрос на обновление позволяет изменять данные в существующих таблицах. Например, на 10% поднимаются цены на все молочные продукты или на
5% увеличивается зарплата сотрудников определенной категории. Запрос на добавление добавляет группу записей из одной или нескольких таблиц вконец одной или нескольких таблиц. Например, появилось несколько новых клиентов, а также база данных, содержащая сведения о них. Чтобы не вводить все данные вручную, их можно добавить в таблицу Клиенты. Запрос на создание таблицы создает новую таблицу на основе всех или части данных из одной или нескольких таблиц. Запрос на создание таблицы полезен при создании таблицы для экспорта в другие базы данных Microsoft Access или при создании архивной таблицы, содержащей старые записи. Запрос на удаление удаляет группу записей из одной или нескольких таблиц. Например, запрос на удаление позволяет удалить записи о товарах, поставки которых прекращены или на которые нет заказов. С помощью запроса на удаление можно удалять только всю запись, а не отдельные ее поля.
5.1 Создание запроса на обновление записей таблицы С помощью запроса на обновление можно добавлять, изменять или удалять данные водной или нескольких записях. Необходимо помнить приведенные ниже правила
•
запрос на обновление нельзя использовать для добавления новых записей в таблицу, но можно менять имеющиеся пустые значения на определенные значения. Для добавления новых записей в одну или несколько таблиц следует использовать запрос на добавление
•
запрос на обновление нельзя использовать для удаления записей целиком строк) из таблицы, но можно менять имеющиеся непустые значения на пустые. Для удаления записей целиком (процесс, удаляющий также значение первичного ключа) следует использовать запрос на удаление. Создание запроса на обновление записей таблицы
1 В режиме конструктора создать новый запрос, включив в него таблицы, содержащие записи, которые следует обновить.
2 а В среде Microsoft Access 2003: в панели инструментов щелкнуть стрелку рядом с кнопкой Тип запроса на панели инструментов и выбрать Обновление б в среде Microsoft Access 2007: в панели инструментов на вкладке Конструктор в группе Тип запроса щелкнуть Обновление.
3 Включить в бланк запроса поля, которые нужно обновить, а также поля, для которых нужно определить условие отбора.
37
4 Для полей, которые нужно обновить, в ячейку Обновление ввести выражение или значение, которое должно быть использовано для изменения полей Для полей, по которым нужно выполнить отбор обновляемых записей, ввести условия отбора.
6 Для предварительного просмотра обновляемых записей переключиться в режим таблицы, а затем вновь вернуться в режим конструктора запросов.
7 а В среде Microsoft Access 2003: для обновления записей нажать Запуск б в среде Microsoft Access 2007: для обновления записей нажать Выполнить.
8 Появится предупреждающее сообщение. Нажать кнопку Да, чтобы выполнить запроси обновить данные. Например, требуется создать запрос на обновление, после выполнения которого в таблице Преподаватели будут увеличены на 20% оклады сотрудников, принятых на должность ассистент (рисунок 21). Рисунок 21 − Создание запроса на обновление записей таблицы Создание запроса на обновление для удаления нескольких полей
1 В режиме конструктора создать новый запрос, включив в него таблицу, содержащую данные, которые нужно удалить.
2 а В среде Microsoft Access 2003: в панели инструментов щелкнуть стрелку рядом с кнопкой Тип запроса на панели инструментов и выбрать Обновление б в среде Microsoft Access 2007: в панели инструментов на вкладке Конструктор в группе Тип запроса щелкнуть Обновление.
3 Включить в бланк запроса поля, данные из которых нужно удалить, а также поля, для которых нужно определить условие отбора.
4 Для полей, данные из которых нужно удалить, в ячейку Обновление ввести или " " (пар двойных кавычек, неразделенных пробелом.
5 Для полей, по которым нужно выполнить отбор обновляемых записей, ввести условия отбора.
6 Для предварительного просмотра обновляемых записей переключиться в режим таблицы, а затем вновь вернуться в режим конструктора запросов.
7 а В среде Microsoft Access 2003: для обновления записей нажать кнопку Запуск на панели инструментов
38 б в среде Microsoft Access 2007: для обновления записей нажать Выполнить.
8 Появится предупреждающее сообщение. Нажать кнопку Да, чтобы выполнить запроси обновить данные.
5.2 Создание запроса на создание таблицы При создании запроса на создание таблицы сначала создается запрос на выборку, а затем он преобразуется в запрос на создание таблицы. В запросе на выборку можно использовать вычисляемые поля и выражения. Процесс создания таблицы с помощью запроса состоит из трех шагов
1) создать запрос на выборку
2) преобразовать запрос на выборку в запрос на изменение, задав параметры размещения новой таблицы
3) выполнить запрос на изменение. Создание запроса на создание таблицы
1 В режиме конструктора создать запрос, выбрав таблицы или запросы, содержащие записи, которые следует поместить в новую таблицу.
2 а В среде Microsoft Access 2003: в режиме конструктора запроса щелкнуть стрелку рядом с кнопкой Тип запроса на панели инструментов и выбрать Создание таблицы б в среде Microsoft Access 2007: в панели инструментов на вкладке Конструктор в группе Тип запроса щелкнуть Создание таблицы.
3 Откроется диалоговое окно Создание таблицы. В поле Имя таблицы ввести имя таблицы, которую требуется создать.
4 Выполнить одно из следующих действий
- выбрать параметр в текущей базе данных
- выбрать параметр в другой базе данных, а затем ввести путь к базе данных, в которую следует поместить новую таблицу, или нажать кнопку Обзор для поиска базы данных.
5 Нажать кнопку OK.
6 а В среде Microsoft Access 2003: нажать кнопку Запуск на панели инструментов, а затем нажать кнопку Да для подтверждения операции б в среде Microsoft Access 2007: нажать кнопку Выполнить, а затем нажать кнопку Да для подтверждения операции.
1 2 3 4 5 6 7
7 Появится второе сообщение, показывающее число записей, которые будут помещены в новую таблицу в результате выполнения запроса. Нажать кнопку Да. Замечание. Запрос на создание таблицы может использоваться многократно, при этом каждый раз будет удаляться старая таблица и создаваться новая. Например, требуется создать запрос на создание таблицы Студенты- математики, которая должна содержать все поля из таблицы Студенты. В бланк запроса нужно включить все поля из таблицы Студенты, для поля Номер группы задать условие отбора «Like "М (рисунок 22).
39 Рисунок 22 − Создание запроса на создание новой таблицы
5.3 Создание запроса на добавление Процесс создания запроса на добавление состоит из следующих шагов
•
создание запроса на выборку
•
преобразование запроса на выборку в запрос на добавление
•
выбор конечных полей для каждого столбца в запросе на добавление
•
выполнение запроса на добавление записей. У запросов на добавление есть одна особенность в области таблиц конструктора запросов отображены не те таблицы, куда вы добавляете запись, а откуда выберете данные. Целевая таблица (в которую добавляются записи) указывается в окне, которое появляется сразу после установки тип запроса на добавление. Создание запроса на добавление
1 В режиме конструктора создать новый запрос, включив в него таблицу, записи из которой необходимо добавить в другую таблицу.
2 а В среде Microsoft Access 2003: в панели инструментов щелкнуть стрелку рядом с кнопкой Тип запроса на панели инструментов и выбрать Добавление б в среде Microsoft Access 2007: в панели инструментов на вкладке Конструктор в группе Тип запроса щелкнуть Добавление.
3 Откроется диалоговое окно Добавление. В поле Имя таблицы ввести имя таблицы, в которую необходимо добавить записи.
4 Выполнить одно из следующих действий- если таблица находится в открытой в настоящий момент базе данных, выбрать параметр в текущей базе данных
- если таблица не находится в открытой в настоящий момент базе данных, выбрать параметр в другой базе данных и ввести имя базы данных, в которой находится таблица, или нажать кнопку Обзор и указать путь к базе данных Нажать кнопку OK.
6 Включить в бланк запроса поля, которые нужно добавить, а также поля, для которых нужно определить условие отбора. Внимание При создании запроса поле с типом данных Счетчик в бланк запроса перетаскивать не надо
7 Если в обеих таблицах выделенные поля имеют одинаковые имена, то соответствующие имена автоматически вводятся в строку Добавление. Если имена полей двух таблиц отличны друг от друга, тополя с несовпадающими именами будут оставлены пустыми, и нужно будет указать в строке Добавление имена конечных полей таблицы-получателя. Если результирующее поле оставлено пустым, запрос не добавит данных к этому полю.
8 Для полей, перемещенных в бланк запроса, ввести в ячейку Условие отбора условие отбора, по которому будет осуществляться добавление.
9 Для предварительного просмотра добавляемых записей переключиться в режим таблицы, а затем вновь вернуться в режим конструктора запросов.
10 а В среде Microsoft Access 2003: для обновления записей нажать кнопку Запуск на панели инструментов б в среде Microsoft Access 2007: для обновления записей нажать Выполнить.
11 Появится предупреждающее сообщение. Нажать кнопку Да, чтобы выполнить запроси добавить данные.
Замечание. Отменить результаты выполнения запроса на добавление невозможно, поэтому до выполнения запроса нужно убедиться, что действительно необходимо добавить записи (переключиться в режим таблицы. В качестве примера рассмотрим запрос, который будет выбирать из базы данных студентов всех курсов, обучающихся на специальности Математика, и добавлять их в другую таблицу Математики (рисунок 23). Рисунок 23 − Создание запроса на добавление записей
41 Сначала создадим копию структуры таблицы Студенты, которая будет хранить требуемые данные. Для этого нужно скопировать таблицу Студенты в буфер обмена (команда Правка – Копировать, а затем вставить таблицу из буфера (команда Правка – Вставить, указав в окне диалога имя новой таблицы Математики и выбрав параметр только структура. Далее создадим запрос на добавление для переноса в новую таблицу данных из таблицы Студенты. В структуре запроса для поля Номер группы задается условие отбора записей базы данных. Замечание Если записи добавляются в таблицу, которая уже не пуста, то наиболее часто встречающейся ошибкой при выполнении запроса является попытка вставить записи, у которых значение первичного ключа совпадает с ключами уже имеющихся в ней записей. Такие записи вставлены не будут, будет только выдано сообщение об их количестве.
5.4 Создание запроса на удаление Запросы на удаление записей позволяют отобрать требуемые записи иуда- лить их за один прием. Принцип создания такого запроса аналогичен созданию запроса на обновление, однако, удаляя записи из связанных таблиц, необходимо помнить о том, что при этом не должна нарушаться целостность данных. При использовании запроса на удаление необходимо учитывать следующее При удалении записей с помощью запроса на удаление отменить операцию невозможно. Поэтому, прежде чем выполнить такой запрос, необходимо просмотреть данные, выбранные для удаления (в режиме таблицы.
•
Рекомендуется всегда делать резервные копии данных. Таким образом, если были удалены не те записи, их можно восстановить из резервных копий Если требуется удалить данные из нескольких таблиц, и эти таблицы связаны, включите параметры Целостность данных и Каскадное удаление связанных записей для каждой из связей. Это позволит запросу удалять данные из таблиц со стороны отношения один и многие. При необходимости удалить данные из связанных таблиц следует помнить следующее правило. Если данные находятся на стороне многие отношения
«один-ко-многим», можно удалять их без внесения изменений в отношение. Однако если данные находятся на стороне один отношения «один-ко- многим, нужно сначала изменить отношение, иначе Microsoft Access заблокирует удаление. Процесс удаления связанных данных состоит из следующих основных этапов определить, какие из записей находятся на стороне один отношения, а какие − на стороне многие
42
•
если нужно удалить записи со стороны один отношения и связанные записи со стороны многие, включить набор правил Целостность данных и разрешите операцию каскадного удаления
•
если нужно удалить данные только на стороне один отношения, сначала удалить отношение, а затем удалять данные
•
если требуется удалить данные только со стороны многие отношения, создать и выполнить запрос на удаление, не внося изменений в отношение. Создание резервной копии базы данных
1 а В среде Microsoft Access 2003: вменю Файл выбрать команду Резервная копия базы данных б в среде Microsoft Access 2007: щелкнуть кнопку Microsoft Office вверх- нем левом углу окна программы, выбрать пункт Управление, а затем выбрать команду Резервная копия базы данных.
2 В диалоговом окне Сохранение указать имя и расположение для резервной копии и нажать кнопку Сохранить. Исходный файл будет закрыт, после чего будет создана резервная копия и повторно открыт исходный файл. Удаление записей с помощью запроса, включающего только таблицу на стороне один отношения «один-ко-многим»
1 Создать новый запрос, включив в него главную таблицу (на стороне один, из которой необходимо удалить записи.
2 а В среде Microsoft Access 2003: в панели инструментов щелкнуть стрелку рядом с кнопкой Тип запроса на панели инструментов и выбрать Удаление б в среде Microsoft Access 2007: в панели инструментов на вкладке Конструктор в группе Тип запроса щелкнуть Удаление.
3 Переместить символ « * » из списка полей таблицы в бланк запроса в строку Поле. В строке Удаление появляется при этом текст Из.
4 Чтобы задать условия отбора для удаляемых записей, переместить поля, для которых необходимо установить условия отбора, в бланк запроса. В ячейке Удаление в этих полях появляется значение Условие.
5 Для полей, перемещенных в бланк запроса, ввести условия в ячейку Условие отбора.
6 Для предварительного просмотра удаляемых записей переключиться в режим таблицы, а затем вновь вернуться в режим конструктора запросов.
7 а В среде Microsoft Access 2003: для удаления записей нажать кнопку Запуск на панели инструментов б в среде Microsoft Access 2007: для удаления записей нажать Выполнить.
8 Появится предупреждающее сообщение. Нажать кнопку Да, чтобы выполнить запроси удалить данные. Например, требуется удалить из таблицы Студенты все записи о студентах группы М. При заполнении бланка запроса перетаскиваем символ «*» в строку Поле первого столбца, дополнительно включаем в бланк поле Номер группы. Для поля Номер группы в строке Условие отбора вводим условие М (рисунок 24).
43 Рисунок 24 − Создание запроса на удаление записей из таблицы Удаление записей с помощью запроса, включающего обе таблицы, связанные отношением «один-ко-многим»
1 Создать новый запрос, включив в него таблицы, из которых необходимо удалить записи.
2 а В среде Microsoft Access 2003: в панели инструментов щелкнуть стрелку рядом с кнопкой Тип запроса на панели инструментов и выбрать Удаление б в среде Microsoft Access 2007: в панели инструментов на вкладке Конструктор в группе Тип запроса щелкнуть Удаление.
3 Для подчиненной таблицы, содержащей связанные записи (на стороне многие отношения «один-ко-многим»), переместить символ « * » из списка полей таблицы в бланк запроса в строку Поле. В строке Удаление появится текст Из.
4 Чтобы задать условия отбора для удаляемых записей, переместить поляна которые необходимо установить условия, из главной таблицы (на стороне один отношения «один-ко-многим») в бланк запроса. В строке Удаление в этих полях появляется значение Условие.
5 Для полей, перемещенных в бланк запроса, ввести условия в ячейку Условие отбора.
6 Для предварительного просмотра удаляемых записей переключиться в режим таблицы, а затем вновь вернуться в режим конструктора запросов.
7 а В среде Microsoft Access 2003: для удаления записей нажать кнопку Запуск б в среде Microsoft Access 2007: для удаления записей нажать Выполнить.
8 Появится предупреждающее сообщение. Нажать кнопку Да, чтобы выполнить запроси удалить данные.
9 Выделить список полей для каждой подчиненной таблицы на стороне многие в верхней области таблиц конструктора запросов и нажать клавишу
10 а В среде Microsoft Access 2003: в панели инструментов щелкнуть стрелку рядом с кнопкой Тип запроса на панели инструментов и выбрать Удаление
44 б в среде Microsoft Access 2007: в панели инструментов на вкладке Конструктор в группе Тип запроса щелкнуть Удаление.
11 Когда в запросе останется лишь главная таблица, а в бланке запроса − поле, для которого установлено условие отбора, снова выполнить запрос.
Microsoft Access удалит отобранные записи из таблицы, находящейся на стороне один. Например, требуется удалить из базы данных Деканат все записи осту- денте Петрове из группы М (рисунок 25). Рисунок 25 – Первый этап удаления записей из связанных таблиц При заполнении бланка запроса перетаскиваем из таблицы Оценки символ в строку Поле первого столбца, включаем в бланк также поля Фамилия и Номер группы из таблицы Студенты. Для поля Фамилия в строке Условие отбора вводим условие Петров, для поля Номер группы
– условие М. После выполнения запроса в окне конструктора удаляем список полей таблицы Оценки (рисунок 26) и вновь выполняем запрос. В результате вся информация о студенте Петрове удалена из базы данных. Рисунок 26 – Второй этап удаления записей из связанных таблиц
45 Удаление записей из одной или нескольких таблиц, связанных отношением «один-к-одному»
1 Создать новый запрос, включив в него таблицы, из которых необходимо удалить записи.
2 а В среде Microsoft Access 2003: в панели инструментов щелкнуть стрелку рядом с кнопкой Тип запроса на панели инструментов и выбрать Удаление б в среде Microsoft Access 2007: в панели инструментов на вкладке Конструктор в группе Тип запроса щелкнуть Удаление.
3 Для таблиц, из которых необходимо удалить записи, переместить символ (*) из списка полей в бланк запроса. В строке Удаление появится текст Из.
4 Чтобы задать условия отбора удаляемых записей, перетащить в бланк запроса поля, для которых необходимо установить условия отбора. В ячейке Удаление этих полей появится значение Условие.
5 Для полей, перемещенных в бланк запроса, ввести условия в ячейку Условие отбора.
6 Для предварительного просмотра удаляемых записей переключиться в режим таблицы, а затем вновь вернуться в режим конструктора запросов.
7 а В среде Microsoft Access 2003: для удаления записей нажать кнопку Запуск на панели инструментов б в среде Microsoft Access 2007: для удаления записей нажать Выполнить.
5.5 Практическая работа № 5 Перед выполнением заданий создать резервные копии изменяемых таблиц
1 Построить запрос на создание таблицы Список заданной группы, содержащей список студентов группы, номер которой задается как параметр запроса.
2 Построить запрос на создание таблицы Итоги сессии, содержащей средние баллы в группах по каждому экзамену.
3 Построить запрос на создание таблицы Список отличников, содержащей фамилии, имена и номера групп студентов, сдавших сессию на 5.
4 Построить запрос на создание таблицы Список двоечников, содержащей коды, фамилии, имена и номера групп студентов, получивших в сессию хотя бы одну двойку.
5 Построить запрос на создание таблицы Подлежат отчислению, содержащей коды, фамилии, имена и номера групп студентов, получивших в сессию более одной двойки.
6 Построить запрос на создание таблицы Итоги сдачи информатики в заданной группе, содержащей поля «ФИО студента, Оценка и Экзаменатор. Номер группы – параметр запроса. Записи таблицы должны быть отсортированы по полю «ФИО студента, образованному путем слияния полей Фамилия, Имя и Отчество.
7 Разработать запрос под именем Индексация стипендии для индексации размера стипендии в копии таблицы Стипендия нас декабря.
8 Разработать запрос Удаление студентов для удаления заданного студента из копии таблицы Студенты. Фамилия студента и номер его группы – параметры запроса. Следует учесть, что если требуется удалить из базы данных какого-то студента, то должны быть удалены не только запись об этом студенте в копии таблицы Студенты, но и все оценки данного студента в копии таблицы Оценки.
9 Создайте запрос на выборку из таблицы Студенты тех студентов, кто не получает стипендии, и преобразуйте его в запрос для сохранения результатов в виде отдельной таблицы с именем Студенты без стипендии.
10 Создайте запрос на выборку из копии таблицы Студенты всех, кто проживает в определенном городе (название города – параметр запроса. Преобразуйте его в запрос на обновление названия города (новое название города – параметр запроса.
11 Создайте запрос-обновление для копии таблицы Студенты, выбрав всех студентов определенного курса, и измените курс на другой.
12 Создайте запрос к копии таблицы Студенты, который позволяет удалить все записи о студентах заданного курса, не получающих стипендию в течение всего семестра (номер курса − параметр запроса.
6 Работа с формами Форма − это объект базы данных, который можно использовать для ввода, изменения или отображения данных из таблицы или запроса. Источником записей формы являются поля в базовых таблицах и запросах. Форму можно использовать как кнопочную форму, открывающую другие формы или отчеты базы данных, а также как пользовательское диалоговое окно для ввода данных и выполнения действий, определяемых введенными данными Автоматическое создание формы
Access предлагает несколько способов создания форм. Самым простым из них является использование средств автоматического создания форм на основе одной таблицы или одного запроса. При использовании этого средства все поля базового источника данных размещаются в форме. Весть несколько видов автоматически создаваемых форм (автоформы), каждый из которых отличается способом отображения данных Автоформа в столбец − одновременно отображает только одну запись. Поля записи отображаются в виде набора элементов управления, расположенных в один или несколько столбцов.
•
Автоформа табличная выглядит также, как обычная таблица Access.
•
Автоформа ленточная − одновременно отображает несколько записей. Поля каждой записи располагаются в отдельной строке, как в простой таблице. В Microsoft Access также три вида автоформ:
•
форма – аналогична автоформе в столбец
47
•
разделенная форма − позволяющая одновременно отображать данные в двух представлениях в режиме формы в столбец ив режиме таблицы
•
несколько элементов – аналогична ленточной автоформе. Создание автоформы:
В среде Microsoft Access 2003
1 В окне базы данных нажать кнопку Формы на панели Объекты, а затем нажать кнопку Создать на панели инструментов окна базы данных.
2 В открывшемся диалоговом окне Новая форма выбрать тип автоформы.
3 В поле со списком в нижней части окна Новая форма выбрать таблицу или запрос, содержащие данные, на основе которых создается форма.
4 Нажать кнопку ОК. Access создаст и откроет форму выбранного вида.
5 Сохранить созданную форму, выбрав вменю Файл команду Сохранить или нажав кнопку Сохранить на панели инструментов. Затем в диалоговом окне ввести название новой формы и нажать ОК.
В среде Microsoft Access 2007
1 В области переходов выделить таблицу или запрос сданными, которые должны отображаться в форме.
2 В панели инструментов на вкладке Создание в группе Формы щелкнуть кнопку, соответствующую нужному типу автоформы (Форма или Разделенная форма. Access создаст форму и отобразит ее в режиме макета.
1 2 3 4 5 6 7