By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,852 Members | 2,116 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,852 IT Pros & Developers. It's quick & easy.

How to not count repeated record with the same ID

P: 51
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 "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?

Expand|Select|Wrap|Line Numbers
  1. SELECT Count(IIf([Hypotension]=1,"Yes")) AS Hypotension_Y
  2. FROM tblReview;
  3.  
thanks!
Sep 10 '08 #1
Share this Question
Share on Google+
7 Replies


Megalog
Expert 100+
P: 378
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT tblReview.MRN, Count(tblReview.Hypotension) AS Hypotension_Y
  2. FROM tblReview
  3. GROUP BY tblReview.MRN, tblReview.Hypotension
  4. HAVING (((tblReview.Hypotension)=1));
Sep 10 '08 #2

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

thanks!
Sep 10 '08 #3

Megalog
Expert 100+
P: 378
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT tblReview.MRN, Sum(IIf([Hypotension]=1,1,0)) AS Hypotension_Y, Sum(IIf([SecondCriteria]=1,1,0)) AS SecondCriteria_Y
  2. FROM tblReview
  3. GROUP BY tblReview.MRN;
or even simpler:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblReview.MRN, Sum(tblReview.Hypotension) AS Hypotension_Y, Sum(tblReview.SecondCriteria) AS SecondCriteria_Y
  2. FROM tblReview
  3. GROUP BY tblReview.MRN;
Sep 10 '08 #4

P: 51
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 "hypotension 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!
Sep 11 '08 #5

Expert Mod 2.5K+
P: 2,545
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*) AS [Overall Hypotension Count]
  2. FROM 
  3.      (SELECT    tblReview.[Cus#], tblReview.Hypotension 
  4.       FROM      tblReview
  5.       GROUP BY  tblReview.[Cus#], tblReview.Hypotension
  6.       HAVING (((tblReview.Hypotension)=1))) 
  7. AS A;
  8.  
Test data:
Expand|Select|Wrap|Line Numbers
  1. RecID    Cus#    Hypotension
  2. 9       111     0
  3. 3       111     1
  4. 8       222     0
  5. 7       222     0
  6. 2       222     1
  7. 1       222     1
  8. 10      333     1
  9. 4       333     1
  10. 6       444     1
  11. 5       444     1
  12.  
Result:
Expand|Select|Wrap|Line Numbers
  1. Overall Hypotension Count
  2. 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
Sep 11 '08 #6

P: 51
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:
Expand|Select|Wrap|Line Numbers
  1. RecID   Cus#   Hypotension   Ulcer
  2. 9       111               0              0
  3. 3       111               1              1   
  4. 8       222               0              0
  5. 7       222               0              1
  6. 2       222               1              1
  7. 1       222               1              1
  8. 10     333               1              1
  9. 4       333               1              1
  10. 6       444               1              0
  11. 5       444               1              0
  12.  
  13. Result should show:
  14. Hypotension   Ulcer
  15. 4                      3
  16.  
thanks alot! Really appreciate your help.
Sep 11 '08 #7

Expert Mod 2.5K+
P: 2,545
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT SUM(H) AS [Count Hypotension], SUM(U) as [Count Ulcer] FROM 
  2.       (SELECT     tblReview.[Cus#],   
  3.                   Max(tblReview.Hypotension) AS H, 
  4.                   Max(tblReview.Ulcer) AS U
  5.        FROM       tblReview
  6.        GROUP BY   tblReview.[Cus#]
  7.        HAVING  (((Max(tblReview.Hypotension))=1)) OR 
  8.                (((Max(tblReview.Ulcer))      =1))) 
  9. AS A;
Test Data
Expand|Select|Wrap|Line Numbers
  1. RecID Cus# Hypotension Ulcer
  2. 1     222     1          0
  3. 2     222     1          0
  4. 3     111     1          1
  5. 4     333     1          1
  6. 5     444     1          0
  7. 6     444     1          0
  8. 7     222     0          0
  9. 8     222     0          0
  10. 9     111     0          1
  11. 10    333     1          1
Output
Expand|Select|Wrap|Line Numbers
  1. Count Hypotension  Count Ulcer
  2. 4                  2
-Stewart
Sep 12 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.