Создание базы данных в Microsoft Access. Создание, заполнение и редактирование таблиц

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

Базовый интерфейс СУБД Access представлены на рисунке.

Рассмотрим основные элементы пользовательского интерфейса Ассess.

Строка заголовка содержит имя базы данных, название формата файла (например, Access ) и название программы – Microsoft Access.

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

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

При открытии базы данных лента появляется в верхней части главного окна Access . На ней отображаются команды активной вкладки команд.

Вкладки Главная, Создание, Внешние данные и Работа с базами данных находятся на ленте постоянно. Остальные появляются или исчезают в зависимости от того, какой документ активен в рабочей области. Например, вкладка Работа с таблицами, содержащая набор инструментов для работы с таблицами, отображается только в случае, если в рабочей области Access открыта таблица. Именно поэтому интерфейс Access не перегружен и в нем легко ориентироваться: в любой момент отображаются только те вкладки и кнопки, которые сейчас нужны.

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

В Access для организации хранения, обработки информации и представления этой информации пользователю определены следующие объекты:

- Таблицы - объекты базы данных, ответственные за организацию хранения информации в базы данных: все данные хранятся в таблицах.

- Запросы - объекты базы данных, предназначенные для создания, редактирования, а также поиска информации в таблицах базы данных средствами языка запросов SQL. Запросы также используются как эффективное средство обработки данных.

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

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

- Страницы - объекты, используемые для создания Интернет-страниц доступа к данным.

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

- Модули - объекты для хранения кода программ на языке Access-VBA (Visual Basic for Application). Используются для организации сложной обработки данных, для которой недостаточно только запросов на языке SQL и макросов.

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

Для скрытия и открытия области переходов служит кнопка в правом верхнем углу области.

• Окна/вкладки документов. Документы базы данных располагаются в рабочей области Access в виде вкладок или в виде перекрывающихся окон.

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

Порядок работы с таблицами

Для создания и редактирования таблиц предназначены два режима.

• Режим таблицы предназначен для ввода, просмотра и редактирования записей.

При вводе данных программа Access автоматически добавляет недостающие поля и определяет тип данных для этих полей. Так, если введены буквы, полю будет присвоен тип данных Текстовый, если цифры – Числовой. Затем при необходимости можно настроить свойства полей в режиме конструктора.

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

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

Создание таблицы

Итак, вы открыли или создали базу данных. Теперь программа Access готова к работе с таблицами. Если вы создали пустую базу данных, программа автоматически создала и открыла новую пустую таблицу и вы можете начать заполнять эту таблицу.

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

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

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

Для приведенной модели такими таблицами будут таблицы: «Контрагенты», «Накладная приход материалов», «Товар».

2. Создать связи между таблицами.

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

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

Или же можно сразу создать таблицу в режиме конструктора для этого необходимо нажать на кнопку Конструктор таблиц на вкладке Создание.

2.В столбцы «Имя поля» и «Тип данных» на основе физической модели ввести данные о наименовании атрибутов и их типах данных.

В Access выделяются следующие правила, которых нужно при­держиваться при создании полей таблиц:

- имена полей в одной таблице не должны повторяться;

- имена полей должны содержать не более 64 символов, включая пробелы;

- имя поля не должно начинаться с пробела;

- желательно начинать имя поля с буквы;

- имена полей могут содержать любые символы, включая буквы, цифры, пробелы, специальные символы, за исключением точки (.), воскли­цательного знака (!), апострофа (') и квадратных скобок ([]).

Рассмотрим окно конструктора таблиц. В верхней части окна конст­руктора таблицы находится список с колонками: Имя поля, тип данных, и описание.

Первая колонка содержит имя поля, которое указывается с учетом описанных правил.

Во второй колонке указывается тип данных для данного поля. Наибо­лее удобным способом является выбор типа из выпадающего списка.

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

При этом типы данных выбираются из числа возможных для систе­мы Access. Основными типами данных являются: текстовый, поле MЕМО, числовой, дата/время, денежный, счетчик, логический, объекты OLE, гиперссылки, мастер подстановок.

Текстовый - символьные или числовые данные, не требующие вы­числений. Поле данного типа может содержать до 255 символов. Размер текстового поля задается с помощью свойства «Размер поля», в котором указывается максимальное количество символов, которые могут быть вве­дены в данное поле.

Числовой - этот тип применяется для хранения числовых данных: целых и вещественных. По умолчанию используется Длинное целое (Longlnteger), которое представляет собой число в пределах от -2 147 483 648 до +2 147 483 647.

Тип числового значения устанавливается в свойстве «размер поля», которое может принимать следующие значения: Байт, Целое, Одинарное с плавающей точкой, Двойное с плавающей точкой, Десятичное, Код репликации.

Поле MEMO - этот тип данных предназначен для ввода текстовой информации, по объему превышающей 255 символов. Такое поле может содержать до 65535 символов. Поле МЕМО не может быть ключевым или проиндексированным.

Дата/Время – тип предназначен для хранения информации о дате и времени. Позволяет вводить даты с 100 по 9999 год.

Денежный - тип предназначен для хранения информации о денежных единицах. Точность представления колеблется от 1 до 4 знаков после запятой.. Целая часть данного типа может содержать до 15 знаков.

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

Заполним столбцы «Имя поля» и «Тип данных» атрибутами сущности «Контрагенты».

Настройка свойств полей

В нижней части окна расположен список свойств активного поля. Рас­смотрим основные свойства.

Свойство Размер поля предназначено для выбора вида информации, хранимой в поле - типа данных.

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

3. Необходимо установить первичный ключ таблицы.

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

Как видно из представленной выше даталогической модели для сущ­ности «Контрагенты» ключом будет являться атрибут «Код контрагента». Следовательно, назначим поле «Код контрагента» в качестве первичного ключа таблицы.

4.Сохраним таблицу базы данных под соответствующим именем.

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

Продолжим создание таблиц приведенной выше физической модели данных. Создадим таблицу «Накладная».

Установим в качестве первичного ключа поле «Код накладной» и сохраним таблицу под именем «Накладная».

Заполним созданную таблицу. Предположим имеются следующие накладные:

№0011 от 01.09.2011 ООО «Лотос»

№Н024 от 05.10.2011 ООО «Олимп»

№36 от 10.10.2011 ЗАО «Монолит»

Тогда таблица будет выглядеть следующим образом:

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

1. Открыть таблицу «Контрагенты».

2. Выбрать необходимого контрагента (т.е. одну из записей таблицы).

3. Выписать значение поля «Код контрагента».

4. Закрыть таблицу «Контрагенты».

5. В поле «Код контрагента» таблицы «Накладная» ввести запи­санное ранее значение поля «Код контрагента» для выбранной ранее записи из таблицы «Контрагенты».

Для упрощения этого процесса в Access существует понятие поля подстановки.

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

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

Рассмотрим процесс создания поля подстановки в таблице Access 2010 на примере таблицы «Контрагенты».

Создание подстановки на основе таблицы или запроса

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

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

2.На экране появится окно Создание подстановки.

3. Установите переключатель в положение Объект “столбец подстановки” будет использовать значения из таблицы или запроса и нажмите кнопку Далее.

4. Выберите источник, на основе которого будет создана подстановка (рис. 12). Если источником должна быть таблица, установите переключатель Показать в положение Таблицы и щелкните кнопкой мыши на названии нужной таблицы. Если источником должен быть запрос, то установите переключатель в положение Запросы и щелкните кнопкой мыши на названии нужного запроса. Нажмите кнопку Далее.

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

Чтобы добавить поле в подстановку, щелкните кнопкой мыши на названии поля в списке Доступные поля и нажмите кнопку .

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

После того как набор полей сформирован, нажмите кнопку Далее.

Примечание

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

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

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

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

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

10. Сохраним таблицу и закроем режим конструктора.

Откроем таблицу «Накладная» (двойным щелчком по наименованию таблицы) и в поле «Код контрагента» значения уже не вводятся с клавиатуры, а выбираются из выпадающего списка.

Создание связей между таблицами

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

Само понятие «связь между таблицами» предполагает наличие между таблицами базы данных какой-либо зависимости.

Как уже известно из предыдущих занятий существует три основных типа зависимости: «один к одному», «один ко многим», «многие ко мно­гим».

Зависимость «один к одному» (1:1) означает, что одной записи од­ной таблицы строго соответствует только одна запись другой таблицы. Та­кой тип связи применяется, когда в таблице с большим числом полей более часто используемые поля выносят в одну таблицу, менее используемые -в другую.

Зависимость «один ко многим» (1:М) означает, что одной записи таблицы соответствует много записей в другой таблице. Такой вид связи является наиболее распространенным. Пример, таблица «Контрагенты» свя­зана с таблицей «Накладная» связью «один ко многим» по полю «Код контрагента».

Зависимость «многие ко многим» (М:М) означает, что одной запи­си первой таблицы соответствует много записей во второй, и одной записи во второй таблице соответствует много записей в первой. В нормализован­ной базе данных таблиц с такими связями быть не может, и эта связь реа­лизуется через третью таблицу, связываемую с исходными отношением «один ко многим».

Практическое значение имеет установление связей «один к одному» и «один ко многим» в Access 2010.

В рассматриваемой в качестве примера даталогической модели имеются следующие связи:

1. Связь «один ко многим» сущности «Контрагенты» с сущностью «Накладная» по значению поля «Код контрагента» (т.к. выше уже были созданы таблицы для этих сущностей, то это будет уже связь «один ко многим» между таблицей «Контрагенты» и таблицей «На­кладная» по полю «Код контрагента».

2. Связь «один ко многим» сущности «Накладная» с сущностью «Товары» по полю «Код накладной».

Создадим эти связи между таблицами базы данных в Access. Для этого необходимо выполнить следующую последовательность действий:

1. Перейти на вкладку Работа с базами данных и нажать на кнопку Схема данных (рис. 19).

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

В результате будет выведено окно конструктора схемы данных.

Такая схема была создана автоматически при создании полей под­становки в таблице «Накладная». Для того чтобы последовательно рас­смотреть процесс создания схемы данных, удалим все таблицы и связи, созданные программой автоматически.

2. Нажать на кнопку Очистить макет для очистки ра­нее созданной схемы данных.

3. Для того, чтобы создать связь Контрагент - Накладная необходимо:

- Выполнить команду нажать на кнопку Отобразить таблицу или выбрать соответствующий пункт в контекстном меню.

- Выбрать таблицу «Контрагенты» и нажать «Добавить».

- В этом же диалоге выбрать остальные таблицы аналогичным образом.

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

Как видно из рисунка, между таблицами «Контрагенты» или «Накладная» уже установлена связь «один ко многим». Однако не ус­тановлен режим поддержки этой связи - обеспечение целостности данных.

Существует два метода поддержки целостности связи: каскадное об­новление и каскадное удаление.

Каскадное обновление - механизм, при котором при изменении значения поля в родительской таблице автоматически изменяются значе­ния во всех таких же полях связных таблиц.

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

Для рассматриваемой связи режим каскадного удаления не требует­ся: при накладных содержат информацию о фактически произведенных операциях движения материальных ценностей, и удаление записей из этой таблицы будет означать искажение экономической информации. Если организация перестала работать с контрагентом (поставщиком, покупате­лем и др.), то его нужно просто исключить из списков, выводимых пользо­вателю при выборе. Фактически удалять запись о контрагенте можно толь­ко в случае отсутствия ссылок на него в других документах, что и обеспе­чивается установлением флажка «обеспечение целостности данных».

Установите флажок «обеспечение целостности данных» и на­жмите «ОК».

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

ВНИМАНИЕ!Необходимо правильно установить связи между таблица­ми, целостность этой связи и режим поддержки целостности. Без этого база данных не сможет нормально работать.

Окно схемы данных примет следующий вид:

Связь «один ко многим» в Access обозначается как 1:∞.

5. Изменим тип связи между таблицами «Контрагенты» и «Накладная». Для этого необходимо:

- открыть диалог «Изменить связи» (двойной щелчок левой кнопкой мыши по связи между таблицами);

- установить флажок «Обеспечение целостности данных».

6.Добавим связь между таблицей «Накладная» и «Товары». Для этого необходимо:

- выделить поле «Код товара» в таблице «Накладная» (т.е. в родительской таблице).

- нажать левую кнопку мыши, и, удерживая ее, переместить указа­тель мыши на поле «Код» таблицы Товары, после чего отпустить левую кнопку мыши;

В результате на экране появится диалог создания связи между таб­лицами:

- установить «Обеспечение целостности данных» и режим каскад­ного удаления связанных записей как показано на следующем рисунке.

- нажать «Создать».

Созданная схема базы данных используется Access для обеспечения ссылочной целостности базы данных - т.е. для сохранения непротиворечи­вости базы данных по ссылкам:

- если будет удалена запись из родительской таблицы, то будут уда­лены все связные записи из дочерних таблицы. Если этого не сделать, то после удаления записи из родительской таблицы, связные записи в дочер­них таблицах будут указывать на несуществующие данные;

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