Aggregate function - Access asks 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?
 
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?
Click to expand...

I would double check first the spelling of Contributor_ID in the design
of both tables.

BTW, although doing Count(Contribution.Candidate_ID) seems more logical
to me also, there are performance reasons for using Count(*) instead.

From:

Microsoft Jet Database Engine Programmer's Guide, Second Edition, 1997,
ISBN 1-57231-342-0, p. 159:

Common Pitfalls

Using Count(<fieldname>) instead of Count(*). When you have to
determine the number of records, you should use Count(*) rather than
Count(<fieldname>) because there are Rushmore optimizations that allow
Count(*) to be executed much more quickly than Count(<fieldname>).

James A. Fortune
(e-mail address removed)
 
Hello damon.
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?
Click to expand...

Check, if the name of the column in the Contribution table really is
Contributor_ID, and, if not, correct the name in the query.
 
Back
Top