Group By Month and Year

  • Thread starter Thread starter C
  • Start date Start date
C

C

I Have a church database which has all the donations given to the church over
a period of 3 years. I have created a query that allows me to select a year
and a member by member id.

What I want to do is group together all the days that a donation has been
given over the period of a month into one row. Do for example:

ID Date Amount
1 Feb 2006 2.00

How would I go about doing this please? I have tried working with the
sorting and grouping button, but to no avail. Thanks for your help!
 
This is how to get a result using the sorting and grouping box in your
report.

You probably have 2 tables something like this:
a) Donor table: one record for each donor, with fields:
- DonorID AutoNumber primary key
- Surname Text
- FirstName Text
b) Donation table: one record for each donation with a fields:
- DonationID AutoNumber primary key
- DonorID Number relates to Donor.DonorID
- DonationDate Date/Time
- Amount Currency

1. Create query using both tables, outputing all the fields you want in your
report.

2. Create a report using this query.

3. In report design view, open the Sorting And Grouping Box.
Set up the rows in the box like this:
a) DonorID, with properties:
group header: yes
group footer: yes
b) DonationDate, with properties:
group footer: yes
Group interval: month

4. In the DonorID group header, place the fields for the person's name. For
example you might set the Control Source of a text box to:
=[Surname] & ", " + [FirstName]

5. In the DonationDate group footer, place a text box with properties:
Control Source: =Sum([Amount])
Format: Currency

6. Add another text box to the group footer, with properties:
Control Source: DonationDate
Format: mmmm yyyy
This shows what month that total was for.

7. Put nothing in the Detail section, and drag it up to zero height. (This
suppresses the display of each donation, since you only want the monthly
total.

8. (Optional) If you want a person's total, place a text box in the DonorID
group footer, with the same properties as the one in step 5 above.

There are other ways to approach this question, but this way is really
flexible when it comes to filtering (to show only one donor, and/or only a
specific date period.)
 
Back
Top