Report Challenge

[Michael]

Hi Folks - Here's the situation .....

Let's say this is a medical test database. A doctor can order up to 10
tests. Sometimes they order 3 tests, sometimes 9 tests, etc. It would be
easy for me to create a report that lists the number of tests per doctor.
However, I need to produce a one-page report that lists all 10 tests, then
count the number of times that the doctor ordered the test. If he did not
order a test, then the test should have a count of zero. Any thoughts on
this? Thanks.

Michael

 

[Duane Hookom]

Without tables and fields to store data, this might be difficult. If you
have tables and fields, you should tell us about them. I expect there will
be a table of each different test type but I am not certain.

 

[Michael]

Sorry ... I do have tables and fields .... here's is a simple example. I
have a doctor's table, a test table, and a junction table that stores the
doctor number, test number and test date. The data is entered within a
form/subform situation. I hope that helps a little.

Michael

 

[Duane Hookom]

Aren't there patients or samples or something involved here? Are doctors
getting tested? Does the junction table have a unique index on the primary
keys from the doctor and test tables?

 

[Michael]

Duane - Nope, just doctors, tests and dates. Doctors order up to 10 tests on
any given date. I just want to count the number of tests per doctor, but I
always need all 10 tests to be displayed on a one page report whether they
were ordered or not. Thanks.

Michael

 

[Duane Hookom]

I would first create a cartesian query (qcarDrTest) like:
SELECT d.*, t.*
FROM Doctors d, Test t;

This should include all combinations of information from both tables. Then
create a final query that uses you junction table and qcarDrTest. Set your
join lines to include all records from qcarDrTest.