How To Build Table Relationships In Microsoft Access 2007



Access 2007 Relationships

At this point, you’ve set up the tables you requirement for your database and have made fields for the distinctive tables. Connections give Microsoft Access 2007 the way to unite this data for you when you require it.

This lesson discloses how to set up connections between the tables in anMicrosoft  Access 2007 database. You will figure out how to peruse and control the relationship outline. You will likewise find out about essential and remote keys, relationship composes, and referential trustworthiness.


To build up a connection between tables:

  • Tap the Relationships order in the Show/Hide amass on the Database Tools tab in the Ribbon.

NOTE: Tables must be shut keeping in mind the end goal to set up connections.

Relationships Command

  • At the point when the Show Table discourse box shows up:
  • Select each table name, at that point click Add for the tables you need to relate.
  • When you’re set, close the Show Table discourse box.

Show Table Dialog Box

  • You should now observe a relationship delineate contains the greater part of the tables that were chosen.

Relationship Map

To move a table that shows up in the relationship delineate:

  • Place your mouse over the table you need to move.
  • Hold down the left mouse catch, at that point drag the table to another area.
  • Discharge the mouse catch to drop the table in its new residence.

Understanding the relationship outline

The relationship outline the greater part of the tables that were chosen to relate, and the majority of the fields that were already set up for that table. Notice that the main field has a key symbol beside it. This is the essential key for the table.

Primary Keys

Essential and remote keys

An essential key is the principal field in each table of the database. You may review that this field auto-numbers as a matter of course, so every record in the table has its own one of a kind number to recognize it. Microsoft Access utilizes this number to rapidly pull data together when you run questions or reports, which are secured later.

In the case over, the essential key for the Customers table is Customer ID, the essential key for the Orders table is Order ID, and the essential key for the Books table is Book ID.

A remote key is a field that is the essential field in its own particular table yet that appears in another table. On the off chance that you take a gander at the Orders table, the fields Customer ID and Book ID show up there, and in addition in their own separate tables. These fields are the essential key in their own tables, yet in the Orders table, they are viewed as outside keys.

Foreign Keys

Relating tables

There are a couple of approaches to set up connections between tables:

  • Utilizing the Edit Relationships summon situated on the Design tab of the Ribbon
  • Utilizing the simplified strategy

The two techniques give you a similar final product, however the intuitive strategy is significantly less demanding and spares you a few stages.

To relate tables with the intuitive strategy:

Select a field name from one table by holding down the left mouse catch.

Relationship Map

  • Drag the field name from one table to the next table in the coveted relationship.
  • Drop the principal field name onto the field name you need to relate by discharging the left mouse catch.
  • In the case above, we chose the Book ID field from the Books table and relocated it on the Book ID field in the Orders table.
  • The Edit Relationships discourse box shows up.

Edit Relationships Dialog Box

  • Select the Enforce Referential Integrity choice. This alternative is clarified in detail beneath.
  • Snap Create.

Understanding kinds of connections

Access 2007 takes into consideration a few unique kinds of connections. These include:

  • Coordinated
  • One-to-Many
  • Many-to-Many

The relationship write you will go over most much of the time—and the one made in our book shop situation—is the One-to-Many relationship.


The One-to-Many relationship implies information for that field will show up a solitary time in one table however ordinarily in the related table.

For instance, how about we take a gander at one of the book titles in our book shop. The Book ID for the book ought to seem just once in the Books table since this table records each title loaded in the store. Be that as it may, it will most likely seem ordinarily in the Orders table since we trust it gets requested by numerous individuals commonly.

The images for the One-to-Many relationship resemble this:

One to Many Relationships

Implementing referential trustworthiness

In the Edit Relationships discourse box, a choice to Enforce Referential Integrity shows up.

You should click Enforce Referential Integrity to ensure you never have a request for a book that doesn’t show up in the Books table. Choosing this choice advises Microsoft Access  to check for these things when somebody is working with your information records.

Altering existing connections

Microsoft Access 2007 enables you to alter connections that as of now exist. This should be possible utilizing the Edit Relationships charge on the Ribbon. In any case, a substantially more straightforward path is to just double tap the connection that shows up in the relationship outline. Either strategy raises the Edit Relationships discourse box, where you can change your settings.

to know more about Microsoft Access Releted Articles visit:office com setup

Leave a Reply

Your email address will not be published. Required fields are marked *