Relationship versus a filter field in each table

[waubain]

I am building a database to manage the drug inventory of research studies. Anything done with the inventory, who it was for, dispensed by whoever, all revolves the specific study. The main table tblStudy has an primary key StudyID. Attached is a jpeg of the relationships as I think it should be. I have put a foreign key fk_StudyID in each table as a filter, since when I am working with a table I only want to see information about that study. My question is:

Do I need to create a relationship in each table that contains fk_StudyID with the main tblStudy?

If I do Access tells me a relationship already exists, r do I want to create another. I am new to normalization.

Thank you in advance

You don't know what you don't know...

 

[PHV]

You have a lot of redundant fk_StudyID columns in your schema and then cyclic relationships.
Have a look here:

http://www.r937.com/Relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[waubain]

Thanks PHV,

I read your suggestion and other Access books and mostly understand the concepts, but I still must be missing something. Maybe it is how I envision my data to look in a form versus how the data is structured in the tables. Up until now my databases have been flat with lookup tables

In my database, my main form is contains a continuous subform of all the studies. I highlight the study in which I need to do something. This places the StudyID(autonumber) in a textbox named txtStudySelected. If I then click a cmdButton (on the main form) that opens the Inventory, Dispensing, etc form, only the records associated with that Study are displayed. This was the reason for the redundant fk_StudyID. Every Inventory item, dispensing record, or patient all need to be tied back to a specific study in a one to many relationship. Only researchers have a many to many relationship with studies.

You don't know what you don't know...