Many of us work in production, where databases are most often used for reporting. In this article, we will talk in detail about how to join tables in Access. This article is recommended for advanced users.
Microsoft Access is one of the database management systems. It includes the VBA programming language, which allows the user to write different applications for working with the database himself.
How to join tables in Access?
Access usually has an array of tables that are related to each other. Between one or more tables, logical relationships can be established using the data schema description window. This gives:
- Ability to improve work efficiency.
- It is easier to expand the information that may be needed at different stages of production.
All changes that occur are displayed in all databases.
To answer the question about how to link tables in Access, you need to show everything with an example: four tables are created in the database: Students, Disciplines, Academic Performance and Student Groups. We establish logical connections between them. How to do it?
Algorithm:
- Select the main key: Group Code, Student Code and Discipline Code.
- Between the KodGy key field. in table "Group" and the secondary key CodeGy. In the table. Students will establish a one-to-many relationship.
- Close all open tables in the Access form, including related tables.
- In the special "Add" dialog box, select the field name.
- Close all dialog boxes.
Instructions on how to establish logical connections in all tables of the "Data Schema" window:
- From the "Groups" table, you need to move the Group Code field to the same field from the "Students" table.
- The “Change Links” window appears.
When all the actions are completed, in the “Data Scheme” window, you can see the one-to-many relationship between the tables of the Group of Students and Students.
According to the same plan, it is possible to establish a connection between the Student Code and Academic Performance fields and the Students tables. Then in the tables "Performance" and "Disciplines" for the field Code Discipline.
- When all logical connections are established, close the "Data Schema" window.
- Fill in all tables. First, enter the data in the table “Groups” of students, since in the table “Students” the field Group Code is used as a column to populate the field in the table “Students”.
- Indicate the relationship between the tables "Students" and "Academic Performance", "Disciplines" and "Academic Performance".
Benefits of Using Logical Links
Why is keeping data separate in related tables better? Consider the advantages of such storage.
Coherence
In the process of establishing relationships, the likelihood of inconsistent and ambiguous data is reduced. This happens because any item is written to the same table just once. For example, the surname of the student is contained in the table "Students".
Efficiency
- Saves disk space by storing information in one place.
- Information is extracted better from small tables.
Simplicity
Information is stored in various tables, so the structure of the database can be better understood. There is also Access, VBA related tables - this is such a programming language that makes it possible to link tables in Access and Excel. If you know this language, you can program the database.
Important tip!
When planning to design a database, immediately think about the relationships. If you already have a table with a primary key, using the lookup wizard you can build a foreign key field. Then the lookup wizard assists in creating the links.
Finally
We hope that after reading this article about how to connect tables in Access, you have greatly improved your database management skills, which in the modern world is a very, very useful skill, especially for beginning programmers. We also recommend that you go to the official website of the application and read the user manual.