Help Totaling More Than One Entry

[CRBI]

I've posted this before but now can't locate my post.

I have a membership database in which I have a form created for input of
general data on each member (address, phone, email, etc) that is posted to
the Members table. Members make contributions and some members make more than
one throughout the year. I also have a Contributions table that shows each
Member's ID #, the date of the contribution, the reason, and the amount. I
created a subform of the Contributions form and imbedded it into the Members
form so that when I enter Member info I can also enter their Contribution
info.

Now I have to create an "End of Year" report broken down by amounts. For
example, I need one report for all members who contributed between $50 and
$250, then another for all members who contributed between $251 and $1,000,
and so on. I have created a query that pulls the data based on my criteria
(For Date I have put >12/31/2009 AND <01/01/2010 to it will pull only those
contributions from 2009. The second criteria set is the amount they gave, to
which my criteria is >$49 AND <$249. This is a Make Table Query that builds a
new table based on the recordset. Next I have a report based on each of those
tables.

However, I have a problem. Some members may contribute more than once over
the period of a year. So, John Doe may have contributed $200 in April and
then $100 in December, so his total for the year would be $300, falling into
the "Between $250 and $1,000 range. However, due to my criteria it would pull
John Doe twice into the "$50-$249 table. I need to somehow be able to pull
only John Doe's cumulative total so that he does not show up twice in the
$50-$250 table, but pulls into the query for the "$251-$1,000" table, and
eventually the report. I need to be able to somehow do this in the query so
that John Doe would actually be left out of the $50-$250 table made from the
query and instead his cumulative amount should have him post to the
$250-$1,000 table. Is there any possible (and fairly simple) way to do this?
I'm very much a novice at creating expressions in Access.

Thanks for any help!

 

[Jeff Boyce]

Take a look at a Totals query.

First run the Totals query to get the total amount contributed by each
person during your time period.

Then process it ...


--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John W. Vinson]

However, I have a problem. Some members may contribute more than once over
the period of a year. So, John Doe may have contributed $200 in April and
then $100 in December, so his total for the year would be $300, falling into
the "Between $250 and $1,000 range. However, due to my criteria it would pull
John Doe twice into the "$50-$249 table. I need to somehow be able to pull
only John Doe's cumulative total so that he does not show up twice in the
$50-$250 table, but pulls into the query for the "$251-$1,000" table, and
eventually the report. I need to be able to somehow do this in the query so
that John Doe would actually be left out of the $50-$250 table made from the
query and instead his cumulative amount should have him post to the
$250-$1,000 table. Is there any possible (and fairly simple) way to do this?
I'm very much a novice at creating expressions in Access.

An expression isn't the right tool here... you need a Totals Query.

Create a query joining your two tables. Pick the donor name from the donors
table, and the contribution date and amount from the donations table (you may
want other fields).

Change the query to a Totals query by clicking the Greek Sigma icon on the
toolbar (looks like a sideways M).

Change the default "Group By" to "Where" under the date, and put on the
criteria line

= DateSerial(Year(Date()) - 1, 1, 1) AND < DateSerial(Year(Date()), 1, 1)

to get just last year's donations.

Change the Group By to "Sum" under the amount field.

It's certainly not necessary or appropriate to do a MakeTable query though!
What you might want to do is create a little table named Ranges with fields
Low, High and RangeName with records like

0;50;"Skinflint"
50;250;"Scrooge"
250;500;"Scrooge's Nephew"

and so on. You can then create a second query joining this table to the Totals
query:

SELECT totalsquery.DonorName, Ranges.RangeName
FROM totalsquery INNER JOIN Ranges
ON Ranges.Low <= totalsquery.SumOfAmount
AND Ranges.High > totalsquery.SumOfAmount;