Access Query

Topic

New Reply

I have a table of contributors to a charity. There are three fields:

Contact ID, Contribution $, Contribution Date.

Many people have contributed more than once over the years, so there is separate record for each contribution.
I want to find how many Contacts have contributed more than $999 (Criteria for Contribution $) each time. When I run a Select Query I get, of course, all of the multiple contributions made by each contact. I want a list showing each Contact only once. When I use a Totals Query, with various combinations of Group By and Count, I still get multiple records. What am I doing wrong?
HH

Reply | Quote

Replies

Try this:
SELECT ContactID, Sum(Contribution) AS SumOfContribution, Right([ContributionDate],4) AS [year]
FROM yourfilename
GROUP BY ContactID, Right([ContributionDate],4)
HAVING (((Sum(Contribution))>=1000));

Reply | Quote

Is this what you are after

SELECT DISTINCT ContactID FROM yourtable WHERE Contribution > 999

Reply | Quote

Seeing how you want to know how many contributors ALWAYS contribute at least $1000, try this:

SELECT [ContactID], Min([Contribution]) AS MinC FROM [Contributions]
GROUP BY [ContactID] HAVING Min([Contribution]) >= 1000

The above gives you a list of contributors who always contribute at least $1000, along with the smallest amount on record for the contributor.

If you want merely a count of these folks, then use this:

SELECT Count(*) AS MyCount
FROM (SELECT [ContactID] FROM [Contributions] GROUP BY [ContactID] HAVING Min([Contribution])>=1000)

Reply | Quote

Is this what you are after

SELECT DISTINCT ContactID FROM yourtable WHERE Contribution > 999

Hi Browni,

Your SQL statement will return a list of contacts that have contributed over $999 at least once. It does not return a list of contacts that always contribute over $999. Forgive me if I misread the OP’s original need.

Reply | Quote

Thanks to all.
Browni’s response was the simplest, and gave me the answer I needed. All the big spenders who have given at least $1000 each time.
However, I am experimenting with the other posts because they have given me new insight into my data as well as some new approaches to using SQL. Previously I had only used the Design grid with a Totals Query.

**After further analysis I found that James Pitcher is correct. I do want those who ALWAYS contribute at least $1000.
HH

Reply | Quote