Создание связи один ко многим. Создание, изменение и удаление отношения

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

Второй предложенный способ - вставлять листы Excel непосредственно в документ, - приводит к неоправданному «раздуванию» файла, кроме того, внедренные объекты иногда ведут себя непредсказуемо.

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

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

Создаем в Word точно такую же, но пустую таблицу:

Заголовки не набираем, а специально оставляем пустые строки.

После проделанных операций, на экране должно быть открыты оба окна: окно MS Word с заготовкой и окно MS Excel с расчетной таблицей. Можем приступать к связыванию. Суть его состоит в том, чтобы последовательно, одна за другой, связать ячейки таблицы с необходимыми позициями в текстовом документе.

Для этого:

Перейдите на лист Еxcel и сделайте текущей ячейку, которую необходимо связать с документом (начнем с В1).
Выполняем копирование ячейки (меню Правка-Копировать либо сочетание клавиш Ctrl+C ).
Переходим в окно с документом Word и помещаем курсор в позицию для вставки. В данном случае это будет заголовок, расположенный через одну строку над таблицей.
В меню Word выбираем Правка-Специальная вставка . В результате откроется диалоговое окно Специальная вставка , где в области Источник (в левом верхнем углу) указаны координаты выделенной ячейки:
Устанавливаем переключатель в положение Связать .
В списке Как выбираем Неформатированный текст .
Щелчком по кнопке ОК закрываем окно.

Таким образом мы установили связь между текстовой позицией в документе и ячейкой в рабочем листе. Необходимо сразу отметить, что с объединенными ячейками связывание работает не совсем корректно. Аналогично необходимо связать оставшиеся ячейки. Окончательно документ должен выглядеть так:

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

Режим обновления также можно настраивать. Для этого необходимо:

Выбрать любое вставленное значение в Word и нажать правую кнопку мыши:
В контекстном меню выбрать Связанный объект Лист-Связи .
В открывшемся окне можно выбирать Способ обновления связи:

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

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

Очень многие в своей работе сталкиваются с необходимостью формировать отчёты, сводки, обобщающие документы на основе каких-то вычислений, расчетов, таблиц с исходными данными и их анализа. И часто для выполнения этих задач приходится прибегать к комбинации таких программных продуктов как Word (где формируется итоговый отчет для дальнейшей распечатки или пересылки кому-либо) и Excel (в котором собираются и обрабатываются исходные данные для отчета).

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

Создание связи между Word и Excel

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

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

Выбор способа вставки данных

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

Кроме того, важно отличать связанные объекты от внедренных. Внедрение - операция, которая не рассматривается в этой статье. Суть ее в том, что в документ Word внедряется объект Excel, который позволяет работать с данными в табличном процессоре прямо из документа Word. При этом исходный файл и текстовый документ никак не связаны. Внедрение используется редко, так как сильно увеличивает размер файла.

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

Бывают ситуации, когда приходится вставлять не всю таблицу, а данные из отдельных ячеек. Например, когда нужно вставить в предложение какую-то цифру или текст, которые есть в файле Excel и будут изменяться, или же когда таблица в Word составляется из нескольких разрозненных ячеек Excel (из разных таблиц, листов или даже книг). В таких случаях копируем данные ячеек в нужное место и выбираем способ вставки «Неформатированный текст» .

Настройка способа обновления связей и работа с диспетчером связей

Вставленные связи можно обновлять несколькими способами. Например, если на вставленном объекте кликнуть правой кнопкой мыши, то в меню будет кнопка «Обновить связь», нажатие которой приведет к обновлению выбранной связи.

Можно задать параметры обновления сразу для всех объектов. Для этого нужно вызвать диспетчер связей документа. Проходим по пути «Файл»-«Сведения»-«Изменить связи с файлами» (данная команда доступна, когда в документе есть как минимум одна связь, а сам документ сохранен).

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

Работа со связями через коды полей Word

По своей сути каждая связь представляет собой специальное поле Word. Если в документе со связями Вы нажмете ALT+F9 , то увидите, что вместо вставленных данных отобразятся коды этих полей.

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

1) Изменение ссылок на документы сразу во всех связях

Связи с исходными документами осуществляются через прямые ссылки. Если Вы переложили исходный файл, то нужно будет переподтянуть все связи. В диспетчере связей придется делать это для каждой связи отдельно (даже если выбрать несколько - диспетчер будет по очереди предлагать заменять каждую ссылку). Если же вы перейдете в режим отображения кодов полей (ALT+F9 ), то старые ссылки можно поменять на новые простой заменой (CTRL+H или «Главная-Заменить» ), указав старый адрес и новый.

2) Использование именованных диапазонов для вставки таблиц

Если вы вставляете в Word таблицу из Excel, то ссылка на нее будет содержать конкретный диапазон ячеек. То есть, если вы добавите в таблицу строки или столбцы - то данные обновятся только в пределах жестко указанного диапазона. Диспетчер связей позволяет изменить диапазон вручную. Автоматическое же изменение можно настроить, задав в Excel именованный динамический диапазон (урок здесь и здесь, не разберетесь - пишите нашему боту в Telegram , поможем), а в файле Word в режиме правки кодов полей после этого нужно будет заменить прямые ссылки на ячейки на название именованного диапазона. Теперь при изменении размера исходной таблицы в Excel документ в Word обновится корректно.

Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @KillOfBot

С уважением, команда tDots.ru

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

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

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

Создание связей между рабочими книгами

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

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

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

Прежде чем создавать связи между таблицами

Прежде чем вы начнете распространять знания на свои грандиозные идеи, прочитайте несколько советов по работе со связями в Excel:

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

Автоматические вычисления. Исходная книга должна работать в режиме автоматического вычисления (установлено по умолчанию). Для переключения параметра вычисления перейдите по вкладке Формулы в группу Вычисление. Выберите Параметры вычислений –> Автоматически.

Обновление связей

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

В появившемся диалоговом окне Изменение связей, Обновить.

Разорвать связи в книгах Excel

Разрыв связи с источником приведет к замене существующих формул связи на значения, которые они возвращают. Например, связь =Цены!$B$4 будет заменена на 16. Разрыв связи нельзя отменить, поэтому прежде чем совершить операцию, рекомендую сохранить книгу.

Перейдите по вкладке Данные в группу Подключения . Щелкните по кнопке Изменить связи. В появившемся диалоговом окне Изменение связей, выберите интересующую вас связь и щелкните по кнопке Разорвать связь.

Работа с базами данных - процесс, требующий определённых навыков и понимания некоторых нюансов. Разработанная компанией Microsoft программа Access даёт пользователям возможность быстро создавать и редактировать БД. Один из важнейших моментов, с которым обязательно нужно разобраться, при создании БД - связывание её элементов. В этой статье разберёмся с тем, как создать или удалить связи между таблицами в Access. Давайте же начнём. Поехали!

Прежде чем мы перейдём к связыванию, необходимо создать так называемые ключевые поля. Что это такое? Ключевое поле - это поле, определяющее каждую запись. Теперь перейдём непосредственно к его созданию. Открыв нужную таблицу , щёлкните правой кнопкой мыши по вкладке и выберите пункт «Конструктор». Затем добавьте новое поле. Например, если у вас есть список сотрудников, то ключевым стоит сделать «код сотрудника», указав тип данных «счётчик» либо «числовой». Чтобы сделать его ключевым, нажмите кнопку «Ключевое поле», расположенную на панели инструментов Microsoft Access. Далее, необходимо проделать то же самое для всех остальных таблиц.

Теперь уже можно связывать наши таблицы . Чтобы сделать это, откройте вкладку «Работа с базами данных» и в разделе «Отношения» ленты инструментов выберите пункт «Схема данных». Перед вами откроется окно, в котором понадобится выбрать все таблицы. Поочерёдно кликните по каждой из них. Закончив, выйдите из этого окна. Теперь нужно определиться с тем, как именно связывать. Например, есть две таблицы: «Бренд» и «Одежда», между которыми необходимо связать между собой. Поле «Код бренда» должно быть и там, и там. Если оно отсутствует - создайте его. При этом для записей категории «Бренд» оно будет ключевым (так называемый первичный ключ), а для «Одежда» - обычным (внешний ключ). Первичный ключ «Код бренда» перетащите на внешний ключ «Код бренда». После этого появится диалоговое окно, в котором нужно отметить галочками пункты: «Обеспечение целостности…», «Каскадное обновление связанных полей» и «Каскадное удаление связанных записей». Отметив все пункты, нажмите кнопку «Создать». Появится чёрная линия, соединяющая указанные строки. Созданная связь имеет название «один ко многим». То есть в приведённом примере это будет означать, что один бренд может выпускать много разной одежды.

Далее, аналогичным образом свяжите все остальные, не забывая добавлять поля. Имея минимум три таблицы (среди которых две основных и одна промежуточная), вы можете создать тип связи «многие ко многим». Для заполнения полей, являющихся внешними ключами, можно использовать «Мастер подстановок». Чтобы сделать это, перейдите в режим конструктора и в разделе «Тип данных» выберите пункт «Мастер подстановок». В открывшемся окне выберите откуда будет взята информация для заполнения. Затем укажите нужные поля и нажмите кнопку «Далее». Следующие два окна можно пропустить. В конце кликните по кнопку «Готово». Прежде чем начать заполнение, программа предложит вам сохраниться, щёлкните «Да». Обратите внимание, что заполнение таким способом возможно только в случае, если связь ещё не установлена. Если у вас так не получилось, то вы можете удалить её и заполнить ячейки . После этого она автоматически появится, однако, вам понадобится кликнуть по ней два раза и отметить галочками пункты: «Обеспечение целостности…», «Каскадное обновление связанных полей» и «Каскадное удаление связанных записей».

Чтобы удалить связь, кликните по кнопке «Схема данных» на панели инструментов, затем щёлкните по линии, показывающей её, и нажмите на клавиатуре клавишу delete.

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

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

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

В таблице "Заказы" указаны заказы, сделанные разными клиентами из таблицы "Клиенты". Каждый клиент мог сделать несколько заказов.

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

Каждый продукт может входить в один заказ как в одном, так и в нескольких экземплярах.

Например, в заказ Арины Ивановой № 1012 могут входить продукты № 12 и 15, а также пять продуктов № 30.

Создание связи "многие-ко-многим"

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

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

Ниже рассмотрим пример, когда в заказ Арины Ивановой № 1012 входят продукты № 12, 15 и 30. Это значит, что записи в таблице "Сведения о заказах" выглядят следующим образом:

Создание промежуточной таблицы

Создание полей в промежуточной таблице

Столбец "Код" автоматически добавляется в Access в качестве первого. Измените имя этого поля на идентификатор вашей первой таблицы в связи "многие-ко-многим". Например, если первая таблица называется "Заказы", поле "Код" в ней переименовано в "Номер заказа", и его первичный ключ - число, измените имя поля "Код" в новой таблице на "Номер заказа", а в качестве типа данных выберите Числовой .

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

Выберите переименованное поле.

На вкладке Поля в списке Тип данных выберите тип, как в соответствующем поле исходной таблицы, например Числовой или Короткий текст .

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

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

Объединение полей для создания первичного ключа

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

Соединение трех таблиц для создания связи "многие-ко-многим"

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

После этого связи должны выглядеть следующим образом:

Вам нужны дополнительные возможности?

Отношения «многие ко многим» применяются, когда один элемент таблицы А может быть связан со множеством элементов таблицы Б и наоборот. Например, товар может входить во множество заказов, а заказ может содержать множество товаров.

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

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

Чтобы создать отношение «многие ко многим», сначала нужно создать еще одну таблицу.

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

Промежуточная таблица также называется связующей.

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

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

Так как связующая таблица создается в режиме конструктора , открываем вкладку Создание и нажимаем кнопку Конструктор таблиц . Вот наша новая, пустая таблица.

Теперь откроем таблицы «Товары» и «Заказы».

Взглянем на первичные ключи обеих таблиц. В таблице «Товары» используется поле «ИД товара» с типом данных Счетчик .

Первичный ключ для таблицы «Заказы» называется «ИД заказа». Он также имеет тип данных Счетчик .

Теперь возвращаемся к связующей таблице, вводим имя поля «ИД товара», выбираем тип данныхЧисловой , вводим имя «ИД заказа» и снова выбираем тип данных Числовой .

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

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

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

Теперь мы можем создать отношения. Для начала закройте все открытые таблицы. Выберите Работа с базами данных > Схема данных .

На вкладке Конструктор выберите элемент Добавление таблицы . Добавьте таблицы «Сведения о заказе», «Заказы» и «Товары». Закройте диалоговое окно.

Разместите таблицы так, чтобы связующая таблица «Сведения о заказе» была посередине. Перетащите поле «ИД заказа» из таблицы «Заказы» в связующую таблицу.

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

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

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

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

В окне открытого листа выделите ячейку с исходным значением.
Используйте любой известный способ копирования (кнопка «Копировать» на вкладке «Главная», Ctrl+C и т. д.).
Выделите ячейку, которая будет связанна с исходной.
Перейдите к вкладке «Главная» и в группе «Буфер обмена» раскройте меню кнопки «Вставить».
В списке команд выберите пункт «Вставить связь» (рис. 4.4).
Закрепите результат щелчком по клавише Esc.

Примечание

Дальнейшее изменения значений в исходной ячейке будут автоматически отображаться в связанной ячейке.

Рис. 4.4. Вкладка «Главная». Меню кнопки «Вставить». Пункт «Вставить связь»

Создание связей между рабочими книгами

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

Прежде чем создавать связи между таблицами

Обновление связей

В появившемся диалоговом окне Изменение связей, Обновить.

Разорвать связи в книгах Excel

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

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

Создание связанных таблиц

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

Способ 1: прямое связывание таблиц формулой

Самый простой способ связывания данных – это использование формул, в которых имеются ссылки на другие табличные диапазоны. Он называется прямым связыванием. Этот способ интуитивно понятен, так как при нем связывание выполняется практически точно так же, как создание ссылок на данные в одном табличном массиве.

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

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

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

На первом листе выделяем первую ячейку столбца «Ставка». Ставим в ней знак «=». Далее кликаем по ярлычку «Лист 2», который размещается в левой части интерфейса Excel над строкой состояния.
Происходит перемещения во вторую область документа. Щелкаем по первой ячейке в столбце «Ставка». Затем кликаем по кнопке Enter на клавиатуре, чтобы произвести ввод данных в ячейку, в которой ранее установили знак «равно».
Затем происходит автоматический переход на первый лист. Как видим, в соответствующую ячейку подтягивается величина ставки первого сотрудника из второй таблицы. Установив курсор на ячейку, содержащую ставку, видим, что для вывода данных на экран применяется обычная формула. Но перед координатами ячейки, откуда выводятся данные, стоит выражение «Лист2!», которое указывает наименование области документа, где они расположены. Общая формула в нашем случае выглядит так:
Теперь нужно перенести данные о ставках всех остальных работников предприятия. Конечно, это можно сделать тем же путем, которым мы выполнили поставленную задачу для первого работника, но учитывая, что оба списка сотрудников расположены в одинаковом порядке, задачу можно существенно упростить и ускорить её решение. Это можно сделать, просто скопировав формулу на диапазон ниже. Благодаря тому, что ссылки в Excel по умолчанию являются относительными, при их копировании происходит сдвиг значений, что нам и нужно. Саму процедуру копирования можно произвести с помощью маркера заполнения.
Итак, ставим курсор в нижнюю правую область элемента с формулой. После этого курсор должен преобразоваться в маркер заполнения в виде черного крестика. Выполняем зажим левой кнопки мыши и тянем курсор до самого низа столбца.
Все данные из аналогичного столбца на Листе 2 были подтянуты в таблицу на Листе 1. При изменении данных на Листе 2 они автоматически будут изменяться и на первом.

Способ 2: использование связки операторов ИНДЕКС - ПОИСКПОЗ

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

Выделяем первый элемент столбца «Ставка». Переходим в Мастер функций, кликнув по пиктограмме «Вставить функцию».
В Мастере функций в группе «Ссылки и массивы» находим и выделяем наименование «ИНДЕКС».
Данный оператор имеет две формы: форму для работы с массивами и ссылочную. В нашем случае требуется первый вариант, поэтому в следующем окошке выбора формы, которое откроется, выбираем именно его и жмем на кнопку «OK».
Выполнен запуск окошка аргументов оператора ИНДЕКС. Задача указанной функции - вывод значения, находящегося в выбранном диапазоне в строке с указанным номером. Общая формула оператора ИНДЕКС такова:
ИНДЕКС(массив;номер_строки;)

«Массив» - аргумент, содержащий адрес диапазона, из которого мы будем извлекать информацию по номеру указанной строки.

«Номер строки» - аргумент, являющийся номером этой самой строчки. При этом важно знать, что номер строки следует указывать не относительно всего документа, а только относительно выделенного массива.

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

Ставим курсор в поле «Массив». После этого переходим на Лист 2 и, зажав левую кнопку мыши, выделяем все содержимое столбца «Ставка».
После того, как координаты отобразились в окошке оператора, ставим курсор в поле «Номер строки». Данный аргумент мы будем выводить с помощью оператора ПОИСКПОЗ. Поэтому кликаем по треугольнику, который расположен слева от строки функций. Открывается перечень недавно использованных операторов. Если вы среди них найдете наименование «ПОИСКПОЗ», то можете кликать по нему. В обратном случае кликайте по самому последнему пункту перечня – «Другие функции…».
Запускается стандартное окно Мастера функций. Переходим в нем в ту же самую группу «Ссылки и массивы». На этот раз в перечне выбираем пункт «ПОИСКПОЗ». Выполняем щелчок по кнопке «OK».
Производится активация окошка аргументов оператора ПОИСКПОЗ. Указанная функция предназначена для того, чтобы выводить номер значения в определенном массиве по его наименованию. Именно благодаря данной возможности мы вычислим номер строки определенного значения для функции ИНДЕКС. Синтаксис ПОИСКПОЗ представлен так:
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;)

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

«Просматриваемый массив» - аргумент, представляющий собой ссылку на массив, в котором выполняется поиск указанного значения для определения его позиции. У нас эту роль будет исполнять адрес столбца «Имя» на Листе 2.

«Тип сопоставления» - аргумент, являющийся необязательным, но, в отличие от предыдущего оператора, этот необязательный аргумент нам будет нужен. Он указывает на то, как будет сопоставлять оператор искомое значение с массивом. Этот аргумент может иметь одно из трех значений: -1; 0; 1. Для неупорядоченных массивов следует выбрать вариант «0». Именно данный вариант подойдет для нашего случая.

Итак, приступим к заполнению полей окна аргументов. Ставим курсор в поле «Искомое значение», кликаем по первой ячейке столбца «Имя» на Листе 1.
После того, как координаты отобразились, устанавливаем курсор в поле «Просматриваемый массив» и переходим по ярлыку «Лист 2», который размещен внизу окна Excel над строкой состояния. Зажимаем левую кнопку мыши и выделяем курсором все ячейки столбца «Имя».
После того, как их координаты отобразились в поле «Просматриваемый массив», переходим к полю «Тип сопоставления» и с клавиатуры устанавливаем там число «0». После этого опять возвращаемся к полю «Просматриваемый массив». Дело в том, что мы будем выполнять копирование формулы, как мы это делали в предыдущем способе. Будет происходить смещение адресов, но вот координаты просматриваемого массива нам нужно закрепить. Он не должен смещаться. Выделяем координаты курсором и жмем на функциональную клавишу F4. Как видим, перед координатами появился знак доллара, что означает то, что ссылка из относительной превратилась в абсолютную. Затем жмем на кнопку «OK».
Результат выведен на экран в первую ячейку столбца «Ставка». Но перед тем, как производить копирование, нам нужно закрепить ещё одну область, а именно первый аргумент функции ИНДЕКС. Для этого выделяем элемент колонки, который содержит формулу, и перемещаемся в строку формул. Выделяем первый аргумент оператора ИНДЕКС (B2:B7) и щелкаем по кнопке F4. Как видим, знак доллара появился около выбранных координат. Щелкаем по клавише Enter. В целом формула приняла следующий вид:
ИНДЕКС(Лист2!$B$2:$B$7;ПОИСКПОЗ(Лист1!A4;Лист2!$A$2:$A$7;0))
Теперь можно произвести копирование с помощью маркера заполнения. Вызываем его тем же способом, о котором мы говорили ранее, и протягиваем до конца табличного диапазона.
Как видим, несмотря на то, что порядок строк у двух связанных таблиц не совпадает, тем не менее, все значения подтягиваются соответственно фамилиям работников. Этого удалось достичь благодаря применению сочетания операторов ИНДЕКС-ПОИСКПОЗ.

Способ 3: выполнение математических операций со связанными данными

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

Посмотрим, как это осуществляется на практике. Сделаем так, что на Листе 3 будут выводиться общие данные заработной платы по предприятию без разбивки по сотрудникам. Для этого ставки сотрудников будут подтягиваться из Листа 2, суммироваться (при помощи функции СУММ) и умножаться на коэффициент с помощью формулы.

Выделяем ячейку, где будет выводиться итог расчета заработной платы на Листе 3. Производим клик по кнопке «Вставить функцию».
Следует запуск окна Мастера функций. Переходим в группу «Математические» и выбираем там наименование «СУММ». Далее жмем по кнопке «OK».
Производится перемещение в окно аргументов функции СУММ, которая предназначена для расчета суммы выбранных чисел. Она имеет нижеуказанный синтаксис:
СУММ(число1;число2;…)

Поля в окне соответствуют аргументам указанной функции. Хотя их число может достигать 255 штук, но для нашей цели достаточно будет всего одного. Ставим курсор в поле «Число1». Кликаем по ярлыку «Лист 2» над строкой состояния.
После того, как мы переместились в нужный раздел книги, выделяем столбец, который следует просуммировать. Делаем это курсором, зажав левую кнопку мыши. Как видим, координаты выделенной области тут же отображаются в поле окна аргументов. Затем щелкаем по кнопке «OK».
После этого мы автоматически перемещаемся на Лист 1. Как видим, общая сумма размера ставок работников уже отображается в соответствующем элементе.
Но это ещё не все. Как мы помним, зарплата вычисляется путем умножения величины ставки на коэффициент. Поэтому снова выделяем ячейку, в которой находится суммированная величина. После этого переходим к строке формул. Дописываем к имеющейся в ней формуле знак умножения (*), а затем щелкаем по элементу, в котором располагается показатель коэффициента. Для выполнения вычисления щелкаем по клавише Enter на клавиатуре. Как видим, программа рассчитала общую заработную плату по предприятию.
Возвращаемся на Лист 2 и изменяем размер ставки любого работника.
После этого опять перемещаемся на страницу с общей суммой. Как видим, из-за изменений в связанной таблице результат общей заработной платы был автоматически пересчитан.

Способ 4: специальная вставка

Связать табличные массивы в Excel можно также при помощи специальной вставки.

Выделяем значения, которые нужно будет «затянуть» в другую таблицу. В нашем случае это диапазон столбца «Ставка» на Листе 2. Кликаем по выделенному фрагменту правой кнопкой мыши. В открывшемся списке выбираем пункт «Копировать». Альтернативной комбинацией является сочетание клавиш Ctrl+C. После этого перемещаемся на Лист 1.
Переместившись в нужную нам область книги, выделяем ячейки, в которые нужно будет подтягивать значения. В нашем случае это столбец «Ставка». Щелкаем по выделенному фрагменту правой кнопкой мыши. В контекстном меню в блоке инструментов «Параметры вставки» щелкаем по пиктограмме «Вставить связь».
Существует также альтернативный вариант. Он, кстати, является единственным для более старых версий Excel. В контекстном меню наводим курсор на пункт «Специальная вставка». В открывшемся дополнительном меню выбираем позицию с одноименным названием.
После этого открывается окно специальной вставки. Жмем на кнопку «Вставить связь» в нижнем левом углу ячейки.
Какой бы вариант вы не выбрали, значения из одного табличного массива будут вставлены в другой. При изменении данных в исходнике они также автоматически будут изменяться и во вставленном диапазоне.

Урок: Специальная вставка в Экселе

Способ 5: связь между таблицами в нескольких книгах

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

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

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

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

Разрыв связи между таблицами

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

Способ 1: разрыв связи между книгами

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

В книге, в которой подтягиваются значения из других файлов, переходим во вкладку «Данные». Щелкаем по значку «Изменить связи», который расположен на ленте в блоке инструментов «Подключения». Нужно отметить, что если текущая книга не содержит связей с другими файлами, то эта кнопка является неактивной.
Запускается окно изменения связей. Выбираем из списка связанных книг (если их несколько) тот файл, с которым хотим разорвать связь. Щелкаем по кнопке «Разорвать связь».
Открывается информационное окошко, в котором находится предупреждение о последствиях дальнейших действий. Если вы уверены в том, что собираетесь делать, то жмите на кнопку «Разорвать связи».
После этого все ссылки на указанный файл в текущем документе будут заменены на статические значения.

Способ 2: вставка значений

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

Выделяем диапазон, в котором желаем удалить связь с другой таблицей. Щелкаем по нему правой кнопкой мыши. В раскрывшемся меню выбираем пункт «Копировать». Вместо указанных действий можно набрать альтернативную комбинацию горячих клавиш Ctrl+C.
Далее, не снимая выделения с того же фрагмента, опять кликаем по нему правой кнопкой мыши. На этот раз в списке действий щелкаем по иконке «Значения», которая размещена в группе инструментов «Параметры вставки».
После этого все ссылки в выделенном диапазоне будут заменены на статические значения.

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

Мы рады, что смогли помочь Вам в решении проблемы.

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

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

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

Перед началом работы с отношениями изучите основные понятия. Дополнительные сведения о см. в статьях Руководство по связям между таблицами и Начало работы со связями между таблицами .

В этой статье

Обзор

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

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

Перетащите поле в таблицу из области Список полей .

При создании отношения между таблицами общие поля могут называться по-разному, однако часто требуется, чтобы эти имена совпадали. Очевидно, что общие поля должны иметь одинаковый тип данных. Однако если поле первичного ключа имеет тип "Счетчик", поле внешнего ключа также может быть числовым, если свойство Размер поля (FieldSize) обоих полей совпадает. Например, можно сопоставить поля с типами "Счетчик" и "Числовой", если свойство Размер поля обоих полей имеет значение "Длинное целое". Если оба общих поля являются числовыми, у них должно совпадать значение свойства Размер поля .

Создание отношения между таблицами с помощью окна отношений

Создание отношения между таблицами с помощью панели списка полей

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

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

Открытие таблицы в режиме таблицы

Дважды щелкните таблицу в области навигации.

Открытие области "Список полей"

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

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

Добавление поля и создание связи из области "Список полей"

Открыв таблицу в режиме таблицы, нажмите клавиши ALT+F8. Будет отображена область Список полей .

Чтобы отобразить список полей в таблице, в группе Доступные поля в другой таблице щелкните знак плюс (+ ) рядом с именем таблицы.

Перетащите нужное поле из области Список полей в таблицу, открытую в режиме таблицы.

Когда появится линия вставки, поместите поле в выбранное место.

Появится окно мастера подстановок .

Следуйте инструкциям мастера подстановок .

Поле будет отображено в таблице в режиме таблицы.

При перетаскивании поля из "другой" (несвязанной) таблицы и выполнении инструкций мастера подстановок между таблицей из области Список полей и таблицей, в которую было перетаскивается поле, автоматически создается новое отношение "один-ко-многим". Это отношение, созданное Access, не обеспечивает целостность данных по умолчанию. Чтобы обеспечить целостность данных, нужно изменить отношение. Дополнительные сведения см. в разделе .

Изменение отношения

Чтобы изменить отношение, выберите его в окне "Схема данных" и внесите нужные изменения.

Установите указатель на линию связи и щелкните линию, чтобы выделить ее.

При выделении линия связи становится толще.

Дважды щелкните выделенную линию связи

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

Откроется диалоговое окно Изменение связей .

Открытие диалогового окна "Изменение связей"

Указание типа соединения

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

С помощью запроса к нескольким таблицам можно комбинировать данные из них путем сопоставления значений в общих полях. Операция сопоставления и комбинирования называется объединением. Например, требуется отобразить заказы клиентов. Для этого создается запрос, объединяющий таблицы "Клиенты" и "Заказы" по полю "Код клиента". Результаты запроса содержат сведения о клиенте, а также другие сведения только для строк с найденным соответствующим значением.

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

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

Примечание: В этом случае термины "слева" и "справа" относятся к положению таблиц в диалоговом окне Изменение связей , а не в окне "Схема данных".

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

Указание типа соединения

В диалоговом окне Изменение связей нажмите кнопку Тип соединения .

Откроется диалоговое окно Параметры соединения .

Выберите нужные параметры и нажмите кнопку ОК .

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

	Родственное объединение	Левая таблица	Правая таблица
1. Объединение только тех записей, в которых связанные поля обеих таблиц совпадают.	Внутреннее соединение	Сопоставленные строки	Сопоставленные строки
2. Объединение ВСЕХ записей из таблицы "Клиенты" и только тех записей из таблицы "Заказы", в которых связанные поля совпадают.	Левое внешнее соединение	Все строки	Сопоставленные строки
3. Объединение ВСЕХ записей из таблицы "Заказы" и только тех записей из таблицы "Клиенты", в которых связанные поля совпадают.	Правое внешнее соединение	Сопоставленные строки	Все строки

При выборе варианта 2 или 3 на линии связи будет отображена стрелка, указывающая на ту сторону связи, в которой отображаются только сопоставленные строки.

Внесение изменений в окне "Параметры объединения"

Обеспечение целостности данных

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

Включение и отключение обеспечения целостности данных

На вкладке Работа с базами данных в группе Отношения нажмите кнопку Схема данных .

На вкладке Конструктор в группе Связи нажмите кнопку Все связи .

скрытый в диалоговом окне Свойства Параметры переходов

Дважды щелкните линию связи. Откроется диалоговое окно Изменение связей .

Выберите или отмените параметр .

ОК .

В режиме обеспечения целостности данных действуют перечисленные ниже правила.

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

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

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

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

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

Общие поля должны иметь одинаковый тип данных. Единственное исключение - поле типа "Счетчик" можно связать с полем типа "Числовой", если его свойство Размер поля имеет значение Длинное целое .

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

Задание каскадных параметров

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

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

Включение и отключение каскадного обновления и каскадного удаления

На вкладке Работа с базами данных в группе Отношения нажмите кнопку Схема данных .

На вкладке Конструктор в группе Связи нажмите кнопку Все связи .

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

Щелкните линию отношения, которое вы хотите изменить. При выделении линия связи становится толще.

Дважды щелкните линию связи.

Откроется диалоговое окно Изменение связей .

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

Установите флажок , Каскадное удаление связанных записей или оба эти флажка.

Внесите в отношение необходимые изменения и нажмите кнопку ОК .

Примечание: Если первичным ключом является поле "Счетчик", установка флажка Каскадное обновление связанных полей не произведет никакого эффекта, поскольку изменить значение поля "Счетчик" нельзя.

Удаление отношения между таблицами

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

Чтобы удалить отношение между таблицами, нужно удалить линию связи в окне "Схема данных". Установите указатель мыши на линию связи и щелкните ее. При выделении линия связи становится толще. Выделив линию связи, нажмите клавишу DEL.

На вкладке Работа с базами данных в группе Отношения нажмите кнопку Схема данных .

На вкладке Конструктор в группе Связи нажмите кнопку Все связи .

Щелкните линию связи, которую требуется удалить. При выделении линия связи становится толще.

Нажмите клавишу DEL

При этом может появиться сообщение Подтвердите удаление выделенной связи из базы данных . В этом случае нажмите кнопку Да .

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

Создание, изменение и удаление отношения в веб-приложении Access

Существуют важные отличия при работе со связями в веб-приложении Access.

Создание отношения

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

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

Вот как можно создать поле подстановки в веб-приложении Access:

Изменение отношения

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

Удаление отношения

В веб-приложении Access отсутствует окно "Схема данных". Поле в одной таблице служит источником (полем подстановки) значений для связанного поля в другой таблице. Чтобы удалить связь между двумя таблицами в веб-приложении Access, необходимо удалить поле подстановки и связанные с ним данные.

Создание или изменение отношения в веб-базе данных Access 2010

Чтобы создать отношение в веб-базе данных Access 2010, необходимо использовать мастер подстановок. Окно "Схема данных" отсутствует в веб-базе данных. Поле в одной таблице используется в качестве источника для значения в связанном поле другой таблицы.

Примечание: Воспользоваться мастером подстановок можно только при наличии поля, используемого в качестве источника значений.

Создание в веб-базе данных Access 2010 отношения с помощью мастера подстановок

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

Справа от последнего поля нажмите кнопку Щелкните для добавления , а затем выберите пункт Подстановка и отношение .

Примечание: Для отображения кнопки Щелкните для добавления , может понадобиться прокрутить страницу по горизонтали.

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

Выберите таблицу-источник и нажмите кнопку Далее .

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

Если нужно, определите порядок сортировки для поля подстановки. Нажмите кнопку Далее .

При необходимости отрегулируйте ширину окна подстановки - это удобно, если значения имеют большую длину. Нажмите кнопку Далее .

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

Каскадное удаление позволяет при удалении записи в одной таблице удалять соответствующую запись в другой.

Ограниченное удаление не разрешает удалить запись из таблицы, если она связана с записью в другой таблице.

Примечание: Не выбирайте элемент Разрешить несколько значений в мастере подстановок, если он используется для создания отношения.

Изменение отношения в веб-базе данных Access 2010

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

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

На вкладке Поля в группе Свойства нажмите кнопку Изменить подстановки .

Внесите нужные изменения в соответствии с инструкциями мастера. Можно изменить следующие элементы.