Hi,

I have set up three tables all of which have a column for 'Individual_ID' and a column for their last name. The ID column is Autonumber in two of these tables.

What I would like, having made relationships between all three tables using the Indiviual ID column, is that the ID number and last name are linked so that I do not have to keep entering the last name in all three tables when adding new individuals. How can I do this?

Thanks for your help!

Hello Sarah,

Hope you enjoyed your Access training.

Thank you for your question regarding tables and relationships. When you first learn about Access there are some fundamental concepts that you need to follow to ensure that your database design give you a good result. As Access is an open platform for design, you are free to design the database in any way you see fit. However, that does not mean you wikll always get a good result. Here are some thoughts about your situation.

As I am not 100% up to date with what you are wanting to acheive with your database, it may be useful to know a bit more. Until then I wikkl share some general concepts that you will hopefully find useful in your database design.


When creating tables, primary keys and relationships, there are some general guidlines you want to follow:

Build tall not wide
What this means is that you should group together all the fields that of similar nature. In your example, I would suggest that you have one table, that contains all the 'Individual_ID's and a column for their last name.
In principal, there is no need to have 3 tables for this information.


Understand how to use relationships effectively
Based on the fact that you have attended our Access Introduction course, you will not have had much exposure to relationships between tables. Our Intermediate course has more detail.
From what you have described, it seems that you are trying to link the 3 tables so you dont have to duplicate effort. This actually gets handled when you follow the first suggestion above, as you should have only one table containing the 'Individual_ID's and a column for their last name.
Regarding the relationships use in this case, you would relate the individual ID to a similar field in a related table, which contains other information related to the Individual.

Please bear in mind that that building relationships has many complexities, and can take a while to understand fully. Take your time in doing this, as it is often not possible to assismilate all the information on this topic in one go.
It takes time and application, so be patient.


I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Access question?

Have a great day.
Regards,

Richard
Microsoft Office Specialist Trainer