DCount on multiple tables

[botuco via AccessMonster.com]

I have three tables in my database with the following fields:

TbAo
- AoID (autonumber)
- StudyID
- AoRead (number field, value from 1 to 5)

TbStudyInfo
- StudyID (autonumber)
- StudyDate
- 5 other fields

TbSono
- SonoID (autonumber)
- StudyID
- SonographerID

All tables are related by the StudyID field.

I would like to create a query where I can see for each SonographerID the
total number of matching records in TbAo, and the number of AoRead=1,
AoRead=2, AoRead=3, AoRead=4, AoRead=5. I have tried using Count and DCount
but I can only get the total number of AoRead=1, AoRead=2, etc fot the entire
TbAo, not for each SonographerID.

Thanks for your help.

 

[Allen Browne]

1. Create a query using all 3 tables.
In the upper pane of the query design window, you should see joins from:
a) TbStudyInfo.StudyID to TbSono.StudyID
b) TbStudyInfo.StudyID to TbAo.StudyID.

2. Double-click the (b) join line.
Access pops up a dialog offering 3 options.
Choose the one that says:
All records from TbStudyInfo, and any matches form TbAo.

3. Depress the Total button on the toolbar (upper Sigma icon.)
Access adds a Total row the the design grid.

4. Drag TbSono.SonographerID into the grid.
In the Total row, accept Group By under this field.

5. Drag TbAo.AoRead into the grid.
Accept Group By under this field.

6. Drag TbAo.AoID into the grid.
In the Total row, choose Count under this field.

The query groups by each SonographerID and by each AoRead, and gives the
count of records for each combination.

 

[botuco via AccessMonster.com]

Never ceases to amaze me how easy you guys make it all seem.
Thanks a lot Allen.