Monthly activity report

  • Thread starter Thread starter JLF
  • Start date Start date
J

JLF

I've been scouring these ng's for days trying to find something that would
help me with this.

I have a database which tracks peoples' membership for a non-profit
organization.
I have fields for Date Joined, Date Renewed, Date of Donation (if the
member has made any charitable donations to the organization.)

I;m trying to create a report which shows the the totals by month of
# new members (ie date joined is within the month)
# renewals (date renewed is within the month)
# donations (donation Date is within the month)

I was able to create 3 separate queries, which sortof get the data, but they
only work if the date exists. (for instance, if there was no renewal in
October 03, the report/query would just skip October 03, rather than show a
zero value.

Any thoughts on how to do this?

Thanks
 
Queries can't retrieve data that doesn't exist, unfortunately.

The easiest (and possibly only) way is to create a table that has one row
for each month of interest, and join that table to your other queries. Use a
Left Join to select each row in the month table and all matching rows in the
other tables.
 
Thanks Doug,
But what i'm still puzzled about is how to create the report that will count
the right number of transactions.

For instance, even with the "month" table,
each member record has 3 dates. A member could have joined in 1/03, made a
donation in 5/03, renewed in 12/03.
So if i run a report to group by month, and show count of matching record,
I can only link 1 date field. The others get counted whether or not they
match the Month.
(eg. for instance I just mentioned, my report shows JAN-03 New members 1,
donations 1, renewal 1. Even though the donation and renewal are in may and
dec, they get counted in Jan along with the new members (date joined
field). )

Is what I'm trying to do feasible within normal queries/reports? or am I
getting into writing VBA code here?

If you or anyone else here has further suggestions, I'd really appreciate
it.
 
Pardon me.

Your problem seems to be that you have the actions in separate fields in the
same table as your members. If at all possible you need to redesign your table
structure so that you have (at a minimum) a members table and an actions table.
Also, what happens if a member makes more than one donation in a year. Or maybe
I don't understand your structure.

If you are stuck with the structure there are some solutions, but as your data
seems to be currently structured you will always have problems.

Try making a normalizing UNION query to get all the data in another format and
then use that as the basis of your count query. You can't build the query in
the grid view, but will have to use the SQL window to do this. Substitute your
field and tablenames.

SELECT "DateJoined" as DateType, [Date Joined]
FROM YourTable
WHERE [Date Joined] is Not Null
UNION ALL
SELECT "DateRenewed", [Date Renewed]
FROM YourTable
WHERE [Date Renewed] is Not Null
UNION ALL
SELECT "DateOfDonation", [Date of Donation]
FROM YourTable
WHERE [Date of Donation] is Not Null

That will give you one record for each existing date. Now using that SAVED
query as the source, you can do a Totals query that looks like:

SELECT DateType, Count([Date Joined]) as CountEm
FROM YourUnionQuery
Where [Date Joined] Between #1/1/04# and #1/31/04#
GROUP BY DateType

You could make this process more efficient by putting the date range in the
UNIONed queries.
 
Thank You, John.

I had never used Union query before (or written queries in SQL for that
matter!)
But that was just the trick to get to the query & report that I needed.
Much Appreciated,
-Jonathan
John Spencer (MVP) said:
Pardon me.

Your problem seems to be that you have the actions in separate fields in the
same table as your members. If at all possible you need to redesign your table
structure so that you have (at a minimum) a members table and an actions table.
Also, what happens if a member makes more than one donation in a year. Or maybe
I don't understand your structure.

If you are stuck with the structure there are some solutions, but as your data
seems to be currently structured you will always have problems.

Try making a normalizing UNION query to get all the data in another format and
then use that as the basis of your count query. You can't build the query in
the grid view, but will have to use the SQL window to do this. Substitute your
field and tablenames.

SELECT "DateJoined" as DateType, [Date Joined]
FROM YourTable
WHERE [Date Joined] is Not Null
UNION ALL
SELECT "DateRenewed", [Date Renewed]
FROM YourTable
WHERE [Date Renewed] is Not Null
UNION ALL
SELECT "DateOfDonation", [Date of Donation]
FROM YourTable
WHERE [Date of Donation] is Not Null

That will give you one record for each existing date. Now using that SAVED
query as the source, you can do a Totals query that looks like:

SELECT DateType, Count([Date Joined]) as CountEm
FROM YourUnionQuery
Where [Date Joined] Between #1/1/04# and #1/31/04#
GROUP BY DateType

You could make this process more efficient by putting the date range in the
UNIONed queries.
I've been scouring these ng's for days trying to find something that would
help me with this.

I have a database which tracks peoples' membership for a non-profit
organization.
I have fields for Date Joined, Date Renewed, Date of Donation (if the
member has made any charitable donations to the organization.)

I;m trying to create a report which shows the the totals by month of
# new members (ie date joined is within the month)
# renewals (date renewed is within the month)
# donations (donation Date is within the month)

I was able to create 3 separate queries, which sortof get the data, but they
only work if the date exists. (for instance, if there was no renewal in
October 03, the report/query would just skip October 03, rather than show a
zero value.

Any thoughts on how to do this?

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