Желательно, чтобы каждая таблица имела ключевое поле. Ключ однозначно определяет каждую запись в таблице; повторяющиеся значения ключа не допускаются. Связываться могут только таблицы, имеющие ключевые поля.
Для определения ключа выделяется, как правило, одно поле, и на панели инструментов нажимается кнопка "Ключ" с изображением ключа (логотип Access). Однако в ситуации, когда данные одного поля не могут быть уникальными для каждой записи, можно назначить ключевыми два или более полей.
Ключевые поля таблицы индексируются автоматически. Если при создании таблицы в качестве ключа не определено ни одно из полей, то Access предлагает создать поле «Счетчик», содержащее уникальный номер записи таблицы.
Для осуществления одновременной выборки данных из нескольких таблиц, их следует связать между собой. Связать можно только те таблицы, которые содержат поля с одинаковыми значениями. Имена этих полей могут не совпадать, однако обязательно должны совпадать типы данных, длины полей, а главное, значения. Связь устанавливается на схеме данных (рис.1). Схема данных строится в соответствии с информационно-логической моделью данных. При построении схемы данных Access автоматически определяет по выбранному полю тип связи между таблицами. Создание схемы данных позволяет упростить конструирование многотабличных форм, запросов и отчетов, а также обеспечить целостность взаимосвязанных данных.
Рис. 1. Схема данных
Access позволяет устанавливать связи следующих типов:
Связь один-к-одному, при которой одной записи из первой таблицы соответствует только одна запись из второй. Такая связь устанавливается для таблиц, которые могли бы быть объединены в одну, но разделены с целью ускорения работы.
Связь один-ко-многим используется для связи одной записи из первой таблицы с несколькими записями из второй таблицы.
Связь многие-к-одному указывает, что несколько записей одной таблицы связаны с одной записью другой таблицы.
Access позволяет устанавливать не только связи между таблицами, но и правила, определяющие условия взаимодействия между таблицами и поддерживающие целостность данных.
Одна из связанных таблиц является базовой, а вторая – подчиненной.
При создании связи таблиц определяется необходимость поддержания целостности данных. Если необходимо поддерживать целостность данных, то Access требует жесткого соответствия между базовыми и подчиненными записями (т.е. записей – «сирот» быть не может). При создании связи можно и не накладывать условие целостности данных, тогда в структуре базы могут присутствовать базовые записи без подчиненных и наоборот (т.е. записи – «сироты»).
Access создает связи определенного типа в зависимости от значений связанного поля базовой таблицы. Если связанное поле базовой таблицы не индексировано, то Access не может установить природу связи, и такая связь описывается, как «не определено».
Если связанное поле базовой таблицы индексировано, то тип связи определяется в соответствии со следующими правилами:
если связанное поле подчиненной таблицы содержит только уникальные значения, то имеет место связь типа «один-к-одному»;
если в подчиненной таблице содержатся повторяющие значения в связанном поле, то имеет место связь типа «один-ко-многим».
Схема данных отображается в окне "Схема данных", в котором таблицы представлены списками полей, а связи линиями между полями.
Для того, чтобы вызвать окно "Схема данных" можно выполнить команду СЕРВИС/Схема данных и щелкнуть по кнопке инструментальной панели с соответствующим названием.
Добавить таблицу на схеме данных можно щелчком правой клавиши и выбрать в контекстном меню пункт "Добавить таблицу". Или выполнить команду СВЯЗЬ/Добавить таблицу или щелкнуть по кнопке с таким названием на инструментальной панели "Связь".
В окне "Схема данных" отдельные таблицы можно скрывать и отображать.
Для установки связи между таблицами следует перетащить поле базовой таблицы в поле подчиненной таблицы. В появившемся окне «Связи» щелкнуть по кнопке «Создать».
Для устанавливаемой связи следует задать параметры.
В окне «Связи» можно изменить связь, если она была установлена неверно, выбрав в списках полей связываемых таблиц нужные поля.
Обычно следует включить поддержку целостности данных с помощью флажка «Обеспечение целостности данных». Если такая установка произведена, то становятся доступными параметры «Каскадное обновление связанных полей» и «Каскадное удаление связанных полей».
Если поле ключа в первичной таблице является полем связи между таблицами, то указанные опции должны быть выбраны для всех связей.
Если включена поддержка целостности данных в установленной связи, то на схеме линия типа 1 — ∞изображается более жирной.
Ввод данных в таблицы и их редактирование ведется в режиме таблицы. В режиме таблицы данные отображаются в строках и столбцах. Строки (записи) таблицы в зависимости от выполняемых операций помечаются маркерами:
► - маркер текущей операции;
! («карандаш») – маркер редактируемой записи;
Æ - маркер блокированной записи при работе в многопользовательском режиме;
*- маркер новой записи.
Последняя запись таблицы всегда пустая и помечена маркером новой записи.
Ввод данных осуществляется после установки курсора в поле таблицы. При вводе данных в поле осуществляется автоматическая проверка данных типов: числовой, денежный, дата/время, логический. Кроме того, можно установить дополнительные правила проверки вводимых данных в свойстве поля «Условие на значение». Для ввода в поле текущей записи значения того же поля предыдущей записи следует нажать комбинацию клавиш CTRL+’ (апостроф), для ввода текущей даты – CTRL+; для ввода текущего времени – CTRL+:.
Сохранение введенной записи выполняется автоматически при переходе к другой записи или при закрытии таблицы.
Настройка отображения записей в режиме таблицы
Access позволяет:
Изменить порядок следования полей в режиме таблицы. Для этого необходимо выделить столбец или группу столбцов и перетащить их в нужное место. Изменение порядка следования столбцов в режиме таблицы не вызывает изменения в структуре таблицы.
Изменить ширину поля. Это можно сделать командой ФОРМАТ/Ширина столбца или перетаскиванием правой границы столбца (указатель мыши должен принять вид крестика с двунаправленной стрелкой ¬½½®). Если дважды щелкнуть мышью у правой границы столбца, ширина столбца будет выровнена по значению поля.
Изменить высоту записи. Это действие можно выполнить командой ФОРМАТ/высота строки или перетаскиванием верхней границы строки таблицы. Установленная высота будет применена ко всем записям. Высота строки указывается в пунктах.
Изменить шрифт отображения данных с помощью команды ФОРМАТ/Шрифт.
Столбцы можно скрывать и отображать.
Сортировка записей
Если установлен первичный ключ, то записи таблицы автоматически упорядочиваются в соответствии со значением первичного ключа. Сортировка записей может выполняться по одному или нескольким полям. Для этого необходимо установить указатель в поле на уровне любой записи или выделить один или несколько столбцов и выполнить команду ЗАПИСИ/СОРТИРОВКА ПО ВОЗРАСТАНИЮ (ПО УБЫВАНИЮ) или воспользоваться кнопками панели инструментов.
Для восстановления первоначального порядка расположения записей следует выполнить команду ЗАПИСИ/УДАЛИТЬ ФИЛЬТР.
Фильтрация записей
Фильтрация – это способ быстрого отбора записей таблицы в соответствии с заданным критерием. Установить критерий отбора можно с помощью следующих команд:
фильтр по выделенному;
исключить выделение;
изменить фильтр;
расширенный фильтр.
Фильтр по выделенному отбирает записи таблицы на основании текущего выделения (выделяется значение поля). Фильтр обладает свойством накопления условий отбора. Условия добавляются одно к другому и образуют итоговый критерий, связывающий условия отбора логической функцией «И». Функция «Исключить выделение» позволяет произвести отбор всех записей, кроме тех, которые удовлетворяют критерию отбора по выделенному.
Команда «Изменить фильтр» позволяет сформировать критерий отбора, используя для формирования условий отбора значения полей и связывая условия отбора логической операцией «ИЛИ».
Команда «Расширенный фильтр» позволяет сформировать критерий отбора в окне команды, связывая условия отбора операциями «И» и «ИЛИ». В результате выполнения команды появится окно расширенного фильтра для формирования условия отбора (фактически запроса).
Команда «Применить фильтр» позволяет выполнить отбор записей на основании критерия отбора.
Запросы в Access предназначены для отбора данных, удовлетворяющих заданным критериям. Данные, полученные в результате выполнения запроса, называются динамическим набором данных. Динамический набор данных является временным (или виртуальным) набором записей и не хранится в базе данных. При закрытии запроса динамический набор данных этого запроса прекращает свое существование. В самом же запросе хранится структура запроса, в которой определены: таблицы, список полей, тип запроса, порядок сортировки, условия отбора записей и т.д.
Access позволяет создавать запросы двух типов: QBE – запросы и SQL – запросы.
QBE – запросы (Query By Example – запрос по образцу) строится с помощью конструктора в окне конструктора запросов.
SQL – запросы – запросы, представляющие собой последовательность операторов и функций языка SQL (Structured Query Language –структурированный язык запросов).
QBE – запросы в Access можно преобразовать в SQL – запрос.
Все запросы можно разделить на две группы: запросы на выборку и запросы – действия.
Access позволяет создать запрос двумя способами: с помощью конструктора или с помощью мастеров.
Для создания запросов имеются следующие мастера:
- Мастер создания простого запроса.
- Мастер создания перекрестного запроса.
- Мастер создания запросов на поиск повторяющихся записей.
- Мастер запросов на поиск записей без подчиненных записей.
Окно конструктора запроса состоит из двух частей: схемы данных и бланка запроса.
Схема данных – это место, где размещаются таблицы или запросы и устанавливаются связи между ними.
Бланк запроса представляет собой таблицу для определения полей и условий, которые будут использованы при создании динамического набора данных. Размер таблицы определяется количеством выбранных полей (столбцы) и видом запроса. Каждому виду запроса соответствует свой набор строк.
Выбор таблицы осуществляется в окне «Добавление таблицы», в котором отображаются все таблицы и запросы базы данных. Окно «Добавление таблицы» вызывается:
- при создании нового запроса конструктором;
- при выполнении команды ЗАПРОС/Добавить таблицу;
- при нажатии кнопки «Добавление таблицы» (кнопка с рисунком «таблица со знаком плюс»);
- щелчком правой кнопкой мыши по области схемы данных и выбором в контекстном меню команды Добавить таблицу.
Добавить таблицу в запросе можно также, перетащив значок таблицы из окна базы данных в окно конструктора с открытым запросом.
Удалить таблицу можно клавишей DEL или командой ЗАПРОС/Удалить таблицу– для предварительно выбранной таблицы.
Многотабличные запросы позволяют получить информацию нескольких таблиц, которые должны быть связаны. Создать связи в запросах можно следующим образом:
1. Предварительно перед созданием запроса создать связи. В этом случае связь таблиц в запросе будет установлена автоматически.
2. При создании связанных таблиц необходимо для связываемых полей задать одно имя и определить один тип, указав в одной из таблиц данное поле – ключевым. Связь будет установлена автоматически.
Создать связь можно в окне конструктора запросов. Созданные в окне конструктора запросов связи функционируют только в запросе. Кроме того, Access позволяет изменить в окне запросов установленные в схеме данных связи.
Запросы на выборку не изменяют содержимое базы данных, служат только для отображения данных, отвечающих заданным условиям. Запросы на выборку могут быть следующих видов:
- простой запрос на выборку;
- запрос с параметром;
- запрос с итогами;
- запрос перекрестный;
- запрос с вычисляемым полем;
Простой запрос на выборку предназначен для извлечения данных из одной или нескольких таблиц и отображения их в режиме таблицы.
Бланк простого запроса содержит шесть строк:
- имя поля;
- имя таблицы;
- сортировка;
- вывод на экран (указывает, будет ли поле присутствовать в динамическом наборе данных);
- условие отбора (содержит первое условие, ограничивающее набор данных);
- или (содержит другие условия ограничения данных).
Разработка простого запроса выполняется в несколько этапов:
- выбор таблицы;
- выбор полей (добавление полей в запрос);
- установление критериев отбора;
- задание порядка расположения записей (сортировка).
Перекрестный запрос вычисляет сумму, среднее значение, число элементов и значения других статистических функций, группируя данные и выводя их в компактном виде, напоминающем сводную электронную таблицу.
Перекрестный запрос создается с помощью соответствующего мастера или в конструкторе запросов. В бланке запроса указывается, значения каких полей будут использоваться в вычислениях или в качестве заголовков строк и столбцов.
Перекрестный запрос – это специальный тип группового запроса. Строка "Групповая операция" обязательно должна быть включена. В запросе обязательно должны быть установлены как минимум три параметра – поле заголовка строк, поле заголовка столбцов и поле для выбора значений. Поля, используемые в качестве строк и столбцов, должны содержать функцию "Группировка" в строке "Групповая операция". Для создания запроса необходимо выполнить следующие действия:
- создать новый запрос для таблицы (таблиц), включив в макет нужные поля;
- выполнить команду ЗАПРОС/Перекрестный;
- в строке "Перекрестная таблица" указать, какое поле используется в качестве заголовков строк, какое – в качестве заголовков столбцов и какое - для выполнения вычислений в соответствии с выбранной групповой операцией;
- в строке "Групповая операция" поля значений необходимо выбрать итоговую функцию.
Запрос с параметром - это запрос, при выполнении которого в диалоговом окне пользователю выдается приглашение ввести данные, например условие для возвращения записей или значение, которое должно содержаться в поле. Можно создать запрос, в результате которого выводится приглашение на ввод нескольких данных, например, двух дат. В результате будут возвращены все записи, находящиеся между указанными двумя датами.
Запросы с параметрами удобно использовать в качестве основы для создания форм и отчетов. Например, на основе запроса с параметрами можно создать месячный отчет о доходах. При выводе данного отчета, на экране появится приглашение ввести месяц, доходы которого интересуют пользователя. После ввода месяца на экране будет представлен требуемый отчет.
Чтобы создать запрос с параметром, необходимо в строку "Условия отбора" для заданного поля ввести текст приглашения для ввода данного, заключив его в прямоугольные скобки. Можно задать параметры для нескольких полей или для одного поля определить несколько параметров для отбора, используя запись условия в несколько строк совместно с логической операцией «ИЛИ».
Если в запрос вводится несколько параметров, то порядок их ввода через диалоговое окно определяется порядком расположения полей с параметром в бланке запроса.
Запрос с итогами позволяют производить выборку данных одновременно с их группировкой и вычислением групповых итогов с использованием различных статистических функций. В Access предусмотрено 9 статистических функций:
Sum- сумма значений некоторого поля для группы;
Avg - среднее значение некоторого поля для группы;
Max, Min - максимальное или минимальное значение поля для группы;
Count - число значений поля в группе (пустые значения поля не учитываются);
StDev - среднеквадратическое отклонение от среднего;
Var - дисперсия значений поля в группе;
First, Last - значение поля из первой или последней записи.
Для выполнения групповой операции необходимо в режиме конструктора включить в таблицу описания запроса строку "Групповая операция" и указать в том поле, то поле, по которому должна быть группировка, а в тех полях, по которым должно вестись вычисление заменить слово "группировка" на требуемую статистическую функцию.
Результат запроса с использованием групповых операций содержит по одной записи (строке) для каждой группы, исходные строки групп отсутствуют.
В запросах с итогами можно выполнять следующие операции:
1. Групповые операции над всеми записями. Для этого в полях указываются итоговые функции. Результат запроса представляет собой одну запись, заголовки столбцов соответствуют названию функции и имени поля.
2. Групповые операции для некоторых групп записей. Функция "Группировка" указывается для полей, по которым будут группироваться данные. В полях, по которым будут выполняться вычисления, должны быть выбраны соответствующие вычислениям функции.
3. Группировку записей, которые соответствуют условию отбора. Для этого необходимо указать условие отбора для поля, где выбрана функция "Группировка".
4. Вывод только тех результатов, которые удовлетворяют условию отбора. Для этого условие отбора задается для тех полей, по которым в строке "Групповая операция" выбрана итоговая функция (например, вывести те группы, средний балл студентов которых больше 4,5).
Запрос с вычисляемым полем позволяет произвести вычисления на основе данных, имеющихся в таблицах. В такой запрос вводятся новые дополнительные поля, содержащие формулы вычислений, причем эти поля создаются только в самом запросе, а не в таблицах базы данных.
Значенияновых вычисляемых полей определяются в соответствии с выражениями над числовыми, строковыми значениями или над значениями дат, которые выполняются для всех записей или для записей, удовлетворяющих условиям отбора.
Для ввода выражения следует создать новое вычисляемое поле непосредственно в бланке запроса.
Наряду с выводом результатов вычислений на экран допускается использование вычисляемых полей:
- для определения условий отбора записей в запросе или для определения записей, над которыми производятся действия;
- для обновления данных в запросе на обновление.
Вычисляемые поля позволяют:
-рассчитывать числовые значения и даты.
-комбинировать значения в текстовых полях.
-создавать подчиненные запросы.
-рассчитывать значения итоговых полей с помощью групповых функций.
Для вычисляемых полей допускается сортировка, задание условий отбора и расчет итоговых значений, как и для любых других полей. Для расчета итоговых значений следует выбрать в ячейке строки "Групповая операция" элемент "Выражение".
Выражение – это набор элементов, объединенных операторами. Выражения могут задавать условия отбора в запросах. При этом Access вычисляет выражение при каждом его использовании, т.е. при каждом выполнении отбора.
Выражения могут содержать следующие элементы: константы, литералы, функции, имена объектов.
Константы – это величины, которые не могут быть изменены: Yes, No, Null, False, True.
Литералы – это фактические значения, которые водятся в виде чисел, текстовых строк или дат. Они используются в том виде, как их ввели. Числовой литерал – это обычное число; текстовый литерал – это последовательность символов, заключенных в кавычки; литералы даты и времени – это значения, заключенные в # ... #.
Ввод данных в вычисляемые поля или их изменение в режимах таблицы или формы невозможен. При изменении значений полей, используемых в выражении, значение вычисляемого поля обновляется автоматически.
Формула, помещаемая в вычисляемое поле, всегда начинается со знака равенства (=).
