How to Point to Dynamic Column Headers in Crosstab Query

[hwalker]

Hello all. Long time reader, first time poster :)

I'm creating a repot that shows "the last two weeks of data the next 6 weeks of data, week over week".

So, I have a calculated field called "Week number" as the column header in a crosstab and then in the criteria for that field I put:

DateDiff("ww",# 12/22/2007#,Now(),0,# 12/22/2007#)-2
DateDiff("ww",# 12/22/2007#,Now(),0,# 12/22/2007#)-1
DateDiff("ww",# 12/22/2007#,Now(),0,# 12/22/2007#)
DateDiff("ww",# 12/22/2007#,Now(),0,# 12/22/2007#)+1
DateDiff("ww",# 12/22/2007#,Now(),0,# 12/22/2007#)+2
DateDiff("ww",# 12/22/2007#,Now(),0,# 12/22/2007#)+3
DateDiff("ww",# 12/22/2007#,Now(),0,# 12/22/2007#)+4
DateDiff("ww",# 12/22/2007#,Now(),0,# 12/22/2007#)+5

(the financial year starts on 12/22/07)

So it shows up perfectly. Here's where it gets hairy.

I also have another crosstab query that is exactly the same, but for another set of data (Lets call them Dataset A and Dataset B).

THEN, I have a third query that uses a junction table to pull the two crosstabs together so that my final result has columsn that look like this:

Name, , Etc.....Week1A, Week 1B, Week 2A, Week 2B, etc

This whole process worked fine for showing all 52 weeks. When I throw the criteria expressions on the column header fields in the crosstabs it creates a situation where the Column header names are different every week. This poses a problem because my joins on the juntion table query break when the column header names change. See my dilemma? This will also pose a problem when I go to make a report, because I wont know the names of the columns.

Any suggestions?

Thanks,
Henry

[Stewart Ross]

...This poses a problem because my joins on the juntion table query break when the column header names change. See my dilemma? This will also pose a problem when I go to make a report, because I wont know the names of the columns.

Hi. Could you post the SQL for the two crosstab queries, and your join query please? I would suggest that the best way forward is to revise the query structure, joining Select queries then crosstabbing. Your current SQL will give some indication whether this is feasible or not.

As crosstabs create pivoted field names dynamically joining crosstabs together on those pivoted fields is not recommended. It is possible to assign static headers to a crosstab as a simple list of values, but this would not work in your case as the set of weeks roll over, and in any event with (from what you mention) up to 52 pivoted fields the list would be way too long.

-Stewart

[hwalker]

The First Crosstab is:

TRANSFORM Sum(MASTER_TABLE.ACT) AS SumOfACT
SELECT TAB_2.OBS_HIERARCHY, MASTER_TABLE.PROJECT_ID, MASTER_TABLE.PROJECT_NAME, TAB_2.RESOURCE_ID, TAB_2.FULL_NAME, MASTER_TABLE.PRIMARY_ROLE, TAB_2.RESOURCE_MANAGER, TAB_2.EMAIL_ADDRESS, TAB_2.EMPLOYMENT_TYPE
FROM ((MASTER_TABLE LEFT JOIN ASE_RESOURCE_EXCLUDE_TABLE ON MASTER_TABLE.RESOURCE_UNIQUE_NAME = ASE_RESOURCE_EXCLUDE_TABLE.NBKID) RIGHT JOIN TAB_2 ON MASTER_TABLE.RESOURCE_UNIQUE_NAME = TAB_2.RESOURCE_ID) LEFT JOIN CLARITY_WEEKS_TABLE ON MASTER_TABLE.SLICE_DATE = CLARITY_WEEKS_TABLE.START_DATE
WHERE (((TAB_2.OBS_HIERARCHY)=[Forms]![Main_Form]![Combo19]) AND ((ASE_RESOURCE_EXCLUDE_TABLE.Name) Is Null))
GROUP BY ASE_RESOURCE_EXCLUDE_TABLE.Name, TAB_2.OBS_HIERARCHY, MASTER_TABLE.PROJECT_ID, MASTER_TABLE.PROJECT_NAME, TAB_2.RESOURCE_ID, TAB_2.FULL_NAME, MASTER_TABLE.PRIMARY_ROLE, TAB_2.RESOURCE_MANAGER, TAB_2.EMAIL_ADDRESS, TAB_2.EMPLOYMENT_TYPE
ORDER BY MASTER_TABLE.PROJECT_ID, TAB_2.FULL_NAME
PIVOT CLARITY_WEEKS_TABLE.Week_Number In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52);

The seconod Crosstab is :

TRANSFORM Sum(MASTER_TABLE.ALLOC) AS SumOfALLOC
SELECT TAB_2.OBS_HIERARCHY, MASTER_TABLE.PROJECT_ID, MASTER_TABLE.PROJECT_NAME, TAB_2.RESOURCE_ID, TAB_2.FULL_NAME, MASTER_TABLE.PRIMARY_ROLE, TAB_2.RESOURCE_MANAGER, TAB_2.EMAIL_ADDRESS, TAB_2.EMPLOYMENT_TYPE
FROM ((MASTER_TABLE LEFT JOIN ASE_RESOURCE_EXCLUDE_TABLE ON MASTER_TABLE.RESOURCE_UNIQUE_NAME = ASE_RESOURCE_EXCLUDE_TABLE.NBKID) RIGHT JOIN TAB_2 ON MASTER_TABLE.RESOURCE_UNIQUE_NAME = TAB_2.RESOURCE_ID) LEFT JOIN CLARITY_WEEKS_TABLE ON MASTER_TABLE.SLICE_DATE = CLARITY_WEEKS_TABLE.START_DATE
WHERE (((TAB_2.OBS_HIERARCHY)=[Forms]![Main_Form]![Combo19]) AND ((ASE_RESOURCE_EXCLUDE_TABLE.Name) Is Null))
GROUP BY ASE_RESOURCE_EXCLUDE_TABLE.Name, TAB_2.OBS_HIERARCHY, MASTER_TABLE.PROJECT_ID, MASTER_TABLE.PROJECT_NAME, TAB_2.RESOURCE_ID, TAB_2.FULL_NAME, MASTER_TABLE.PRIMARY_ROLE, TAB_2.RESOURCE_MANAGER, TAB_2.EMAIL_ADDRESS, TAB_2.EMPLOYMENT_TYPE
ORDER BY MASTER_TABLE.PROJECT_ID, TAB_2.FULL_NAME
PIVOT CLARITY_WEEKS_TABLE.Week_Number In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52);

The Union/Junction Query is:

SELECT [PROJECT_ID],[RESOURCE_ID],[PROJECT_NAME],[FULL_NAME],[OBS_HIERARCHY],[PRIMARY_ROLE]
FROM [WEEKLY_PAW_Crosstab_ACT]

UNION SELECT [PROJECT_ID],[RESOURCE_ID],[PROJECT_NAME],[FULL_NAME],[OBS_HIERARCHY],[PRIMARY_ROLE]
FROM [WEEKLY_PAW_Crosstab_ALLOC];

And the Query that ties it all together (with all 52 weeks) is:

SELECT WEEKLY_PAW_JUNCTION_QUERY.OBS_HIERARCHY AS Hierarchy, WEEKLY_PAW_JUNCTION_QUERY.PROJECT_ID AS [Clarity ID], WEEKLY_PAW_JUNCTION_QUERY.RESOURCE_ID AS NBKID, WEEKLY_PAW_JUNCTION_QUERY.PROJECT_NAME AS [Project Name], WEEKLY_PAW_JUNCTION_QUERY.FULL_NAME AS [Resource Name], WEEKLY_PAW_JUNCTION_QUERY.PRIMARY_ROLE AS Role, WEEKLY_PAW_Crosstab_ALLOC.[1] AS [Allocated Week 1], WEEKLY_PAW_Crosstab_ACT.[1] AS [Actual Week 1], WEEKLY_PAW_Crosstab_ALLOC.[2] AS [Allocated Week 2], WEEKLY_PAW_Crosstab_ACT.[2] AS [Actual Week 2], WEEKLY_PAW_Crosstab_ALLOC.[3] AS [Allocated Week 3], WEEKLY_PAW_Crosstab_ACT.[3] AS [Actual Week 3], WEEKLY_PAW_Crosstab_ALLOC.[4] AS [Allocated Week 4], WEEKLY_PAW_Crosstab_ACT.[4] AS [Actual Week 4], WEEKLY_PAW_Crosstab_ALLOC.[5] AS [Allocated Week 5], WEEKLY_PAW_Crosstab_ACT.[5] AS [Actual Week 5], WEEKLY_PAW_Crosstab_ALLOC.[6] AS [Allocated Week 6], WEEKLY_PAW_Crosstab_ACT.[6] AS [Actual Week 6], WEEKLY_PAW_Crosstab_ALLOC.[7] AS [Allocated Week 7], WEEKLY_PAW_Crosstab_ACT.[7] AS [Actual Week 7], WEEKLY_PAW_Crosstab_ALLOC.[8] AS [Allocated Week 8], WEEKLY_PAW_Crosstab_ACT.[8] AS [Actual Week 8], WEEKLY_PAW_Crosstab_ALLOC.[9] AS [Allocated Week 9], WEEKLY_PAW_Crosstab_ACT.[9] AS [Actual Week 9], WEEKLY_PAW_Crosstab_ALLOC.[10] AS [Allocated Week 10], WEEKLY_PAW_Crosstab_ACT.[10] AS [Actual Week 10], WEEKLY_PAW_Crosstab_ALLOC.[11] AS [Allocated Week 11], WEEKLY_PAW_Crosstab_ACT.[11] AS [Actual Week 11], WEEKLY_PAW_Crosstab_ALLOC.[12] AS [Allocated Week 12], WEEKLY_PAW_Crosstab_ACT.[12] AS [Actual Week 12], WEEKLY_PAW_Crosstab_ALLOC.[13] AS [Allocated Week 13], WEEKLY_PAW_Crosstab_ACT.[13] AS [Actual Week 13], WEEKLY_PAW_Crosstab_ALLOC.[14] AS [Allocated Week 14], WEEKLY_PAW_Crosstab_ACT.[14] AS [Actual Week 14], WEEKLY_PAW_Crosstab_ALLOC.[15] AS [Allocated Week 15], WEEKLY_PAW_Crosstab_ACT.[15] AS [Actual Week 15], WEEKLY_PAW_Crosstab_ALLOC.[16] AS [Allocated Week 16], WEEKLY_PAW_Crosstab_ACT.[16] AS [Actual Week 16], WEEKLY_PAW_Crosstab_ALLOC.[17] AS [Allocated Week 17], WEEKLY_PAW_Crosstab_ACT.[17] AS [Actual Week 17], WEEKLY_PAW_Crosstab_ALLOC.[18] AS [Allocated Week 18], WEEKLY_PAW_Crosstab_ACT.[18] AS [Actual Week 18], WEEKLY_PAW_Crosstab_ALLOC.[19] AS [Allocated Week 19], WEEKLY_PAW_Crosstab_ACT.[19] AS [Actual Week 19], WEEKLY_PAW_Crosstab_ALLOC.[20] AS [Allocated Week 20], WEEKLY_PAW_Crosstab_ACT.[20] AS [Actual Week 20], WEEKLY_PAW_Crosstab_ALLOC.[21] AS [Allocated Week 21], WEEKLY_PAW_Crosstab_ACT.[21] AS [Actual Week 21], WEEKLY_PAW_Crosstab_ALLOC.[22] AS [Allocated Week 22], WEEKLY_PAW_Crosstab_ACT.[22] AS [Actual Week 22], WEEKLY_PAW_Crosstab_ALLOC.[23] AS [Allocated Week 23], WEEKLY_PAW_Crosstab_ACT.[23] AS [Actual Week 23], WEEKLY_PAW_Crosstab_ALLOC.[24] AS [Allocated Week 24], WEEKLY_PAW_Crosstab_ACT.[24] AS [Actual Week 24], WEEKLY_PAW_Crosstab_ALLOC.[25] AS [Allocated Week 25], WEEKLY_PAW_Crosstab_ACT.[25] AS [Actual Week 25], WEEKLY_PAW_Crosstab_ALLOC.[26] AS [Allocated Week 26], WEEKLY_PAW_Crosstab_ACT.[26] AS [Actual Week 26], WEEKLY_PAW_Crosstab_ALLOC.[27] AS [Allocated Week 27], WEEKLY_PAW_Crosstab_ACT.[27] AS [Actual Week 27], WEEKLY_PAW_Crosstab_ALLOC.[28] AS [Allocated Week 28], WEEKLY_PAW_Crosstab_ACT.[28] AS [Actual Week 28], WEEKLY_PAW_Crosstab_ALLOC.[29] AS [Allocated Week 29], WEEKLY_PAW_Crosstab_ACT.[29] AS [Actual Week 29], WEEKLY_PAW_Crosstab_ALLOC.[30] AS [Allocated Week 30], WEEKLY_PAW_Crosstab_ACT.[30] AS [Actual Week 30], WEEKLY_PAW_Crosstab_ALLOC.[31] AS [Allocated Week 31], WEEKLY_PAW_Crosstab_ACT.[31] AS [Actual Week 31], WEEKLY_PAW_Crosstab_ALLOC.[32] AS [Allocated Week 32], WEEKLY_PAW_Crosstab_ACT.[32] AS [Actual Week 32], WEEKLY_PAW_Crosstab_ALLOC.[33] AS [Allocated Week 33], WEEKLY_PAW_Crosstab_ACT.[33] AS [Actual Week 33], WEEKLY_PAW_Crosstab_ALLOC.[34] AS [Allocated Week 34], WEEKLY_PAW_Crosstab_ACT.[34] AS [Actual Week 34], WEEKLY_PAW_Crosstab_ALLOC.[35] AS [Allocated Week 35], WEEKLY_PAW_Crosstab_ACT.[35] AS [Actual Week 35], WEEKLY_PAW_Crosstab_ALLOC.[36] AS [Allocated Week 36], WEEKLY_PAW_Crosstab_ACT.[36] AS [Actual Week 36], WEEKLY_PAW_Crosstab_ALLOC.[37] AS [Allocated Week 37], WEEKLY_PAW_Crosstab_ACT.[37] AS [Actual Week 37], WEEKLY_PAW_Crosstab_ALLOC.[38] AS [Allocated Week 38], WEEKLY_PAW_Crosstab_ACT.[38] AS [Actual Week 38], WEEKLY_PAW_Crosstab_ALLOC.[39] AS [Allocated Week 39], WEEKLY_PAW_Crosstab_ACT.[39] AS [Actual Week 39], WEEKLY_PAW_Crosstab_ALLOC.[40] AS [Allocated Week 40], WEEKLY_PAW_Crosstab_ACT.[40] AS [Actual Week 40], WEEKLY_PAW_Crosstab_ALLOC.[41] AS [Allocated Week 41], WEEKLY_PAW_Crosstab_ACT.[41] AS [Actual Week 41], WEEKLY_PAW_Crosstab_ALLOC.[42] AS [Allocated Week 42], WEEKLY_PAW_Crosstab_ACT.[42] AS [Actual Week 42], WEEKLY_PAW_Crosstab_ALLOC.[43] AS [Allocated Week 43], WEEKLY_PAW_Crosstab_ACT.[43] AS [Actual Week 43], WEEKLY_PAW_Crosstab_ALLOC.[44] AS [Allocated Week 44], WEEKLY_PAW_Crosstab_ACT.[44] AS [Actual Week 44], WEEKLY_PAW_Crosstab_ALLOC.[45] AS [Allocated Week 45], WEEKLY_PAW_Crosstab_ACT.[45] AS [Actual Week 45], WEEKLY_PAW_Crosstab_ALLOC.[46] AS [Allocated Week 46], WEEKLY_PAW_Crosstab_ACT.[46] AS [Actual Week 46], WEEKLY_PAW_Crosstab_ALLOC.[47] AS [Allocated Week 47], WEEKLY_PAW_Crosstab_ACT.[47] AS [Actual Week 47], WEEKLY_PAW_Crosstab_ALLOC.[48] AS [Allocated Week 48], WEEKLY_PAW_Crosstab_ACT.[48] AS [Actual Week 48], WEEKLY_PAW_Crosstab_ALLOC.[49] AS [Allocated Week 49], WEEKLY_PAW_Crosstab_ACT.[49] AS [Actual Week 49], WEEKLY_PAW_Crosstab_ALLOC.[50] AS [Allocated Week 50], WEEKLY_PAW_Crosstab_ACT.[50] AS [Actual Week 50], WEEKLY_PAW_Crosstab_ALLOC.[51] AS [Allocated Week 51], WEEKLY_PAW_Crosstab_ACT.[51] AS [Actual Week 51], WEEKLY_PAW_Crosstab_ALLOC.[52] AS [Allocated Week 52], WEEKLY_PAW_Crosstab_ACT.[52] AS [Actual Week 52]
FROM (WEEKLY_PAW_Crosstab_ALLOC RIGHT JOIN WEEKLY_PAW_JUNCTION_QUERY ON (WEEKLY_PAW_Crosstab_ALLOC.PROJECT_ID = WEEKLY_PAW_JUNCTION_QUERY.PROJECT_ID) AND (WEEKLY_PAW_Crosstab_ALLOC.RESOURCE_ID = WEEKLY_PAW_JUNCTION_QUERY.RESOURCE_ID)) LEFT JOIN WEEKLY_PAW_Crosstab_ACT ON (WEEKLY_PAW_JUNCTION_QUERY.PROJECT_ID = WEEKLY_PAW_Crosstab_ACT.PROJECT_ID) AND (WEEKLY_PAW_JUNCTION_QUERY.RESOURCE_ID = WEEKLY_PAW_Crosstab_ACT.RESOURCE_ID);

What I want to show is the previous two weeks, the current week and the next 6 weeks on a report. I'm currently working on a way to change the control source for the textfields in a report s follows:

Private Sub Command10_Click()


Reports![WEEKLY_PAW_CROSSTAB_FINAL]!Text106.ControlSource = "Allocated Week " & FinancialWeek(Now)
Reports![WEEKLY_PAW_CROSSTAB_FINAL]!Text63.ControlSource = "Actual Week " & FinancialWeek(Now)

Reports![WEEKLY_PAW_CROSSTAB_FINAL]!Text65.ControlSource = "Allocated Week " & FinancialWeek(Now)
Reports![WEEKLY_PAW_CROSSTAB_FINAL]!Text67.ControlSource = "Actual Week " & FinancialWeek(Now)

Reports![WEEKLY_PAW_CROSSTAB_FINAL]!Text82.ControlSource = "Allocated Week " & FinancialWeek(Now)
Reports![WEEKLY_PAW_CROSSTAB_FINAL]!Text83.ControlSource = "Actual Week " & FinancialWeek(Now)

Reports![WEEKLY_PAW_CROSSTAB_FINAL]!Text84.ControlSource = "Allocated Week " & FinancialWeek(Now)
Reports![WEEKLY_PAW_CROSSTAB_FINAL]!Text85.ControlSource = "Actual Week " & FinancialWeek(Now)

Reports![WEEKLY_PAW_CROSSTAB_FINAL]!Text90.ControlSource = "Allocated Week " & FinancialWeek(Now)
Reports![WEEKLY_PAW_CROSSTAB_FINAL]!Text91.ControlSource = "Actual Week " & FinancialWeek(Now)

Reports![WEEKLY_PAW_CROSSTAB_FINAL]!Text92.ControlSource = "Allocated Week " & FinancialWeek(Now)
Reports![WEEKLY_PAW_CROSSTAB_FINAL]!Text93.ControlSource = "Actual Week " & FinancialWeek(Now)

Reports![WEEKLY_PAW_CROSSTAB_FINAL]!Text98.ControlSource = "Allocated Week " & FinancialWeek(Now)
Reports![WEEKLY_PAW_CROSSTAB_FINAL]!Text99.ControlSource = "Actual Week " & FinancialWeek(Now)

Reports![WEEKLY_PAW_CROSSTAB_FINAL]!Text100.ControlSource = "Allocated Week " & FinancialWeek(Now)
Reports![WEEKLY_PAW_CROSSTAB_FINAL]!Text101.ControlSource = "Actual Week " & FinancialWeek(Now)

Reports![WEEKLY_PAW_CROSSTAB_FINAL]!Text104.ControlSource = "Allocated Week " & FinancialWeek(Now)
Reports![WEEKLY_PAW_CROSSTAB_FINAL]!Text105.ControlSource = "Actual Week " & FinancialWeek(Now)
DoCmd.OpenReport "WEEKLY_PAW_CROSSTAB_FINAL", acViewPreview
    
End Sub

But I cant seem to get it to work....

BTW, FinancialWeek returns a number, which is the financial week# for that date.

My reasoning for tieing the two crosstabs together is because I need a column for the ACT data and a column for the ALLOC data on each row..like this, for example:

Name|employeeID |ACT Week 1|ALLOC Week 1|ACT Week 2|ALLOC Week 2

Any thoughts would be greatly appreciated!

Thanks,
Henry