Problem returning DISTINCT records

25 May 2005 7:22 PM

Martin

Using an SQL query against an MS Access database:

I have a table containing these records:

Wave    Route    Order    Carton
12    123    1212    002
12    123    1212    005
12    a12    7364    002
12    a12    7364    003
12    a12    4545    001
12    a12    7364    006
02    567    6152    002
02    567    6152    002
12    123    1212    004
12    123    1212    005
12    123    1212    008

I need to execute a query that will return the number
of UNIQUE records for each Wave-Route-Order combination.
Like this:

Wave    Route    Order    Count
02    567    6152    2
12    123    1212    4
12    a12    4545    1
12    a12    7364    3

Notice that Wave 12, Route 123, Order 1212 actually
has 5 records in the table but two of them are identical
- thus, a count of 4 is proper.

I'm using this SQL statement;
SELECT DISTINCT Wave, Route, Order, Count(1) AS MyCount FROM TraceLog
   GROUP BY Wave, Route, Order
   ORDER BY Wave, Route, Order;

But the "DISTINCT" has no effect; whether it's there or not, the query
returns a count of 5 instead of 4.

Is there any way to get this to work properly?

Thanks.

25 May 2005 7:51 PM

Jeff Johnson [MVP: VB]

Show quote Hide quote

"Martin" <martinval***@comcast.net> wrote in message
news:5tj99196hmdnnjivr8l5s3c1u44rkkvutc@4ax.com...

> Notice that Wave 12, Route 123, Order 1212 actually
> has 5 records in the table but two of them are identical
> - thus, a count of 4 is proper.
>
> I'm using this SQL statement;
> SELECT DISTINCT Wave, Route, Order, Count(1) AS MyCount FROM TraceLog
> GROUP BY Wave, Route, Order
> ORDER BY Wave, Route, Order;
>
> But the "DISTINCT" has no effect; whether it's there or not, the query
> returns a count of 5 instead of 4.
>
> Is there any way to get this to work properly?

What you want is known as COUNT(DISTINCT) and Access/Jet simply doesn't
support it. (SQL Server does.) You need to make two queries, the first which
does a DISTINCT on all your columns and the second which does the GROUP BY
query using the first as the source.

25 May 2005 7:58 PM

João Simão

hi,

try

SELECT t.Wave, t.Route, t.Order, Count( t.Carton )
FROM (
               SELECT DISTINCT Wave, Route, Order, Carton
               FROM TraceLog
              ) AS t
GROUP BY t.Wave, t.Route, t.Order
ORDER BY t.Wave, t.Route, t.Order

João Simão F.

Show quoteHide quote

"Martin" <martinval***@comcast.net> escreveu na mensagem
news:5tj99196hmdnnjivr8l5s3c1u44rkkvutc@4ax.com...
> Using an SQL query against an MS Access database:
>
> I have a table containing these records:
>
> Wave Route Order Carton
> 12 123 1212 002
> 12 123 1212 005
> 12 a12 7364 002
> 12 a12 7364 003
> 12 a12 4545 001
> 12 a12 7364 006
> 02 567 6152 002
> 02 567 6152 002
> 12 123 1212 004
> 12 123 1212 005
> 12 123 1212 008
>
> I need to execute a query that will return the number
> of UNIQUE records for each Wave-Route-Order combination.
> Like this:
>
> Wave Route Order Count
> 02 567 6152 2
> 12 123 1212 4
> 12 a12 4545 1
> 12 a12 7364 3
>
> Notice that Wave 12, Route 123, Order 1212 actually
> has 5 records in the table but two of them are identical
> - thus, a count of 4 is proper.
>
> I'm using this SQL statement;
> SELECT DISTINCT Wave, Route, Order, Count(1) AS MyCount FROM TraceLog
> GROUP BY Wave, Route, Order
> ORDER BY Wave, Route, Order;
>
> But the "DISTINCT" has no effect; whether it's there or not, the query
> returns a count of 5 instead of 4.
>
> Is there any way to get this to work properly?
>
> Thanks.

25 May 2005 8:57 PM

Martin

OK - got it working.

Jeff, thanks for the guidance.
Joao, thanks for the example.

Martin

Problem returning DISTINCT records - MS Access