Totals by month query 1

[osp001]

I've been trying to get a query to work so that I can produce a running total by month of products that have been purchased. In order to do so, I use [tbl_items]![quantity]*[tbl_items]![unit_price] to get the line total. I've yet to figure out how to get a total by order ([tbl_orders]![sub_total]), but that's peripheral to what I really want to get: a sum of all the orders placed, broken down by month. Here's the select query I've been using:

SELECT tbl_orders.order_number, tbl_orders.date_requested, tbl_items.quantity, tbl_items.unit_price, [tbl_items]![quantity]*[tbl_items]![unit_price] AS Expr1

FROM tbl_shipping, tbl_suppliers INNER JOIN ((tbl_ordered_by INNER JOIN tbl_orders ON tbl_ordered_by.name = tbl_orders.ordered_by) INNER JOIN tbl_items ON tbl_orders.order_number = tbl_items.order_number) ON tbl_suppliers.SupplierID = tbl_orders.ordered_from

GROUP BY tbl_orders.order_number, tbl_orders.date_requested, tbl_items.quantity, tbl_items.unit_price, [tbl_items]![quantity]*[tbl_items]![unit_price]

ORDER BY tbl_orders.order_number;

Am I using the wrong type of query? Any help would be appreciated. Thanks!

 

[PHV]

A starting point:
SELECT Format(tbl_orders.date_requested,'yyyy mmmm') AS [Month], Sum(tbl_items.quantity*tbl_items.unit_price) AS MonthTotal
FROM tbl_orders
INNER JOIN tbl_items ON tbl_orders.order_number = tbl_items.order_number
GROUP BY Format(tbl_orders.date_requested,'yyyy mmmm')ORDER BY 1;



Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[osp001]

Much more elegant than my kludge. Thank you!