Aggregate function - Access looking for value, why?

  • Thread starter Thread starter damon.blackmore
  • Start date Start date
D

damon.blackmore

I'm trying to get a count of all the Contributors who have given to
more than one candidate. Here's my code:

SELECT Contributor.Contributor_ID, Contributor.Contributor,
Count(Contribution.Candidate_ID) AS NumberContributions
FROM Contributor INNER JOIN Contribution
ON Contributor.Contributor_ID = Contribution.Contributor_ID
GROUP BY Contributor.Contributor_ID, Contributor.Contributor
HAVING Count(Contribution.Candidate_ID >1);

When I try to run the query, it asks for Contribution.Contributor_ID,
as though it's looking for a form input. If I enter nothing and just
accept, it returns a blank query. How do I get this to just pull every
instance?
 
Are you sure it's asking for Contribution.Contributor_ID, or is it actually
asking for Contribution_Candidate_ID, what you've got inside the Count
function?

Try

SELECT Contributor.Contributor_ID, Contributor.Contributor, Count(*) AS
NumberContributions
FROM Contributor INNER JOIN Contribution
ON Contributor.Contributor_ID = Contribution.Contributor_ID
GROUP BY Contributor.Contributor_ID, Contributor.Contributor
HAVING Count(*) >1

If that still gives you a pop-up, make sure you haven't mistyped any of the
table or field names.
 
Back
Top