Grouping and Summing in Access Query

[Hey Lucy]

I have a Membership database which I maintain for a local non-profit environmental group. The db has many tables, but the two main tables are the Members Table (demographic data) and the Contributions Table (shows each contribution made by a member). One member can have multiple contributions so I do have a one-to-many relationship defined between the Members table and the Contributions table.

At the end of each year, reports have to be compiled for the Board of Directors and split into giving levels -- $50-$249, $250-$1,000, and so on. So, I have to pull all the contributions for the year and I do that using an expression in the date field of >=[Beg Date] and <=[End Date]. That way my end user, who is menu driven and never actually sees the database can input the correct dates to get a particular year's contributions.

Now, since I have to split these into giving level amounts, I wrote an expression in a query >=50 and <=249, but that doesn't give me what I want.

Let's say John Doe has made six contributions in 2011 of $50 each. When I run the above query, it will show John Doe six times in the resulting table, but what I actuallly need is for John Doe to show one time in a table in the $250-$1,000 level (because his total contributions for the year was $300).

I need to somehow be able to group by member (using their ID#) then sum all of their contributions for the year and have it return only one resulting record.

I know I have somehow done this in the past with an earlier version of Access (I'm using 2010 Pro now), but can't seem to accomplish this now. I have used the Total button but it just gives me the same results as if I hadn't. (Puzzled)




I do not know how to use SQL or VBA, so .....

 

[plog]

I need to somehow be able to group by member (using their ID#) then sum all of their contributions for the year and have it return only one resulting record.

For some one who doesn't know SQL or VBA you've perfectly defined what you need to do. Your statement above is so close its almost the actual SQL itself. When grouping and summing you need an aggregate query. This is the SQL for what you need:

SELECT [MemberID], YEAR([ContributionDate]) AS ContributionYear, SUM([Contribution]) AS TotalContribution FROM Contributions GROUP BY [MemberID], YEAR([ContributionDate]);

Everything inside brackets are my guesses as to what your actual field names are, you probably will need to change them. That SQL will return results like this:

MemberID, ContributionYear, TotalContribution
17, 2010, 550
17, 2011, 400
42, 2011, 500
43, 2009, 1000
43, 2010, 1000
43, 2011, 1000

This groups up every ID by year and sums their contribution for that year. Since you have some expressions already built to put TotalContributions into segments, I recommend you use this new query as the basis of another one in which you use those expressions. In that query you will be able to use criteria to limit the data to just one ContributionYear.

 

[vbaInet]

For some one who doesn't know SQL or VBA you've perfectly defined what you need to do. Your statement above is so close its almost the actual SQL itself.

It must be SQLSpeak