Count Query doubles results

[Guest]

Hi,

i have a query with two fields, IND_ID and Contact_ID, looking like this:

IND_ID Contact_ID
1 5
1 17
1 22
2 7
3 10
3 11

I want to count the number of Contact_ID's for each unique IND_ID, BUT the
result I get doubles the count, ie:

IND_ID CountofContact_ID
1 6
2 2
3 4

What is going on here?

My SQL is:

SELECT qryRdistrict_1A_1B.IND_ID, Count(qryRdistrict_1A_1B.contactID) AS
contact
FROM qryRdistrict_1A_1B
GROUP BY qryRdistrict_1A_1B.IND_ID;


This is driving me crazy, and is a problem I have often, any help greatly
appreciated

 

[Allen Browne]

You'll need to do that in 2 stages:
SELECT DISTINCT [IND_ID] FROM Table1;
and then
SELECT Count("*") FROM Query1;

As an alternative, see:
Extended DCount()
at:
http://allenbrowne.com/ser-66.html

The ECount() function behaves like DCount(), but with an optional argument
that gives you a distinct count, so you can use:
ECount("IND_ID", "Table1",, True)

 

[Guest]

I would take a look at qryRdistrict_1A_1B to make sure what you think is what
is fact.