Count of Groups

[Tieval]

I have a simple query to throw up suppliers that have been used more than twice. Unfortunately my base data has:

Supplier, Order Number, Item

I want to group by supplier and count the unique order numbers as I may have three line items for order number 123 and two line items for 124 and want to make a quantity of 2 order numbers.The following obviously gives five:

SELECT tblSuppliers.Supplier, Count(tblPOrders.ONumber) AS CountOfONumber
FROM tblPOrders INNER JOIN tblSuppliers ON tblPOrders.Supplier = tblSuppliers.ID
WHERE (((tblSuppliers.SApproved) Is Null))
GROUP BY tblSuppliers.Supplier
HAVING (((Count(tblPOrders.ONumber))>2))
ORDER BY tblSuppliers.Supplier;

 

[plog]

I want to group by supplier and count the unique order numbers

Then you need to base that query off a sub-query. You need to write a query that GROUPS BY Supplier and GROUPS BY order number. Then take that query and use it in the FROM clause of the one you posted above.

 

[Tieval]

Thanks Plog,

I have a touch of OCD and like to cram everything into one query where possible, I have the sub-query working but was hoping for a single stage solution.