A better way?

[Pangloss14]

Hi,

Sorry about the vague title, couldn't think of succinct way to describe this... I have a table (tblSample) with the primary key of SampleID. It is the one side of a one to many relationship with tblDetails (foreign key is SampleID) whose primary key is the combination between SampleID and TestID. The number of TestIDs is fixed. It also has a boolean field for Result.

What I'm trying to do is make a report that can display the number of positive results for each TestID. I could make a table that has a one to many relationship with tblDetails containing TestID and TotalPos for the number of positive results for each TestID and build a report off of that. Just wanted to know if anyone could suggest a better way of doing this because I don't need to store this data (some sort of query I would guess). Thanks

John

 

[neileg]

My teachers used to say that the answer was in the question, and in this case it is.

You need to create a query to join the two tables on the SampleID. Ad a criterion to the reults to include only the +ve results, and then make it a totals query

 

[Pangloss14]

Hi,

Perhaps I'm missing something here... Anyhow, the query I'm using is as follows:

SELECT tblTestDetails.Result, tblTestDetails.TestID
FROM tblSample INNER JOIN tblTestDetails ON tblSample.SampleID = tblTestDetails.SampleID
WHERE (((tblTestDetails.Result)<>0);

For each Sample, I have TestID 1-7 and I want to make a report that counts the total number of TestID 1 that have a positive result, TestID 2 that have a positive result... and ideally the number of positive tests (as opposed to samples). I.e. Sample 1, has TestID 1, 4, 7 positive, therefore that counts three in the grand total of things.

It seems to me that when I use the count function of totals query, it counts the SampleIDs that have positive tests.

By looking at the query results, I can get what I want (i.e. I could count the number of positives in the table, or I could extract the info via VBA and put it into a table) but somehow that doesn't seem to be the best way to do it.

Thanks again.

John

P.S. Neil, Howay the Lads .