To manage fines the following spreadsheet was created by one of the Librarians.

If you look closely you will see that there are actually 2 entities involved, the person and the fine, and for Bob Kowhai, his address and phone are repeated (and will need to be repeated every time he has a fine). Obviously this is not great and can introduce a lot of problems if for example he changes address.

So if you think about it a person can have 0 1 or many fines, but a fine can belong to only one person. This is known as a 1-many relationship.

Carrying this thinking on it would make sense to have a table that contains just the persons information and a second table that would just contain the fine information with a link back to the person. This means that each row needs a unique identifier (this is called the primary key). Then in the fine table if we have a way to link back to the person table we only need to record the persons information once. So we add another field (in this case the PerID) to the fine table. This is known as a foreign key. So we would wind up with the following...

So you will see that the fine table now has a way to link back to the person table.

If I was to represent this in database terms I would prefix the fields and name the tables so this would look something like

If I was to draw the relationship, (this is called an Entity Relationship diagram (ERD)) it would look like:

But is is actually more useful to show the fields (attributes). So this can be represented as an extended ERD (E-ERD) as follows:

So if you read it - it says that:

• each person can have 0, 1 or many fines, and

• a fine can belong to only one (and only one) person.

If we were to plan out tables we would need to know what the tables were called, what the Fields (attributes) were and what the properties of each attribute were. In MS-Access this is done in the design view and for our tables it would look like:

I have shown the properties for the PerID in tblFines as you must use Number if you want to link to an AutoNumber (the actual type is a Long Integer).

THIS IS REALLY IMPORTANT as if your PerID is not Long Integer it cannot be joined to the AutoNumber.

Procedure

So to create a database in MS-Access you would

1. Create a new table called tblPerson and set PerID to a Primary Key (right click after adding)

2. Create a table called tblFines and set FineID to a Primary Key (right click after adding), and add the PerID field (With a Field Size of Long Integer) so you can link back to tblPerson

3. Use [Database tools] > [Relationships], then select both tables and add to the relationship window.

4. In the relationship window drag from the PerID (in tblPerson) to the PerID (in tblFines) (always drag from the 1->many). This should give you a pop-up so click Enforce referential integrity and then [Join Tyle] and select "2. Include ALL records from 'tblPerson" ..." then [Ok] & [Ok]

If you have an existing table with all the data you can copy the table, name each then manually fix the data (in tblPerson, delete all the file info & any repeating names, in tblFines add the PerID field and put in the link to tblPerson, then remove the Person details). It is possible to automate - but not easy.

The following YouTube video shows you how .....

Troubleshooting

Why won't Access let me create a relationship link?

• Both tables must have a primary key

• The field you are linking to in the many table is a primary key (it shouldn't be!)

• If you are trying to link tables with data in them, the "many" table field you are linking to must have valid data (must contain a valid value from the 1-table and it can't be blank)

• Check that one doesn't exist already. When you have both tables in the relationship view the link should show. If you right click on the link and delete you should be able to recreate the link (2019-01-27 Thanks CaseyT).