База Данных ms Access 2007

Программа MS Access обеспечивает эффективную разработку и сопровождение реляционных баз данных. Данные в таких базах представляются в виде одной или нескольких таблиц, состоящих из однотипных записей (строк). Каждая таблица содержит набор данных по конкретной теме (объекту). Система управления базой данных Access позволяет обеспечить вод данных в таблицы, их хранение, отбор по заданным признакам, преобразование структуры, вывод данных.

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

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

Краткое описание технологии работы с Access 2007

Для запуска программы MS Office: Пуск\Все программы\Microsoft office\ Microsoft office Access.

Создание базы данных - щелкнуть на кнопке Новая база данных (появляется окно Новая база), затем щелкнуть на кнопке Создать – появляется окно с незаполненной таблицей. (В дальнейшем для описанных действий будем использовать краткие обозначения типа: Новая база данных\ Создать).

Сохранение созданной Базы данных - щелкнуть на кнопке ”Оффис” (в левом верхнем углу окна)\Сохранить как (ввести имя файла)\ОК.

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

Создание новой таблице (T ) в режиме Конструктор. Появляется вспомогательная таблица с тремя столбцами: Имя поля (куда вы вписываете заголовки полей (столбцов) таблицы T), Тип данных (в столбцах T) и Описание (последнее необязательно). В каждой строке вспомогательной таблицы содержится информация о соответствующем столбце создаваемой таблицы столбцов таблицы T . Задаем имя первого поля (т.е. столбца таблицы) и соответствующий тип данных (для этого щелкаем в правой части ячейки столбца Тип данных- возникает список типов: числовой, текстовый, счетчик, дата и проч. Выбираем нужный).

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

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

Для последующего заполнения таблицы данными следует перейти в режим Таблица (Главная\Режим\Таблица).

Отметим, что значения в первом столбце таблицы T не должны повторяться. Для него удобно использовать тип данных Счетчик. В этот столбец для каждой записи (строки ) таблицы автоматически вводится уникальное число. Наиболее часто используется счетчик последовательно возрастающих чисел. Такой тип счетчика удобно использовать как первичный ключ таблицы. Счетчик случайных чисел создает случайный номер для каждой записи в таблице.

Создание новой таблицы (путем ввода данных). Перейти на вкладку Создание, на панели Таблицы щелкнуть на кнопке Таблица. Открывается окно Таблица в режиме Таблица. В режиме Таблица можно сразу вписывать заголовки столбцов и их содержимое. Вводить в каждый столбец данные определенного типа следует без ошибок, чтобы Access мог автоматически правильно определить тип и формат данных. Однако, если вы допустите ошибки при вводе, то исправление последствий может оказаться достаточно трудоемким. Отметим, что Access автоматически создает и затем заполняет первое поле (столбец) Код типа Счетчик (которое затем делается ключевым). Если вы хотите сделать ключевым другой столбец, то переходим в режим Конструктор и см. выше.

Сохранение созданной таблицы. Переходим в режим Таблицы (Режим \ Режим таблицы). Щелкнуть на имени таблицы над таблицей\ Сохранить. Присвоить таблице имя. Следует отметить, что при закрытии таблицы щелчком на соответствующем крестике в правой части окна, таблица сохраняется автоматически.

Открытие уже существующего объекта (например, Таблицы или Запроса)

Щелчок на черном треугольнике раскрывающегося списка (в верхней части левого окна Все объекты Access над вкладкой Таблицы), выбрать Таблицы (или Запросы), в появившемся списке выбрать нужную таблицу.

Поиск и замена данных в таблице. Поместить курсор в любую строку нужного столбца таблицы, на вкладке Главная щелкнуть на кнопке Найти в группе Найти (справа). Появится диалоговое окно Поиск и замена. В поле Образец ввести искомый набор символов. Можно использовать символы-заменители * и ?, например, И* для поиска всех слов, начинающихся на букву И.

Сортировка. Щелкнуть на соответствующем столбце таблицы, затем на вкладке Главная в группе Сортировка и фильтр щелкнуть на соответствующей кнопке сортировки (по возрастанию или убыванию); или на Фильтр (изображение воронки) затем выбрать вариант сортировки.

Иначе щелкнуть правой кнопкой в столбце и выбрать вариант в контекстном меню.

Или щелкнуть на треугольнике списка в заголовке столбца, выбрать нужное.

Фильтрация. В режиме Таблица щелчок правой кнопкой на одной из ячеек столбца, щелчок на Числовые фильтры, выбрать вариант фильтрации, ОК.

Для отмены фильтрации: щелчок на Удалить фильтр (в правом нижнем углу панели Сортировка и фильтр).

Фильтр по выделенному. В окне базы данных выбрать таблицу или запрос. Установить курсор в поле записи для отбора записей с тем же значением поля, например, ”Иван” . На Главная-Сортировка и фильтр нажать кнопку Выделение (кнопку с изображением воронки с молнией или кнопку Фильтр воронка вверху посередине) , щелкнуть Равно , вписать ”Иван” (варианты: Не равно ”Иван”, Содержит ”Иван”, не содержит ”Иван”).

Для отмены фильтра – щелчок на воронке Удалить фильтр (внизу справа панели Сортировка и фильтр), или щелчок правой в ячейке столбца и в контекстном меню. Выбрать Снять фильтр с Имя, или нажать на кнопке Параметры расширенного фильтра, затем на Очистить все фильтры.

Другой вариант: щелкнуть правой кнопкой мыши на ячейке с ”Иван” и в появившемся окне выбрать нужное.

Можно выделить фрагмент ”Ив”и щелкнуть Начинается с ”Ив”. Тогда при фильтрации останутся только строки с именем, начинающимся на ”Ив”. Можно выделить фрагмент ”ан” и и щелкнуть заканчивается на ”ан”. Тогда при фильтрации останутся только строки с именем, заканчивающимся на ”ан”.

Фильтрация с помощью расширенного фильтра (по одному столбцу):

- щелкнуть на соответствующем столбце, затем на вкладке Главная-Сортировка и фильтр, щелкнуть на Параметры расширенного фильтра (средняя воронка справа), Расширенный фильтр. Щелкнуть на черном треугольнике списка в 1-й строке 1-го столбца внизу соответствующего окна фильтра (щелкнув в правой части 1-й ячейки 2-й строки можно задать фильтрацию). В ячейку 3-й строки вписать условие отбора , например ”Иван”. Затем Параметры расширенного фильтра (справа посередине), Применить фильтр. Если вписываем ”Ив*”, то отбираются строки с именами на ”Ив”.

В 4-ю строку( “или” ) можно вписать условие типа ИЛИ, причем последовательно можно добавить несколько таких условий.. Аналогично можно добавить условие фильтрации еще по одному или нескольким столбцам.

Для отмены - кнопка Параметры расширенного фильтра, затем Очистить все фильтры. Иначе: щелкнуть Удалить фильтр (справа внизу).

При фильтрации по числовому столбцу задать условие типа, например, >1 AND <5.

Создание Запроса на выборку

Запрос на выборку извлекает данные из одной или нескольких таблиц и представляет их в табличном виде (т.е. создает новую вспомогательную таблицу, содержащую нужную часть информации из таблиц базы). В практической работе обычно приходится использовать в одном запросе информацию сразу из несколько связанных таблиц. Как и в случае фильтра может происходить извлечение части записей. Но фильтр не позволяет: объединять таблицы, отображать только часть полей таблицы, вычислять суммы, средние значения и т.д., не может быть сохранен как отдельный объект.

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

Создание простого Запроса на выборку с помощью Мастера. На вкладке Создание в группе Другие щелкнуть на Мастер запросов, в окне Новый запрос выбрать, например, Простой запрос, ОК. В списке Таблицы и запросы выбрать таблицу (или ранее созданный Запрос). В окне Доступные поля выделяем поочередно нужные поля (столбцы) и нажимаем кнопку “>”. Далее, Готово. Аналогично для остальных таблиц.

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

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

Создание Запроса на выборку в режиме Конструктора (применяют более опытные пользователи). На Создание - Другие щелкнуть на Конструктор запросов, открывается окно Запрос, в окне Добавление таблицы выделить таблицу, Добавить (и так для всех таблиц). Закрыть. Можно выделить сразу несколько таблиц с помощью клавиш Shift или Ctrl.

В верхней части окна Запроса находятся изображения добавленных таблиц, содержащие имена полей. Для вставки в Запрос выбранного столбца выбранной таблицы: в нижней части Окна Запроса в очередном свободном столбце делаем щелчок в правой части ячейки 2-й строки (Имя таблицы). В раскрывшемся списке выберите имя нужной таблицы. Аналогично в ячейке Поле выберите имя поля данной таблицы. В следующих столбцах аналогично выбираем поля одной или нескольких таблиц. Переходим в режим Таблицы (щелчок на панели слева). Появляется таблица, составленная из выбранных столбцов выбранных таблиц.

Другой способ построения Запроса: для вставки в Запрос столбца выбранной таблицы следует щелкнуть на его названии в изображения этой таблицы (в верхней части Окна Запроса ) и, не отпуская кнопки, протащить курсор до соответствующей ячейки 1-й строки (Поле) в нижней части Окна Запроса. При этом название таблицы будет вставлено автоматически.

Сортировка и фильтрация в Запросе производятся так же, как для простой таблицы.

Условия отбора в запросе

В режиме Конструктора в строке Условие отбора задается условие (одно на столбец). Например: <5 OR >1 ( или > 10 AND <50).

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

Или: щелчок правой кнопкой на заголовке столбца, в контекстном меню Вставить столбец.

Удаление записи из таблицы. Выделяем запись в режиме Таблицы (щелчок в левой части записи), Delete. Да.

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

Импорт внешних данных в новую таблицу. На вкладке Внешние данные-Импорт щелкнуть Excel, в появившемся окне (вверху справа) щелкнуть Обзор, отметить нужную книгу Excel, Открыть, выбрать вариант сохранения (например, Импортировать данные в новую таблицу, или Добавить копию записей в конец таблицы)), ОК, выбрать Лист или диапазон книги Excel, Далее, Готово, Закрыть.

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

В открывшемся окне щелчок на названии 1-го столбца (например,”ЧислоШтук”), Вставить, знак умножения *, щелчок на названии 2-го столбца, Вставить, ОК.

После этого выражение будет выглядеть: Выражение1:[ЧислоШтук]*[ЦенаШтуки]

Заменить в заголовке 'Выражение1’ на нужное название столбца, например, Общая цена товара.

Иначе (без использования Построителя ) можно ввести руками в ячейке Поле: [название 1-го столбца]*[ название 2-го столбца]. Перейти в режим Таблицы.

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

Вычисления в Запросе (с одной или несколькими таблицами) без Построителя. Создаются дополнительные столбцы Запроса , в которые выводятся результаты вычислений. Например, произведение значений, содержащихся в двух столбцах одной таблицы (или двух таблиц): в режиме конструктор в ячейке Поле свободного столбца введите выражение

[имя 1-й таблицы].[имя столбца 1-й таблицы]* [имя 2-й таблицы].[имя столбца 2-й таблицы].

Если нет совпадений имен столбцов в разных таблицах, то имя таблицы можно опустить. Например, в свободном столбце строки Поле ввести [ЧислоШтук]*[ЦенаШтуки]), ENTER.

Вычисление значения элементарной функции. Например косинуса COS(): cos[имя таблицы].[имя столбца таблицы]

Групповые операции в Запросе. В запросе в режиме Конструктор на Работа с запросами-Конструктор нажимаем на кнопку ∑. В нижней части появляется строка Групповая операция. Щелкаем в правой части ячейки этой строки нужного столбца, выбираем нужное. Для возврата повторно нажимаем на ∑.

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

Запросы с параметрами

Если Запрос приходится выполнять многократно с различными значениями в строке Условие отбора (например, отбирать продажи на сумму, большую заданного значения), то удобно пользоваться Запросом с параметром (или с несколькими параметрами).

Для этого: открыть запрос в режиме Конструктор, в строку Условие отбора вместо конкретного значения ввести слово (или фразу) в квадратных скобках. Теперь, если щелкнуть Выполнить (слева под знаком !) на вкладке Конструктор, то при выполнении Запроса появится диалоговое окно Параметры Запроса, вписать требуемое значение параметра.

В одном Запросе может быть несколько параметров.

Если тип данных дата или числовой, то лучше определить тип данных (после введения слова в строку Условие отбора). Для этого щелкнуть Параметры в группе Показать или скрыть на вкладке Конструктор . Откроется окно Параметры Запроса. В левом столбце ввести слово (можно без квадратных скобок), в правом – выбрать тип данных.

Связывание таблиц

Если в базе несколько таблиц с данными, то для получения общей информации таблицы следует связать. Связи между таблицами устанавливают в схеме данных, но сначала надо в каждой таблице определить ключевое поле, используемое для связывания таблиц. Ключевые поля – одно или несколько полей (столбцов), комбинация значений которых однозначно определяет каждую запись в таблице (например, три столбца – Фамилия, Имя, Отчество).

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

Например (подробнее см. выше в разделе Базы Данных), пусть имеются две таблицы Типы товаров (1-я таблица) и Товары (2-я таблица). Каждый тип включает несколько товаров, но каждый товар относится только к одному типу (именно это свойство относит данный пример к типу «один ко многим»). 1-я таблица называется родительской, 2-я - дочерней. В обоих таблицах есть столбец КодТипа, по которому их можно связать. Значения в столбце КодТипа 1-й таблицы не повторяются, 2-й таблицы – могут повторяться (этот столбец называется внешним ключом).

Чтобы определить связи между таблицами следует обратиться к вкладке Работа с базами данных-Показать или скрыть, щелкнуть Схема данных, в появившемся окне щелкнуть правой клавишей, Добавить таблицу, выбрать нужную таблицу, Добавить, выбрать следующую таблицу и т.д., и так для всех связываемых таблиц. Закрыть.

Для того, чтобы установить связь типа «один ко многим» между таблицами Типы товаров и Товары, щелкаем на поле КодТипа таблицы Типы товаров и, не отпуская кнопку мыши, наведем курсор на поле КодТипа таблицы Товары (возможно отличие имен этих двух полей друг от друга), затем отпустим кнопку. В появившемся окне устанавливаем флажки Обеспечение целостности данных, каскадное обновление связанных полей, каскадное удаление связанных записей. Щелкаем Создать. Установить связь можно только между полями одного типа (например, длинное целое).

Связывание других типов:

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

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

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

Удаление связи таблиц

Закрыть все открытые таблицы. Перейти в окно базы данных. Схема данных.

Щелкнуть правой кнопкой на средней части линии связи и в контекстном меню: Удалить.

Создание поля подстановки. Access позволяет использовать коды вместо значений (например, текстовых) данных – это ускоряет доступ к данным. При необходимости внести изменения (или просмотреть значение в данном поле) удобно использовать поле подстановки. В этом случае, вместо того, чтобы определять числовой код конкретного товара (например, стола) и вносить его, можно выбрать текст «Стол» в раскрывающемся списке.

Например, пусть имеются две таблицы Заказы и Товары. Их можно связать общим полем Код Товара. До связывания этих таблиц, в таблице Заказы, в режиме Конструктор, в строке Код товара щелкнуть левой кнопкой в правой части ячейки столбца Тип данных. Появится раскрывающийся список, в нем выбрать пункт Мастер подстановок. В первом окне диалога мастера подстановок выбрать вариант использования данных из таблицы, Далее. Во втором окне выбрать таблицу Товары. , Далее. В третьем окне выбрать поле Название товара, щелкнуть на кнопке “ >”, Далее. Выбрать порядок сортировки. Далее, Готово. Установить связь между таблицами. Теперь при просмотре таблицы Заказы, в поле Код товара вместо числового значения будет видно текстовое название товара. При изменении значения Кода товара в таблице Заказы (в режиме таблицы) надо щелкнуть в правой части ячейки столбца Код товара и в раскрывшемся списке выбрать нужный текст (например, “Стол”). Аналогично, при заполнении записи нового заказа.

Формы

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

Использование инструмента Форма для создания новой формы и ввода данных.

В области переходов (слева) щелкнуть на имени таблицы или запроса, которые должны отображаться в форме. На вкладке Создание-Формы щелкаем Форма (слева наверху). Access создаст форму (с отображением всех полей таблицы) и представит ее в режиме макета. Сохранить Форму.

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

Разделенная форма (слева на вкладке Создание-Формы) позволяет одновременно отображать данные в двух представлениях  — в режиме формы и в режиме таблицы. Данные можно добавлять, изменять или удалять в каждой из двух частей формы.

Создание форм с помощью Мастера форм. С помощью Мастера форм можно отобрать только часть нужных полей, указать способ группировки и сортировки данных, а также включить в форму поля из нескольких таблиц или запросов, при условии, что заранее заданы отношения между этими таблицами и запросами. На вкладке Создание-Формы щелкнуть на черном треугольнике Другие Формы (внизу посередине), выбрать Мастер форм. Следовать инструкциям на страницах Мастера форм. Для добавления к форме полей из нескольких таблиц или запросов не нажимайте кнопки Далее или Готово после выбора полей из первой таблицы или запроса на первой странице мастера форм. Повторите шаги для выбора другой таблицы или запроса и щелкните все дополнительные поля, которые требуется включить в форму. Для продолжения нажмите кнопку Далее или Готово. На последней странице нажмите кнопку Готово.

Отчеты

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

С помощью мастера: Создание\ Отчеты\Мастер отчетов. Указываем источник – таблицу, или запрос, сделанный по одной или нескольким связанным таблицам; выбираем нужные поля и внешний вид отчета. В отчете можно сгруппировать данные, для этого выбираем уровни группировки, их может быть несколько. Например, группировка по факультетам, а затем по специальности или наоборот, сначала по специальности, а затем по факультетам. Данные в отчете можно отсортировать (до 4 полей) в порядке возрастания или убывания.

В режиме конструктора Создание\ Отчеты\Конструктор отчетов. Начинающему пользователю лучше сделать отчет с помощью мастера, а затем в конструкторе его отредактировать.

Отчет можно перенести в файл MS Excel или в файл MS Word (используя меню Сервис  Связи с MS Office или соответствующую кнопку на панели инструментов).