Count unique field1 combined with count field2, both grouped andungrouped

[john.mctigue]

I am using Access 2003 SP3

I have two tables, haema_Patient with PK 'ID' joined 1:many to
haema_Diagnosis with PK 'DiagID' and FK 'ID'. haema_Diagnosis also
records the hospital at which a diagnosis was made.

haema_Patient
----------
PatientID (PK)


haema_Diagnosis
------------
DiagID (PK)
PatientID (FK)
HospitalID

A patient may have one or more diagnoses, each diagnosis occurring at
a particular hospital. A patient with more than one diagnosis may
have had those diagnoses made, in aggregate, at one or more hospitals.

I would like a query that will give a count of unique patient IDs, and
of all diagnoses associated with those patient IDs, for each hospital
such as:

Hospital Count of unique PatientID Count of DiagID
-------- ------------------------- ----------------------
A 12 13
B 4 4
C 23 25

I would also like a query to do summary counts across all hospitals,
such as (using the same columns as above):

[ALL] 37* 42

*NB: Not 39 - two patients each had two diagnoses, each at separate
hospitals.

Any help would be greatly appreciated.

John McTigue

 

[Allen Browne]

Hi John.

There are a few ways to do this. Simplest might be to use a subquery to get
the count:
Subquery basics: Aggregation, counts, and totals
at:
http://allenbrowne.com/subquery-01.html#Aggregation

If you want to do it programmatically, this DCount() replacement function
has an optional argument illustrating how to get a distinct count:
http://allenbrowne.com/ser-66.html

All the best for the Christmas season.

 

[john.mctigue]

Allen,

Many thanks for the reply - I had hours of frustration with this,
which is now so simple using your ECount function. (Subqueries are
still a mystery to me.)

The summary by establishment:
SELECT
haema_Diagnosis.DiagnosisEstablishmentID,
ECount("PatientID","haema_Diagnosis","DiagnosisEstablishmentID = " &
[haema_Diagnosis]![DiagnosisEstablishmentID],True) AS [Unique
PatientIDs],
DCount("DiagID","haema_Diagnosis","DiagnosisEstablishmentID = " &
[haema_Diagnosis]![DiagnosisEstablishmentID]) AS [Count of DiagIDs]
FROM haema_Diagnosis
GROUP BY haema_Diagnosis.DiagnosisEstablishmentID;


The overall summary:
SELECT DISTINCT
ECount("PatientID","haema_Diagnosis","",True) AS [Unique PatientIDs],
DCount("DiagID","haema_Diagnosis") AS [Count of DiagIDs]
FROM haema_Diagnosis;

In passing I note that I needed to insert an empty string for the
Criteria argument in ECount, rather than leaving the argument blank as
in the examples on your ECount web page. When I left it blank I got
an error message on attempting to leave the criteria cell, "The
expression you entered contains invalid syntax. You may have entered
a comma without a preceding value or identifier". Would entering the
empty string as an argument have any bearing on the fact that the
query, without the DISTINCT keyword, returns multiple rows?

Once again, Allen, thank you for pointing me towards a solution.

Best wishes for Christmas and the New Year.

 

[Allen Browne]

The 'empty string' issue might have to do with the context from which it is
called. The missing argument should be fine within VBA code, but may not
work in other contexts (such as in a query.)