To achieve a count for each month, you would need a table of dates,
containing just the first of each month. Name the table (say)
tblDate, with a date/time field named TheDate, marked as primary key.
Then enter records: 1/1/2005
1/2/2005
1/3/...
This guarantees you a result for every month, even if there were no
transactions in that month.
Then use this table as the source for a query, and use a series of
subqueries to get the desired count from your Leases table:
SELECT tblDate.TheDate,
(SELECT Count("*") FROM Leases
WHERE tblDate.TheDate Between Leases.StartDate And
Nz(Leases.EndDate, #1/1/2299#)) AS StartOfMonthCount,
(SELECT Count("*") FROM Leases
WHERE Leases.StartDate Between tblDate.TheDate And
DateAdd("m",1,[tblDate].[TheDate])-1) AS MovedIn,
(SELECT Count("*") FROM Leases
WHERE Leases.EndDate Between tblDate.TheDate And
DateAdd("m",1,[tblDate].[TheDate])-1) AS MovedOut
FROM tblDate
ORDER BY tblDate.TheDate;
If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
Notes:
a) Results will be read-only when you use a subqeury in the SELECT
clause. b) Reports can barf with a "multi-level group by error" if
you try to group or sort by the results of the subqueries.
Joe Cilinceon said:
fredg said:
On Sun, 6 Nov 2005 19:53:24 -0500, Joe Cilinceon wrote:
I can't seem to grasp what I need to do with this.
I have a Table (LEASES) that has a UnitNo (storage unit), StartDate
and EndDate. Now the EndDate will be Null if the unit is rented,
StartDate is when it was rented. Now I want to query the LEASE
table to get a count of units rented on the last day of each
month. What I'm looking for is the occupancy at the End of the
Month only.
If all you need are the Null EndDates for the current month, run the
query at the end of the current month.
SELECT Sum(IIf(IsNull([EndDate]),1,0)) AS Rented
FROM Leases;
Yes that I had figured out. Actually I want to be able to query and
get the # of units rented for last day of any given month. I could
also use a movin / moveout count over a period. We often will rent a
unit in the beginning of a month and re rent it before the end of
the same month. Right now the max I could go back is 1/1/05 as this
app didn't existance prior to that. This will eventually become
part of an End of Year report.