Stuck with my relationships!

[davewilldo]

I am trying to set up a database of communication cable test project results.
The database simply stores client data and test results for individual test projects.
To ask my question I will not confuse things with detail but will focus on what I am having problems with.
In the database there is a TEST PROJECT that always has results from tests in a "PRINCIPLE BOX", and that "PRINCIPLE BOX" will have circuits, and it may also connect to other "SUB BOXES" that have test results from circuits in these boxes.
I am using three tables to hold the test results for each test project

First table has data about the PRINCIPLE box
It has a unique project ID field - (the field that I want to be the unique and primary key)
and many more test result fields

Second table for SUB boxes connected from the PRINICIPLE box
the SUB box has a unique sub box ID field - (the field that I want to be the unique and primary key)
and date, location and description fields



A third table for CIRCUITS within the SUB boxes
with one unique circuit ID field - (the field that I want to be the unique and primary key)
and many more test result fields

I thought this would be easy-peasy to relate in that the PRINCIPLE box project ID primary key would be a one-to-many to the SUB box ID field which is a one to many to the circuit ID field in the circuits table.
But I find that Access will not allow a one to many field if there is a primary key on both ends of the relationship.
I fixed this by creating a field called "copy of sub box ID field" in the sub box table, made the required one-to-many relationship from the principle box table to sub box ID field, and then made the copy field the primary key and made the one-to-many from "copy of sub box ID field" to the circuit ID field and then made a "copy of circuits ID field" in the circuits table and made this the primary key.
By this route I was able to complete the required logical relationships.
Then I realised I would have to do some automatic setup that made sure the "copy of" fields were always kept synchronised with the primary key fields.
AND THEN I realised that this approach was completly stupid and I was obviously doing it all wrong.
So what am I doing wrong and how should I do this?
Apologies for long drawn out question but I think you will get the drift.
Any suggestions gratefully received.
Regards

[ItsMe]

Maybe you could have a table for projects that would identify the project number, team leader, start date, status... simple and basic stuff unique to the project. In another table, tblBoxes, you could list all of the boxes. In tblBoxes you could have an FK field for the Project Id and an FK field for the box sub type.

A third table would provide a list of Box type/category. The PK of this table would go in the appropriate FK for the Box description table.

Now you can log events and stuff in another table using PK's from tblBoxes. Maybe....

[davewilldo]

Maybe you could have a table for projects that would identify the project number, team leader, start date, status... simple and basic stuff unique to the project. In another table, tblBoxes, you could list all of the boxes. In tblBoxes you could have an FK field for the Project Id and an FK field for the box sub type.

A third table would provide a list of Box type/category. The PK of this table would go in the appropriate FK for the Box description table.

Now you can log events and stuff in another table using PK's from tblBoxes. Maybe....

Hi, thanks for your reply.
Please excuse my ignorance, I am no Access expert.
I know PK is Primary Key but not sure what the FK is?

[ItsMe]

FK stands for Foreign Key. The table that logs events should contain several of these foreign key fields. Any table that gets updates often should be like this. When you open it and look at the table, it should not be intuitive because most of the columns hold key values in the form of long integer data types.

Foreign Key fields represent Primary Key values from other tables.

[orange]

davewilldo,

My suggestion is to work through this tutorial that has all steps to identify tables, table structure with attributes and relationships between tables.
It also has the answers.
Good luck.

[davewilldo]

FK stands for Foreign Key. The table that logs events should contain several of these foreign key fields. Any table that gets updates often should be like this. When you open it and look at the table, it should not be intuitive because most of the columns hold key values in the form of long integer data types.

Foreign Key fields represent Primary Key values from other tables.

Thanks ItsMe, appreciate your help.

[davewilldo]

davewilldo,

My suggestion is to work through this tutorial that has all steps to identify tables, table structure with attributes and relationships between tables.
It also has the answers.
Good luck.

Thank you orange.
That did the trick.
I had not got as far as surrogate fields but the tutorial is just the solution I needed.
In fact it turns out I was not being as stupid as I thought and my original solution will work correctly as long as I get the auto-entry links working.
Probably do that in a form.
Many thanks

[orange]

Glad to help. Good luck with your project.