How to not count repeated record with the same ID

[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 222 1
2 222 1
3 111 1

then my query should count the total of 2 for "hypotensio n =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 "Hypotensio n". 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;

thanks!

[Megalog]

A mixture of Groups & Counts will solve this. Basically you need to group your MRN #, Group by each criteria, and then Count those criteria.

Paste this into a query and see how it's set up, you should be able to add in all the other fields you require in the same manner.

SELECT tblReview.MRN, Count(tblReview.Hypotension) AS Hypotension_Y
FROM tblReview
GROUP BY tblReview.MRN, tblReview.Hypotension
HAVING (((tblReview.Hypotension)=1));

[elainenguyen]

thanks for you help, I've tried the code with about 2 fields and it doesn't work. The counting is not correct.

thanks!

[Megalog]

After re-reading your first post, maybe this is the result you're truly after, using Sums instead of counts. Replace the 'SecondCriteria ' with a valid field name, and then test it out.

SELECT tblReview.MRN, Sum(IIf([Hypotension]=1,1,0)) AS Hypotension_Y, Sum(IIf([SecondCriteria]=1,1,0)) AS SecondCriteria_Y
FROM tblReview
GROUP BY tblReview.MRN;

or even simpler:

SELECT tblReview.MRN, Sum(tblReview.Hypotension) AS Hypotension_Y, Sum(tblReview.SecondCriteria) AS SecondCriteria_Y
FROM tblReview
GROUP BY tblReview.MRN;

[elainenguyen]

Hi,
what I really want to do is actually "count" not "sum".
For example, in my table, there are 3 fields, field 1 is RecID, field 2 is cust#, field 3 is "hypotensio n with either 1=Y or 2=No.

RecID 1 (auto num), cust#5, hypotension =1
RecID 2 (auto num), cust#5, hypotension =1
RecID 3 (auto num), cust#6, hypotension =1

instead of counting the total number of Y for hypotension which is 3, I only want to count the records with hypotension=1 which has the same cust# only once; therefore, the actual count that I want is 2, not 3. Which mean I only want to count "the total # of customer with hypotention=1, not the number with hypotension=1. I've tried to use DISTINCT but as I have mentioned in the previous message, there are many fields in my query, not just hypotension, so DISTINCT doesn't work in this case.

thanks for your help. I am still working on finding the right code. Any help will be appreciated.

thanks!

[Stewart Ross]

Hi. The following subquery-based count will provide you with an overall count of the number of records where field Hypotension = 1 without taking into account the IDs. It uses a Group By subquery to group the records by customer and hypotension.

SELECT Count(*) AS [Overall Hypotension Count]
FROM 
     (SELECT    tblReview.[Cus#], tblReview.Hypotension 
      FROM      tblReview
      GROUP BY  tblReview.[Cus#], tblReview.Hypotension
      HAVING (((tblReview.Hypotension)=1))) 
AS A;

Test data:

RecID	Cus#	Hypotension
9       111     0
3       111     1
8       222     0
7       222     0
2       222     1
1       222     1
10      333     1
4       333     1
6       444     1
5       444     1

Result:

Overall Hypotension Count
4

By the way, I find it much easier to visualise potential solutions if test data closer to real data is provided, not dummy values such as 222, 333, 111. More sample rows would also help in testing that the solution is correct.

-Stewart

[elainenguyen]

Hi,
thanks for you help. The count is correct this time.
How do I add an additional count into the code for field Ulcer=1 into the same query? For example:

Test Data:

RecID   Cus#   Hypotension   Ulcer
9       111               0              0
3       111               1              1   
8       222               0              0
7       222               0              1
2       222               1              1
1       222               1              1
10     333               1              1
4       333               1              1
6       444               1              0
5       444               1              0

Result should show:
Hypotension   Ulcer
4                      3

thanks alot! Really appreciate your help.

[Stewart Ross]

Hi. The additional requirement really changes the query - it would have been better to let us know of it in post 1!

The grouping has to change to accommodate more than one count-field, and I have used the Max function instead of simple Group By in the subquery, then Sum instead of Count to obtain the result you need.

SELECT SUM(H) AS [Count Hypotension], SUM(U) as [Count Ulcer] FROM 
      (SELECT     tblReview.[Cus#],   
                  Max(tblReview.Hypotension) AS H, 
                  Max(tblReview.Ulcer) AS U
       FROM       tblReview
       GROUP BY   tblReview.[Cus#]
       HAVING  (((Max(tblReview.Hypotension))=1)) OR 
               (((Max(tblReview.Ulcer))      =1))) 
AS A;

Test Data

RecID Cus# Hypotension Ulcer
1     222     1          0
2     222     1          0
3     111     1          1
4     333     1          1
5     444     1          0
6     444     1          0
7     222     0          0
8     222     0          0
9     111     0          1
10    333     1          1

Output

Count Hypotension  Count Ulcer
4                  2

-Stewart