report based on query

[hiya5150]

i have the following query which gives me the total donations for all clients for a given time period, say, a year. (not for profit charitable organization).

SELECT PaymentTable.date, DonorTable.donor_title, DonorTable.donor_f_name, DonorTable.donor_l_name, Donations.donation_type, Donations.receipt_amount, PaymentTable.amt_paid, IIf([donations]![receipt_amount]>=[paymenttable]![amt_paid] And [paymenttable]![amt_paid]>0,[paymenttable]![amt_paid],[donations]![receipt_amount]) AS Expr1, PaymentTable.payment_type
FROM (Donations INNER JOIN PaymentTable ON Donations.donation_id = PaymentTable.donation_id) INNER JOIN DonorTable ON Donations.donor_id = DonorTable.donor_id
WHERE (((PaymentTable.date) Between [beginning date] And [ending date]) AND ((PaymentTable.payment_type)<>"non-cash") AND ((Donations.currency)="canadian"));

i would like to design a report that reflects the total NOT ONLY for the entire year but for each month seperately. For example:

january $15,000
february 40,000

and then one total for the whole year.

i'm not a very polished access user and this seems a bit beyond my grasp. any help would be appreciated. thanks.

 

[hneal98]

Create a crosstab query for the months, then in your report add them up for the year.

 

[PHV]

Sort the report by date and create a month group level.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[hiya5150]

i tried to create a month group level, but the total amount i get, per month, is not correct. instead of getting the total for the month, i get the total for the day (in the query, i used the aggregate function SUM). this is what i get:

january - 1500 (total for jan 5)

february - 1700 (total for feb 2)

etc.


i cannot figure out how to total the amount by month. if i try a crosstab query, it will not let me input any criteria like between jan and dec. i know i'm in a bit over my head here, but perhaps there's a simple solution?

 

[hneal98]

When you create your group, are you only grouping on the date?