No. The crosstab just provides the count of the dates in the MemberDate
field, using the month of that field. It does not correspond to a period of
membership.
If you want the count of current members as of the first of every month, the
months need to come from somewhere, so you will need to create a table that
has a record for the first of every month. Just one field named (say)
TheDate, Date/Time type, primary key, and save the table as tblDate. Enter a
record for the first of each month, or use the code below to populate all
the dates from 2000 through 2019.
You can then create a query that contains this table along with your
original, without any join between the 2 tables. Drag tblDate.TheDate into
the grid. In the criteria below this field, enter the range of dates you
want to query, e.g.:
Between #1/1/2005# And #12/31/2005#
Then drag your membership start date into the grid. Criteria:
<= [tblDate].[TheDate]
Then drag your membership end date into the grid. Criteria:
This query will show the first of each month, and a row for every person who
has a membership in that month.
You can then turn it into a Totals query or Crosstab to aggregate the data.
To populate the date table programmatically:
Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset
Dim i as Inteber
Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For i = 0 to 119
.AddNew
!TheDate = DateAdd("m", i, #1/1/2000#)
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
argear said:
I tried this and it worked, but I have one further question. I'm not sure
I'm getting the correct member count. The table I use has start and end
dates for membership. If a member has a start date of 4/1/05 and and end
date of 6/30/05, the member should be counted for April, May and June.
Does
this query do that, or do I need to add something else?
Thanks!!!
Allen Browne said:
A crosstab query will give this to you.
1. Create a query that uses your table.
2. Change it to a Crosstab query (Crosstab on Query menu.)
Access adds some new rows to the grid.
3. Drag the [Group] field into the grid.
Accept Group By in the Total row under this field.
Chooose Row Heading in the Crosstab row.
4. In the next column, type this into the Field row:
TheMonth: Format([MemberDate], "yyyy\-mm")
Replace "MemberDate" with the name of your date field.
Accept Group By in the Total row.
Choose Column Heading in the Crosstab row.
5. Drag the primary key field into the grid.
Choose Count in the Total row.
Choose Value in the Crosstab row.
The query will generate a row for each group (step 3).
It will show a column of each month (step 4).
It will count the number of entries in the table that match the group and
the month, and show the count at the intersection point (step 5.)
Note that the column heading shows differently than you asked for, but at
least the columns display in a meaningful way.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
I need to produce a report that shows a count by month by group for a
selected date range.
Example:
Jan 05 Feb 05 Mar 05
Group 1 2000 2463 1980
Group 2 1432 1465 1503
The members aren't stored by month, there's just a start date and end
date
for membership, and it's not the same set of months every time. How
can I
get that count to work? I don't know a lot about modules and macros,
but
I'm
willing to try it to get this to work!
