Create PLZ and Ort effectively in Access as a lookup reference table

Managing data in Access can lead to unexpected problems, especially when it comes to compliance with normal forms. Often, we find ourselves in a situation where we store data redundantly or represent it incorrectly. In this tutorial, I will show you how to create a lookup reference table for the effective management of postal codes (PLZ) and places. This will not only improve the data integrity of your databases but also significantly reduce the likelihood of errors and duplicates.

Key takeaways

• Creating a reference table for postal codes and places improves data quality.
• Using the correct data types prevents input errors.
• Redundant fields can be removed through links, simplifying the database.

Step-by-step guide

First of all, you may have created your tables but violated some normal forms in the process. This affects both the customer database and the driver database. For example, if you enter the postal code and the associated locality in the customer database, this can lead to duplicates. To avoid such errors, we should create a reference table.

Identifying problem areas

Go to the design view of the customer database. Here, you will notice that the customer postal code and the locality are combined in one record. This means that you should define the field for the postal code not just as a simple number but as a text field to be able to properly capture foreign postal codes as well.

Correctly defining data types

You should change the field for the postal code to “short text.” This ensures that postal codes from countries like Holland or England can also be stored correctly. The corresponding field for the place should also be set with the data type “short text.” While in Germany most postal codes consist of five digits, some international ones can be longer.

Requirements for the reference table

To optimize the database, it is important that the data types for the reference fields match. Set the postal code field in the reference table as “short text” as well. Determine the field size according to the requirements; in Germany, postal codes are typically no longer than ten characters.

Creating the reference table

Create a new table named “Postal Code” and define the necessary fields. Add a field for the postal code and another for the place. Do not forget to mark the postal code as the primary key to prevent duplicates. This creates a clear basis for your database.

Linking the reference table

Now that you have created the reference table, we can insert the localities and postal codes from the reference table into the driver database and the customer database. This is done by establishing a relationship between the tables so that you can directly access the postal code.

Summary – Creating PLZ and Place effectively in Access as a lookup reference table

In this tutorial, you learned how important a structured database is and how implementing a lookup reference table for postal codes and places can ensure data integrity. You also learned how to choose the right data types and what steps are necessary to create the reference table. With this methodology, you can not only improve your database but also future-proof it by avoiding error-prone data redundancy.