Single Query

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
 
V

vanderghast

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
 
V

vanderghast

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
 
J

John Spencer

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
'====================================================
 
V

vanderghast

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
 
V

vanderghast

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
 
A

Ananth

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab returns blank fields 6
Custom Format for crosstab headings 2
Crosstab Returns Empty Fields 2
UNION and Crosstab query 5
Parameter in Qry 2
Complex Query 4
Group by Range in Crosstab Query 4
Count Instances 1

Top