INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Relationship versus a filter field in each table

Relationship versus a filter field in each table

(OP)
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...

RE: Relationship versus a filter field in each table

(OP)
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...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close