Counting No Duplicates

[Dante1980]

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

 

[ByteMyzer]

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

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

 

[Dante1980]

Help :-(

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

 

[ByteMyzer]

(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.