Correcting Table Structure

[bvanscoy678]

Hello,

I have been working on a a table design, but I am failing to create the correct relationships.

My main table is the tblIncident with the three remaining tables being linked via a one to many relationship. I am using an auto number as my primary key, then linking that number to a FK in each table.

I am not doing something correct, but not sure what it is. The end goal is to create one form with a varying sample of combo boxes, check boxes and data entry fields from each table.

I did not plan to create a subform for each table, but maybe that is required?

I have attached my sample database in hopes of explaining it better.

Thanks for any help.

Brent

 

[jdraw]

Relationships are based on business rules/facts. So if you could tell us about the business (a typical day in the business), I'm sure readers will offer suggestions.

 

[KenHigg]

For starters, it looks like you could normalize the exposure table

tblIncident
ID pk
date
etc

tblExposures
ID PK
IncidentID fk
ExposureID fk

tblExposureTypes
ID pk

Each Incident can have multiple Exposures and each Exposure has an Exposure Type

Hope this makes sense...

 

[bvanscoy678]

Hello,

Sorry, I was delayed at work longer than I thought.

The form will be used for fire fighters to report infectious disease exposures. The incident table contains the basic information about the call and the person involved in the exposure. The exposure table will contain all the information about the exposure, same for the source (person) and office use only.

Ken,

Yes that makes sense (many to many relationship). I must confess that the original database was a mess, the numbers of the exposures are limited and I was very short on time. Let me take your suggestion, rework it a bit and see if I can do a better job of normalizing the data.

As you suggest, the incident can have many different exposures and the employees can have many different incidents. I'll need to create a linked table between everything. I will take the primary key from both tables and insert into a new table for my many to many relationship.

I get a little confused when on how to create the auto numbers and link them so all the tables are related.

Thanks. Brent