Sum by year query

[wellmabt]

I'm slowly learning access and have now encountered this problem: I would
sum/count records by their year. I want it to look something like this when
I'm done.

Year #sold revenue expenses
1997 35 5000 1000
1998 40 2000 2000
....
totals 75 7000 3000

I've figured out how to sum or count all records at once from a table but
not how to break it out by year and then total. Can this done using a
regular query or is this going to involve some SQL work?

Thanks,
Brian

 

[Golfinray]

Try in the field criteria Between 01/01/1997 and 12/31/1997

 

[wellmabt]

I'm dealing with a lot of different years, and I want the actual years to
show up in the table, along with totals of several different fields for each
of those years. Is a crosstab query useful for this?

 

[John Spencer]

You can use
Year(SomeDateField) to get the year

Add a calculated field to your query

Field: TheYear: Year([TableName].[FieldName])
Total: Group BY

SELECT Year([TableName].[FieldName]) as TheYear
, Count(Items) as [#Sold]
, Sum(Income) as Revenue
, Sum(Costs) as Expenses
FROM TableName
GROUP BY Year([TableName].[FieldName])

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