Building relationships in Microsoft Access 2019

With this section, you will come to understand the termrelationship, and be able to outline the different types ofrelationships and when they should be applied. We will look at the rulesfor creating relationships between tables in a database.

Access is a relational database, which means that tables that arerelated will have fields that have a common association between them.Related fields do not have to have the same names, but it is obviouslyeasier to identify the relationship if they are the same. Related fieldsare required to have exactly the same data type unless theprimary key field is set to AutoNumber. This will only work if the fieldsize property is the same on both matching fields, for example, Long Integer.

A relationship is a join (or link) between related fields in thedatabase.

If we think of a company when creating a database, we would create atable for each of the following—employees, customers, products, orders,and order details. There would normally be orders with more than oneproduct, so the linking of tables would be necessary. If we consider thecompany example, the tables listed would have the followingrelationships between them:

  • Employees sell to a customer.
  • The customer orders products.
  • The order will contain a number of order details.

A relationship needs to be created in order for data to be used frommore than one table at a time. For example, when creating a query, youwould need to have access to more than one table so that the data can becombined to produce a specific query result. Relationships are also usedto maintain referential integrity, which will be discussed later in thisbook.

Leave a Comment