Easy question, I would have thought..

[Pangloss14]

Hi,

I'm trying to create a database to keep track of tests. Basically, I want to have a sample on which multiple tests are done, then each of these tests has subtests.

When all is said and done, I would like to be able to query by sampleID and find a list of everything pertaining to that sample. I thought this would be easy but I'm getting bogged down on the second set of tests.

Table one contains SampleID (primary key) as well as a bunch of other info for that sample.

Table two contains TestID, Result and SampleID (which has a one to many relationship with the same field in table one, the many side is in this table). I've also created another field RecordID as the primary key (I could have used a combination of TestID and SampleID).

Currently, table three contains subtestID, subtestResult, and a lookup to RecordID in table two (therefore a one to many). Primary key is an autonumber field called SubTestRecord but it could also be a combination of subtestID and RecordID.

I can't get a query to list everything related to the SampleID in question. Am I completely out to lunch? thanks.

 

[FoFa]

Assumptions:
Tbl1
PK1

Tbl2
PK2
FK1 (ties to PK1)

Tbl3
PK3
FK2 (ties to PK2)

SELECT YourColumns
FROM Tbl1
INNER JOIN Tbl2 ON FK1 = PK1
INNER JOIN Tbl3 ON FK2 = PK2
WHERE YourWhereCriteria

Should give you everything where there is a match in all tables. If say Tbl3 has no records, but you still want to see them, use LEFT JOIN instead.

 

[Pangloss14]

Fofa,

OMG, I'm so stupid. I had the query right, I had my tables set up right, I just didn't populate the tables correctly. Anyhow, thanks for your input, it made me think about what I had done.

Regards,

John