How do I Group by LocID and Month while totaling sales for each month

[Bill Knott]

I have a table with year's worth of weekly sales figures for some
fifty different locations , I would like to group my data in a query
by Location , by month and have monthly totales for each of the
locations by month.

The fields of interest in my table are: LocID , Sales Ending
Date , and Sales Amount


thanks for any advice that you can provide.

 

[John Spencer]

SELECT Locid
, Month([Sales Ending Date]) as theMonth
, Sum([Sales Amount]) as MonthTotal
FROM [Some Table]
GROUP BY Locid
, Month([Sales Ending Date])

This assumes that you only have one year's worth of data in your table.

If you can only build a query using the design view
-- Open a new query
-- Add your table
-- Select Locid, Sales Ending Date, and SalesAmount
-- Edit the Sales ending date to
TheMonth: Month([Sales Ending Date]
-- Select View: Totals from the menu
-- Change GROUP BY to SUM under the Sales Amount field

Run the query

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================