Count Distinct Syntax

[jdb]

I have a query and need to count the total number of unique records.

For example:

Table: 2004 & 2005 donors
Field1: ID
Field 2: Name
Feild 3: Date
Field 4: Amount

There are multiple entries for each ID. But, I only want to count each ID
once, regardless of how many times it appears.

There is quite a bit on these discussion boards about Count Distinct, and I
think I have the theory right, but am having trouble actually writing the sql
statement - i keep getting an error that says check the syntax.

SELECT Count (ID) FROM (SELECT Distinct ID FROM tbl.2004 & 2005 donors)

Please help with the syntax, and am I correct in placing that statement in
the 'Field' row of a new column in the query design? Do I put anything else
in the 'Table' 'Total', etc rows?

 

[Douglas J. Steele]

When your table name has spaces in it (which isn't recommended, btw), you
need to enclose the table name in square brackets. As well, you've included
a superfluous tbl. in your subquery.

Try:

SELECT Count(*) FROM (SELECT DISTINCT ID FROM [2004 & 2005 donors])

 

[jdb]

Thank you.

I'm sure this is just some stupid novice user problem, but i pasted exactly
what you wrote into the field row of a new column in the query. But I still
get a syntax error -check the subquery syntax and enclose subquery in
paranthesis.

I subsituted 'ID' for * - same problem; added a ';' at the end (don't know
why, but it seems to turn up alot) - same problem; added a '=' at the
beginning - same problem.

Also, if anyone could refer me to a good resource that shows the syntax for
this type of thing (I have Access 2002 inside out, but it doesnt go into this
kind of detail) it would save this board from having so many rudimentary
questions from me.

Thank you.

 

[Douglas J. Steele]

What I gave was the complete SQL statement, not something that should be
pasted into the grid.

Open your query in SQL view, and try pasting it there.