Distinct count syntax?

[Anne Heddal]

In the first line I'm trying to count the the unqiue values of the ID
column, but I'm getting syntax error, any idea how to format the distinct
count?

SELECT Count(test.ID) AS IDCOUNT FROM (select distinct test.ID FROM test),
Sum(test.balance) AS BAL, test.statement, billing.YYMM
FROM billing INNER JOIN test ON billing.ID = test.ID
WHERE (((test.invoice) Like "X*") AND ((test.due)=0) AND
((test.collections)=0))
GROUP BY test.statement, billing.YYMM;

[Steve Jorgensen]

Unfortunately, count distinct is simply not provided in Jet. You have to make
a nested query instead, where the first query groups by all the intended group
by fields plus the value in question, then the second includes just the
intended group by fields and includes a simple Count(*) in the Select list.

On Sat, 24 Jan 2004 10:24:22 +0100, "Anne Heddal" <an********@yahoo.com>
wrote:

In the first line I'm trying to count the the unqiue values of the ID
column, but I'm getting syntax error, any idea how to format the distinct
count?

SELECT Count(test.ID) AS IDCOUNT FROM (select distinct test.ID FROM test),
Sum(test.balance) AS BAL, test.statement, billing.YYMM
FROM billing INNER JOIN test ON billing.ID = test.ID
WHERE (((test.invoice) Like "X*") AND ((test.due)=0) AND
((test.collections)=0))
GROUP BY test.statement, billing.YYMM;

[Anne Heddal]

Thought I tried that in the syntax below, but I guess my nesting is wrongly
formatted...

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:lj********************************@4ax.com...

Unfortunately, count distinct is simply not provided in Jet. You have to make a nested query instead, where the first query groups by all the intended group by fields plus the value in question, then the second includes just the
intended group by fields and includes a simple Count(*) in the Select list.
On Sat, 24 Jan 2004 10:24:22 +0100, "Anne Heddal" <an********@yahoo.com>
wrote:

In the first line I'm trying to count the the unqiue values of the ID
column, but I'm getting syntax error, any idea how to format the distinct
count?

SELECT Count(test.ID) AS IDCOUNT FROM (select distinct test.ID FROM test),Sum(test.balance) AS BAL, test.statement, billing.YYMM
FROM billing INNER JOIN test ON billing.ID = test.ID
WHERE (((test.invoice) Like "X*") AND ((test.due)=0) AND
((test.collections)=0))
GROUP BY test.statement, billing.YYMM;