Count No of orders for customer

  • Thread starter Thread starter mogens
  • Start date Start date
M

mogens

I have a qry that extracts the open orders, ie orders that have not yet
been processed. The qry is based on table_orders and a linked
table_orders_detail.

What I need is to find out how many occurrences of the field
[table_orders].[customers_name] there are for each order line. Can this
be done? As far as I can see I need somehow to re-link to table_orders,
otherwise I guess a count of [table_orders].[customers_name] would only
reveal the occurrences in the extract contained in the qry, or???

Have tried to self-join table_orders, but this does not seem to do the
trick.
 
Hi Mogens,

Can you try and explain more clearly? Your description doesn't make
sense in terms of the usual structure for an "orders" database. See for
example the Northwind sample database, where the fields containing
customer name are (correctly) in the Customers table and do not appear
in either the Orders table or the Order Details table, and the
CustomerID field appears in Customers and Orders but not OrderDetails.


I have a qry that extracts the open orders, ie orders that have not yet
been processed. The qry is based on table_orders and a linked
table_orders_detail.

What I need is to find out how many occurrences of the field
[table_orders].[customers_name] there are for each order line. Can this
be done? As far as I can see I need somehow to re-link to table_orders,
otherwise I guess a count of [table_orders].[customers_name] would only
reveal the occurrences in the extract contained in the qry, or???

Have tried to self-join table_orders, but this does not seem to do the
trick.
Click to expand...
 
John said:
Hi Mogens,

Can you try and explain more clearly? Your description doesn't make
sense in terms of the usual structure for an "orders" database. See for
example the Northwind sample database, where the fields containing
customer name are (correctly) in the Customers table and do not appear
in either the Orders table or the Order Details table, and the
CustomerID field appears in Customers and Orders but not OrderDetails.
Click to expand...
Thanks John,
I'm using an Oscommerce database where table_orders holds one line per
order, and table_orders_products one line per product included in the
order (but the latter is of no importance for the question I have
asked). There is a facility in Oscommerce so that customers can purchase
without account, so the customers name and address info is stamped into
table_orders. A customer who have purchased for instance 5 times, will
occur 5 times in table_orders.

I know of course that the name might be imprecise (customers might type
different from order to order), but I can live with this. What I'm after
is to count the number of occurrences the customer name included in the
qry occurs in the entire table_orders. So what is unclear to me is how I
can work on some database information that is in fact not "within" the
qry. Hope this makes more sence.

Cheers/Mogens
 
This sounds like a job for a subquery rather than a join. assuming you
have a query qryOpenOrders which returns OrderID, try terms of something
like this:

SELECT customers_name, COUNT(customers_name) AS CustomerCount
FROM table_orders
WHERE OrderID IN (SELECT OrderID FROM qryOpenOrders);



Hi Mogens,

Can you try and explain more clearly? Your description doesn't make
sense in terms of the usual structure for an "orders" database. See for
example the Northwind sample database, where the fields containing
customer name are (correctly) in the Customers table and do not appear
in either the Orders table or the Order Details table, and the
CustomerID field appears in Customers and Orders but not OrderDetails.


I have a qry that extracts the open orders, ie orders that have not yet
been processed. The qry is based on table_orders and a linked
table_orders_detail.

What I need is to find out how many occurrences of the field
[table_orders].[customers_name] there are for each order line. Can this
be done? As far as I can see I need somehow to re-link to table_orders,
otherwise I guess a count of [table_orders].[customers_name] would only
reveal the occurrences in the extract contained in the qry, or???

Have tried to self-join table_orders, but this does not seem to do the
trick.
Click to expand...
Click to expand...
 
John said:
This sounds like a job for a subquery rather than a join. assuming you
have a query qryOpenOrders which returns OrderID, try terms of something
like this:

SELECT customers_name, COUNT(customers_name) AS CustomerCount
FROM table_orders
WHERE OrderID IN (SELECT OrderID FROM qryOpenOrders);
Click to expand...
Great - thank a lot. Subqueries was a concept I didn't know about, but
this really open new opportunities!
 
Back
Top