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? -
SELECT Count(IIf([Hypotension]=1,"Yes")) AS Hypotension_Y
-
FROM tblReview;
-
thanks!
7 5442 Megalog 378
Recognized Expert Contributor
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));
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 378
Recognized Expert Contributor
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;
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!
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
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.
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Hari Om |
last post by:
Here are the details of my error log files:
I execute the command and get following message at console:
----------------------------------------------------------------------
../sqlldr...
|
by: Simon Matthews |
last post by:
Hope someone can help an Access beginner!
I've just started keeping my surgical logbook on access and it's a
simple flat-file affair. I have created several queries that will
list cases...
|
by: darrel |
last post by:
I'm trying to whip up a fancy repeater control that will put records into a
two-column table for me.
This is how I envision it working:
itemtemplate
if record count = odd then write out the...
|
by: Peter W Johnson |
last post by:
Hi guys,
I have a problem with a datagrid record count. Here is the code:-
<snip>
Public Class frmMerchantDeposit
Inherits System.Windows.Forms.Form
Dim myconnection As New...
|
by: MP |
last post by:
vb6,ado,mdb,win2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(const) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source="...
|
by: n00m |
last post by:
http://www.spoj.pl/problems/SUMFOUR/
3
0 0 0 0
0 0 0 0
-1 -1 1 1
Answer for this input data is 33.
My solution for the problem is...
|
by: isabelle |
last post by:
hi, every body..
I have two program I couldn’t solve them
So, can any body help me. please!!
1-Write a program that accepts a character and count number of occurrences in a file.
The file...
|
by: jaxkookie |
last post by:
I have been trying to count each answer for each question and roll this up to one line item per question with totals for each answer. what I am getting is staggered answers. example, question 1 has 9...
|
by: kirkgilbert |
last post by:
I am trying to do an onchange event in a form using a text field. The form is tied to a record set that is part of a repeated region. One the first record when I edit the data it works perfectly. ...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |