Re: Please help with a query

From: MGFoster (me_at_privacy.com)
Date: 04/29/04 

Date: Thu, 29 Apr 2004 21:04:00 GMT

-----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 wrote:
> 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
>