Чтобы собрать данные из нескольких таблиц в одном запросе, форме или отчете, необходимо задать связи между таблицами. Связь между таблицами организуется на основе общего поля, причем в одной из таблиц оно обязательно должно быть ключевым, то есть на стороне «один» должно выступать ключевое поле, содержащее уникальные, неповторяющиеся значения. Значения на стороне «многие» могут повторяться.
Существует три типа возможных отношений между таблицами. Отношение «один-к-одному» означает, что в любой момент времени каждая записи в Таблице 1 соответствует не более одной записи в Таблице 2 и наоборот – каждой записи в Таблице 2 соответствует не более одной записи в Таблице 1. Отношения этого типа используются нечасто, поскольку обычно сведения, связанные таким образом, хранятся в одной таблице. Например, одному гражданину страны соответствует только один номер паспорта, в то же время один номер паспорта соответствует только одному человеку.
Рис. 7.6.3.1 Связь «один-к-одному»
Отношение «один-ко-многим» означает, что каждой записи в Таблице 1 соответствует одна или несколько записей в другой таблице, но каждой записи в Таблице 2 – не более одной записи в Таблице 1. Например, каждый студент относится к одной академической студенческой группе, но к этой же группе относятся также и другие студенты
Рис. 7.6.3.2. Связь «один-ко-многим»
При этом одна из таблиц считается главной, а другая – связанной. Главная – это та таблица, которая участвует в связи своим ключевым полем. Тип отношений между связываемыми таблицами Access распознает автоматически. Если установлен флажок Обеспечение целостности данных, со стороны главной таблицы над линией будет отображаться цифра 1, а со стороны подчиненной таблицы связи – символ бесконечности (∞).
Отношение «многие к одному» означает, что каждой группе записей в Таблице 1 может соответствовать не более одной записи в таблице 2, а любой записи Таблицы 2 могут соответствовать несколько записей Таблицы 1. Например, несколько студентов работают над одним общим проектом.
Рис. 7.6.3.3. Связь «многие-к-одному»
Отношение «многие-ко-многим» возникает, например, между преподавателями и группами студентов: преподаватель ведет занятия в нескольких группах, и у каждой группы ведут занятия несколько преподавателей, т.е. множеству записей в Таблице 1 соответствует множество записей в Таблице 2. Чтобы представить отношение «многие-ко-многим», нужно создать третью (связующую) таблицу, в которой отношение «многие-ко-многим» разбивается на два отношения «один-ко-многим».
Рис. 7.6.3.4. Связь «многие-ко-многим»
Механизм, который обеспечвивает согласованность данных между двумя связанными таблицами, называется поддержка целостности данных.
Целостность данных означает систему правил, используемых в Access для поддержания связей между записями в связанных таблицах, а также обеспечивает защиту от случайного удаления или изменения связанных данных. Установить целостность данных можно, если выполнены следующие условия:
1. Связанное поле главной таблицы является ключевым полем или имеет уникальный индекс.
2. Связанные поля имеют один тип данных.
3. Обе таблицы принадлежат одной БД Access.
Установив целостность данных, необходимо действовать в соответствии со следующими правилами.
1. Невозможно ввести в поле внешнего ключа связанной таблицы значение, не содержащееся в ключевом поле главной таблицы.
2. Не допускается удаление записи из главной таблицы, если существуют связанные с ней записи в подчиненной таблице.
3. Невозможно изменить значение ключевого поля в главной таблице, если существуют записи, связанные с данным значением.
Чтобы наложить эти правила на конкретную связь, при ее создании следует установить флажок Обеспечение целостности данных. Если данный флажок установлен, то любая попытка выполнить действие, нарушающее одно из перечисленных выше правил, приведет к выводу на экран предупреждения, а само действие выполнено не будет. Чтобы преодолеть ограничения на удаление или изменение связанных записей, сохраняя при этом целостность данных, следует установить флажки Каскадное обновление связанных полей и Каскадное удаление связанных записей. Если установлен флажок Каскадное обновление связанных полей, то при изменении ключевого поля главной таблицы автоматически изменяются и соответствующие значения связанных записей. Если установлен флажок Каскадное удаление связанных полей, то при удалении записи в главной таблице удаляются и все связанные записи в подчиненной таблице.
Рис. 7.6.3.5. Создание связей между таблицами
Каскадное обновление связанных полей означает, что при изменении значения ключевого поля в главной таблице автоматически будут изменены и соответствующие значения поля в связанных записях подчиненной таблицы.
Каскадное удаление связанных записей означает, что при удалении записи из главной таблицы будут автоматически удалены все связанные записи из подчиненной таблицыю
Пример
Пусть некая фирма занимается торговлей кондитерскими изделиями. Клиентами фирмы являются рестораны, кафе, клубы и т.п. для учета и анализа заказов фирма может вести таблицу с именем Заказы и со следующими полями (табл. 7.6.4.1)
Таблица 7.6.4.1.
| Имя поля | Тип поля |
| Номер накладной | Числовой (целое) |
| Код клиента | Числовой (целое) |
| Наименование клиента | Текстовый |
| Телефон | Текстовый |
| Дата создания фирмы | Дата/время |
| Код продукта | Числовой (целое) |
| Название продукта | Текстовый |
| Количество | Числовой (с плав. точкой.) |
| Дата поставки | Дата/время |
| Цена | Числовой (с плав. точкой.) |
| Стоимость | Числовой (с плав. точкой.) |
Каждая строка этой таблицы содержит полную информацию о конкретном заказе, а вся таблица в целом дает возможность не только вести учет, но и анализировать деятельность фирмы.
Таблицу Заказы мы спроектировали плохо. Например, мы включили в нее поля Телефон и дата создания фирмы, значения которых зависят от значения кода клиента, но не зависит от ключа нашей таблицы – номера накладной. Вводя многократно одни и те же данные (наименование клиента, его телефон и дату создания фирмы), мы не только проделаем массу лишней работы, но и неминуемо ошибемся (и не один раз). Поэтому следует удалить поля наименование клиента, телефон и ДАТА СОЗДАНИЯ ФИРМЫ из таблицы ЗАКАЗЫ, и включить их в классификатор (словарь) КЛИЕНТЫ, имеющий четыре поля – код, наименование, телефон клиента и дату создания фирмы.
Продолжив рассмотрение таблицы ЗАКАЗЫ, мы обнаружим еще три лишних поля – НАЗВАНИЕ ПРОДУКТА, ЦЕНА ПРОДУКТА, СТОИМОСТЬ. Значения первых двух полей не зависят от номера накладной, но зависят от кода продукта. Поэтому и место этих полей в классификаторе ПРОДУКТЫ (код, название, цена).
Значение поля СТОИМОСТЬ – это произведение цены на количество, поэтому его вообще не следует включать в таблицы: система обязана при необходимости просто вычислить стоимость заказа.
В результате из исходной таблицы ЗАКАЗЫ мы получили три таблицы: оперативную таблицу ЗАКАЗЫ, классификатор КЛИЕНТЫ и классификатор ПРОДУКТЫ. Этот процесс называется нормализацией.
Таблица КЛИЕНТЫ связана с таблицей ЗАКАЗЫ по полю Код клиента, а таблица ПРОДУКТЫ связана с таблицей ЗАКАЗЫ по полю Код продукта. В паре КЛИЕНТЫ – ЗАКАЗЫ первая таблица считается главной, а вторая – подчиненной. Поле Код клиента является для первой таблицы первичным ключом, а для второй таблицы – внешним ключом. Кроме того, вторая таблица имеет собственной первичный ключ (Номер накладной).
Легко видеть, что каждому значению первичного ключа в главной таблице соответствует одна, несколько или ни одной записи в подчиненной таблице. В самом деле, таблица ЗАКАЗЫ содержит перечень заказов какой-то группы клиентов за определенный промежуток времени. Ясно, что какой-то клиент за это время мог сделать и один, и несколько заказов, а мог и вообще не заказывать продукты.
Такое отношение между двумя таблицами называется связью «Один-ко-Многим».
Аналогичная связь наблюдается между таблицами ПРОДУКТЫ – ЗАКАЗЫ по полю Код продукта.
Рис.7.6.4.1. Окно Схема данных
7.6.5. Свойства полей базы данных.
При создании БД прежде всего возникает задача описания полей, образующих запись. Поля базы данных не просто определяют структуру базы — они еще определяют групповые свойства данных, записываемых в ячейки, принадлежащие каждому из полей. Ниже перечислены основные свойства полей таблиц баз данных.
Имя поля – определяет, как следует обращаться к данным этого поля при автоматических операциях с базой (по умолчанию имена полей используются в качестве заголовков столбцов таблиц). Длина имени не может превышать 64 символа. Целесообразно так именовать поля записи, чтобы они сообщали пользователю хотя бы какую-нибудь информацию о размещаемых в поле данных. В именах полей можно использовать любые комбинации букв, цифр, пробелов и других символов, за исключением символов «.», «!», «”», «[» и «]».
Тип поля – определяет тип данных, которые могут содержаться в данном поле.
Типы данных. Каждая таблица описывает некоторый класс объектов выбранной предметной области, например, студентов вуза или преподавателей, а каждая строка-запись содержит информацию о конкретном объекте (студенте или преподавателе). Каждый же столбец-поле описывает один из атрибутов данного объекта, например, должность или дату рождения. Поэтому все данные одного столбца характеризуются одинаковым типом – множеством допустимых значений и операций над ними. Чтобы задать тип данных для определенного поля, следует в режиме конструктора таблиц (рис. 1.3.13) щелкнуть левой кнопкой мыши в столбце Тип данных, после чего из раскрывающегося списка выбрать требуемый тип данных.
Рис.7.6.5.1. Типы данных в MS Access 2007
Ÿ Текстовый, длиной от 1 до 255 символов. Значение каждого текстового данного представлено совокупностью произвольных алфавитно-цифровых символов. Текстовыми данными являются ФИО людей. Названия фирм. Адреса и т.п. Количество символов определяется пользователем в процессе создания таблицы. По умолчанию СУБД устанавливает длину текстового поля равной 50;
Ÿ Числовой. Данные этого типа используюся для представления атрибутов, со значениями которых нужно осуществлять арифметические операции (цены, количества, коэффициенты и т. п.);
Ÿ Дата/время. Данные этого типа задаются в некотором известном формате, например, ДД.ММ.ГГ (день, месяц, год). С первого взгляда – это частный случай текстовогого данного. Однако использование особого типа для даты/времени имеет ряд преимуществ. Во-первых система получает возможность вести жесткий контроль (например. значение месяца может быть только дискретным в диапазоне 01-12). Во-вторых, появляется возможность представления формата даты в зависимости от традиций той или иной страны (например, в США принят формат даты ММ.ДД.ГГ). В-третьих резко появляется возможность осуществлять арифметические операции с датами (попробуйте, например, вручную вычислить дату спустя 87 дней после заданного числа).
Ÿ Денежный – используется для хранения денежных значений (валюты);
Ÿ Счетчик – используется для создания уникальных значений. Значения данного типа автоматически увеличиваются на 1 для каждой новой записи. Пользователь не может сам модифицировать значения поля данного типа;
Ÿ Логический тип данных удобно использовать для тех атоибутов, которые могут применять одно из двух взимоисключающих значений.
Ÿ Поле МЕМО (комментарий) – текстовое поле произвольной длины. У текстового поля есть недостаток, связанный с тем, что оно имеет ограниченный размер (не более 256 символов). Если нужно вставить в поле длинный текст, для этого служит поле типа MEMO. В нем можно хранить до 65 535 символов. Особенность поля MEMO состоит в том, что реально эти данные хранятся не в поле, а в другом месте, а в поле хранится только указатель на то, где расположен текст.
Ÿ Поле объекта OLE – используется для хранения объектов из других приложений Microsoft Windows (рафика, звук, видео);
Ÿ Гиперссылка – ссылка на файл (в данном компьютере, в локальной сети, в Интернете);
Ÿ Вложение – рисунки, изображения, двоичные файлы, файлы Microsoft Office);
Ÿ Мастер подстановок. Фактически типом данных не является. Используется для запуска мастера подстановок, с помощью которого можно создать поле, позволяющее выбрать значение из другой таблицы, запроса или списка значений, используя поле со списком.
Размер поля – определяет предельную длину (в символах) данных, которые могут размещаться в данном поле.
Рис. 7.6.5.2. Значения свойства Размер поля для типа данных Числовой
Формат поля – определяет способ форматирования данных в ячейках, принадлежащих полю.
Рис. 7.6.5.3. Значение свойства Формат поля для типов полей Числовой и Денежный
Для полей типа Дата/Время также можно задать свойство Формат поля. По умолчанию устанавливается Полный формат даты.
Рис. 7.6.5.4. Значение свойства Формат поля для полей типа Дата/Время
Логический тип предусматривает использование одного из следующих форматов в качестве свойства Формат поля: Истина/Ложь, Да/Нет, Вкл/Выкл. По умолчанию устанавливается формат Да/Нет.
Рис. 7.6.5.4. Значение свойства Формат поля для полей логического типа
Маска ввода – определяет форму, в которой вводятся данные в поле (средство автоматизации ввода данных). Маска ввода – это шаблон, позволяющий вводить в поле значения, имеющие одинаковый формат. Маска ввода автоматически изображает в поле определенные символы в некоторых позициях вводимой строки. При вводе данных в поле нет необходимости набирать эти символы, даже если они должны быть включены в значение поля – достаточно заполнить пустые позиции в маске ввода. Кроме того, Microsoft Access не позволит ввести в поле значения, не вписывающиеся в определенную для этого поля маску ввода.
Самым простым и ярким примером таких данных являются номера телефонов. Маска ввода должна обеспечить возможность вводить только цифры номера, а остальные символы (скобки вокруг кода города, дефис между цифрами номера) будут добавляться автоматически. Маска задается с помощью символов, приведенных в таблице 7.6.5.1.
Таблица 7.6.5.1
| обязательный ввод цифры; | |
| необязательный ввод цифры или пробела; | |
| # | ввод цифры, пробела, знаков + или – (если значение не введено, то эта позиция автоматически заполняется пробелом); |
| L | обязательный ввод буквы; |
| ? | необязательный ввод буквы или пробела; |
| A | обязательный ввод буквы или цифры; |
| a | необязательный ввод буквы, цифры или пробела; |
| & | обязательный ввод произвольного символа или пробела; |
| C | необязательный ввод произвольного символа или пробела; |
| < | все символы справа от текущей позиции преобразуются к нижнему регистру; |
| > | все символы справа от текущей позиции преобразуются к верхнему регистру. |
Подпись – определяет заголовок столбца таблицы для данного поля (если подпись не указана, то в качестве заголовка столбца используется свойство Имя поля).
Значение по умолчанию – то значение, которое вводится в ячейки поля автоматически (средство автоматизации ввода данных). Например, если в таблицу заносится город проживания студента и преобладает Калининград, то целесообразно именно его установить в качестве значения по умолчанию. При занесении другого города данное значение необходимо удалить.
Условие на значение – ограничение, используемое для проверки правильности ввода данных (средство автоматизации ввода, которое используется, как правило, для данных, имеющих числовой тип, денежный тип или тип даты). После перехода в данное поле необходимо раскрыть путем нажатия на кнопку 
окно, в котором формируется само условие с помощью указанных в нём операций. Например, можно установить условие на экзаменационные оценки в виде ограничения >1 And <6, что позволит вводить только цифры 2, 3, 4 и 5.
Сообщение об ошибке – текстовое сообщение, которое выдается автоматически при попытке ввода в поле ошибочных данных (проверка ошибочности выполняется автоматически, если задано свойство Условие на значение).
Обязательное поле – свойство, определяющее обязательность заполнения данного поля при наполнении базы. Установка в данном поле значения «Да»приводит к тому, что переход к новой записи не будет разрешен, пока пользователь не заполнит обязательное поле;
Пустые строки – свойство, разрешающее ввод пустых строковых данных (от свойства Обязательное поле отличается тем, что относится не ко всем типам данных, а лишь к некоторым, например к текстовым).
Индексированное поле – если поле обладает этим свойством, все операции, связанные с поиском или сортировкой записей по значению, хранящемуся в данном поле, существенно ускоряются. Кроме того, для индексированных полей можно сделать так, что значения в записях будут проверяться по этому полю на наличие повторов, что позволяет автоматически исключить дублирование данных.
Поскольку в разных полях могут содержаться данные разного типа, то и свойства у полей могут различаться в зависимости от типа данных.
Конструирование запросов
Запросы являются основным рабочим инструментом базы данных и могут выполнять множество различных функций. Самая распространенная функция запросов – извлечение из таблиц данных, удовлетворяющих заданным условиям Данные, которые необходимо просмотреть, как правило, находятся в нескольких таблицах; запросы позволяют представить их в одной таблице. Кроме того, поскольку обычно не требуется просматривать все записи сразу, с помощью запросов можно, задав ряд условий, «отфильтровать» только нужные записи. Часто запросы служат источником записей для форм и отчетов.
С помощью запросов выполняют такие операции как отбор данных, их сортировку и фильтрацию. С помощью запросов можно выполнять преобразование данных по заданному алгоритму, создавать новые таблицы, выполнять автоматическое наполнение таблиц данными, импортированными из других источников, выполнять простейшие вычисления в таблицах и многое другое. Начинающие пользователи не сразу понимают роль запросов, поскольку все те же операции можно делать и с таблицами. Да, действительно, это так, но есть соображения удобства (в первую очередь быстродействия) и соображения безопасности.
При внесении запроса ACCESS считывает данные из таблиц и отображает результат выполнения в режиме таблицы. В запросе ACCESS хранит только инструкции о том, как должны быть организованны данные в результате выполнения запроса.
При создании макета запроса в общем случае необходимо выполнить следующие базовые операции:
1. указать, какие поля и из каких таблиц мы хотим включить в запрос;
2. описать вычисляемые поля, т.е. поля, значения которых являются функциями значений существующих полей;
3. описать групповые операции над записями исходных таблиц.
В СУБД Microsoft Access для создания запросов используется язык SQL.
SQL (Structured Query Language – язык структурированных запросов) – универсальный компьютерный язык, предназначенный для манипулирования данными в реляционных базах данных, определения структуры баз данных и для управления правами доступа к данным в многопользовательской среде.
В предложении языка SQL можно описать все базовые операции: какие поля и откуда выбрать, какие вычислить, как их сгруппировать (просуммировать, найти среднее и т.п.) и при каких условиях включить записи в выборку. Однако в реальности пользоваться этим языком могут только специалисты (или очень грамотные пользователи).
Когда пользователь создает запрос в режиме Конструктора (Режим конструктора. Окно, в котором отображается макет следующих объектов базы данных: таблицы, запросы, формы, отчеты, макросы и страницы доступа к данным. В режиме конструктора пользователь создает новые объекты базы данных или изменяет макеты существующих.) запроса, Microsoft Access автоматически создает эквивалентную инструкцию SQL. При необходимости, пользователь имеет возможность просматривать и редактировать инструкции SQL в режиме SQL.
Например, запрос с вычисляемым полем для определения стоимости продукции в режиме Конструктора запросов выглядит так:
а на языке SQL – так:
А для обычных людей разработчики придумали упрощенный механизм создания запросов, называемый QBE (Query By Example – запрос по образцу). Вам предлагают бланк QBE – некую заготовку запроса, и на этом бланке, пользуясь определенными правилами, Вы сообщаете системе о своих планах: помечаете поля, вводите выражения, значения и т.п. На рис. 7.7.1 показан вид окна Access с запросом в режиме Конструктора.
В верхней части этого окна показана схема данных выбранных таблиц с указанием связей и имен всех полей. В нижней части окна размещается бланк QBE.
Рис.7.7.1. Окно создания запроса в режиме Конструктора.
В строке Поле: указываются имена столбцов (полей) создаваемого запроса. Существующее поле можно выбрать из раскрывающегося списка (щелкнув мышью на поле) или просто перетащить из нужной таблицы. В строке Сортировка: можно указать порядок вывода на экран записей (по возрастанию, по убыванию). В строке Вывод на экран: находятся флажки. Установив или сбросив флажок, мы разрешим или запретим вывод на экран. Если мы собираемся применять в запросе групповые операции (т.е. объединять записи в группы), нужно в меню Вид выбрать пункт Групповые операции – набланке появится строка Групповые операции:. В строках Условие Отбора: и или: можно указать условные или логические выражения, которые позволят отобрать для запроса только записи, удовлетворяющие заданному условию. Над строкой Поле находится зона выделения столбца.
Исходно бланк запроса пуст. Для занесения конкретного поля в бланк необходимо двойным щелчком левой клавиши мыши поместить его в первый свободный столбец бланка или перетащить его мышью (при нажатой левой клавише мыши).
Полная очистка бланка при его переформировании может быть выполнена командой глав
Запуск запроса на выполнение осуществляется нажатием кнопки 
на панели инструментов.
Сохранение запроса для дальнейшего использования производится командами главного меню ФайлÞСохранить как/Экспорт или нажатием кнопки 
(сохранить) на панели инструментов. Далее СУБД запросит имя сохраняемого запроса. Целесообразно, чтобы оно имело смысловую нагрузку, что облегчит дальнейшее использование запроса.
Существуют разные типы запросов.
1. Запрос на выборку. Это производная таблица, которая содержит те же структурные элементы, что и обычная таблица (столбцы-поля и строки-записи). Запрос на выборку отбирает данные из одной или более таблиц по заданным условиям, а затем отображает их в нужном порядке.
Самое главное в запросе – возможность использования критериев выборки, которые вводятся в строку Условие отбора. Можно выделить следующие типы запросов на основе критериев выборки:
1) Выборка по строгому совпадению. В строку Условие отбора для определенного поля вводится одно из значений, существующих в таблице. Например, название конкретного товара или название фирмы, города.
2) Выборка по строгому несовпадению. В этом случае в выборку отбираются все записи таблицы, кроме записей, содержащих значение, указанное в строке Условие отбора. Для реализации данного запроса перед значением вводится префикс Not или <>.
3) Выборка по неточному совпадению. Для выборки записей в условиях неполноты знаний о требуемых значениях используется оператор Like <условие>. Само <условие> образуется следующими подстановочными символами:
Ÿ? - любой один символ;
Ÿ * - любое количество символов (0 - ¥);
Ÿ # - любая одна цифра;
Ÿ [список_символов] - любой символ из списка;
Ÿ [!список_символов] - любой символ, не входящий в список;
4) Выборка по диапазону. Для формирования данных условий выбора используются операторы сравнения >, >=, <, <= и <>. Операции сравнения могут связываться логическими операциями And (И) и Or (ИЛИ). Для этих же целей используется оператор диапазона Between<нижнее_значение> and < верхнее_значение >.
Перечень значений в условии выборки можно задать и оператором In (значение, значение,...).
Для выбора записей с пустыми значениями в некотором поле надо в соответствующем поле бланка запроса указать оператор Is Null. Наоборот, записи с непустыми значениями в данном поле выбираются по оператору Is not Null.
Таблица 7.7.1
