|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
July 8th, 2009, 07:36 AM
posted to microsoft.public.access.queries
|
|||
|
|||
Single Query
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 
|
|
#2
July 8th, 2009, 12:15 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Single Query
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 "Ananth" wrote in message ... 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
|
|
#3
July 8th, 2009, 12:16 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Single Query
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 "Ananth" wrote in message ... 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
|
|
#4
July 8th, 2009, 12:24 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Single Query
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 '================================================= === Ananth wrote: 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
|
|
#5
July 8th, 2009, 12:35 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Single Query
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 "Ananth" wrote in message ... 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
|
|
#6
July 8th, 2009, 12:35 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Single Query
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 "Ananth" wrote in message ... 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
|
|
#7
July 9th, 2009, 06:06 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Single Query
John
Thanks for yr suggestion. I have taken note of yr advise too re data names. Ananth "John Spencer" wrote: 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 '================================================= === Ananth wrote: 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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
