A2000: group detail records by customer

[A Man]

I want to make a query on an OrderDetail table that sums the sales for
year 2007 by customer. This query will be used to make a table, the
table will be used to make a report later on.

Anyway, the detail order table has the item number, qty ordered, and
BillDate. The year can be extracted using: Year: mid(str
([BillDate]),2,4).

I want each row to show one customer, and the sum of sales for the year
2007. Anyone know how to do this?

Thank you.

 

[John Spencer]

Lots of information mssing.
Assumptions:
You have a Customer Table
You have an Order Table - this has a customer id
You have an OrderDetails Table with fields
-- referencing the Order record
-- qtyOrdered
-- itemcost

You need to give us more details on the structure of the Orders table and
the OrderDetails table. If the orderdetails table does not include the cost
of the items ordered then you need to tell us where that information is
stored and what fields are involved.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Michael Gramelspacher]

I want to make a query on an OrderDetail table that sums the sales for
year 2007 by customer. This query will be used to make a table, the
table will be used to make a report later on.

Anyway, the detail order table has the item number, qty ordered, and
BillDate. The year can be extracted using: Year: mid(str
([BillDate]),2,4).

I want each row to show one customer, and the sum of sales for the year
2007. Anyone know how to do this?

Thank you.

This is something similar that runs in Northwind. Without actual table names
and column names and data types, it is a little difficult to construct a
tailor-made query.

SELECT Orders.CustomerID,
SUM(IIF(DATEPART("yyyy",Orders.ShippedDate) = 1997,
[Order Subtotals].Subtotal,0)) AS [1997 Sales]
FROM Orders
INNER JOIN [Order Subtotals]
ON Orders.OrderID = [Order Subtotals].OrderID
WHERE Orders.ShippedDate IS NOT NULL
GROUP BY Orders.CustomerID;