Relationship Help(No not my girlfriend)

[Spentak]

I am trying to figure out what is the best way to lay out my situation.

I am designing a drug testing database. I need to be able to "sign" a donor in for a test(s) for drugs. It could be 1 drug, or 5 drugs. When the donor is signed in, i need a date and timestamp to be placed on that test for that day. But I also need to have the database allow the user(the company that will use the database)to create their own tests(In case there is a drug that the company needs testing for that I don't already have on the list)

I was told to have 3 tables, one for the Donor(with personal info on it), a table for the drugs(with drugs listed in rows, rather than drugs listed as fields, allowing them to have an autonumber id) and an intersecting table with both of those primary keys). I was told also to use a combo box to select the drugs(but a combo box can only let you select one item right?)

If I did it that way, each donor that had a test would have a lot of drug test ID numbers for each drug being tested. Anyway can someone clarify my problem?

 

[Kiwiman]

Howzit

I would have something like the following:

tblDonors
DonorID (PK) [autonumber]
FirstName
LastName
etc

tblDrugs
DrugID (PK) [autonumber]
DrugName
etc

tblTests
TestID (PK) [autonumber]
DonorID (FK) [combobox on form looking up Donor]
TestDate
etc

tblTestDetails
TestDetailID (PK) [autonumber]
TestID (FK) [automatic as relationship to tblTtests]
DrugID (FK) [combobox on form looking up drug]
etc


This way a donor can be tested on different days, and in each test can be tested for multuple drugs. Also a donor could be tested for the same drug many times (I would ensure that these tests were performed on different days - not tested twice for the same drug in the same test id).

You would have to select them individually using the combobox. If you were to test 5 drugs on teh one day, you would have 5 lines in the tblTestDetails.

It is probably doable using a list box, but I haven't used these much - so don't really know.

 

[Spentak]

Which Table

Which table then would the fields, Positive, Negative, and PositiveValue go in? And if its that last table you listed, how do those results associate themselves with the certain test?

 

[Kiwiman]

Howzit

That would be the last table - you will be able to specify the result against each of the selected drugs tested.

The table tblTest (one) is related to the table tblTestDetails (many) on the field TestID, on a one to many relationship

• Each test can test for many drugs

The table tblDonors (one) is related to the table tblTests (many) by DonorID (one), on a one to many relationship.

• Each donor can be tested many times

You can then link the 4 tables together to get the results of the tests in a query \ report etc