SQL query to return a new generated column based on another column

I have an table in an Access database with three columns:

File
Date
Amount

If I would like to retrieve the sum of the amounts for each file, from 1st of October to 1st of November 2006, I would write an SQL query like this:

SELECT File, SUM(Amount) AS Total FROM Table1 WHERE ([Date] >= #01/10/2006#) AND ([Date] < #01/11/2007) GROUP BY File

Now, what if I wanted to return three columns instead of two. The first two columns would be lthe same as above (File and Total), and the third column would be another total - the sum of all Amounts from 1st of October 2005 to 1st November 2005.
SETPAsked:
Who is Participating?
 
TheSloathCommented:
Adjust the query you have so that one of the columns is the period, e.g.:

SELECT IIf(Month([Date]) IN (10,11,12), Year([Date]), Year([Date]) - 1) As YearStart, File, SUM(Amount) AS Total FROM Table1 WHERE ([Date] >= #01/10/2006#) AND ([Date] < #01/11/2007) GROUP BY File

Then create a crosstab query based on that query, with the YearStart as the column headers: just choose the 'FIRST' Total for the Value column.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.