There's a trick to this and it's
Create a table called occupancyMonths
say
Year,Month,StartOfMonth,EndOfMonth and populate it with the ranges you are interested in reporting on.
The if you have an Occupancy table like
OccupancyID, ResidentID, DateEntered, Dateleft
Then given you want to include those who were there for part of the month, that you have some who were there for more than one month and coping with those who had a period of occupancy that spanned a year Dec 2010 to Jan 2010 say...
Select DistinctRow PersonID,[Year],[Month] From OccupancyMonths m
join Occupancy o on
(m.Year = DatePart(year,o.DateEntered
and
m.Month = DatePart(month,o.DateEntered)
or
(
(m.Year = DatePart(year,o.DateLeft
and
m.Month = DatePart(month,o.DateLeft)
)
Where m.year = 2010
will give you the people who were there in any part of the month for that year
In access you'd make this one query Q_PeopleInMonth, in a proper sql compliant DBMS it would be an inner query
either way
Select [Month],Count(*) From Q_PeopleInmonth
should do the job
Haven't tested the syntax, bit the method is sound, I've used something like it several times.
In this case you could do away with OccupancyMonths but the sql would be horrendous and I fail to see the point.
If you can't figure out how to get this in the graphical query builder in access, switch to sql view, type in the sql, prove it and then flip back, I couldn't tell you anyway, never use them, come to think of it I don't use access except at gun point.
Donlt worry about not seeing this trick, took me well over a day to come up with it and I've been doing this 92.
It's one of those obvious after you've been told maneouvres.
Comes in handy for all sorts of things like seeing how many days each employee was working, or what rooms you have free in a hotel and such.
