transform data by summing columns

C

Chuck W

I have a table with 6000 records called tblDevice that has fields such as
DRDate, Division, Device1, Device2, Device3, Device4 and Device5. Device1
through Device5 are formatted as Yes/No. I want to create a query (or
queries) that will sum the total yes answers for Device2 through Device5 with
the name OtherDevices and compare this to Device1 without doing a crosstab.
So my query would have Devision, Device1 and OtherDevices. There are five
divisions. So my results would be like this:

Division Device1 OtherDevices
ICU 18 28
Neuro 10 25
WP 8 2
Rehab 4 0
ED 23 18

Thanks,
 
K

KARL DEWEY

Try this --
SELECT Division, Abs(Sum([Device1])) AS Device1_, Abs(Sum([Device2] +
[Device3] + [Device4] + [Device5])) AS OtherDevices
FROM tblDevice
GROUP BY Division;
 
G

Graham Mandeno

Hi Chuck

I guess you don't want to be told that your table structure is badly
designed and that it should be two related tables, so I'll refrain from that
particular lecture this morning <smile>.

Try this:

Select Division,
Abs(Sum(YourTable.Device1)) as Device1,
Abs(Sum(Device2+Device3+Device4+Device5)) as OtherDevices
from YourTable
group by Division;

This works because Yes is stored as True (-1) and No as False (0). So
adding up all the field values will give you a negative number which, when
made positive by the Abs() function, is a count of the Yeses.
 
Top