Print Report with Records with a Sum of $5000 or more

[sandingdude]

I have a report that prints all the donors for given period. This report then sums up all the donations for that given year. What is the easiest way to print another report that only prints out the donors who donations totalled more than $5000.

Thank you in advance.

Dave

 

[GingerR]

Write a totals query that sums up the donations, and put in a criteria of

>5000

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[sandingdude]

How can I write the query which takes the sum of all the donations? I have that in a report which sorts and groups, but how do I do that in a query?

The tables I have just list the donor, the amount, and the date. I did the sum on the donations be specific to the year and the record.

Thanks,

Dave

 

[GingerR]

Make a new query
Add your table.
Bring down the Donor and Amount fields into the grid.
from the menu, pick VIEW + TOTALS
In the AMOUNT column, change GROUP BY to SUM.
in the Criteria row of the AMOUNT column, put

>5000

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[sandingdude]

Ginger

Thanks for the reponse. This helped, but its only taking the sum for that single record? Do if donor1 gave $20 the sumofamount is $20. My quesry lists all the donations a donor has given, for example donor1 has made 10 - $20 donations. I want th sumofamount to equal $200.

Any ideas?

 

[KMKelly]

I know there is probably a more elegant way to do this with subqueries etc, but I would take the table listing the donors and their amounts and create a query that Group By Donor ID (assuming there is a record that is a unique id) and sum(donation amts) for the given year. You can then filter that list by >5000 for the sum(donation amt). Then you use that query to filter the original table. You create a new query with the original table and join the query you just created (one to one). It will limit the records in the original table to just those donors with over 5000 for the year.

Kris

 

[Lilliabeth]

sandingdude,
Since Ginger's suggestion isn't working, there is something about the table that you haven't told us.

The way you put it, it sounds like the table has one field for donor and one field for Amount.

It should be as simple as Ginger's advice. Be sure you didn't bring any other fields into the grid.

If you can group by Donor and sum Amount in a report, then you can certainly do it in a query - easy as pie.

Kris, Is there something you know about this data of which I am unaware? Why would this person need to go through all of that for a simple Group By and Sum?

 

[sandingdude]

The database is broken in 2 tables - donors and contributions

The donors has an ID field

The contributions table has an ID field and a NAMEID field. The NAMEID field is the foreign key.

Both tables are linked.

Will this affect our the Group By feature works?

 

[sandingdude]

I've narrowed down the problem - it is with the date field. Eash donation is tied to a date. The date is very important because I need the report to be between a certain date range, but the problem is the Amount field cannot sum itself properly when the date field is in there, but if I don't have it in there I get all donations.

Any ideas?

Thanks,

Dave

 

[Lilliabeth]

Create a query that just pulls the donor and the amount the correct dates - use something like Between 1/1/2005 and 12/31/2005 as criteria for the date field. Save it and close it.

Make another query - this one should use the previous query. Bring to the grid only Donor and Amount - not date. Group By Donor and Sum Amount.

 

[Lilliabeth]

I forgot - in the second query, give the amount field a criteria of >5000.

 

[GingerR]

Uncheck the little check box in the DATE column. So you can use it to filter but it will not be VISIBLE in your results.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html