Compound Primary Kep and Relationships (1 Viewer)

[JeepsR4Mud]

Hello,

I am designing a database with the informatin in weekly reports submitted by 34 different sites. A few of the questions can have multiple answers (ex. staff injuries).

I have a primary table comprised of fields from questions with only one answer (ex. number of employees). The primary keys are SITE and WEEK, where week is the week number of the year. At no time should there ever be a duplicate record where BOTH of these fields are equal.

How do I set up the relationships between the primary table and the secondary tables?

Is it the same one to many relationship?


In the secondary tables (ex. staff injuries), there may be cases where there were 2 or more staff injuries at a site.

Eventually, I am working toward a form where the secondary tables are subforms.

Thanks.

 

[The_Doc_Man]

If your primary table can never have a record where both Site and Week are duplicated, then make the combination your table's primary key. (This is called a compound primary key.)

This means that dependent tables must also have this combination as an index, though the combination does not have to be set to Unique.

Then you could have a many-to-one relationship with your main table as the "one" side and other tables in the "many" position.

Just for clarification, you should be aware that "one to many" is really "one to ANY" - where ANY could be zero, one, or more than one. So when you build your explicit relationships, you click on the join type that selects all records from your MAIN table and ANY records from the other linked table.

This way, reports behave rationally even if there is no entry for that site for that week in the secondary tables.

If you need help with the mechanics of how to declare compound keys, keep posting in this thread. One of the older hands will be glad to answer.

 

[JeepsR4Mud]

Thanks.

I actually figured out the posted problem, but now have another.

How do I design a filter that lets me make a request?

For example, I designed a database for weekly reports form 33 different sites, using site and date as the compound keys.

Figured out all the relationship things for data entry.

BUT, how can I design a filter that lets me specify the site and the date, that will then call up the forrect form, so I don't have to wade through several hundred.

And, is the process the same for a report?

Thanks.

Gayle Ann