Is there a way to sort multiple records by SUM ?

[JJordan]

I have two tables
Contacts
Donations

They are joined by the ContactID in the Contacts table (an autoGenerated Priomary Key)

One Contact can have Many Donations during the year.

I need to be able to figure out who has donated IN TOTAL for the year between Ranges of Numbers.

So for example, I need to know who has donated between $1 and $100; between 100.01 and $500, etc. and I need to sort by year (those amounts during 2001)

My problem is: I can generate a report that totals the donations for each person, but I cannot SORT the report by totals per person/contactID

I want the report to only print out names of peole that donated between x and y for time period between Q and R.

The fields I need for the report are contacts.contactId; contacts.firstname, contacts.lastname and then donations.donationamount (but sorted by TOTAL for the linked ContactID) and donations.donationdate (Solely to sort the Period of the Total Donations for each ContactID Between date1 and Date 1).

I cannot figure out how to write the query/queries to get this kind of report. I tried a cross tab query to get the totals donated by each ContactID/Person and that worked, but I cannot seem to use a date parameter in that query or an amount parameter on the Total SUM by ContactID.

Is this possible ?

 

[Rich]

Use a totals query to get the summed fields group by ContactID etc use a second query on the first to output the data in the manner you require

 

[JJordan]

I can get a total between paarameter Donation Amounts with the following Query (SQL):

SELECT Contacts.LastName, Contacts.FirstName, Contacts.ContactId, Sum(Donations.[Donation Amount]) AS [SumOfDonation Amount]
FROM Contacts INNER JOIN Donations ON Contacts.ContactId = Donations.ContactID
GROUP BY Contacts.LastName, Contacts.FirstName, Contacts.ContactId
HAVING (((Sum(Donations.[Donation Amount])) Between [Starting Amount] And [ending amount]));

This provides me a total of donations by any one person by just using a group by on the ID and SUM on the Donation Amount. The problem comes when I try to add any kind of date parameter (or date at all) because the SUM includes many dates of donations. If I add the date field to the query it gives me muiltiple donation amounts for each person, not one total.

I do not understand what you are suggesting I do.

 

[Cosmos75]

Use two queries!

1)

I need to be able to figure out who has donated IN TOTAL for the year between Ranges of Numbers.

Create a paramter query that pulls together all the data (Person, Date of donation, donation amount) required and use parameters (donation date - Between StartDate and EndDate, contact - Person's Name)


2)

So for example, I need to know who has donated between $1 and $100; between 100.01 and $500, etc. and I need to sort by year (those amounts during 2001)

Then create another query that takes the info (Person and donation amount, leave out date since you've already accounted for that) from the first query and totals the donations by person and put criteria in the donation totals (between $0-$100) [you may have to do this for each donation range]

Hope this helps!

EDIT: You can then make a report for each query and make a report using the donation category report as subreports to show all the data. (Might be hard to format everything though. Am not sure since I haven't used subreports before but I am told that they function like subforms.)

 

[Cosmos75]

To help you get started...

Use form qbfDate to enter the start and end date you want to see.

Query1 - Pull initial info with date criteria

Query2a - Totals donations and shows if total donations is <=100
Query2b - Totals donations and shows if total donations is >100 and <=1000
Query2c - Totals donations and shows if total donations is >1000

Report2a - Show results of query2a
Report2b - Show results of query2b
Report2c - Show results of query2c
FinalReport, show all reports on one report (Might have to mess with the formatting on this as I don't ever use subreports enough to know what would happen if a subreport was very long, but I figure this'll at least get you started)

Post back with any questions!

Let me know if this isn't what you are trying to accomplish.

 

[JJordan]

WOW Thanks. I think I can use that to learn and do what I need.

 

[Pat Hartman]

You really only need two queries.

query1:

SELECT Contacts.LastName, Contacts.FirstName, Contacts.ContactId, Sum(Donations.[Donation Amount]) AS SumOfDonationAmount, IIf(SumOfDonationAmount < 100.01, "lvl1", IIf(SumOfDonationAmount > 500, "lvl3", "lvl2")) AS DonationLevel
FROM Contacts INNER JOIN Donations ON Contacts.ContactId = Donations.ContactID
Where Donations.DonationDate Beweeen [Enter Start Date] and [Enter End Date]
GROUP BY Contacts.LastName, Contacts.FirstName, Contacts.ContactId;

Query2:
Select *
From query1
Where DonationLevel = "lvl2";

You could actually use the query that you developed but you need to add a Where clause to specify the date range. You can't use the Having clause since the Having clause is not applied until after the aggregation is completed and at that time, the DonationDate would not be available.

 

[Cosmos75]

Pat Hartman,

I don't know SQL so am not sure I am getting it right.

To use your way would be to just use my way but for Query2, just take away the criteria to <100 and show totals, and then add a new expression IIF (<100, "LVL1",IIF(AND(>100, <=1000),"LVL2","LVL3"))?

I knew there was a better and am not surprised that the great Pat Hartman showed me up!

Once again I fail to help someone.... (been trying so hard to help people out since I've been helped so often!!)

Edit: Pat, wouldn't your second query only return LVL2 donations where as JJordan wants a report to show ALL Donation levels?

JJordan, Did I misunderstand what you where looking for?? (i.e. Pat got it right and I got it wrong, again!)

 

[Cosmos75]

Updated db - only two queries!

With some inspiration from Pat Hartman I've refined the db.

Still not sure though if you wanted to

1) Create a report looking for people in a particular Donation Level (Pat's approach)

2) Create a report looking for people sorted by Donation Level (My appraoach)
- Updated with inspiration from Pat's IIF(<100, "LVL1 .... (I give credit where it's due!)
- Had I done something like this in EXCEL I would've have come up with the excact IF formula, but I guess I think differently when I work in ACCESS...


THe 2) approach will work MUCH nicer than the original db I posted. Post back and let me know how it goes!

 

[JJordan]

Thank you BOTH. I have now created a SINGLE report that will tell me Who donated between Amount X and Amount Y duriing a year. We use this to generate lists of Donors by their giving total for hte year. All Donors that donated between $0 and $50, All that donated between $50.01 and $100, etc. in 2000. I still need to refine it a little and if our Director can tell me what the preset AMOUNTS are, I will set it up as you did to generate ONE report for ALL levels for the Year ! Thanks A Lot.