Crosstab query or similar?

[usr33t]

Hi,

I am trying to query on a table, predominantly made up of check boxes, so that I can get a summary which essentially shows the number of 'checks' in each field within a given date range. I have attached a Word file, which should explain exactly what I would like to achieve.

If anyone can help me I would be extremely grateful.

Many thanks and best wishes
Russell

 

[macca the hacke]

Write a query along the lines of :

SELECT Sum(IIf([check1]=True,1,0)) AS Expr1, Sum(IIf([check2]=True,1,0)) AS Expr2, Sum(IIf([check3]=True,1,0)) AS Expr3, Sum(IIf([check4]=True,1,0)) AS Expr4
FROM Table1
WHERE (((Table1.date)=[enter date]));

obviously substitute field names check1, check2, check3, check4 and date for your field names. Substitute expr1-4 with names you want to appear on query

 

[usr33t]

Thanks very much for that,

That side of things works just fine now. What I would like to achieve next is explained in the attached file.

If you could help me please, that would be great.

Many thanks
Russell

 

[macca the hacke]

This is the same as before is it not?

 

[usr33t]

Hi,

Similar, but not the same. The first query you helped me with counted all the instances of "Yes" in a date range for each type of treatment.

Patients have an entry in this table for each day they spend on our intensive care unit. Each month we are required to report on the number of days in a month that a particular treatment was administered (which is what the first query pulls out).

i.e.
ID Treatdate Ventilated
001 01/11/05 Yes
001 02/11/05 Yes
001 03/11/05 Yes
002 01/11/05 Yes
002 02/11/05 Yes
003 02/11/05 Yes
003 03/11/05 No

Using the query you helped me with, the table shows that between 01/11/05 and 03/11/05 there were 6 instances of Ventilation.

What I now need to do is show how many individual patients in the same date range were Ventilated. Based on the table above, the query would have to return a value of 3 (i.e. three different ID numbers are associated with a "Yes" in the Ventilated field. I can do this using the 'regular' Design View mode in Access, however, only for one treatment at a time. What I would really like is a summary table, similar to the one you helped me produce in my previous question.

I hope this is a slightly more lucid explanation, if rather wordy.

Thanks for your assistance so far. Very much appreciated.
Russell


We also need to know how many patients

 

[neileg]

SELECT Sum(IIf([check1]=True,1,0)) AS Expr1, Sum(IIf([check2]=True,1,0)) AS Expr2, Sum(IIf([check3]=True,1,0)) AS Expr3, Sum(IIf([check4]=True,1,0)) AS Expr4
FROM Table1
WHERE (((Table1.date)=[enter date]));

Actually, True is stored in Access as -1 and False is zero, so you don't need the iif statements, just a minus to change the sign of the sum.