Help needed with table formation

[jojo86]

Hi

I will give you an overview of what I have.
I have a database that records sites in Blackburn and each site has a unique ID (RTP_ID). Each site has 10 objectives with 25 Measures in which the sites are scored against.
Objective 1 has 2 measures
Objective 2 has 8 measures
Objective 3 has 1 measure
Objective 4 has 1 measure
Objective 5 has 1 measure
Objective 6 has 3 measures
Objective 7 has 2 measures
Objective 8 has 1 measure
Objective 9 has 2 measures
Objective 10 has 3 measures

Each site has the same objectives and the same measures, but the scores for each are individual.

Table: SiteDetails
RTP_ID, SiteName, Postcode

Table: Objectives
OBJ_ID, ObjName, ObjScore, RTP_ID

Table: Measures
OBJ_ID, MSR_ID, MsrName, MsrWeight, MsrScore

I don't know how to create the relationship between them so that each site can have 10 objectives and 25 measures (with scores) individually. At the moment, I have them connecting as SiteDetails.RTP_ID > Objectives.RTP_ID and Objectives.OBJ_ID > Measures.OBJ_ID but it doesn't work, it thinks that the scores apply to every site.

Please help me, I know that this may not make sense without seeing the database, but unfortunately it is too large to attach.

 

[Dennisk]

create a scores table for each site, similar to your measures table
, and remove the score from the measure table.

 

[jojo86]

I have done that, and it does make the form/tables more manageable, but it still doesn't link to the main form, for some reason. I have a relationship going from the objectives table to the site details table, but its not even showing on the form.

When i go to the next site record, the objective scores and such stay the same.

 

[boblarson]

Table: SiteDetails
RTP_ID - (PK)
SiteName
Postcode

Table: Objectives
OBJ_ID - (AutoNumber PK)
ObjName

Table: Measures
MSR_ID - (AutoNumber PK)
OBJ_ID - (FK)
MsrName
MsrWeight

Table - Measure Scores
MsrScore_ID - (AutoNumber PK)
RTP_ID - (Long Integer FK)
MSR_ID - (Long Integer FK)
MsrScore

 

[jojo86]

Thank you for that! It seems to work ok now.