Can you conduct the expression express?

[knowshowrosegrows]

OK, I have a query (qryRunningCapacity) that produces the following results:
Program_ID, Capacity, CapStartDate, CapEndDate.

Over time, a Program_ID may have the Capacity change numerous times, e,g.,

Program_ID Capacity CapStartDate CapEndDate
22 100 3/1/2004 6/30/2006
22 200 7/1/2006 8/31/2008
22 250 9/1/2008

So the current capacity is 250 but it was not always so.

I also have a table (tblCensusEvent) that collects a daily census amount.
Program_ID, CensusEvent_ID, CensusDate, Census

I want to trend the utilization of my programs, so I will divide the
tlbCensusEvent.Census by the qryRunningCapacity.Capacity that was in effect
DURING THE APPROPRIATE TIME FRAME in the qryRunningCapacity.

Can someone get me started on a formula?

 

[MGFoster]

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

Possibly:

SELECT Q.Program_ID, C.CensusDate, C.Census/Q.Capacity As Trend
FROM qryRunningCapacity As Q INNER JOIN tblCensusEvent As C
ON Q.Program_ID = C.Program_ID
WHERE C.CensusDate BETWEEN Q.CapStartDate And Q.CapEndDate

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSXDNWYechKqOuFEgEQLiXACggxoc1XE02Ksz8ZkjyCkZvXvuvMEAn1hv
fM9j4P2JCyVsw+fmeH/BQ3a3
=ZMor
-----END PGP SIGNATURE-----

 

[Dale Fye]

Or, if CapEndDate truely is NULL for the last entry, then maybe:

SELECT Q.Program_ID, C.CensusDate, C.Census/Q.Capacity As Trend
FROM qryRunningCapacity As Q INNER JOIN tblCensusEvent As C
ON Q.Program_ID = C.Program_ID
WHERE C.CensusDate BETWEEN Q.CapStartDate
AND NZ(Q.CapEndDate, Date())

Dale