Esay way to turn rows into columns

G

Guest

Hello experts!

I have a union query that I am running in access 2003 that is as follows:
-- get hours from 3 different locations and union together
SELECT TSC.CHG_UID, TSC.CHG_CHARGENUMBER AS CN, [CHG_NORMALHOURS]/60000 AS
[Reg Hours], [CHG_OVERTIMEHOURS]/60000 AS [OT Hours], TS.PRD_ID, EP.PRD_NAME
AS Period, MWR.RES_NAME AS Resource, TS.TS_DEPT_NAME AS Dept, TS.TS_DEPT_UID,
EPT.PT_PROJ_NAME AS Project, EPT.PT_TASK_NAME AS Task, "Project" AS [Row Type]
FROM (((dbo_EPK_TS_CHARGES AS TSC INNER JOIN dbo_EPK_TS_TIMESHEETS AS TS ON
TSC.TS_UID = TS.TS_UID) INNER JOIN dbo_EPK_PERIODS AS EP ON TS.PRD_ID =
EP.PRD_ID) INNER JOIN dbo_MSP_WEB_RESOURCES AS MWR ON TS.WRES_ID =
MWR.WRES_ID) INNER JOIN dbo_EPK_PROJ_TASKS AS EPT ON (TSC.WPROJ_ID =
EPT.PT_WPROJ_ID) AND (TSC.TASK_UID = EPT.PT_TASK_UID)
WHERE (((EP.CB_ID)=0));

UNION SELECT TSC.CHG_UID, TSC.CHG_CHARGENUMBER AS CN,
[CHG_NORMALHOURS]/60000 AS [Reg Hours], [CHG_OVERTIMEHOURS]/60000 AS [OT
Hours], TS.PRD_ID, EP.PRD_NAME AS Period, MWR.RES_NAME AS Resource,
TS.TS_DEPT_NAME AS Dept, TS.TS_DEPT_UID, NWI.NWI_NAME AS Project, Null AS
Task, "Non-Work Item" AS [Row Type]
FROM (((dbo_EPK_TS_CHARGES AS TSC INNER JOIN dbo_EPK_TS_TIMESHEETS AS TS ON
TSC.TS_UID = TS.TS_UID) INNER JOIN dbo_EPK_PERIODS AS EP ON TS.PRD_ID =
EP.PRD_ID) INNER JOIN dbo_MSP_WEB_RESOURCES AS MWR ON TS.WRES_ID =
MWR.WRES_ID) INNER JOIN dbo_EPK_NONWORK_ITEMS AS NWI ON TSC.NWI_ID =
NWI.NWI_ID
WHERE (((EP.CB_ID)=0));

UNION SELECT TSC.CHG_UID, TSC.CHG_CHARGENUMBER AS CN,
[CHG_NORMALHOURS]/60000 AS [Reg Hours], [CHG_OVERTIMEHOURS]/60000 AS [OT
Hours], TS.PRD_ID, EP.PRD_NAME AS Period, MWR.RES_NAME AS Resource,
TS.TS_DEPT_NAME AS Dept, TS.TS_DEPT_UID, EPP.PROJECT_NAME AS Project,
EPW.WORKITEM_NAME AS Task, "Portfolio Item" AS [Row Type]
FROM ((((dbo_EPK_TS_CHARGES AS TSC INNER JOIN dbo_EPK_TS_TIMESHEETS AS TS ON
TSC.TS_UID = TS.TS_UID) INNER JOIN dbo_EPK_PERIODS AS EP ON TS.PRD_ID =
EP.PRD_ID) INNER JOIN dbo_MSP_WEB_RESOURCES AS MWR ON TS.WRES_ID =
MWR.WRES_ID) INNER JOIN dbo_EPKP_PROJECTS AS EPP ON TSC.PROJECT_ID =
EPP.PROJECT_ID) LEFT JOIN dbo_EPKP_PI_WORKITEMS AS EPW ON (TSC.TASK_UID =
EPW.WORKITEM_ID) AND (TSC.PROJECT_ID = EPW.PROJECT_ID)
WHERE (((TS.PRD_ID) In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19))
AND ((EP.CB_ID)=0));

The above query will return the dept, resource, project, task, period, etc.
However, I need each period to display as a field/column instead of a new row
for each period. For example: The query above will generate a new row for
each instance that time is reported to a new period, even if the other data
(dept, resource, project, and task) is the same. What I would like is for
the periods to be displayed individually as columns, that way there are less
rows returned and data is in the format that I need.

The method that I have been using to move the hours into columns by period
looks like the following:
--Jan 05 Query (1 of 12 sub queries used to get hours into individual period
columns)
SELECT [TS Actuals 001 Union].Dept, [TS Actuals 001 Union].Resource, [TS
Actuals 001 Union].Project, [TS Actuals 001 Union].Task, 0 AS [Pre 2005],
Sum([TS Actuals 001 Union].[Reg Hours]) AS [Jan 05], 0 AS [Feb 05], 0 AS [Mar
05], 0 AS [Apr 05], 0 AS [May 05], 0 AS [Jun 05], 0 AS [Jul 05], 0 AS [Aug
05], 0 AS [Sep 05], 0 AS [Oct 05], 0 AS [Nov 05], 0 AS [Dec 05], [TS Actuals
001 Union].PRD_ID, [TS Actuals 001 Union].Period, [TS Actuals 001
Union].TS_DEPT_UID, [TS Actuals 001 Union].[Row Type]
FROM [TS Actuals 001 Union]
GROUP BY [TS Actuals 001 Union].Dept, [TS Actuals 001 Union].Resource, [TS
Actuals 001 Union].Project, [TS Actuals 001 Union].Task, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, [TS Actuals 001 Union].PRD_ID, [TS Actuals 001 Union].Period,
[TS Actuals 001 Union].TS_DEPT_UID, [TS Actuals 001 Union].[Row Type]
HAVING (((Sum([TS Actuals 001 Union].[Reg Hours]))<>0) AND (([TS Actuals 001
Union].PRD_ID)=2 Or ([TS Actuals 001 Union].PRD_ID)=3 Or ([TS Actuals 001
Union].PRD_ID)=4 Or ([TS Actuals 001 Union].PRD_ID)=5 Or ([TS Actuals 001
Union].PRD_ID)=6))
ORDER BY [TS Actuals 001 Union].Dept, [TS Actuals 001 Union].Resource, [TS
Actuals 001 Union].Project;

The problem with this query is I have to write a query for each period or
group of periods and then union them back together.
--Final UNION
SELECT [Pre 05].Dept, [Pre 05].Resource, [Pre 05].Project, [Pre 05].Task,
[Pre 05].[Pre 2005], [Pre 05].[Jan 05], [Pre 05].[Feb 05], [Pre 05].[Mar 05],
[Pre 05].[Apr 05], [Pre 05].[May 05], [Pre 05].[Jun 05], [Pre 05].[Jul 05],
[Pre 05].[Aug 05], [Pre 05].[Oct 05], [Pre 05].[Nov 05], [Pre 05].[Dec 05],
[Pre 05].Period, [TS Actuals 001 Union].TS_DEPT_UID, [TS Actuals 001
Union].[Row Type]
FROM [TS Actuals 002 Pre 2005] AS [Pre 05]

UNION SELECT [Jan 05].Dept, [Jan 05].Resource, [Jan 05].Project, [Jan
05].Task, [Jan 05].[Pre 2005], [Jan 05].[Jan 05], [Jan 05].[Feb 05], [Jan
05].[Mar 05], [Jan 05].[Apr 05], [Jan 05].[May 05], [Jan 05].[Jun 05], [Jan
05].[Jul 05], [Jan 05].[Aug 05], [Jan 05].[Oct 05], [Jan 05].[Nov 05], [Jan
05].[Dec 05], [Jan 05].Period, [TS Actuals 001 Union].TS_DEPT_UID, [TS
Actuals 001 Union].[Row Type]
FROM [TS Actuals 002 Jan 05] AS [Jan 05]

UNION SELECT [Feb 05].Dept, [Feb 05].Resource, [Feb 05].Project,[Feb
05].Task, [Feb 05].[Pre 2005], [Feb 05].[Jan 05], [Feb 05].[Feb 05], [Feb
05].[Mar 05], [Feb 05].[Apr 05], [Feb 05].[May 05], [Feb 05].[Jun 05], [Feb
05].[Jul 05], [Feb 05].[Aug 05], [Feb 05].[Oct 05], [Feb 05].[Nov 05], [Feb
05].[Dec 05], [Feb 05].Period, [TS Actuals 001 Union].TS_DEPT_UID, [TS
Actuals 001 Union].[Row Type]
FROM [TS Actuals 002 Feb 05] AS [Feb 05]

UNION SELECT [Mar 05].Dept, [Mar 05].Resource, [Mar 05].Project, [Mar
05].Task, [Mar 05].[Pre 2005], [Mar 05].[Jan 05], [Mar 05].[Feb 05], [Mar
05].[Mar 05], [Mar 05].[Apr 05], [Mar 05].[May 05], [Mar 05].[Jun 05], [Mar
05].[Jul 05], [Mar 05].[Aug 05], [Mar 05].[Oct 05], [Mar 05].[Nov 05], [Mar
05].[Dec 05], [Mar 05].Period, [TS Actuals 001 Union].TS_DEPT_UID, [TS
Actuals 001 Union].[Row Type]
FROM [TS Actuals 002 Mar 05] AS [Mar 05];

UNION SELECT [Apr 05].Dept, [Apr 05].Resource, [Apr 05].Project, [Apr
05].Task, [Apr 05].[Pre 2005], [Apr 05].[Jan 05], [Apr 05].[Feb 05], [Apr
05].[Mar 05], [Apr 05].[Apr 05], [Apr 05].[May 05], [Apr 05].[Jun 05], [Apr
05].[Jul 05], [Apr 05].[Aug 05], [Apr 05].[Oct 05], [Apr 05].[Nov 05], [Apr
05].[Dec 05], [Apr 05].Period, [TS Actuals 001 Union].TS_DEPT_UID, [TS
Actuals 001 Union].[Row Type]
FROM [TS Actuals 002 Apr 05] AS [Apr 05];

I would like to know if there is a more efficient way to complete this
process.

Thanks for the help!!
 
V

Van T. Dinh

Have you tried Cross-tab Queries?

Check Access Help on Cross-tab queries.

HTH
Van T. Dinh
MVP (Access)



anthonysjo said:
Hello experts!

I have a union query that I am running in access 2003 that is as follows:
-- get hours from 3 different locations and union together
SELECT TSC.CHG_UID, TSC.CHG_CHARGENUMBER AS CN, [CHG_NORMALHOURS]/60000 AS
[Reg Hours], [CHG_OVERTIMEHOURS]/60000 AS [OT Hours], TS.PRD_ID, EP.PRD_NAME
AS Period, MWR.RES_NAME AS Resource, TS.TS_DEPT_NAME AS Dept, TS.TS_DEPT_UID,
EPT.PT_PROJ_NAME AS Project, EPT.PT_TASK_NAME AS Task, "Project" AS [Row Type]
FROM (((dbo_EPK_TS_CHARGES AS TSC INNER JOIN dbo_EPK_TS_TIMESHEETS AS TS ON
TSC.TS_UID = TS.TS_UID) INNER JOIN dbo_EPK_PERIODS AS EP ON TS.PRD_ID =
EP.PRD_ID) INNER JOIN dbo_MSP_WEB_RESOURCES AS MWR ON TS.WRES_ID =
MWR.WRES_ID) INNER JOIN dbo_EPK_PROJ_TASKS AS EPT ON (TSC.WPROJ_ID =
EPT.PT_WPROJ_ID) AND (TSC.TASK_UID = EPT.PT_TASK_UID)
WHERE (((EP.CB_ID)=0));

UNION SELECT TSC.CHG_UID, TSC.CHG_CHARGENUMBER AS CN,
[CHG_NORMALHOURS]/60000 AS [Reg Hours], [CHG_OVERTIMEHOURS]/60000 AS [OT
Hours], TS.PRD_ID, EP.PRD_NAME AS Period, MWR.RES_NAME AS Resource,
TS.TS_DEPT_NAME AS Dept, TS.TS_DEPT_UID, NWI.NWI_NAME AS Project, Null AS
Task, "Non-Work Item" AS [Row Type]
FROM (((dbo_EPK_TS_CHARGES AS TSC INNER JOIN dbo_EPK_TS_TIMESHEETS AS TS ON
TSC.TS_UID = TS.TS_UID) INNER JOIN dbo_EPK_PERIODS AS EP ON TS.PRD_ID =
EP.PRD_ID) INNER JOIN dbo_MSP_WEB_RESOURCES AS MWR ON TS.WRES_ID =
MWR.WRES_ID) INNER JOIN dbo_EPK_NONWORK_ITEMS AS NWI ON TSC.NWI_ID =
NWI.NWI_ID
WHERE (((EP.CB_ID)=0));

UNION SELECT TSC.CHG_UID, TSC.CHG_CHARGENUMBER AS CN,
[CHG_NORMALHOURS]/60000 AS [Reg Hours], [CHG_OVERTIMEHOURS]/60000 AS [OT
Hours], TS.PRD_ID, EP.PRD_NAME AS Period, MWR.RES_NAME AS Resource,
TS.TS_DEPT_NAME AS Dept, TS.TS_DEPT_UID, EPP.PROJECT_NAME AS Project,
EPW.WORKITEM_NAME AS Task, "Portfolio Item" AS [Row Type]
FROM ((((dbo_EPK_TS_CHARGES AS TSC INNER JOIN dbo_EPK_TS_TIMESHEETS AS TS ON
TSC.TS_UID = TS.TS_UID) INNER JOIN dbo_EPK_PERIODS AS EP ON TS.PRD_ID =
EP.PRD_ID) INNER JOIN dbo_MSP_WEB_RESOURCES AS MWR ON TS.WRES_ID =
MWR.WRES_ID) INNER JOIN dbo_EPKP_PROJECTS AS EPP ON TSC.PROJECT_ID =
EPP.PROJECT_ID) LEFT JOIN dbo_EPKP_PI_WORKITEMS AS EPW ON (TSC.TASK_UID =
EPW.WORKITEM_ID) AND (TSC.PROJECT_ID = EPW.PROJECT_ID)
WHERE (((TS.PRD_ID) In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19))
AND ((EP.CB_ID)=0));

The above query will return the dept, resource, project, task, period, etc.
However, I need each period to display as a field/column instead of a new row
for each period. For example: The query above will generate a new row for
each instance that time is reported to a new period, even if the other data
(dept, resource, project, and task) is the same. What I would like is for
the periods to be displayed individually as columns, that way there are less
rows returned and data is in the format that I need.

The method that I have been using to move the hours into columns by period
looks like the following:
--Jan 05 Query (1 of 12 sub queries used to get hours into individual period
columns)
SELECT [TS Actuals 001 Union].Dept, [TS Actuals 001 Union].Resource, [TS
Actuals 001 Union].Project, [TS Actuals 001 Union].Task, 0 AS [Pre 2005],
Sum([TS Actuals 001 Union].[Reg Hours]) AS [Jan 05], 0 AS [Feb 05], 0 AS [Mar
05], 0 AS [Apr 05], 0 AS [May 05], 0 AS [Jun 05], 0 AS [Jul 05], 0 AS [Aug
05], 0 AS [Sep 05], 0 AS [Oct 05], 0 AS [Nov 05], 0 AS [Dec 05], [TS Actuals
001 Union].PRD_ID, [TS Actuals 001 Union].Period, [TS Actuals 001
Union].TS_DEPT_UID, [TS Actuals 001 Union].[Row Type]
FROM [TS Actuals 001 Union]
GROUP BY [TS Actuals 001 Union].Dept, [TS Actuals 001 Union].Resource, [TS
Actuals 001 Union].Project, [TS Actuals 001 Union].Task, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, [TS Actuals 001 Union].PRD_ID, [TS Actuals 001 Union].Period,
[TS Actuals 001 Union].TS_DEPT_UID, [TS Actuals 001 Union].[Row Type]
HAVING (((Sum([TS Actuals 001 Union].[Reg Hours]))<>0) AND (([TS Actuals 001
Union].PRD_ID)=2 Or ([TS Actuals 001 Union].PRD_ID)=3 Or ([TS Actuals 001
Union].PRD_ID)=4 Or ([TS Actuals 001 Union].PRD_ID)=5 Or ([TS Actuals 001
Union].PRD_ID)=6))
ORDER BY [TS Actuals 001 Union].Dept, [TS Actuals 001 Union].Resource, [TS
Actuals 001 Union].Project;

The problem with this query is I have to write a query for each period or
group of periods and then union them back together.
--Final UNION
SELECT [Pre 05].Dept, [Pre 05].Resource, [Pre 05].Project, [Pre 05].Task,
[Pre 05].[Pre 2005], [Pre 05].[Jan 05], [Pre 05].[Feb 05], [Pre 05].[Mar 05],
[Pre 05].[Apr 05], [Pre 05].[May 05], [Pre 05].[Jun 05], [Pre 05].[Jul 05],
[Pre 05].[Aug 05], [Pre 05].[Oct 05], [Pre 05].[Nov 05], [Pre 05].[Dec 05],
[Pre 05].Period, [TS Actuals 001 Union].TS_DEPT_UID, [TS Actuals 001
Union].[Row Type]
FROM [TS Actuals 002 Pre 2005] AS [Pre 05]

UNION SELECT [Jan 05].Dept, [Jan 05].Resource, [Jan 05].Project, [Jan
05].Task, [Jan 05].[Pre 2005], [Jan 05].[Jan 05], [Jan 05].[Feb 05], [Jan
05].[Mar 05], [Jan 05].[Apr 05], [Jan 05].[May 05], [Jan 05].[Jun 05], [Jan
05].[Jul 05], [Jan 05].[Aug 05], [Jan 05].[Oct 05], [Jan 05].[Nov 05], [Jan
05].[Dec 05], [Jan 05].Period, [TS Actuals 001 Union].TS_DEPT_UID, [TS
Actuals 001 Union].[Row Type]
FROM [TS Actuals 002 Jan 05] AS [Jan 05]

UNION SELECT [Feb 05].Dept, [Feb 05].Resource, [Feb 05].Project,[Feb
05].Task, [Feb 05].[Pre 2005], [Feb 05].[Jan 05], [Feb 05].[Feb 05], [Feb
05].[Mar 05], [Feb 05].[Apr 05], [Feb 05].[May 05], [Feb 05].[Jun 05], [Feb
05].[Jul 05], [Feb 05].[Aug 05], [Feb 05].[Oct 05], [Feb 05].[Nov 05], [Feb
05].[Dec 05], [Feb 05].Period, [TS Actuals 001 Union].TS_DEPT_UID, [TS
Actuals 001 Union].[Row Type]
FROM [TS Actuals 002 Feb 05] AS [Feb 05]

UNION SELECT [Mar 05].Dept, [Mar 05].Resource, [Mar 05].Project, [Mar
05].Task, [Mar 05].[Pre 2005], [Mar 05].[Jan 05], [Mar 05].[Feb 05], [Mar
05].[Mar 05], [Mar 05].[Apr 05], [Mar 05].[May 05], [Mar 05].[Jun 05], [Mar
05].[Jul 05], [Mar 05].[Aug 05], [Mar 05].[Oct 05], [Mar 05].[Nov 05], [Mar
05].[Dec 05], [Mar 05].Period, [TS Actuals 001 Union].TS_DEPT_UID, [TS
Actuals 001 Union].[Row Type]
FROM [TS Actuals 002 Mar 05] AS [Mar 05];

UNION SELECT [Apr 05].Dept, [Apr 05].Resource, [Apr 05].Project, [Apr
05].Task, [Apr 05].[Pre 2005], [Apr 05].[Jan 05], [Apr 05].[Feb 05], [Apr
05].[Mar 05], [Apr 05].[Apr 05], [Apr 05].[May 05], [Apr 05].[Jun 05], [Apr
05].[Jul 05], [Apr 05].[Aug 05], [Apr 05].[Oct 05], [Apr 05].[Nov 05], [Apr
05].[Dec 05], [Apr 05].Period, [TS Actuals 001 Union].TS_DEPT_UID, [TS
Actuals 001 Union].[Row Type]
FROM [TS Actuals 002 Apr 05] AS [Apr 05];

I would like to know if there is a more efficient way to complete this
process.

Thanks for the help!!
Click to expand...
 
G

Guest

Van,

I had not tried Cross-tab queries, becuase I did not know what they were.....

Well the result is that they do exactly what I want in once simple step.
Thanks for the advice.


Van T. Dinh said:
Have you tried Cross-tab Queries?

Check Access Help on Cross-tab queries.

HTH
Van T. Dinh
MVP (Access)



anthonysjo said:
Hello experts!

I have a union query that I am running in access 2003 that is as follows:
-- get hours from 3 different locations and union together
SELECT TSC.CHG_UID, TSC.CHG_CHARGENUMBER AS CN, [CHG_NORMALHOURS]/60000 AS
[Reg Hours], [CHG_OVERTIMEHOURS]/60000 AS [OT Hours], TS.PRD_ID, EP.PRD_NAME
AS Period, MWR.RES_NAME AS Resource, TS.TS_DEPT_NAME AS Dept, TS.TS_DEPT_UID,
EPT.PT_PROJ_NAME AS Project, EPT.PT_TASK_NAME AS Task, "Project" AS [Row Type]
FROM (((dbo_EPK_TS_CHARGES AS TSC INNER JOIN dbo_EPK_TS_TIMESHEETS AS TS ON
TSC.TS_UID = TS.TS_UID) INNER JOIN dbo_EPK_PERIODS AS EP ON TS.PRD_ID =
EP.PRD_ID) INNER JOIN dbo_MSP_WEB_RESOURCES AS MWR ON TS.WRES_ID =
MWR.WRES_ID) INNER JOIN dbo_EPK_PROJ_TASKS AS EPT ON (TSC.WPROJ_ID =
EPT.PT_WPROJ_ID) AND (TSC.TASK_UID = EPT.PT_TASK_UID)
WHERE (((EP.CB_ID)=0));

UNION SELECT TSC.CHG_UID, TSC.CHG_CHARGENUMBER AS CN,
[CHG_NORMALHOURS]/60000 AS [Reg Hours], [CHG_OVERTIMEHOURS]/60000 AS [OT
Hours], TS.PRD_ID, EP.PRD_NAME AS Period, MWR.RES_NAME AS Resource,
TS.TS_DEPT_NAME AS Dept, TS.TS_DEPT_UID, NWI.NWI_NAME AS Project, Null AS
Task, "Non-Work Item" AS [Row Type]
FROM (((dbo_EPK_TS_CHARGES AS TSC INNER JOIN dbo_EPK_TS_TIMESHEETS AS TS ON
TSC.TS_UID = TS.TS_UID) INNER JOIN dbo_EPK_PERIODS AS EP ON TS.PRD_ID =
EP.PRD_ID) INNER JOIN dbo_MSP_WEB_RESOURCES AS MWR ON TS.WRES_ID =
MWR.WRES_ID) INNER JOIN dbo_EPK_NONWORK_ITEMS AS NWI ON TSC.NWI_ID =
NWI.NWI_ID
WHERE (((EP.CB_ID)=0));

UNION SELECT TSC.CHG_UID, TSC.CHG_CHARGENUMBER AS CN,
[CHG_NORMALHOURS]/60000 AS [Reg Hours], [CHG_OVERTIMEHOURS]/60000 AS [OT
Hours], TS.PRD_ID, EP.PRD_NAME AS Period, MWR.RES_NAME AS Resource,
TS.TS_DEPT_NAME AS Dept, TS.TS_DEPT_UID, EPP.PROJECT_NAME AS Project,
EPW.WORKITEM_NAME AS Task, "Portfolio Item" AS [Row Type]
FROM ((((dbo_EPK_TS_CHARGES AS TSC INNER JOIN dbo_EPK_TS_TIMESHEETS AS TS ON
TSC.TS_UID = TS.TS_UID) INNER JOIN dbo_EPK_PERIODS AS EP ON TS.PRD_ID =
EP.PRD_ID) INNER JOIN dbo_MSP_WEB_RESOURCES AS MWR ON TS.WRES_ID =
MWR.WRES_ID) INNER JOIN dbo_EPKP_PROJECTS AS EPP ON TSC.PROJECT_ID =
EPP.PROJECT_ID) LEFT JOIN dbo_EPKP_PI_WORKITEMS AS EPW ON (TSC.TASK_UID =
EPW.WORKITEM_ID) AND (TSC.PROJECT_ID = EPW.PROJECT_ID)
WHERE (((TS.PRD_ID) In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19))
AND ((EP.CB_ID)=0));

The above query will return the dept, resource, project, task, period, etc.
However, I need each period to display as a field/column instead of a new row
for each period. For example: The query above will generate a new row for
each instance that time is reported to a new period, even if the other data
(dept, resource, project, and task) is the same. What I would like is for
the periods to be displayed individually as columns, that way there are less
rows returned and data is in the format that I need.

The method that I have been using to move the hours into columns by period
looks like the following:
--Jan 05 Query (1 of 12 sub queries used to get hours into individual period
columns)
SELECT [TS Actuals 001 Union].Dept, [TS Actuals 001 Union].Resource, [TS
Actuals 001 Union].Project, [TS Actuals 001 Union].Task, 0 AS [Pre 2005],
Sum([TS Actuals 001 Union].[Reg Hours]) AS [Jan 05], 0 AS [Feb 05], 0 AS [Mar
05], 0 AS [Apr 05], 0 AS [May 05], 0 AS [Jun 05], 0 AS [Jul 05], 0 AS [Aug
05], 0 AS [Sep 05], 0 AS [Oct 05], 0 AS [Nov 05], 0 AS [Dec 05], [TS Actuals
001 Union].PRD_ID, [TS Actuals 001 Union].Period, [TS Actuals 001
Union].TS_DEPT_UID, [TS Actuals 001 Union].[Row Type]
FROM [TS Actuals 001 Union]
GROUP BY [TS Actuals 001 Union].Dept, [TS Actuals 001 Union].Resource, [TS
Actuals 001 Union].Project, [TS Actuals 001 Union].Task, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, [TS Actuals 001 Union].PRD_ID, [TS Actuals 001 Union].Period,
[TS Actuals 001 Union].TS_DEPT_UID, [TS Actuals 001 Union].[Row Type]
HAVING (((Sum([TS Actuals 001 Union].[Reg Hours]))<>0) AND (([TS Actuals 001
Union].PRD_ID)=2 Or ([TS Actuals 001 Union].PRD_ID)=3 Or ([TS Actuals 001
Union].PRD_ID)=4 Or ([TS Actuals 001 Union].PRD_ID)=5 Or ([TS Actuals 001
Union].PRD_ID)=6))
ORDER BY [TS Actuals 001 Union].Dept, [TS Actuals 001 Union].Resource, [TS
Actuals 001 Union].Project;

The problem with this query is I have to write a query for each period or
group of periods and then union them back together.
--Final UNION
SELECT [Pre 05].Dept, [Pre 05].Resource, [Pre 05].Project, [Pre 05].Task,
[Pre 05].[Pre 2005], [Pre 05].[Jan 05], [Pre 05].[Feb 05], [Pre 05].[Mar 05],
[Pre 05].[Apr 05], [Pre 05].[May 05], [Pre 05].[Jun 05], [Pre 05].[Jul 05],
[Pre 05].[Aug 05], [Pre 05].[Oct 05], [Pre 05].[Nov 05], [Pre 05].[Dec 05],
[Pre 05].Period, [TS Actuals 001 Union].TS_DEPT_UID, [TS Actuals 001
Union].[Row Type]
FROM [TS Actuals 002 Pre 2005] AS [Pre 05]

UNION SELECT [Jan 05].Dept, [Jan 05].Resource, [Jan 05].Project, [Jan
05].Task, [Jan 05].[Pre 2005], [Jan 05].[Jan 05], [Jan 05].[Feb 05], [Jan
05].[Mar 05], [Jan 05].[Apr 05], [Jan 05].[May 05], [Jan 05].[Jun 05], [Jan
05].[Jul 05], [Jan 05].[Aug 05], [Jan 05].[Oct 05], [Jan 05].[Nov 05], [Jan
05].[Dec 05], [Jan 05].Period, [TS Actuals 001 Union].TS_DEPT_UID, [TS
Actuals 001 Union].[Row Type]
FROM [TS Actuals 002 Jan 05] AS [Jan 05]

UNION SELECT [Feb 05].Dept, [Feb 05].Resource, [Feb 05].Project,[Feb
05].Task, [Feb 05].[Pre 2005], [Feb 05].[Jan 05], [Feb 05].[Feb 05], [Feb
05].[Mar 05], [Feb 05].[Apr 05], [Feb 05].[May 05], [Feb 05].[Jun 05], [Feb
05].[Jul 05], [Feb 05].[Aug 05], [Feb 05].[Oct 05], [Feb 05].[Nov 05], [Feb
05].[Dec 05], [Feb 05].Period, [TS Actuals 001 Union].TS_DEPT_UID, [TS
Actuals 001 Union].[Row Type]
FROM [TS Actuals 002 Feb 05] AS [Feb 05]

UNION SELECT [Mar 05].Dept, [Mar 05].Resource, [Mar 05].Project, [Mar
05].Task, [Mar 05].[Pre 2005], [Mar 05].[Jan 05], [Mar 05].[Feb 05], [Mar
05].[Mar 05], [Mar 05].[Apr 05], [Mar 05].[May 05], [Mar 05].[Jun 05], [Mar
05].[Jul 05], [Mar 05].[Aug 05], [Mar 05].[Oct 05], [Mar 05].[Nov 05], [Mar
05].[Dec 05], [Mar 05].Period, [TS Actuals 001 Union].TS_DEPT_UID, [TS
Actuals 001 Union].[Row Type]
FROM [TS Actuals 002 Mar 05] AS [Mar 05];

UNION SELECT [Apr 05].Dept, [Apr 05].Resource, [Apr 05].Project, [Apr
05].Task, [Apr 05].[Pre 2005], [Apr 05].[Jan 05], [Apr 05].[Feb 05], [Apr
05].[Mar 05], [Apr 05].[Apr 05], [Apr 05].[May 05], [Apr 05].[Jun 05], [Apr
05].[Jul 05], [Apr 05].[Aug 05], [Apr 05].[Oct 05], [Apr 05].[Nov 05], [Apr
05].[Dec 05], [Apr 05].Period, [TS Actuals 001 Union].TS_DEPT_UID, [TS
Actuals 001 Union].[Row Type]
FROM [TS Actuals 002 Apr 05] AS [Apr 05];

I would like to know if there is a more efficient way to complete this
process.

Thanks for the help!!
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

Query to extract data in a specified format 1
convert columns to rows 2
How do I convert some data in the row to columns? 4
Transposing Data in rows to columns (complex) 1
System Resource Exceeded/Query Too Complex 4
Using VLOOKUP to draw data from two columns 5
Sum, IF, Subtotal ??? 1
Lookup by reference to months? 1

Top