Hi guys,

I'm actually starting to use Access and I have a question about a query.
Basically I have a table that says:

ITEM ORDER CAT
A 1200 01
A 1200 02
B 1200 01
B 1100 01

I'd like to count the number of Orders per each item, without counting duplicates. The Output should be:
A = 1 Order
B = 2 Orders

But when I use the Count option on my query table under ORDER it calculates:

A = 2
B = 2

As if is counting the number of records.

How can I solve this problem?

Thanks so much


dante

Try the following SQL (substitute the appropriate highlighted text):

SELECT T1.ITEM, COUNT(T1.ORDER) AS [# OF ORDERS]
FROM
(SELECT DISTINCT ITEM, ORDER
FROM MyTable
) T1
GROUP BY T1.ITEM;

Hi,

Thanks for your help.
Actually I'm not an expert of SQL, so I was wondering if there is an easy way to do that directly from the Query Table.


Thanks again


Dante

(siigghhhhh)

You simply do the following:

1) In the database, click on the Queries tab.
2) Double-click on Create query in Design view.
3) Close the Show Table dialog box.
4) Switch to SQL view (Menu bar: View > SQL View)
5) Copy and paste the text from the SQL statement I posted, making the appropriate changes to the highlighted table and field names.
6) Save/Run the query.