Help in counting

[elainenguyen]

Hi,
I am trying create a query that counts all records which have the field Hypotension=1 (which is Yes), but I only want to count the same customer once.
For example:
My table has 3 fields: RecID (primary key), MRN, and Hypotension

RecID Cus# Hypotension
1 6 1
2 6 1
3 7 1

then my query should count the total of 2 for "hypotension =1", not 3 because I only want to count records with the same Cus# only once. I've tried to use "DISTINCT", but it doesn't work b/c I want to count many fields in the same querry (more than 20), not just one field "Hypotension". Below is my Select code, but it counts all records (which is 3). I need to add criteria such as where to eliminate the ones with the same Cus#, however, I have no clue. Can somebody please help?

SELECT Count(IIf([Hypotension]=1,"Yes")) AS Hypotension_Y
FROM tblReview;

[timothybard]

Hi,
I am trying create a query that counts all records which have the field Hypotension=1 (which is Yes), but I only want to count the same customer once.
For example:
My table has 3 fields: RecID (primary key), MRN, and Hypotension

RecID Cus# Hypotension
1 6 1
2 6 1
3 7 1

then my query should count the total of 2 for "hypotension =1", not 3 because I only want to count records with the same Cus# only once. I've tried to use "DISTINCT", but it doesn't work b/c I want to count many fields in the same querry (more than 20), not just one field "Hypotension". Below is my Select code, but it counts all records (which is 3). I need to add criteria such as where to eliminate the ones with the same Cus#, however, I have no clue. Can somebody please help?

SELECT Count(IIf([Hypotension]=1,"Yes")) AS Hypotension_Y
FROM tblReview;

What you need to do is create two queries, one that wraps the other. For your first step, create a query call "Hypotension Query Step 1" with the following sql code:

SELECT Cus#, Hypotension FROM tblReview WHERE (Hypotension = 1) GROUP BY Cus#,  Hypotension ;

Name the second query "Hypotension Query Step 2" with the following SQL statement:

SELECT Count(Cus#) FROM [Hypotension Query Step 1];

The step 1 query selects all the records in tlbReview with a Hypotension of 1 and groups them by Cus# so that each customer shows up only once. The step 2 query takes the results from the step 1 query and counts the number of records. Of course, the queries can be named something else; you just need to make sure that the second query references the first query.