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?

 

[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