Access 2007

Примечание

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

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

2.На вкладке Свойства таблицы задайте Условие на значение.

В условии на значение для таблицы можно использовать все уже известные вам ключевые слова. Обычно в условии для таблицы сравнивается несколько полей. Условие на значение [DateOrdered] < [DateShipped] гарантирует, что в ноле DateOrdered более ранняя дата, чем используемая в поле DateShipped.

При ссылке на поле в условии на значение для таблицы имена полей следует заключать в квадратные скобки. Таким образом, программа Access может установить разницу межу полями и функциями (например, функцией Date ( ), о которой вы узнали в разд. "Задание значений по умолчанию "далее в этой главе).

3.Задайте текст Сообщения об ошибке.

Это сообщение об ошибке выводится на экран, если условие не выполняется. Оно аналогично сообщению об ошибке для условия на значение поля.

161

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

Подсказка

После вставки условия на значение для таблицы вы, возможно, захотите закрыть Страницу свойств, чтобы увеличить свободное пространство в окне Конструктора, для этого выберите на ленте Работа с таблицами | Конструктор → Показать или скрыть →

Страница свойств.

Подстановки

В БД даже незначительные вариации могут создавать большие неприятности. Допустим, вы управляете компанией International Cinnamon, пекарней многонациональной сети, выпускающей булочки с корицей и имеющей сотни заказов в день. В таблице Orders у вас есть

следующие записи:
Quantity (количество)	Product (изделие)
10	Frosted Cinnamon Buns
24	Cinnamon Buns with Icing
16	Buns, Cinnamon (Frosted)
120	FCBs
…

(В данном примере другие поля, такие как столбец Код и сведения о клиенте, сделавшем заказ, опущены.)

Все заказы, приведенные в таблице, означают одно и то же: различные количества вкусных, покрытых сахарной глазурью булочек с корицей, Но текст в столбце Product слегка отличается. Эти отличия не создают проблем для простых смертных (например, вы без труда выполните эти заказы), если вы захотите в дальнейшем проанализировать характеристику реализации (sales performance), возникнут неприятности. У вас не будет возможности сообщить программе Access о том, что Frosted Cinnamon Bun и FCB — одно и то же, булочка с корицей, программа считает их разными изделиями. Если вы попытаетесь подсчитать наиболее популярные изделия или проверить долгосрочные тренды объемов продаж, у вас ничего не выйдет.

Примечание

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

Подстановки — еще одно средство, позволяющее стандартизировать ваши данные. Подстановки, в первую очередь, позволяют вставить значение в поле из подготовленного списка возможных вариантов. При надлежащем применении это средство решает проблему, возникшую в таблице Orders, — вам просто нужна подстановка, включающая все виды изделий, которые вы продаете. В этом случае вместо набора вручную названия изделия вы можете выбрать из списка Frosted Cinnamon Buns (глазированные булочки с корицей). Вы не только

162

экономите время, но и избегаете таких названий, как FCB, тем самым гарантируя непротиворечивость списка заказов.

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

Примечание

Подстановки не поддерживают следующие типы данных: Поле MEMO, Дата/время,

Денежный, Счетчик, Логический, Объект OLE, Гиперссылка и Вложение.

Создание простого списка подстановок, состоящего из констант

Простые списки подстановок имеют смысл, если у вас короткий простой список, не нуждающийся в частых корректировках. Отличным примером может служить список префиксов в адресе, обозначающих штаты. В данном случае это набор 50 двухбуквенных сокращений (AL, AK, AZ и т. д.).

Для опробования процесса создания списка, состоящего из перечисленных далее действий, можно воспользоваться таблицей Bachelors (холостяки), входящей в примеры к данной главе, размещенные в Интернете (см. файл БД DatingService.accdb). Можно перейти непосредственно

кконечному результату, просмотрев файл DatingServiceLookup.accdb.

1.Откройте таблицу в Конструкторе.

Если вы используете файл DatingService.accdb, откройте таблицу Bachelors.

2.Найдите поле, в которое нужно вставить список подстановок.

Втаблице Bachelors это поле State.

3.Убедитесь в том, что у поля корректный тип данных.

Подстановки применяются чаще всего для данных Текстового и Числового типов.

4.Выберите в списке типов данных Мастер подстановок. Это действие на самом деле не изменяет заданный тип данных. Оно лишь сообщает программе Access о том, что вы хотите запустить мастер Создание подстановки, базирующийся на текущем типе данных. Как только выбран описанный вариант, на экране появляется окно мастера Создание подстановки (рис. 4.17).

5.Выберите переключатель Будет введен фиксированный набор значений (I will type in the values that I want). В разд. "Поиск в связанных таблицах" главы 5 описывается другой вариант: список подстановок из другой таблицы.

6.Щелкните мышью кнопку Далее.

Вследующем окне Создание подстановки вы можете ввести список значений, которые следует использовать по одному в каждой строке (рис. 4.18). В данном случае вводится список сокращенных названий 50 американских штатов.

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

163

Рис. 4.17. Сначала вы выбираете источник для ваших подстановок: константы или данные из другой таблицы

Рис. 4.18. Этот список подстановок содержит сокращенные названия всех американских штатов. Он вряд ли будет меняться в ближайшем будущем, поэтому безопаснее хранить его как набор констант, а не в другой таблице

7.Щелкните мышью кнопку Далее.

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

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

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

164

В поле State нет смысла разрешать множественные значения — помимо всего прочего физически человек может обитать только в одном штате (не принимая во внимание квантовую телепортацию). Но можно придумать примеры, в которых наличие множественных значений вполне оправданно. Например, в таблице Products, используемой компанией International Cinnamon, подстановка множественных значений позволила бы нам сформировать заказ нескольких изделий. (Вы узнаете больше о выборе нескольких значений и связях между таблицами в главе 5.)

9. Щелкните мышью кнопку Готово.

Перейдите в Режим таблицы (щелкните правой кнопкой мыши заголовок вкладки и выберите Режим таблицы) и сохраните изменения. На рис. 4.19 показана подстановка в действии.

Рис. 4.19. Когда вы перейдете в поле со списком подстановок, то справа увидите стрелку, направленную вниз. Щелкните ее кнопкой мыши, и на экране появится раскрывающийся список со всеми введенными вами вариантами. Выберите один из них для вставки в поле

На профессиональном уровне.

Создание списка подстановки, использующего другую таблицу

В предыдущем примере (см. начало данного раздела) был создан список подстановок, сохранявшийся как один из параметров поля. Это хороший подход, но не лучшее решение. Гораздо эффективнее хранить список подстановок в отдельной таблице.

165

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

•Можно вставлять, редактировать и удалять элементы, просто корректируя таблицу подстановок. Даже если вам кажется, что у вас фиксированный неизменный набор значений, есть смысл подумать об отдельной таблице. Например, набор сокращенных названий штатов в предыдущем разделе, казалось бы, не требует изменений, но что, если служба знакомств выйдет на международный уровень и вам придется добавить канадские провинции в список?

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

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

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

Добавление новых значений в ваш список подстановок

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

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

иверификации, препятствующим вводу посторонних значений.

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

1.В Конструкторе перейдите в поле, содержащее список подстановок.

2.В области Свойства поля щелкните кнопкой мыши вкладку Подстановка.

На вкладке Подстановка представлены параметры для тонкой настройки вашего списка подстановки, большинство из которых легче задать в Мастере создания подстановки. В поле Источник строк (Row Source), например, можно откорректировать список предлагаемых вами значений. (Все значения расположены в одной строке, заключены в кавычки

иотделяются друг от друга точкой с запятой.)

3.Задайте значение Да в поле Ограничиться списком (Limit to List). Это действие защитит вас от ввода значений, не включенных в список.

166

1. Можете выбрать значение Да в поле Разрешить изменение списка значений (Value List Edits).

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

Рис. 4.20. ЕСЛИ задать значение поля Разрешить изменение списка значений, равным Да, во время применения списка подстановок появится пиктограмма (слева). Щелкните ее кнопкой мыши, и на экране откроется диалоговое окно Изменение элементов списков (справа), в котором можно откорректировать значения из списка подстановок и изменить значение по умолчанию

167

Глава 5

Связывание таблиц с помощью отношений

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

Допустим, вы намерены создать БД, способную управлять продажами в вашем магазине изделий из бисера, сделанных на заказ. Первая составляющая достаточно проста — таблица Products (изделия), в которой перечислены ваши товары. Но для продолжения вам придется собрать множество дополнительной информации. Проданные изделия из таблицы Products учитываются в таблице Orders (заказы). Товары из таблицы Orders посылаются по почте и фиксируются в таблице Shipments (поставки). Люди из таблицы Customers (клиенты) регистрируются в таблице Invoices (счета). Во всех этих таблицах — Products, Orders, Shipments, Customers и Invoices — содержатся порции связанной информации. В результате, если вы хотите получить ответ на обычный вопрос (например, "Сколько должна Джейн Мэлон (Jane Malone)?" или "Сколько париков из бисера продано на прошлой неделе?"), придется заглянуть в несколько таблиц.

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

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

Основы отношений между таблицами

Одна из основных целей любой БД — разбить информацию на четкие управляемые порции. В хорошо спроектированной БД процесс завершается созданием большого числа таблиц. Несмотря на то, что в каждой из них записано что-то свое, вам часто придется путешествовать из одной таблицы в другую для того, чтобы получить всю нужную информацию.

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

168

Избыточные данные в противоположность связанным

Вернемся к таблице Dolls, созданной в главе 1 для хранения списка кукол-болванчиков. Одна из порций информации данной таблицы — поле Manufacturer (изготовитель), в котором записано имя компании, изготовившей каждую куклу. Несмотря на то, что это достаточно простая деталь, может оказаться, что для точной оценки стоимости куклы-болванчика вам понадобится немного больше информации о процессе изготовления. Возможно, вы захотите узнать, где находится компания-изготовитель, как долго она существует и вынуждена ли отбиваться от судебных исков разъяренных покупателей.

Если вы ленивы, то можете вставить всю эту информацию в таблицу Dolls так, как показано в табл. 5.1 (затемненные столбцы — новые).

Таблица 5.1. Сведения об изготовителе

ID	Character	Manufacturer	Manufacturer-	Manufacturer-	Manu-	Purchase-Price
			Location	OpeningYear	facturer-
					Lawsuits
34	Yoda	MagicPlastic	China	2003	No	$8.99

В первый момент вас, возможно, обеспокоит загроможденность таблицы всеми этими полями. Не волнуйтесь — это жизнь, в таблицы должны быть включены все важные детали, поэтому они порой сильно разрастаются. (Это правило проектирования БД, описанное в разд. "Правило 3. Храпите все детали в одном месте" главы 2.) Пусть громоздкость вас не беспокоит. Можно воспользоваться такими средствами, как скрытие столбцов (см. разд. "Скрытие столбцов " главы 3) для устранения полей, которые вас не интересуют.

Несмотря на то что, что обилие столбцов — не повод для беспокойства, в этом примере кроется другая проблема — избыточные данные.

Подобная ситуация кажется невинной, но если добавить несколько новых строк, все окажется не столь безобидно (табл. 5.2).

Таблица 5.2. Сведения о производителе после добавления строк

ID	Character	Manufacturer	Manu-	Manufacturer-	Manufacturer-	Purchase-
			facturer-	OpeningYear	Lawsuits	Price
			Location
342	Yoda	MagicPlastic	China	2003	No	S8.99
343	Dick	Rebobbiicans	Taiwan	2005	No	S28.75
	Cheney
344	Tiger	MagicPlastic	China	2003	No	$2.99
	Woods

Как только у вас появятся две куклы-болванчика, изготовленные одной компанией (в данном случае MagicPlastic), вы введете дублирующиеся данные — беда всех плохих БД. (Их можно распознать как нарушение правила № 4 хорошего проекта БД, описанного в

169

разд. "Правило 4. Избегайте дублирования данных" главы 2.) Потенциальные проблемы нескончаемы.

■Если компания MagicPlastic переводит свои предприятия из Китая и Южную Корею, вам нужно обновлять целую группу записей о куклах-болванчиках. Если вы использовали две таблицы со связанной информацией (как вы увидите далее), вам придется бороться только с одной записью.

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

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

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

■Если же понадобится информация об изготовителях (а не о куклах), вы потерпите неудачу. Например, вам не удастся распечатать список всех изготовителей кукол-болванчиков в Китае (по крайней мере, не так легко, как хотелось бы).

Проблема вполне очевидна. Из-за желания хранить слишком много подробностей в одном месте в одной таблице объединяется информация, которую лучше всего хранить в двух отдельных таблицах. Для исправления этого проекта нужно создать две таблицы со связанными данными. Например, можно сформировать таблицу Dolls, как показано в табл. 5.3, и отдельную таблицу Manufacturers с подробными данными об изготовителях (табл. 5.4).

Таблица 5.3. Данные в таблице Dolls

ID	Character	Manufacturer	PurchasePrice
342	Yoda	MagicPlastic	$8.99
343	Dick Cheney	Rebobblicans	$28.75
344	Tiger Woods	MagicPlastic	$2.99

Таблица 5.4. Данные в таблице Manufacturers

ID	Manufacturer	Location	OpeningYear	Lawsuits
1	MagicPlastic	China	2003	No
2	Rebobbitcans	Taiwan	2005	No

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

Теперь если компания MagicPlastic переезжает в Южную Корею, вам нужно обновить данные поля Location только в одной записи, вместо множества экземпляров в перегруженной

170