Union two crosstabs 1

[MDTekUser]

Is this possible? It doesn't seem to work for me. I get a syntax error. Yes, both queries have the same number of columns and with the same data types. Thanks..

 

[dhookom]

Have you set the Column Headings properties for both crosstabs?

Can you share any SQL with us?

What is the exact "a syntax error"?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MDTekUser]

Because the column headers are dynamic, I am not hard coding the column names.

I'm posting the query here, and hopefully you can make some sense of it. Right now I am creating a separate query for each crosstab, then creating a third query to union these together.

If I try to union these two directly I get an error. The error I get is "Syntax erro(Missing operator) in query expression 'd.User UNION ALL...'"

Some background about the query. I am creating a list of project codes as the row header and then totaling the number of hours worked per user in the column header. I'm using a second crosstab in order to get totals for just totals.

PARAMETERS [Forms]![frm_Admin_Reports]![cboTask_Order] Text ( 255 ), [Forms]![frm_Admin_Reports]![txtBegin_Date] DateTime, [Forms]![frm_Admin_Reports]![txtEnd_Date] DateTime;
TRANSFORM NZ(Sum(c.Hours),0) AS SumHours
SELECT e.ProjCode, Sum(c.Hours) AS [Total Hours]
FROM tbl_Task_Order AS b INNER JOIN ((tbl_QA_Rep AS d INNER JOIN tbl_Task AS a ON d.UserID=a.UserID) INNER JOIN (tbl_Project AS e INNER JOIN tbl_Task_Details AS c ON e.ProjectID=c.ProjectID) ON a.TaskID=c.TaskID) ON b.TaskOrderID=c.TaskOrderID
WHERE b.TaskOrderID=Forms!frm_Admin_Reports!cboTask_Order AND  (a.TaskDate Between [Forms]![frm_Admin_Reports]![txtBegin_Date] And  [Forms]![frm_Admin_Reports]![txtEnd_Date])
GROUP BY e.ProjCode
PIVOT d.User
UNION ALL
PARAMETERS [Forms]![frm_Admin_Reports]![cboTask_Order] Text ( 255 ), [Forms]![frm_Admin_Reports]![txtBegin_Date] DateTime, [Forms]![frm_Admin_Reports]![txtEnd_Date] DateTime;
TRANSFORM NZ(Sum(c.Hours),0) AS SumHours
SELECT b.TaskOrderID, Sum(c.Hours)
FROM tbl_Task_Order AS b INNER JOIN ((tbl_QA_Rep AS d INNER JOIN tbl_Task AS a ON d.UserID=a.UserID) INNER JOIN (tbl_Project AS e INNER JOIN tbl_Task_Details AS c ON e.ProjectID=c.ProjectID) ON a.TaskID=c.TaskID) ON b.TaskOrderID=c.TaskOrderID
WHERE b.TaskOrderID=Forms!frm_Admin_Reports!cboTask_Order And (a.TaskDate Between Forms!frm_Admin_Reports!txtBegin_Date And Forms!frm_Admin_Reports!txtEnd_Date)
GROUP BY b.TaskOrderID
PIVOT d.User;

 

[PHV]

I'd use two named crosstab queries for the union:
PARAMETERS [Forms]![frm_Admin_Reports]![cboTask_Order] Text ( 255 ), [Forms]![frm_Admin_Reports]![txtBegin_Date] DateTime, [Forms]![frm_Admin_Reports]![txtEnd_Date] DateTime;
SELECT * FROM my1stcrosstabquery
UNION ALL
SELECT * FROM my2ndcrosstabquery

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MDTekUser]

That's exactly what I'm doing now and it works. I wanted to see if I could do it in one query.

 

[dhookom]

The other option might be to union prior to crosstabbing.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MDTekUser]

Duane, that sounds like a good idea. The other option I'm strongly considering is to just write the totals in a report summary field. By the way, how did you end up becoming an Access MVP?

 

[dhookom]

The Microsoft MVP program is described at

http://mvp.support.microsoft.com/.

You basically dedicate a fair amount of time sharing your programming knowledge with others. Some MVP don't frequent on-line forums or news groups but do a lot of speaking and writing.

I am "lucky" enough to have been an Access MVP since 2001. You get some perks but probably not equivalent to the time spent helping others. Of course, I think all MVPs would be helping regardless of the recognition.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MDTekUser]

Thanks for the info...