Crosstab query

[Paula]

A query contains the fields CustomerID, CustomerName, OrderID and ItemID.
The query is based on a customers table, orders table, and order details
table. The orders table has a foreign key of CustomerID and is joined to
CustomerID in the customer table. The order details table has a foreign key
of OrderID and is joined to OrderID in the orders table. How do you create a
crosstab query that shows the total nunber of orders placed by each customer
and the total number of items ordered by each customer? Like the following:
CustomerName Total Orders Placed Total Items Ordered

Thanks!

Paula

[Kaj Julius]

Re: Crosstab query


"Paula" <pmorgan@notmym ail.com> skrev i en meddelelse
news:O5Pdd.3316 $5i5.1527@newsr ead2.news.atl.e arthlink.net...[color=blue]
>A query contains the fields CustomerID, CustomerName, OrderID and ItemID.
> The query is based on a customers table, orders table, and order details
> table. The orders table has a foreign key of CustomerID and is joined to
> CustomerID in the customer table. The order details table has a foreign
> key
> of OrderID and is joined to OrderID in the orders table. How do you create
> a
> crosstab query that shows the total nunber of orders placed by each
> customer
> and the total number of items ordered by each customer? Like the
> following:
> CustomerName Total Orders Placed Total Items Ordered
>
> Thanks!
>
> Paula
>
>[/color]

Maybe:

SELECT Customers.Custo merName, COUNT(DISTINCT Orders.OrderID) AS
TotalOrdersPlac ed, COUNT(DISTINCT OrderDetails.It emID) AS TotalItemsOrder ed
FROM (Customers INNER JOIN Orders ON Customers.Custo merID =
Orders.Customer ID)
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.Or derID
ORDER BY Customers.Custo merName

Kaj