Please help with a query

[Al]

I need to count the number of patients that have a
StartDate <1/1/02 and StopDate >= 1/1/02. At this point
this is easy. The difficult part is I need to do the same
for every month till 12/31/04, i.e. Total no. of patients
where StartDate <2/1/02 and StopDate >= 2/1/02, . Total
no. of patients where StartDate <3/1/02 and StopDate >=
3/1/02, etc. I need to include all patients that have
StopDate = null, as well, in this count. Is this possible
to do it in one qry. I need this qry to plot a graph that
will show the total number of patients per/month from
start to end. Can someone help?
Thanks
Al

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You could use the IIf() function & the Count() function like this:

SELECT
COUNT(IIf(StartDate<#1/1/02# And StopDate>=#1/1/02#,1) As Jan02Count,
COUNT(IIf(StartDate<#2/1/02# And StopDate>=#2/1/02#,1) As Feb02Count,
COUNT(IIf(StartDate<#3/1/02# And StopDate>=#3/1/02#,1) As Mar02Count,
.... etc. ... ,
COUNT(IIf(StopDate Is Null, 1)) As NoStopDateCount
FROM ... etc.

If a NULL StopDate means the patient is still "Active," then you might
wish to change the IIf() function to something like this:

COUNT(IIf(StartDate<#1/1/02# And (StopDate>=#1/1/02# OR StopDate Is
Null), 1) As ... etc.

and ignore the NoStopDateCount expression.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQJFtwoechKqOuFEgEQLu3ACgwQ4UCAh5A9G562BAUURy1YVf89QAoI39
MqDNWrdRye9o9bVWhbkzcX6C
=E8Oj
-----END PGP SIGNATURE-----

 

[Al]

Thank you for your help. I used your second exp. however I
am getting an error message "Missing operator in exp.....).
here is how I used it.
*****************************************
SELECT COUNT(IIf([ProtocolStartDate]<#1/1/02# And
([ProtocolWithdrawlDate]>=#1/1/02# OR
[ProtocolWithdrawlDate] IsNull), 1)) As Jan02Count,

COUNT(IIf(ProtocolStartDate<#2/1/02# And
(ProtocolWithdrawlDate>=#2/1/02# OR ProtocolWithdrawlDate
IsNull), 1)) As Feb02Count

FROM tblPatientTreatment;

I cannot see what operator is missing here. could you help?
Al