I have 2 tables with identical fields that I query from. I created a query to each table and then used a union query to stack the information. I then use that union query to sum up the information based on certain criteria. I then create a crosstab query from the summed query. This all works and give me the correct results. Hoever this is where it gets wierd. I tried to create a DAP from the cross tab and I get an error that says "Cannot add object "query name" because it contains no columns." I have colums set in the crosstab..heres the SQL:
TRANSFORM Sum([HandleTimeByJobTitleAndSkillsetTypeCombinedRanged].[HandleTimeHrs]) AS SumOfHandleTimeHrs
SELECT [HandleTimeByJobTitleAndSkillsetTypeCombinedRanged].[JobTitle]
FROM HandleTimeByJobTitleAndSkillsetTypeCombinedRanged
GROUP BY [HandleTimeByJobTitleAndSkillsetTypeCombinedRanged].[JobTitle]
PIVOT [HandleTimeByJobTitleAndSkillsetTypeCombinedRanged].[Skillset Type] In ("BMW","Credit","CU Always","Debit","DI Branch Services","DI Lending","DI Tier 1","Escalation","Nat City","Paylynx");
SO I checked all the queries that I have attached to this cross tab and in the field listing list on the DAP I noticed that I have column lists all the way up until I take the Union query and sum it up. Heres the SQL for that:
SELECT HandleTimeByJobTitleAndSkillsetTypeRangedUnion.JobTitle, HandleTimeByJobTitleAndSkillsetTypeRangedUnion.[Skillset Type], Sum(HandleTimeByJobTitleAndSkillsetTypeRangedUnion.CallsAns) AS CallsAns, Sum(HandleTimeByJobTitleAndSkillsetTypeRangedUnion.HandleTime) AS HandleTime, HandleTimeByJobTitleCombinedRanged.HandleTime AS TtlHandleTime, Sum(HandleTimeByJobTitleAndSkillsetTypeRangedUnion.HandleTimeHrs) AS HandleTimeHrs, [HandleTime]/[TtlHandleTime] AS PctHandleTime
FROM HandleTimeByJobTitleAndSkillsetTypeRangedUnion INNER JOIN HandleTimeByJobTitleCombinedRanged ON HandleTimeByJobTitleAndSkillsetTypeRangedUnion.JobTitle = HandleTimeByJobTitleCombinedRanged.JobTitle
GROUP BY HandleTimeByJobTitleAndSkillsetTypeRangedUnion.JobTitle, HandleTimeByJobTitleAndSkillsetTypeRangedUnion.[Skillset Type], HandleTimeByJobTitleCombinedRanged.HandleTime;
Now I dont have any idea why it says I dont have columns. Am I just an idiot and forgetting something here?