How to Design Relational Databases in Microsoft Access for Beginners

The ensuing instruction set details how to design relational databases in Microsoft Access. This guide will first show how to properly link two (2) tables. I will then detail how to create a form from this new relationship, allowing the user to input new information into the database. No previous experience with this software is required. All you need is a computer with Microsoft Access installed. This process should take no more than 15 to 20 minutes from start to completion. Once you have this information, you can then link a practically infinite amount of data points, making data entry and logging much more time efficient. Let’s dive in!

Disclaimer: This instruction set uses preloaded data to build the tables in Access. You can either preload your own data or manually input data to make your tables.

Step 1: Open Microsoft Access.

Step 2: After Opening Access, Navigate to "All Access Objects." This Is Where Our Tables Are Listed.

Step 3: Right-click the First Table You Want to Add to Your Relational Database (here on Named the "parent" Table). Select "Design View" in the Corresponding Window.

Step 4: With the Identification Field Highlighted, Select “Primary Key.” a Key Icon Will Populate Next to the ID Field. Then, Close the Table. (Access Will Either Ask You to Save the Table or Save It Automatically).

Step 5: Right-click the Second Table You Want to Add to Your Relational Database (here on Named the "child" Table). Select "Design View" in the Corresponding Window.

With the identification field highlighted, select "Primary Key." A key icon will populate next to the ID field.

Step 6: Add a Final Field to the Child Table by Clicking the First Blank Field Under “Field Name.”

This field’s text must match the field name of the primary key (or first field) from the parent table, and is called the “Foreign Key.” Then, close the table.

Step 7: Using the Navigation Panel, Select “Database Tools,” Then Select “Relationships.”

Step 8: Drag the Parent and Child Tables to the “Relationships” Panel.

Expand the tables as necessary to ensure all text from the tables is shown.

Step 9: Drag the Primary Key From the First Table to the Foreign Key of the Second Table. This Will Open the “Edit Relationships” Window.

Step 10: Select “Enforce Referential Integrity,” Then “Create.” There Will Now Be a Link Between the Two Tables.

Step 11: Using the Navigation Panel, Select “Create” Then “Form Wizard.”

Step 12: Using the “Tables/Queries” Drop Box in the Corresponding Window, Move the Fields From the Parent Table You Want on Your Form. Do the Same With the Child Table.

Note: Do not add the primary nor foreign keys from the child table to the form. Then select “Next.”

Step 13: Choose “Form With Subform(s) for Your Data View, Then Select “Next.”

Step 14: Choose a Layout for Your Subform. We’ll Go With Tabular As It’s a Bit Easier to Adjust. Then Select “Next.”

Step 15: Select “Open the Form to View or Enter Information,” Then “Finish.”

Step 16: Your Form and Subform Have Been Created.

If required, adjust the layout of the form and subform to show all fields. Right-click your form, select “Layout View” and adjust as necessary. Once you’ve fixed the layout, right-click the form and select “Form View” to enter data.

Step 17: Begin Entering and Logging!

Congratulations! You have successfully linked two distinct tables in Microsoft Access. By creating an associated form and subform, you can now enter data which will reflect in the corresponding tables.

To test, enter text in the first field of your form. Select your keyboard’s “Tab” button to move to the next field in the form/subform. On the last field in the subform, selecting “Tab” will clear both form and subform and move the data to their respective tables. Select either the form or subform’s accompanying table. When you see the data you input into both form and subform on the tables, you will have completed the task successfully.

Troubleshooting: only add primary keys from the parent table to your form. Omit adding the primary and foreign keys from the child table to your subform. Do not add more than one primary key per table.

Thanks for reading and enjoy a much more efficient process logging and storing data!