Access to H.E » Creating Relationships in Access

Creating Relationships in Access

posted by trikkky on October 12, 2009

This is a follow on from creating forms in Access. First we are going to create another table and a form for our Invoices.

Create the table: Invoice

Save Table as Invoice TBL

Create a form from the Table Invoice

To create forms please look at creating forms in Access

Create a new form and link it to the table Invoice. Only add the Invoice number and Quantity fields to the form because we are going to create drop down boxes for the other fields.

I have created my form. While still in Design view Drag a combo box on to the form, as we are going to have the Customer details.

Keep all settings as shown.

The Customer Table is going to provide all the details.

Only select the surname for your combo box. Click next on the next couple of screens and then the drop down box has all the surnames in it.

Select the customer number as the field value.

Your form should look like this

Repeat the steps for Product Name

For the next field we want to get our information from the Products Table.

Select the Product Name and put in the opposite box

Keep pressing next until you want to store the values in the field box Product Number.

For the last bit Name your box Product. Add a bit of formatting to the design so your two new boxes look the same as the others.

This is what I have ended up with for my Invoice form. Added a couple of buttons so when I input my data it is easy.

Creating the Relationships

On the tool bar at the top select database tools and click on the relationship tab, Another box appears select all three of the tables then close the window. (to select all just drag down with the left button clicked down).

To Create the relationship click and drag the Customer Number to the Customer number on the Invoice Table. When the box appears make sure yours looks like mine with the Enforce box ticked.

When it throws an Error make sure the form is closed and only the relationships tab is open.

We want the Product Number from the Products Table to link to the Product Number of the Invoice Table.

We now have all our data connected to our tables that we want to produce invoices for the customer. The one to many relationship means that in the customer table you can only have one record with the same content, whereas in the invoice table you can many occurences of the same data. Example a customer can be on more than one invoice but only have one customer number.