Results 1 to 6 of 6

Thread: Access Query

  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Access Query

    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

  2. #2
    Star Lounger
    Join Date
    Dec 2009
    Location
    Findlay, Ohio
    Posts
    57
    Thanks
    4
    Thanked 6 Times in 6 Posts
    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));

  3. #3
    WS Lounge VIP Browni's Avatar
    Join Date
    Dec 2009
    Location
    Rochdale, UK
    Posts
    1,655
    Thanks
    38
    Thanked 161 Times in 139 Posts
    Is this what you are after

    SELECT DISTINCT ContactID FROM yourtable WHERE Contribution > 999

  4. #4
    New Lounger
    Join Date
    Mar 2013
    Posts
    16
    Thanks
    0
    Thanked 1 Time in 1 Post

    Contributors

    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)

  5. #5
    New Lounger
    Join Date
    Mar 2013
    Posts
    16
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Browni View Post
    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.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Smile

    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
    Last edited by Howard Hillman; 2013-03-16 at 13:19.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •