Count of Yes with repeating fields

[Chuck W]

Hi,
I am writing a query against a table I did not create and cannot change. It
is poorly designed with fields that repeat. It is driven by an input form
that has AdmitDate and then HAPU1, HAPU2, HAPU3, HAPU4, HAP5 and HAPU6.
These HAPU fields are all Yes or No (or null). I want to write a query that
will sum all of the Yes values into one field so that I have AdmitDate and
HAPUTotal. My data looks like this:

AdmitDate HAPU1 HAPU2 HAPU3 HAPU4 HAPU5 HAPU6
3/1/09 Yes Yes No NO
3/15/09 Yes No
3/20/09 Yes
3/22/09 Yes Yes Yes
3/25/09 Yes No
3/26/09 Yes Yes Yes Yes Yes Yes

I have been trying the Abs function but can't seem to get it to work. Can
someone help? Thanks, Chuck

 

[KARL DEWEY]

Use a union query to put data in relationship rather than spreadsheet.
SELECT AdmitDate, HAPU1 AS DATA
FROM YourTable
WHERE HAPU1 Is Not Null
UNION ALL SELECT AdmitDate, HAPU2 AS DATA
FROM YourTable
WHERE HAPU2 Is Not Null
UNION ALL SELECT AdmitDate, HAPU3 AS DATA
FROM YourTable
WHERE HAPU3 Is Not Null
UNION ALL SELECT AdmitDate, HAPU4 AS DATA
FROM YourTable
WHERE HAPU4 Is Not Null
UNION ALL SELECT AdmitDate, HAPU5 AS DATA
FROM YourTable
WHERE HAPU5 Is Not Null
UNION ALL SELECT AdmitDate, HAPU6 AS DATA
FROM YourTable
WHERE HAPU6 Is Not Null;

Then --
SELECT AdmitDate, Abs(Sum([DATA])) AS HAPUTotal
FROM qryHAPU
GROUP BY AdmitDate;

 

[John Spencer]

Try using an expression like

IIF(Hapu1=True,1,0) + IIF(Hapu2=True,1,0) + IIF(Hapu3=True,1,0) ...

If you are actually storing the strings, Yes or No in the field then

IIF(Hapu1="yes",1,0) + IIF(Hapu2="yes",1,0) + IIF(Hapu3="yes",1,0) ...

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dorian]

You could write a function that receives as parameters all the columns to be
counted and return the total, you can then call this function from your
query. It may not run fast but it will do the job. E.g.
Public Function AddHAPU (H1 As string, H2 as string, H3 as string, H4 as
string, H5 as string, H6 as string) As Integer
Dim Tot as integer
Tot = 0
If H1 = "YES" THEN Tot = Tot + 1
..... etc etc ...
If H6 = "YES" THEN Tot = Tot + 1
AddHAPU = Tot
end function

From your query:
SELECT AddHAPU(HAPU1,HAPU2,HAPU3,HAPU4,HAPU5,HAPU6) FROM ........
-- David
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".