counting number of occupants over time

  • Thread starter Thread starter Judy
  • Start date Start date
J

Judy

Okay I have a query that can count the number of occupants
as any one given time:

SELECT Count(TblClientInfo.dtmLeaseStart) AS
CountofLeaseStart
FROM tblClientInfo
WHERE ((([Format([dtmLeaseStart], "mmyyyy")) <="052002")
AND ((Format([dtmLeaseEnd], "mmyyyy")) > "052002")) OR
(((Format([dtmLeaseStart], "mmyyyy"))<= "052002") AND
((Format([dtmLeaseEnd], "mmyyyy"))=""));

where of course the "052002" can come from a form or
parameter query if needed.

BUT--how can I allow for a range of months, calculating
the total for *each* month, based on the criteria above
where they're on or past lease start date but not yet past
lease end date?

thanks,

Judy
 
Define a second table called tblLeaseDates with one text field dtmLease.
Enter as many dates as needed in the second table with the format of yyyymm.
Then use the following:

SELECT Count(TblClientInfo.dtmLeaseStart) AS CountOfdtmLeaseStart,
tblLeaseDates.dtmLease
FROM TblClientInfo, tblLeaseDates
WHERE (((Format([dtmLeaseStart],"yyyymm"))<=[dtmLease]) AND
((Format([dtmLeaseEnd],"yyyymm"))>[dtmLease])) OR
(((Format([dtmLeaseStart],"yyyymm"))<=[dtmLease]) AND
((Format([dtmLeaseEnd],"yyyymm"))=""))
GROUP BY tblLeaseDates.dtmLease;

Note that you should use yyyymm instead of mmyyyy in the format statement.

If you just want to enter a beginning and end date, that would get a bit
trickier.
 
Hmm, something not working with this query--it doesn't
find any matches.

I tried putting in a date of 6/1/2003 (date the database
began), to today's date, with some records having lease
start dates between, but all fields came back zero.

I'm looking at the logic, which looks fine--would it care
if the Iota field had 1 instead of 01? (which is how it
gets it from the Iota table as an integer)

-----Original Message-----
To enter a range of dates, first create a table called Iota with one long
integer field called Iota. Then populate this table with, for example, the
numbers from 1 to 100. This would allow you to have the query below
calculate to a maximum of 100 months in the date range:

SELECT Count(TblClientInfo.dtmLeaseStart) AS CountOfdtmLeaseStart,
Format(DateAdd("m",[Iota]-1,#5/1/2002#),"yyyymm") AS dtmLease
FROM TblClientInfo, Iota
WHERE
(((Format([dtmLeaseStart],"yyyymm"))<=Format(DateAdd("m", [Iota]-1,#5/1/2002#
),"yyyymm")) AND
((Format([dtmLeaseEnd],"yyyymm"))>Format(DateAdd("m", [Iota],#5/1/2002#),"yyy
ymm"))) OR
(((Format([dtmLeaseStart],"yyyymm"))<=Format(DateAdd("m", [Iota]-1,#5/1/2002#
),"yyyymm")) AND ((Format([dtmLeaseEnd],"yyyymm"))=""))
GROUP BY Format(DateAdd("m",[Iota]-1,#5/1/2002#),"yyyymm")
HAVING
(((Format(DateAdd("m",[Iota]-1,#5/1/2002#),"yyyymm")) <=Format(#7/1/2004#,"yy
yymm"))) OR
(((Format(DateAdd("m",[Iota]-1,#5/1/2002#),"yyyymm")) <=Format(#7/1/2004#,"yy
yymm")));

Here, the beginning date is 5/1/2002, and the ending date is 7/1/2004.

Judy said:
Okay I have a query that can count the number of occupants
as any one given time:

SELECT Count(TblClientInfo.dtmLeaseStart) AS
CountofLeaseStart
FROM tblClientInfo
WHERE ((([Format([dtmLeaseStart], "mmyyyy")) <="052002")
AND ((Format([dtmLeaseEnd], "mmyyyy")) > "052002")) OR
(((Format([dtmLeaseStart], "mmyyyy"))<= "052002") AND
((Format([dtmLeaseEnd], "mmyyyy"))=""));

where of course the "052002" can come from a form or
parameter query if needed.

BUT--how can I allow for a range of months, calculating
the total for *each* month, based on the criteria above
where they're on or past lease start date but not yet past
lease end date?

thanks,

Judy
Click to expand...


.
Click to expand...
 
Could you copy and paste your query? Also, do you have 100 fields in the
Iota table, the first record being 1, the second 2, etc, all way to record
100 being 100?
Judy said:
Hmm, something not working with this query--it doesn't
find any matches.

I tried putting in a date of 6/1/2003 (date the database
began), to today's date, with some records having lease
start dates between, but all fields came back zero.

I'm looking at the logic, which looks fine--would it care
if the Iota field had 1 instead of 01? (which is how it
gets it from the Iota table as an integer)

-----Original Message-----
To enter a range of dates, first create a table called Iota with one long
integer field called Iota. Then populate this table with, for example, the
numbers from 1 to 100. This would allow you to have the query below
calculate to a maximum of 100 months in the date range:

SELECT Count(TblClientInfo.dtmLeaseStart) AS CountOfdtmLeaseStart,
Format(DateAdd("m",[Iota]-1,#5/1/2002#),"yyyymm") AS dtmLease
FROM TblClientInfo, Iota
WHERE
(((Format([dtmLeaseStart],"yyyymm"))<=Format(DateAdd("m", [Iota]-1,#5/1/2002#
),"yyyymm")) AND
((Format([dtmLeaseEnd],"yyyymm"))>Format(DateAdd("m", [Iota],#5/1/2002#),"yyy
ymm"))) OR
(((Format([dtmLeaseStart],"yyyymm"))<=Format(DateAdd("m", [Iota]-1,#5/1/2002#
),"yyyymm")) AND ((Format([dtmLeaseEnd],"yyyymm"))=""))
GROUP BY Format(DateAdd("m",[Iota]-1,#5/1/2002#),"yyyymm")
HAVING
(((Format(DateAdd("m",[Iota]-1,#5/1/2002#),"yyyymm")) <=Format(#7/1/2004#,"yy
yymm"))) OR
(((Format(DateAdd("m",[Iota]-1,#5/1/2002#),"yyyymm")) <=Format(#7/1/2004#,"yy
yymm")));

Here, the beginning date is 5/1/2002, and the ending date is 7/1/2004.

Judy said:
Okay I have a query that can count the number of occupants
as any one given time:

SELECT Count(TblClientInfo.dtmLeaseStart) AS
CountofLeaseStart
FROM tblClientInfo
WHERE ((([Format([dtmLeaseStart], "mmyyyy")) <="052002")
AND ((Format([dtmLeaseEnd], "mmyyyy")) > "052002")) OR
(((Format([dtmLeaseStart], "mmyyyy"))<= "052002") AND
((Format([dtmLeaseEnd], "mmyyyy"))=""));

where of course the "052002" can come from a form or
parameter query if needed.

BUT--how can I allow for a range of months, calculating
the total for *each* month, based on the criteria above
where they're on or past lease start date but not yet past
lease end date?

thanks,

Judy
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top