Creating a one-to-many relationship. Create, edit, and delete a relationship

The previously described method of inserting a table as a graphic object has certain limitations, such as: the inability to change data, distortion when scaling, unreadability of small font, etc.

The second suggested method is to insert Excel sheets directly into the document - leads to unnecessary “bloat” of the file; in addition, embedded objects sometimes behave unpredictably.

However, using Automation technology, objects can not only be embedded, but also linked.

Now we will link the Excel and Word tables together, so that changes in the source table lead to changes in the document table. So, we have an initial table with calculations:

We create exactly the same, but empty table in Word:

We do not type headings, but deliberately leave empty lines.

After the operations have been completed, both windows should be open on the screen: the MS Word window with the workpiece and the MS Excel window with the calculation table. We can start tying. Its essence is to sequentially, one after another, link table cells with the necessary positions in a text document.

To do this:

Go to the Excel sheet and make the current cell that needs to be linked to the document (let's start with B1).
We copy the cell (menu Edit-Copy or keyboard shortcut Ctrl+C).
Go to the window with the Word document and place the cursor at the insertion position. In this case, it will be the title, located one line above the table.
From the Word menu select Edit-Paste Special. As a result, a dialog box will open Special insert, where in the region Source(in the upper left corner) the coordinates of the selected cell are indicated:
Set the switch to position Link.
On the list How choose Unformatted text.
By clicking the button OK close the window.

Thus, we have established a connection between a text position in the document and a cell in the worksheet. It should be noted right away that linking does not work entirely correctly with merged cells. Similarly, you need to connect the remaining cells. The final document should look like this:

Now we have the opportunity to format the table as it suits us, and the data will change automatically. It can also be noted that a table in Word can be used as a summary table, entering into it values from different sheets and even Excel books.

The update mode can also be customized. To do this you need:

Select any inserted value in Word and right-click:
IN context menu choose Linked Sheet-Link Object.
In the window that opens, you can select the Communication update method:

The update method can be changed for each relationship individually, or you can change it for all values or for several at once.

This method is not suitable for one-time use, since it is quite labor-intensive at the initial stage. However, with frequent calculations (coursework, theses, financial statements, etc.) it pays off. Also, when changing the structure of a table in Excel, connections are broken, which entails destruction of the integrity of the table in the document. Therefore, it is better to perform linking after the final formation of the calculation tables.

Many people in their work are faced with the need to generate reports, summaries, summarizing documents based on some calculations, calculations, tables with source data and their analysis. And often to perform these tasks you have to resort to a combination of such software products like Word (where the final report is generated for further printing or forwarding to someone) and Excel (in which the source data for the report is collected and processed).

If one-time tasks do not raise any special questions, then the generation of periodic reports sometimes causes many problems. Routine copying of the same tables or graphs (with slightly changed data) into the same documents is extremely tiring. But this process can be automated. How to do this - read below.

Create a link between Word and Excel

The fastest and effective way create links between files - copy necessary information from one and paste into the other using the tool "Special Insert" To do this, you need to select a table, cell or chart in Excel, go to Word, place the cursor in the desired part of the document and:

As a result, the pasted object will be linked to the file from which it was copied, and when the source data is updated, you can quickly update the links in the Word document.

Selecting a data insertion method

As you can see in Figure 2, there are several ways to insert data into a document. In everyday work, you will have to choose, in fact, between two options - insert a table as a picture (without the ability to format it) or as text (and therefore with the ability to make changes). For diagrams there is only one option - a picture.

Moreover, it is important to distinguish related objects from implemented ones. Embedding is an operation that is not covered in this article. Its essence is that an Excel object is embedded in a Word document, which allows you to work with data in a spreadsheet directly from Word document. At the same time source file And text document are not connected in any way. Embedding is rarely used because it greatly increases the file size.

In most cases, when choosing how to insert a table, you should choose the text option so that you can customize the formatting to suit your needs. Pictures can be inserted if you are completely satisfied with the formatting in the original Excel document.

There are situations when you have to insert not the entire table, but data from individual cells. For example, when you need to insert into a sentence some number or text that is in Excel file and will change, or when a table in Word is compiled from several disparate Excel cells(from different tables, sheets or even books). In such cases, copy the cell data to the desired location and select the insertion method "Unformatted text".

Configuring how links are updated and working with the Link Manager

Inserted links can be updated in several ways. For example, if you click on the inserted object right click mouse, then the menu will have a “Update connection” button, clicking which will update the selected connection.

You can set update parameters for all objects at once. To do this, you need to call the Document Link Manager. We pass along the way “File” - “Information” - “Change file associations” (this command available when the document has at least one relationship and the document itself is saved).

The manager shows all the connections of the file. In the “Link updating method” group, you can select the option that is more preferable or disable link updating altogether. You can also set the “Keep format when updating” setting for each connection. It is responsible for ensuring that the formatting you choose does not disappear when you update the connection. In the same manager you can break connections, open or change the source.

Working with relationships using Word field codes

At its core, each relationship is a special Word field. If in a document with links you click ALT+F9, you will see that the codes of these fields will be displayed instead of the inserted data.

Working with them opens up possibilities that a standard connection manager does not provide. And although Word field codes are a broad topic that goes far beyond the scope of this article, a couple of examples of them beneficial use we will give:

1) Changing links to documents in all links at once

Links to source documents are made through direct links. If you transferred the source file, you will need to re-tighten all the connections. In the link manager, you will have to do this for each link separately (even if you select several, the manager will offer to replace each link in turn). If you switch to field code display mode ( ALT+F9), then old links can be changed to new ones by simply replacing ( CTRL+H or "Home-Replace"), indicating the old address and the new one.

2) Using named ranges to insert tables

If you insert a table from Excel into Word, the link to it will contain a specific range of cells. That is, if you add rows or columns to the table, the data will be updated only within a strictly specified range. Link Manager allows you to change the range manually. Automatic change can be configured by setting a named dynamic range in Excel (lesson here and here, if you don’t understand, write to our bot at Telegram, we will help), and in Word file in the field code editing mode, after this you will need to replace direct cell references with the name of the named range. Now when resizing the original table in Excel document Word will update correctly.

You can ask your questions about the article through our bot feedback V Telegram:@KillOfBot

Best regards, tDots.ru team

Communication between Excel tables is a formula that returns data from a cell in another workbook. When you open a workbook that contains relationships, Excel reads latest information from the source book (updating links)

Inter-table relationships in Excel are used to retrieve data both from other sheets in a workbook and from other Excel workbooks. For example, you have a table that calculates the total amount of sales. The calculation uses product prices and sales volume. In this case, it makes sense to create a separate table with data on prices, which will be pulled up using the connections of the first table.

When you create a relationship between tables, Excel creates a formula that includes the name of the source workbook, enclosed in parentheses, the name of the worksheet, and exclamation point at the end and a cell reference.

Creating links between workbooks

Open both workbooks in Excel
In the source workbook, select the cell that needs to be linked and copy it (keyboard shortcut Ctrl+C)
Go to the target workbook, right-click on the cell where we want to place the connection. Select from the drop-down menu Special insert
Special insert choose Insert connection.

There is another, simpler option for creating a relationship between tables. In the cell where we want to insert a link, put an equal sign (the same as for a regular formula), go to the source workbook, select the cell that we want to link, and click Enter.

You can use the copy and autocomplete tools for relationship formulas just as you do for regular formulas.

Before you create relationships between tables

Before you start spreading the knowledge of your big ideas, here are some tips for working with relationships in Excel:

Make easily traceable connections. Doesn't exist automatic search all cells containing links. Therefore, use a specific format to quickly identify links to other tables, otherwise the document containing the links may grow to such an extent that it is difficult to maintain.

Automatic calculations. The source workbook must work in automatic calculation mode (set by default). To switch the calculation parameter, click the tab Formulas to the group Calculation. Select Calculation options –> Automatic.

Updating connections

For manual update connections between tables, go to the tab Data to the group Connections. Click the button Change connections.

In the dialog box that appears Changing connections Update.

Break links in Excel workbooks

Unlinking a source will cause existing link formulas to be replaced with the values they return. For example, the link =Prices!$B$4 will be replaced by 16. Breaking the link cannot be undone, so before performing the operation, I recommend saving the book.

Go to the tab Data to the group Connections. Click the button Change connections. In the dialog box that appears Changing connections select the connection you are interested in and click on the button Break the connection.

Working with databases is a process that requires certain skills and an understanding of certain nuances. The Access program developed by Microsoft gives users the ability to quickly create and edit databases. One of the most important moments, which definitely needs to be dealt with when creating a database - linking its elements. In this article we will figure out how to create or delete relationships between tables in Access. Let's get started. Let's go!

Before we move on to linking, we need to create what are called key fields. What is it? A key field is a field that identifies each record. Now let's move on to its creation. Having opened the desired table, right-click on the tab and select “Designer”. Then add a new field. For example, if you have a list of employees, then the key should be “employee code”, indicating the data type “counter” or “numeric”. To make it a key, click the "Key Field" button located on the toolbar Microsoft Access. Next, you need to do the same for all other tables.

Now we can link our tables. To do this, open the “Database Tools” tab and in the “Relationships” section of the tool ribbon, select “Data Schema”. A window will open in front of you in which you will need to select all the tables. Click on each of them in turn. When finished, exit this window. Now you need to decide how exactly to connect. For example, there are two tables: “Brand” and “Clothing”, which need to be linked. The “Brand Code” field should be both there and there. If it is missing, create it. In this case, for records in the “Brand” category it will be a key (the so-called primary key), and for “Clothing” it will be a regular one (a foreign key). Drag the primary key “Brand code” onto the foreign key “Brand code”. After this, a dialog box will appear in which you need to check the boxes: “Ensuring integrity ...”, “Cascading update of related fields” and “Cascading deletion of related records”. After checking all the boxes, click the “Create” button. A black line will appear connecting the specified lines. The created relationship is called one-to-many. That is, in the example given, this will mean that one brand can produce many different clothes.

Next, connect all the others in the same way, not forgetting to add fields. With at least three tables (including two primary and one intermediate), you can create a many-to-many relationship. To fill in fields that are foreign keys, you can use the Lookup Wizard. To do this, go to Design mode and in the Data Type section, select Lookup Wizard. In the window that opens, select where the information to fill out will be taken from. Then specify required fields and click the "Next" button. You can skip the next two windows. At the end, click on the “Done” button. Before you start filling out, the program will prompt you to save, click “Yes”. Please note that filling in this way is only possible if the connection has not yet been established. If this does not work out for you, then you can delete it and fill in the cells. After that, it will automatically appear, however, you will need to double-click on it and check the boxes: “Ensuring integrity ...”, “Cascading update of related fields” and “Cascading deletion of related records”.

To delete a relationship, click on the “Data Schema” button on the toolbar, then click on the line showing it and press the delete key on your keyboard.

Now you know how connections are added and deleted when working with a database in Microsoft program Access. With these skills, you will already be able to create full-fledged, functioning databases, however, continue to develop your knowledge Access programs. Write in the comments whether this article was useful to you and ask any questions you have about the topic discussed.

Many-to-many relationships are the most commonly used relationships between tables. You can use them to find out important information, such as which customers your sales managers contacted and which products were included in orders.

Many-to-many relationship" implies the ability to link one or more elements from one table with one or more elements from another table. Examples:

The Orders table lists the orders placed by different customers from the Customers table. Each client could make several orders.

The "Products" table lists the products being sold, each of which can appear in several orders from the "Orders" table.

Each product can be included in one order in one or several copies.

For example, Arina Ivanova’s order No. 1012 may include products No. 12 and 15, as well as five products No. 30.

Creating a many-to-many relationship

Many-to-many relationships are created differently from one-to-one and one-to-many relationships, where you simply connect the fields you want using a line. To create a many-to-many relationship, you need to add a new table that will link the other two. The new table will be called intermediate(or binder).

In the example discussed earlier, the "Order Information" table was created with records in which, for each product, the order number from the "Orders" table and the product code from the "Products" table were indicated in the required order. The primary key for this table was created by combining the keys from two other tables.

Below we will consider an example when Arina Ivanova’s order No. 1012 includes products No. 12, 15 and 30. This means that the entries in the “Order Information” table look like this:

Creating a staging table

Creating fields in a staging table

The Code column is automatically added as the first column in Access. Change the name of this field to the ID of your first table in the many-to-many relationship. For example, if the first table is called "Orders", the "Code" field in it is renamed "Order Number", and its primary key is a number, change the name of the "Code" field to new table to "Order number", and select as data type Numerical.

IN table mode select column header Code and enter a new field name.

Select the renamed field.

On the tab Fields on the list Data type select the type as in the corresponding field of the source table, for example Numerical or Short text.

Click the caption Click to add and select the data type corresponding to the primary key in the second table. In the column header, enter the name of the primary key field from the second table, such as Product ID.

If you need to track other information about these records, such as product quantities, create additional fields.

Joining fields to create a primary key

Now that you have the ID fields of the two tables that you want to create a relationship between, you should create a primary key in the staging table based on those IDs.

Joining three tables to create a many-to-many relationship

To complete the many-to-many relationship, create a one-to-many relationship between the primary key field in each table and the corresponding field in the staging table. For instructions, see Get started with table relationships.

After this, the connections should look like this:

Do you need additional features?

Many-to-many relationships are used when one element of table A can be associated with many elements of table B and vice versa. For example, a product may be part of multiple orders, and an order may contain multiple products.

In this video we will look at how to make a database simple system order tracking with separate tables for products and orders.

We will learn what a join table and a composite key are and use them with other tables to create a many-to-many relationship.

To create a many-to-many relationship, you first need to create another table.

This table is called an intermediate table. It will store data from other tables that we need to link in the relation.

A staging table is also called a bridge table.

Now you need to create a pair of one-to-many relationships between the linking table and the tables it will link.

To do this, you need to specify both of these fields as the primary key of the linking table. They become a composite key.

Since the link table is created in design mode, open the tab Creation and press the button Table builder. Here is our new, empty table.

Now let’s open the “Products” and “Orders” tables.

Let's take a look at the primary keys of both tables. The "Products" table uses the "Product ID" field with the data type Counter.

The primary key for the Orders table is called Order ID. It also has a data type Counter.

Now we return to the connecting table, enter the field name “Product ID”, select the data type Numerical, enter the name “Order ID” and select the data type again Numerical.

Now let's select both fields. To do this, hover over the gray rectangle next to any of these fields and drag it.

Having selected both fields, select the item on the ribbon Key field. A composite key is created for the linking table.

Both fields must be included in the composite key so that each order and everything included in it has unique identifiers.

Now we can create relationships. First, close all open tables. Select Working with databases > Data Schema.

On the tab Constructor select element Adding a table. Add the Order Details, Orders, and Items tables. Close the dialog box.

Place the tables so that the Order Details link table is in the middle. Drag the Order ID field from the Orders table to the link table.

In the dialog box Changing connections check the box and click the button Create to create an attitude.

Follow the same steps for the “Product ID” field. Check the box Ensuring data integrity, press the button Create- and everything is ready.

So, you have created in the database Access data A many-to-many relationship between existing tables using a join table and a composite key.

Sometimes you need to create an immutable link between one cell and a value in another cell, either entered into it manually or obtained from a formula.

In the open worksheet window, select the cell with the original value.
Use any known copying method (the “Copy” button on the “Home” tab, Ctrl+C, etc.).
Select the cell that will be linked to the original one.
Go to the "Home" tab and in the "Clipboard" group, expand the "Paste" button menu.
In the list of commands, select “Insert Link” (Fig. 4.4).
Save the result by clicking the Esc key.

Note

Further changes to the values in the source cell will automatically be reflected in the linked cell.

Rice. 4.4. Home tab. Insert button menu. Item “Insert connection”

An Excel table link is a formula that returns data from a cell in another workbook. When you open a workbook that contains links, Excel reads the latest information from the source workbook (update links)

When you create a relationship between tables, Excel creates a formula that includes the name of the source workbook enclosed in parentheses, the name of the worksheet followed by an exclamation point, and a cell reference.

Creating links between workbooks

Open both workbooks in Excel
In the source workbook, select the cell that needs to be linked and copy it (keyboard shortcut Ctrl+C)
Go to the target workbook, right-click on the cell where we want to place the connection. Select from the drop-down menu Special insert
Special insert choose Insert connection.

You can use the copy and autocomplete tools for relationship formulas just as you do for regular formulas.

Before you create relationships between tables

Before you start spreading the knowledge of your big ideas, here are some tips for working with relationships in Excel:

Make easily traceable connections. There is no automatic search for all cells containing links. Therefore, use a specific format to quickly identify links to other tables, otherwise the document containing the links may grow to such an extent that it is difficult to maintain.

Updating connections

To manually update the relationship between tables, go to the tab Data to the group Connections. Click the button Change connections.

In the dialog box that appears Changing connections Update.

Break links in Excel workbooks

When performing certain tasks in Excel, sometimes you have to deal with several tables, which are also interconnected. That is, data from one table is pulled into others and when they change, the values in all associated table ranges are recalculated.

Linked tables are very convenient to use for processing large amounts of information. It is not very convenient to place all the information in one table, and if it is not homogeneous. It is difficult to work with such objects and search for them. Linked tables, information between which is distributed but at the same time interrelated, are designed to eliminate this problem. Related table ranges can be located not only within one sheet or one workbook, but also located in separate workbooks (files). The last two options are used most often in practice, since the purpose of this technology is precisely to get away from the accumulation of data, and piling them on one page does not fundamentally solve the problem. Let's learn how to create and work with this type of data management.

Creating Linked Tables

First of all, let's look at the question of how it is possible to create a relationship between different table ranges.

Method 1: Directly linking tables with a formula

The easiest way to link data is to use formulas that reference other table ranges. It's called direct binding. This method is intuitive because it performs linking in almost the same way as creating links to data in a single table array.

Let's see how, using an example, a bond can be formed by direct bonding. We have two tables on two sheets. On one table, wages are calculated using a formula by multiplying the workers’ rate by a coefficient common to all.

The second sheet contains a table range containing a list of employees with their salaries. The list of employees in both cases is presented in the same order.

It is necessary to make sure that the data on rates from the second sheet is pulled into the corresponding cells of the first.

On the first sheet, select the first cell of the “Bet” column. We put a “=” sign in it. Next, click on the “Sheet 2” tab, which is located on the left side of the Excel interface above the status bar.
Moves to the second area of the document. Click on the first cell in the “Bet” column. Then click on the Enter button on the keyboard to enter data into the cell in which you previously set the equal sign.
Then there is an automatic transition to the first sheet. As you can see, the rate of the first employee from the second table is pulled into the corresponding cell. By placing the cursor on the cell containing the bet, we see that the usual formula is used to display data on the screen. But before the coordinates of the cell from which the data is output, there is the expression “Sheet2!”, which indicates the name of the document area where they are located. The general formula in our case looks like this:
Now you need to transfer data on the rates of all other employees of the enterprise. Of course, this can be done in the same way that we completed the task for the first employee, but given that both lists of employees are located in the same order, the task can be significantly simplified and its solution can be speeded up. This can be done by simply copying the formula onto the range below. Due to the fact that links in Excel are relative by default, when they are copied, the values shift, which is what we need. The copying procedure itself can be done using the fill marker.
So, place the cursor in the lower right area of the element with the formula. After this, the cursor should transform into a fill marker in the form of a black cross. Hold down the left mouse button and drag the cursor to the very bottom of the column.
All data from a similar column on Sheet 2 was pulled into the table on Sheet 1. When the data on Sheet 2 changes, it will automatically change on the first one.

Method 2: using a combination of INDEX - SEARCH operators

But what to do if the list of employees in the table arrays is not located in the same order? In this case, as discussed earlier, one option is to manually establish a link between each of those cells that need to be linked. But this is only suitable for small tables. For massive ranges, such an option, at best, will take a lot of time to implement, and at worst, in practice it will not be feasible at all. But decide this problem can be done using a combination of INDEX – SEARCH operators. Let's see how this can be done by linking data in tabular ranges, which were discussed in the previous method.

Select the first element of the “Bet” column. Go to the Function Wizard by clicking on the “Insert Function” icon.
In the Function Wizard, in the “Links and Arrays” group, find and select the name “INDEX”.
This operator has two forms: a form for working with arrays and a reference form. In our case, the first option is required, so in the next form selection window that opens, select it and click on the “OK” button.
The INDEX operator arguments window has been launched. Task specified function- displaying the value in the selected range in the line with specified number. The general formula for the INDEX operator is:
INDEX(array;line_number;)

“Array” is an argument containing the address of the range from which we will extract information by the number of the specified line.

“Line number” is an argument that is the number of this very line. It is important to know that the line number should not be specified relative to the entire document, but only relative to the selected array.

“Column number” is an optional argument. We will not use it to solve our specific problem, and therefore there is no need to describe its essence separately.

Place the cursor in the “Array” field. After this, go to Sheet 2 and, holding down the left mouse button, select the entire contents of the “Bet” column.
After the coordinates are displayed in the operator window, place the cursor in the “Line number” field. We will output this argument using the MATCH operator. Therefore, we click on the triangle, which is located to the left of the function line. A list of recently used operators opens. If you find the name “SEARCH” among them, you can click on it. Otherwise, click on the very last item on the list – “Other functions...”.
The standard Function Wizard window opens. In it we go to the same group “Links and arrays”. This time, select the “SEARCH” item from the list. Click on the “OK” button.
The MATCH operator argument window is activated. This function is designed to display the number of a value in a specific array by its name. It is thanks to this feature that we will calculate the row number of a certain value for the INDEX function. The MATCH syntax is presented as follows:
MATCH(lookup_value, lookedup_array;)

“Search value” is an argument containing the name or address of the cell in the third-party range in which it is located. It is the position of this item in the target range that should be calculated. In our case, the first argument will be links to cells on Sheet 1 in which the names of employees are located.

“Array to be searched” - an argument that is a reference to the array in which the search is performed specified value to determine its position. For us, this role will be played by the address of the “Name” column on Sheet 2.

"Matching type" is an optional argument, but unlike the previous statement, we will need this optional argument. It specifies how the operator will match the search value to the array. This argument can have one of three values: -1; 0; 1. For unordered arrays, select option “0”. Exactly this option suitable for our case.

So, let's start filling out the fields of the arguments window. Place the cursor in the “Search value” field, click on the first cell of the “Name” column on Sheet 1.
After the coordinates are displayed, place the cursor in the “Viewed array” field and go to the “Sheet 2” label, which is located at the bottom Excel windows above the status bar. Hold down the left mouse button and select with the cursor all the cells in the “Name” column.
After their coordinates are displayed in the “Viewed Array” field, go to the “Matching Type” field and use the keyboard to set the number “0” there. After this, we return again to the “Viewed array” field. The fact is that we will copy the formula, as we did in the previous method. There will be a shift in addresses, but we need to fix the coordinates of the array being viewed. It shouldn't move. Select the coordinates with the cursor and click on function key F4. As you can see, a dollar sign has appeared in front of the coordinates, which means that the reference has turned from relative to absolute. Then click on the “OK” button.
The result is displayed on the screen in the first cell of the “Bet” column. But before we copy, we need to secure one more area, namely the first argument of the INDEX function. To do this, select the column element that contains the formula and move to the formula bar. Select the first argument of the INDEX operator (B2:B7) and click on the F4 button. As you can see, a dollar sign appears near the selected coordinates. Click on the Enter key. In general, the formula took the following form:
INDEX(Sheet2!$B$2:$B$7,MATCH(Sheet1!A4,Sheet2!$A$2:$A$7,0))
You can now copy using the fill handle. We call it in the same way that we talked about earlier and stretch it to the end of the table range.
As we can see, despite the fact that the order of the rows of the two linked tables does not match, nevertheless, all values are adjusted according to the last names of the employees. This was achieved by using a combination of INDEX-MATCH operators.

Method 3: Perform Mathematical Operations on Linked Data

Direct data linking is also good because it allows you not only to display values that are displayed in other table ranges in one of the tables, but also to perform various mathematical operations with them (addition, division, subtraction, multiplication, etc.).

Let's see how this is done in practice. Let's make it so that Sheet 3 will display general salary data for the enterprise without breaking it down by employee. To do this, employee rates will be pulled up from Sheet 2, summed up (using the SUM function) and multiplied by a coefficient using a formula.

Select the cell where the payroll calculation results will be displayed on Sheet 3. Click on the “Insert Function” button.
The Function Wizard window should launch. Go to the “Mathematical” group and select the name “SUM” there. Next, click on the “OK” button.
The SUM function, which is designed to calculate the sum of selected numbers, is moved to the arguments window. It has the following syntax:
SUM(number1;number2;…)

The fields in the window correspond to the arguments of the specified function. Although their number can reach 255 pieces, for our purpose only one will be enough. Place the cursor in the “Number1” field. Click on the “Sheet 2” shortcut above the status bar.
After we moved to required section books, select the column that should be summed. We do this with the cursor while holding down the left mouse button. As you can see, the coordinates of the selected area are immediately displayed in the field of the arguments window. Then click on the “OK” button.
After this, we are automatically moved to Sheet 1. As you can see, the total amount of employee rates is already displayed in the corresponding element.
But that's not all. As we remember, the salary is calculated by multiplying the bet by the coefficient. Therefore, we again select the cell in which the summed value is located. After that, go to the formula bar. We add a multiplication sign (*) to the formula in it, and then click on the element in which the coefficient indicator is located. To perform the calculation, click on the Enter key on the keyboard. As you can see, the program calculated the total wages for the enterprise.
We return to Sheet 2 and change the rate of any employee.
After this, we move again to the page with the total amount. As you can see, due to changes in the linked table, the total salary result was automatically recalculated.

Method 4: special insert

You can also link table arrays in Excel using paste special.

We select the values that will need to be “dragged” into another table. In our case, this is the range of the “Rate” column on Sheet 2. Right-click on the selected fragment. In the list that opens, select “Copy”. An alternative shortcut is Ctrl+C. After that we move to Sheet 1.
Having moved to the area of the book we need, we select the cells into which we will need to pull up the values. In our case, this is the “Rate” column. Right-click on the selected fragment. In the context menu in the “Insert Options” tool block, click on the “Insert Link” icon.
There is also alternative option. By the way, it is the only one for older versions of Excel. In the context menu, move the cursor to the “Paste Special” item. In the additional menu that opens, select the position with the same name.
This will open the Paste Special window. Click on the “Insert Link” button in the lower left corner of the cell.
Whichever option you choose, the values from one table array will be inserted into the other. When data in the source changes, it will also automatically change in the inserted range.

Lesson: Paste Special in Excel

Method 5: Linking Tables in Multiple Workbooks

In addition, you can organize relationships between table areas in different books. This uses the Paste Special Tool. The actions will be absolutely similar to those that we considered in the previous method, except that when entering formulas you will have to navigate not between areas of the same book, but between files. Naturally, all linked books must be open.

Select the range of data that needs to be transferred to another workbook. Right-click on it and select “Copy” from the menu that opens.
Then we move to the book into which this data will need to be inserted. Select the desired range. Right-click. In the context menu, in the “Insert Options” group, select “Insert Link”.
After this, the values will be inserted. When data in the source workbook changes, the tabular array from the workbook will automatically update it. Moreover, it is not at all necessary that both books be open for this. It is enough to open just one workbook, and it will automatically pull up data from the closed one linked document, if changes have previously been made to it.

But it should be noted that in this case the insertion will be performed in the form of an immutable array. When you try to change any cell with inserted data, a message will pop up informing you that it is impossible to do this.

Changes to such an array linked to another book can only be made by breaking the link.

Breaking the relationship between tables

Sometimes you need to break the relationship between table ranges. The reason for this may be either the case described above, when you need to change an array inserted from another workbook, or simply the user’s reluctance to have data in one table automatically updated from another.

Method 1: breaking the connection between books

You can break the connection between books in all cells by essentially performing one operation. In this case, the data in the cells will remain, but they will already be static, non-updating values that are in no way dependent on other documents.

In a workbook in which values from other files are pulled up, go to the “Data” tab. Click on the “Edit connections” icon, which is located on the ribbon in the “Connections” tool block. It should be noted that if the current book does not contain links to other files, then this button is inactive.
The window for changing connections opens. We select from the list of linked books (if there are several of them) the file with which we want to break the connection. Click on the “Break connection” button.
An information window opens containing a warning about the consequences of further actions. If you are sure of what you are going to do, then click on the “Break ties” button.
After this, all links to specified file in the current document will be replaced with static values.

Method 2: Inserting Values

But the above method is only suitable if you need to completely break all connections between two books. What should you do if you need to separate related tables located within the same file? You can do this by copying the data and then pasting it in the same place as the values. By the way, in the same way you can break the connection between individual data ranges of different books without breaking general communication between files. Let's see how this method works in practice.

We select the range in which we want to delete the relationship with another table. We right-click on it. In the menu that opens, select “Copy”. Instead of these actions, you can type an alternative hotkey combination Ctrl+C.
Next, without deselecting the same fragment, right-click on it again. This time, in the list of actions, click on the “Values” icon, which is located in the “Insert Options” tool group.
After this, all references in the selected range will be replaced with static values.

As you can see, Excel has ways and tools to link several tables together. At the same time, tabular data can be located on other sheets and even in different books. If necessary, this connection can be easily broken.

We are glad that we were able to help you solve the problem.

Ask your question in the comments, describing the essence of the problem in detail. Our specialists will try to answer as quickly as possible.

A relationship in Access allows you to combine data from two different tables. Each relationship consists of two fields (one in each table) with associated data. For example, the "Products" and "Order Details" tables may contain the "Product Code" field. Each record in the "Order Details" table contains the value of the "Product Code" field, which corresponds to a record in the "Products" table with the same value in this field.

Using the relationships between related tables, Access determines which records from each table to put into the query result set. The relationship also avoids data loss by preventing deleted data from being excluded from synchronization. This is called data integrity.

Before you start working with relationships, learn the basic concepts. More information For details, see the articles A Guide to Table Relationships and Getting Started with Table Relationships.

Review

To create a relationship in an Access database, you can use one of the following methods.

In the Data Schema window, add the tables you want to link, and then drag the field you want from one table to another.

Drag a field into the table from the area List of fields.

When you create a relationship between tables, the common fields can be named differently, but you often want the names to be the same. Obviously, common fields must have the same data type. However, if the primary key field is of type Counter, the foreign key field can also be numeric if the property Field size(FieldSize) of both fields is the same. For example, you can map fields with types Counter and Numeric if the property Field size both fields have the value "Long Integer". If both shared fields are numeric, they must have the same property value Field size.

Create a relationship between tables using the Relationships window

Create a relationship between tables using the Field List panel

You can add a field to an existing table that is open in Datasheet view by dragging the field from the area List of fields. In the area List of fields Displays available fields from related tables as well as from other tables in the database.

List of fields and the table into which the field is dragged automatically creates a new one-to-many relationship. This relationship created by Access does not enforce data integrity by default. To ensure data integrity, the attitude needs to change. For more information, see .

Opening a table in Datasheet view

Double-click the table in the navigation pane.

Opening the Field List area

In the area List of fields All other database tables are displayed, grouped by category. When working with a table in table view in the pane List of fields fields are displayed in one of two categories: Available fields in a linked table And . The first category includes all tables that are related by a relationship to the current table. The second category lists all the tables with which this table not related.

To see a list of all table fields, click the plus sign ( + ) next to the table name in the scope List of fields. To add a field to the table, drag it from the area List of fields to a table in table view.

Add a field and create a relationship from the Field List area

With a table open in Datasheet view, press ALT+F8. The area will be displayed List of fields.

To display a list of fields in a table, in a group Available fields in another table click the plus sign ( + ) next to the table name.

Drag the desired field from the area List of fields into a table opened in Datasheet view.

When the insertion line appears, place the box in the selected location.

A window will appear substitution wizards.

Follow the instructions substitution wizards.

The field will be displayed in the table in Datasheet view.

When you drag a field from "another" (unrelated) table and follow the Lookup Wizard instructions between the table from the area List of fields and the table into which the field was dragged automatically creates a new one-to-many relationship. This relationship created by Access does not enforce data integrity by default. To ensure data integrity, the attitude needs to change. For more information, see .

Changing Attitude

To change a relationship, select it in the Data Schema window and make the changes you want.

Place the pointer over a link and click the line to highlight it.

When highlighted, the connection line becomes thicker.

Double-click the highlighted link

on the tab Constructor in the group Service select team Change connections.

A dialog box will open Changing connections.

Open the Edit Links dialog box

Specifying the connection type

When you define a relationship between tables, the information about it influences the design of your queries. For example, when you define a relationship between two tables and create a query that operates on those two tables, Access automatically selects default mapping fields based on the fields specified in the relationship. These original values can be overridden in a query, but often the values defined by the relationship between tables are correct. Because matching and joining data from two tables is a frequently repeatable activity in all but the most basic databases, default parameters defined by relationships between tables can be useful and time-saving.

By using a query against multiple tables, you can combine data from them by matching values in common fields. The operation of matching and combining is called union. For example, you want to display customer orders. To do this, create a query that combines the “Customers” and “Orders” tables using the “Customer Code” field. The query results contain client information and other information only for rows with a matching value found.

One of the values that you can set for each relationship is the join type. The join type determines which records will be included in the query results. Let's turn to the described example with the union of the "Customers" and "Orders" tables using common fields representing the customer code. When you use the default join type (inner join), the query returns only rows from the Customers and Orders tables for which the common fields (also called related fields) match.

Let's say you want to include all customers in your results - even those who haven't placed an order yet. To do this, you need to change the join type from inner to the so-called left outer join. When using a left outer join, all table rows on the left side of the relationship are returned and only the matched rows on the right side. When using a right outer join, all rows on the right side of the relationship are returned and only those matched on the left.

Note: In this case, the terms "left" and "right" refer to the position of the tables in the dialog box Changing connections rather than in the Data Schema window.

You should determine what results are most often required from a query that joins tables in a particular relationship and select the join type accordingly.

Specifying the connection type

In the dialog box Changing connections click the button Connection type.

A dialog box will open Connection parameters.

Select the options you want and click the button OK.

The table below, based on the Customers and Orders tables, shows the three options displayed in the window Merge Options, specifying the type of join they use, as well as which rows (all or just matched ones) are returned for each of the tables.

	Related Association	Left table	Right table
1. Merging only those records in which the related fields of both tables match.	Inner join	Matched Strings	Matched Strings
2. Merging ALL records from the "Customers" table and only those records from the "Orders" table in which the related fields match.	Left outer join	All lines	Matched Strings
3. Merging ALL records from the Orders table and only those records from the Customers table in which the related fields match.	Right outer join	Matched Strings	All lines

If you select option 2 or 3, the link will display an arrow pointing to the side of the link where only matched rows are displayed.

Making changes in the Merge Options window

Ensuring data integrity

The purpose of data integrity is to prevent unmatched records that reference non-existent records. Data integrity is enabled for a specific table relationship. As a result, Access cancels all actions on this relationship that could compromise the integrity of the data. This means that both an update that changes the link target and a deletion of that link target will be rolled back. To learn how to set Access to propagate update and delete operations so that all associated rows are also updated, see .

Enable or disable data integrity enforcement

On the tab Working with databases in the group Relationship click the button Data Schema.

On the tab Constructor in the group Connections click the button All connections.

hidden in the dialog box Properties Transition options

Double-click the communication line. A dialog box will open Changing connections.

Select or clear the option.

OK.

In data integrity mode, the following rules apply:

It is not allowed to enter values in a foreign key field of a related table that are not in the primary key field of the main table, since this results in orphaned records.

You cannot delete a record from the main table if there are records associated with it in the related table. For example, you cannot delete a record from the Employees table if there are orders in the Orders table that apply to that employee. However, you can delete the master entry And all related entries in one action by checking the checkbox.

It is not allowed to change the value of the primary key in the main table if this will result in orphaned records. For example, you cannot change the order number in the Orders table if there are rows in the Order Details table that relate to that order. However, you can update the master record And all related records in one action by checking the "Cascading update of related fields" checkbox.

Notes: If you are having difficulty enabling data integrity, please note that the conditions listed below must be met.

The general field of the main table should be primary key or have a unique index.

Common fields must have the same data type. The only exception is that a field of type "Counter" can be associated with a field of type "Numeric" if its property Field size matters Long integer.

Both tables exist in the same Access database. Data integrity cannot be enabled for joined tables. However, if the source tables are in Access format, you can open the database in which they are stored and enable data integrity in that database.

Setting cascade parameters

Sometimes a situation arises in which you only need to change the value on the "one" side of the relationship. In this case, you want Access to automatically update all affected rows in one operation. Then the update will be completely completed and the database will not be in an inconsistent state where some rows are updated and others are not. You can avoid this problem by using the Access Cascading Updates for Related Fields option. If you enabled the Cascading Update of Related Fields option when you enabled data integrity, the next time you update the primary key, all fields associated with it will automatically be updated.

You may also want to delete a row and all of its associated records—for example, a record in the Suppliers table and all of the orders associated with that vendor. Access has the "Cascade Delete Related Records" option for this purpose. If you enable data integrity and check the Cascade deletion of related records, when you delete a record that contains a primary key, all records associated with that primary key will be automatically deleted.

Enable or disable cascade update and cascade delete

On the tab Working with databases in the group Relationship click the button Data Schema.

On the tab Constructor in the group Connections click the button All connections.

All tables with relationships will be displayed, as well as the corresponding relationship lines. Please note that hidden tables (tables that have the hidden in the dialog box Properties) and their relationships are not displayed if the dialog box Transition options The "Show hidden objects" option is not selected.

Click the line of the relationship you want to change. When highlighted, the connection line becomes thicker.

Double-click the communication line.

A dialog box will open Changing connections.

Check the box Ensuring data integrity.

Check the box Cascade deletion of related records or both of these checkboxes.

Make the necessary changes to the relationship and click the button OK.

Note: If the primary key is the Counter field, check the Cascade update of related fields will have no effect, since the value of the Counter field cannot be changed.

Removing a relationship between tables

Important: Deleting a relationship also disables data integrity for that relationship, if it was enabled. As a result, Access will no longer automatically prevent orphan records from appearing on the "many" side of a relationship.

To delete a relationship between tables, you need to delete the relationship line in the Data Schema window. Place your mouse pointer over the link and click it. When highlighted, the connection line becomes thicker. With the communication line highlighted, press the DEL key.

On the tab Working with databases in the group Relationship click the button Data Schema.

On the tab Constructor in the group Connections click the button All connections.

Click the link you want to delete. When highlighted, the connection line becomes thicker.

Press DEL key

A message may appear Confirm deleting the selected relationship from the database. In this case, click the button Yes.

Note: If one of the tables participating in the relation is at the moment is used (perhaps by a third party, a process, or in an open database object such as a form), it will not be possible to delete the relationship between the tables. Before attempting to delete a relationship between tables, close all open objects that use these tables.

Create, edit, or delete a relationship in an Access web app

There are important differences when working with relationships in an Access web app.

Creating an Attitude

The Data Schema window is missing from the Access web app. Instead of a relationship, you need to create a lookup field that gets values from the related field in another table. For example, let's say you have an Employees table and you want to add a lookup to the Regions table to show the regions in which employees work.

Note: The field used as a lookup source must exist before the lookup field can be created.

Here's how to create a lookup field in an Access web app:

Changing Attitude

The Data Schema window is missing from the Access web app. A field in one table serves as a source (lookup field) of values for a related field in another table.

Delete a relationship

The Data Schema window is missing from the Access web app. A field in one table serves as a source (lookup field) of values for a related field in another table. To remove a relationship between two tables in an Access web app, you must remove the lookup field and its associated data.

Create or edit a relationship in an Access 2010 web database

To create a relationship in an Access 2010 web database, you must use the Lookup Wizard. The Data Schema window is missing from the web database. A field in one table is used as the source for a value in a related field in another table.

Note: You can use the Lookup Wizard only if you have a field that is used as a source of values.

Create a relationship in an Access 2010 web database using the Lookup Wizard

Open the table into which values from another table will be transferred.

To the right of the last field, click the button Click to add, and then select Substitution and relation.

Note: To display a button Click to add, you may need to scroll the page horizontally.

On the first screen of the Lookup Wizard, select the element Get values from another table for a lookup field and press the button Next.

Select the source table and click the button Next.

In the window Available fields double click select the field containing the desired values and click the button Next.

If necessary, define a sort order for the lookup field. Click the button Next.

If necessary, adjust the width of the substitution window - this is convenient if the values are long. Click the button Next.

Enter a name for the new field. To be absolutely sure that the data in the two tables always matches, check the Enable data integrity check, and then select one of the following options.

Cascading deletion allows you to delete a corresponding record in another when you delete a record in one table.

Limited deletion does not allow deleting a record from a table if it is related to a record in another table.

Note: Don't select element Allow multiple values in the Lookup Wizard if it is used to create a relationship.

Change a relationship in an Access 2010 web database

Open a table into which values from another table are being substituted.

Select the field into which values from another table are substituted.

On the tab Fields in the group Properties click the button Change substitutions.

Make the necessary changes according to the wizard's instructions. You can change the following items.