I have a bit of a dilemma in my related tables (screen shot of relationships attached).

Basically at the moment I am using a 1-1 relationship between my tables using the primary key of CYID. The CYID is populated using an auto number at the moment but the problem I have is if the tables become out of sync with the numbering.

What I would like to do is when I create a new record in tblClientDetails is create a new record in each corresponding table that is linked and then pass the CYID across to each table. Can anyone offer any assistance or give me any suggestions for better ways of doing this?

That's not the way to do it. Each table needs its own primary key, you cant use the same one in each. You hold the primary key of the parent table as a foreign key in the child table. This is simply a number field that acts as a link back to the parent table. The easiest way to ensure this happens is to use a form/subform arrangement and Access will handle the parent/child links.

Plus you don't create empty records, you only create a record when you have data to store.

Plus your design is not normalised. The 3 connections tables should be only one.

Plus you don't need the client name in every table, only tblClientDetails.

That's not the way to do it. Each table needs its own primary key, you cant use the same one in each. You hold the primary key of the parent table as a foreign key in the child table. This is simply a number field that acts as a link back to the parent table. The easiest way to ensure this happens is to use a form/subform arrangement and Access will handle the parent/child links.

Plus you don't create empty records, you only create a record when you have data to store.

Plus your design is not normalised. The 3 connections tables should be only one.

Plus you don't need the client name in every table, only tblClientDetails.

Thanks Neil I'll give it a try. Im still quite noob when it comes to databases so I'll merge the connections all into one. I was working off the assumption that a new related record had to be created for the relationship to work. I know I dont need the client name in each table but while im working on it iv kept it in there as its the easiest way for me to see which record relates to which, but once iv got it all working im going to remove them.