Sum Total By Year Help

[bradleyg]

I have two columns, in one column the date (its by month and its for the last 4 years) and the second column is the sales for that month



DATE TOTAL SALES
Jan 2010 $1.00
feb 2010 $2.00
march 2010 $3.00
Jan 2011 $3.35
Feb 2011 $2.20
and so on....

I would like to run a query that will sum the sales by the year
2010 $20.00
2011 $22.00
2012 $23.50
and so on

I think I need to do some sort of Dsum but really am lost.
the names of the columns are "Date" and "Total Sales"

Thanks!

Brad

any help will do

[June7]

What data type is [Date] field - text with month and year only?

Date is a reserved word. Should not use reserved words as names. Also, advise not to use spaces and special characters/punctuation (underscore is exception), nor all caps in naming convention. Better would be Total_Sales or TotalSales.

[bradleyg]

The date is a short date (and I will change the name. and total sales is currency and I will also change it to total_sales.

t

[June7]

So it is a date/time field with format setting of Short Date?

SELECT Year([Date]) AS Yr, Sum([Total_Sales]) AS SumSales FROM tablename GROUP BY Year([Date]);

Use the query builder and select Totals from the ribbon design tab.

If you want to display detail records as well as aggregate data, build a report using Grouping & Sorting features with summary calcs in footers.

[bradleyg]

thank you!