Solved

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

Posted on 2007-03-29
1
267 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:SETP
1 Comment
 
LVL 9

Accepted Solution

by:
TheSloath earned 500 total points
ID: 18815120
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now