Count Duplicates Once

[Paula]

A query contains the fields CustomerID, CustomerName, OrderID, OrderDate and
ItemID.
The query is based on a customers table, orders table, and order details
table. The orders table has a foreign key of CustomerID and is joined to
CustomerID in the customer table. The order details table has a foreign key
of OrderID and is joined to OrderID in the orders table. The number of
records in the query is determined by the total number of items in all the
orders. Of course, each record in the query contains OrderID. When I convert
the query to a totals query and do a Count on the OrderID field by Customer
and Year of OrderDate, the number I get is the same as the number of items
because each record in the query contains OrderID. How can I do a count so
that where there are duplicate OrderIDs, that OrderID is only counted once?
Is there a CountDistinct, DistinctCount or some such?

Thanks!

Paula

[Squirrel]

Paula,

I don't believe there's a
count distinct
but there is a
select distinct.

So I'd suggest a query with
select distinct OrderID etc. etc.

and then base your count query on the select distinct query.

HTH -Linda
"Paula" <pm*****@notmym ail.com> wrote in message
news:vD******** *********@newsr ead2.news.atl.e arthlink.net...

A query contains the fields CustomerID, CustomerName, OrderID, OrderDate and ItemID.
The query is based on a customers table, orders table, and order details
table. The orders table has a foreign key of CustomerID and is joined to
CustomerID in the customer table. The order details table has a foreign key of OrderID and is joined to OrderID in the orders table. The number of
records in the query is determined by the total number of items in all the
orders. Of course, each record in the query contains OrderID. When I convert the query to a totals query and do a Count on the OrderID field by Customer and Year of OrderDate, the number I get is the same as the number of items
because each record in the query contains OrderID. How can I do a count so
that where there are duplicate OrderIDs, that OrderID is only counted once? Is there a CountDistinct, DistinctCount or some such?

Thanks!

Paula

[Pieter Linden]

Use SELECT DISTINCT instead of just SELECT. There's no doubt an
option for it in the query builder, but just click the SQL button in
design view and type it yourself. Then you should only get unique
values. Of course, if you have fields in your query that make your
records unique, then this won't work. Assuming it does, you could
then base your totals query on this query.