UNION Query issues with crosstab

[mindbender]

I have 2 queries, 1 that shows the data, and the other that sums the data. I am trying to Union them so I can get the data and then basically a subtotal of all the days of the month by rep. As this report will be run daily the number of columns (which is the date) will expand and contract.



I can't seem to get the subtotal query to add to the data query.

Here is my data query

TRANSFORM Nz(Count([Class]))+0 AS Expr1
SELECT IIf([Employees] In ('CA047767','CA065354','CA047763','CA048935','CA065788','CA055115','CA055757'),'CND-ISS',IIf([Employees] In ('CA063571','CA069760','CA071053','CA069610','CA050708','CA068210','CA069997','CA065331'),'XCL-TELESALES','UNKNOWN')) AS Industry, LOGAN_ACTYS.[Created By Name], IIf([Class] In ("Cold Call","Closing Call","Exec Value Proposition / Call","Scheduled Sales Call"),"Customer Contact",IIf([Class] In ("Opportunity Plan","Networking / Industry Event"),"Business Development",[LOGAN_ACTYS].[Class])) AS Type, Nz(Count([Class]))+0 AS Total
FROM LOGAN_ACTYS
GROUP BY IIf([Employees] In ('CA047767','CA065354','CA047763','CA048935','CA065788','CA055115','CA055757'),'CND-ISS',IIf([Employees] In ('CA063571','CA069760','CA071053','CA069610','CA050708','CA068210','CA069997','CA065331'),'XCL-TELESALES','UNKNOWN')), LOGAN_ACTYS.[Created By Name], IIf([Class] In ("Cold Call","Closing Call","Exec Value Proposition / Call","Scheduled Sales Call"),"Customer Contact",IIf([Class] In ("Opportunity Plan","Networking / Industry Event"),"Business Development",[LOGAN_ACTYS].[Class]))
PIVOT DateValue([Created]);

and here is the summed query

TRANSFORM Nz(Count([Class]))+0 AS Expr1
SELECT LOGAN_ACTYS.[Created By Name]
FROM LOGAN_ACTYS
GROUP BY LOGAN_ACTYS.[Created By Name]
PIVOT DateValue([Created]);

I added a screen shot of where I am trying to go as well. Any help would be appreciated.

[ranman256]

you can always run the crosstab,
pop it over to excel to do the sub totaling

[mindbender]

Yeah I would prefer to get it all out of access if I could so I can eliminate the added touch points

[aytee111]

It is all to do with the sort order. In the first column for the summed query, concatenate industry name and "TOTAL", that will sort the total line to the bottom.

[mindbender]

It is all to do with the sort order. In the first column for the summed query, concatenate industry name and "TOTAL", that will sort the total line to the bottom.

ok I updated the summed query to look like this

TRANSFORM Nz(Count([Class]))+0 AS Expr1
SELECT [Created By Name] & " " & "TOTAL" AS Expr2
FROM LOGAN_ACTYS
GROUP BY [Created By Name] & " " & "TOTAL"
PIVOT DateValue([Created]);

I am just not sure how to join them together.

[aytee111]

To join them use "UNION". Both queries have to have exactly the same number of fields.

Syntax:
SELECT * FROM crosstab_query
UNION SELECT * FROM summed_query
ORDER BY industry;

[June7]

Never seen a UNION of CROSSTAB queries but aytee's suggestion certainly looks doable.

Building a stable report to run perpetually based on CROSSTAB is not easy, especially when pivoting on date values because of the dynamic nature of field names. Review http://allenbrowne.com/ser-67.html#ColHead

[aytee111]

BTW, your totals query does not need to be a crosstab, you can bring in the crosstab query and do a totals query.

[CJ_London]

you might also find it better if you declare the crosstab heading

change

...
PIVOT DateValue([Created]);

to

...PIVOT DateValue([Created]) IN (1,2,3);

it might also be an idea to not use a straight date i.e. 04/07/2017. Use something like "D" & format(created,"yymmdd")

as a column name it breaks all the rules about not starting with a number and including non alphanumeric characters

You can always modify the labels in your form/report

[mindbender]

I don't know how you guys know all this stuff but all of this information was awesome. It is now working as expected.

Thank you very much