Single Query

  • Thread starter Thread starter Ananth
  • Start date Start date
A

Ananth

I have 3 year Spend data pulled from the Hyperion in the following format

Supplier_ID
Site_ID
Year
Jan $
Feb $
Mar $
Apr $
May $
Jun $
Jul $
Aug $
Sep $
Nov $
Dec $

Is it possible using a single query to restate the above database to the
following format

Supplier_ID
Site_ID
Year
Spend $
Month Ref


Ananth
 
Sure, in SQL view:


SELECT supplier_ID, site_ID, DateSerial(Year, 1, 1) AS FirstOfMonth, [Jan $]
AS [Spend $] FROM table
UNION ALL
SELECT supplier_ID, site_ID, DateSerial(Year, 2, 1), [Feb $] FROM table
UNION ALL
SELECT supplier_ID, site_ID, DateSerial(Year, 3, 1), [Mar $] FROM table
UNION ALL
SELECT supplier_ID, site_ID, DateSerial(Year, 4, 1), [Apr $] FROM table
.....
UNION ALL
SELECT supplier_ID, site_ID, DateSerial(Year, 12, 1), [Dec $] FROM table




Note that I combined year and month into a single date, the first of the
month of the year.


Vanderghast, Access MVP
 
Sure, in SQL view:


SELECT supplier_ID, site_ID, DateSerial(Year, 1, 1) AS FirstOfMonth, [Jan $]
AS [Spend $] FROM table
UNION ALL
SELECT supplier_ID, site_ID, DateSerial(Year, 2, 1), [Feb $] FROM table
UNION ALL
SELECT supplier_ID, site_ID, DateSerial(Year, 3, 1), [Mar $] FROM table
UNION ALL
SELECT supplier_ID, site_ID, DateSerial(Year, 4, 1), [Apr $] FROM table
.....
UNION ALL
SELECT supplier_ID, site_ID, DateSerial(Year, 12, 1), [Dec $] FROM table




Note that I combined year and month into a single date, the first of the
month of the year.


Vanderghast, Access MVP
 
You would need to use a UNION query with twelve query clauses.

SELECT Supplier_ID, Site_ID, [Year], [Jan $] as [Spend $]
, "Jan" as [Month Ref]
FROM [YourCurrentTable]
UNION ALL
SELECT Supplier_ID, Site_ID, [Year], [Feb $] as [Spend $]
, "Feb" as [Month Ref]
FROM [YourCurrentTable]
UNION ALL
SELECT Supplier_ID, Site_ID, [Year], [Mar $] as [Spend $]
, "Mar" as [Month Ref]
FROM [YourCurrentTable]
UNION ALL
....

By the way good field names would not include the $ or spaces. And Year
is a reserved word in Access. I would use names like DollarsSpent,
MonthRef, and YearRef.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Sure, in SQL view:


SELECT supplier_ID, site_ID, DateSerial(Year, 1, 1) AS FirstOfMonth, [Jan $]
AS [Spend $] FROM table
UNION ALL
SELECT supplier_ID, site_ID, DateSerial(Year, 2, 1), [Feb $] FROM table
UNION ALL
SELECT supplier_ID, site_ID, DateSerial(Year, 3, 1), [Mar $] FROM table
UNION ALL
SELECT supplier_ID, site_ID, DateSerial(Year, 4, 1), [Apr $] FROM table
.....
UNION ALL
SELECT supplier_ID, site_ID, DateSerial(Year, 12, 1), [Dec $] FROM table




Note that I combined year and month into a single date, the first of the
month of the year.


Vanderghast, Access MVP
 
Sure, in SQL view:


SELECT supplier_ID, site_ID, DateSerial(Year, 1, 1) AS FirstOfMonth, [Jan $]
AS [Spend $] FROM table
UNION ALL
SELECT supplier_ID, site_ID, DateSerial(Year, 2, 1), [Feb $] FROM table
UNION ALL
SELECT supplier_ID, site_ID, DateSerial(Year, 3, 1), [Mar $] FROM table
UNION ALL
SELECT supplier_ID, site_ID, DateSerial(Year, 4, 1), [Apr $] FROM table
.....
UNION ALL
SELECT supplier_ID, site_ID, DateSerial(Year, 12, 1), [Dec $] FROM table




Note that I combined year and month into a single date, the first of the
month of the year.


Vanderghast, Access MVP
 
John

Thanks for yr suggestion. I have taken note of yr advise too re data names.

Ananth

John Spencer said:
You would need to use a UNION query with twelve query clauses.

SELECT Supplier_ID, Site_ID, [Year], [Jan $] as [Spend $]
, "Jan" as [Month Ref]
FROM [YourCurrentTable]
UNION ALL
SELECT Supplier_ID, Site_ID, [Year], [Feb $] as [Spend $]
, "Feb" as [Month Ref]
FROM [YourCurrentTable]
UNION ALL
SELECT Supplier_ID, Site_ID, [Year], [Mar $] as [Spend $]
, "Mar" as [Month Ref]
FROM [YourCurrentTable]
UNION ALL
....

By the way good field names would not include the $ or spaces. And Year
is a reserved word in Access. I would use names like DollarsSpent,
MonthRef, and YearRef.


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

I have 3 year Spend data pulled from the Hyperion in the following format

Supplier_ID
Site_ID
Year
Jan $
Feb $
Mar $
Apr $
May $
Jun $
Jul $
Aug $
Sep $
Nov $
Dec $

Is it possible using a single query to restate the above database to the
following format

Supplier_ID
Site_ID
Year
Spend $
Month Ref


Ananth
Click to expand...
Click to expand...
 
Back
Top