Union query with two crosstab querys

[ineedahelp]

I am trying to produce a report using two crosstab queries. Each query works well on its own. When combined I get an error message "syntax error in query expression Format([DatePerformed],"mm-yyyy")

I think I might have too many characters. Any help? Thanks!

Here is my SQL...

TRANSFORM Sum(TaskPerformed.FeeAmount) AS SumOfFeeAmount
SELECT TaskPerformed.Description, ProjectMember.MemberID, Sum(TaskPerformed.FeeAmount) AS YTDTotal
FROM ProjectMember LEFT JOIN TaskPerformed ON ProjectMember.MemberID = TaskPerformed.MemberID
WHERE (((TaskPerformed.DatePerformed) Between #1/1/2009# And #12/31/2009#) AND ((TaskPerformed.Description)="Software Maintenance")) OR (((TaskPerformed.Description)="Software License"))
GROUP BY TaskPerformed.Description, ProjectMember.MemberID
ORDER BY TaskPerformed.Description
PIVOT Format([DatePerformed],"mm-yyyy")

UNION
TRANSFORM IIf([Description]="Consulting Services",Sum([ProjectMember].[Rate]*[TaskPerformed].[HoursWorked]),Sum([FeeAmount])) AS TotalFee
SELECT [LastName] & ", " & [FirstName] AS FullName, TaskPerformed.Description, ProjectMember.MemberID, Sum([TotalFee]) AS YTDTotal
FROM ProjectMember LEFT JOIN TaskPerformed ON ProjectMember.MemberID = TaskPerformed.MemberID
WHERE (((TaskPerformed.Description) In ("Travel Expense","Consulting Services")) AND ((TaskPerformed.DatePerformed) Between #1/1/2009# And #12/31/2009#))
GROUP BY [LastName] & ", " & [FirstName], TaskPerformed.Description, ProjectMember.MemberID
ORDER BY [LastName] & ", " & [FirstName], TaskPerformed.Description
PIVOT Format([DatePerformed],"mm-yyyy");

 

[dhookom]

You can't create a union query with TRANSFORM in it. You can create two separate crosstab queries and then combine them in a union query like:

SELECT FullName, Description, MemberID, YTDTotal, ....
FROM qxtbOne
UNION ALL
SELECT FullName, Description, MemberID, YTDTotal, ....
FROM qxtbTwo;

Duane
Hook'D on Access
MS Access MVP

 

[ineedahelp]

Thank you for your suggestion. I have created a new query and it works for fields FullName, Description, MemberID, YTDTotal. Both crosstab queries calculates totals for each month. Do you know how I can "present" them in each SELECT statement? The query output shows them as 01-2009, 02-2009, 03-2009 and so on. These are actual field names which is part of my problem. I am not sure what to call them in my new UNION query. Thank you!

 

[ineedahelp]

I think I figured it out...I just had to change my heading to text "values" instead of 01-2009..... hopefully this will work and thanks for your help again!

 

[dhookom]

I think I have an FAQ in either this forum or queries on creating a monthly crosstab with "static" type column headings.

Duane
Hook'D on Access
MS Access MVP

 

[ineedahelp]

I seached for your FAQ on static column headings and found one

http://www.invisibleinc.com/divFiles.cfm?divDivID=4...but

I received an error message saying that "the page has experienced an unexpected error". If you can refer me to another link I would appreciate it...thanks!

 

[dhookom]

You looked way too far away when the FAQ is here faq703-5466.

Duane
Hook'D on Access
MS Access MVP