Need help creating a query

Topic

New Reply

Lets say I have a table (Table1) with OrderID’s and CustomerID’s and another field (Type) which can be either S or H. I want to first limit the CustomerID’s to type H, then I want to find the total number of CustomerID’s per OrderID… How can I do this?

Note that the table can have the same CustomerID listed more than once for the same OrderID (I don’t want to get into the full structure of the table to explain why but it is necessary). This means that both OrderID and CustomerID needs to be grouped…. Also note that if a CustomerID is listed more than once for the same OrderID, I only want it counted once.

So for example. I may have in my table (assume I already limited to Customers of Type H)
OrderID CustomerID
1 50
1 52
1 53
1 50
2 50
2 60
2 60
2 60

What I want my query results to look like is:
OrderID Total
1 3
2 2

How can I do this?

Appreciate the help.

Chad

Reply | Quote

Replies

Create one query with CustomerId, OrderId and Type fields, limited to type H. Go to the properties list for the query and set “Unique Values” to yes. Now, create another query on the last query. This will be a totals query. Add the OrdersId field twice. Leave one at “group By” and change the other to “Count”. Run query.

Reply | Quote

Thanks. I got it working.

Chad

Reply | Quote