Union query

[Raj]

I have used a UNION query to join a CrossTab query and a
slect to Total CrossTab. I need help with getting the
Total to be the last line of the UNION Query.
The SQL is below.
Thanks.



SELECT [Monthly Employee Cross Tab].ratm_contact,
[Monthly Employee Crosstab_2].[2003], [Monthly Employee
Cross Tab].[Jan-04], [Monthly Employee Cross Tab].[Feb-
04], [Monthly Employee Cross Tab].[Mar-04], [Monthly
Employee Cross Tab].[Apr-04], [Monthly Employee Cross
Tab].[May-04], [Monthly Employee Cross Tab].[Jun-04],
[Monthly Employee Cross Tab].[Jul-04], [Monthly Employee
Cross Tab].[Aug-04], [Monthly Employee Cross Tab].[Sep-
04], [Monthly Employee Crosstab_2].[<>] AS [To Be Resch],
[Monthly Employee Crosstab_2].[Total Of circuitid]
FROM [Monthly Employee Cross Tab] INNER JOIN [Monthly
Employee Crosstab_2] ON [Monthly Employee Cross
Tab].ratm_contact = [Monthly Employee
Crosstab_2].ratm_contact

UNION

SELECT Count([Monthly Employee Crosstab_3].ratm_contact)
AS CountOfratm_contact, Sum([Monthly Employee Crosstab_3].
[2003]) AS 2003, Sum([Monthly Employee Crosstab_3].[Jan-
04]) AS [Jan-4], Sum([Monthly Employee Crosstab_3].[Feb-
04]) AS [Feb-4], Sum([Monthly Employee Crosstab_3].[Mar-
04]) AS [Mar-4], Sum([Monthly Employee Crosstab_3].[Apr-
04]) AS [Apr-4], Sum([Monthly Employee Crosstab_3].[May-
04]) AS [May-4], Sum([Monthly Employee Crosstab_3].[Jun-
04]) AS [Jun-4], Sum([Monthly Employee Crosstab_3].[Jul-
04]) AS [Jul-4], Sum([Monthly Employee Crosstab_3].[Aug-
04]) AS [Aug-4], Sum([Monthly Employee Crosstab_3].[Sep-
04]) AS [Sep-4], Sum([Monthly Employee Crosstab_3].[To Be
Resch]) AS [SumOfTo Be Resch], Sum([Monthly Employee
Crosstab_3].[Total Of circuitid]) AS [SumOfTotal Of
circuitid]
FROM [Monthly Employee Crosstab_3];

 

[Duane Hookom]

Try change the UNION to UNION ALL

 

[Guest]

Duane,
Thank you..

-----Original Message-----
Try change the UNION to UNION ALL

--
Duane Hookom
MS Access MVP

I have used a UNION query to join a CrossTab query and a
slect to Total CrossTab. I need help with getting the
Total to be the last line of the UNION Query.
The SQL is below.
Thanks.



SELECT [Monthly Employee Cross Tab].ratm_contact,
[Monthly Employee Crosstab_2].[2003], [Monthly Employee
Cross Tab].[Jan-04], [Monthly Employee Cross Tab].[Feb-
04], [Monthly Employee Cross Tab].[Mar-04], [Monthly
Employee Cross Tab].[Apr-04], [Monthly Employee Cross
Tab].[May-04], [Monthly Employee Cross Tab].[Jun-04],
[Monthly Employee Cross Tab].[Jul-04], [Monthly Employee
Cross Tab].[Aug-04], [Monthly Employee Cross Tab].[Sep-
04], [Monthly Employee Crosstab_2].[<>] AS [To Be Resch],
[Monthly Employee Crosstab_2].[Total Of circuitid]
FROM [Monthly Employee Cross Tab] INNER JOIN [Monthly
Employee Crosstab_2] ON [Monthly Employee Cross
Tab].ratm_contact = [Monthly Employee
Crosstab_2].ratm_contact

UNION

SELECT Count([Monthly Employee Crosstab_3].ratm_contact)
AS CountOfratm_contact, Sum([Monthly Employee Crosstab_3].
[2003]) AS 2003, Sum([Monthly Employee Crosstab_3]. [Jan-
04]) AS [Jan-4], Sum([Monthly Employee Crosstab_3]. [Feb-
04]) AS [Feb-4], Sum([Monthly Employee Crosstab_3]. [Mar-
04]) AS [Mar-4], Sum([Monthly Employee Crosstab_3]. [Apr-
04]) AS [Apr-4], Sum([Monthly Employee Crosstab_3]. [May-
04]) AS [May-4], Sum([Monthly Employee Crosstab_3]. [Jun-
04]) AS [Jun-4], Sum([Monthly Employee Crosstab_3]. [Jul-
04]) AS [Jul-4], Sum([Monthly Employee Crosstab_3]. [Aug-
04]) AS [Aug-4], Sum([Monthly Employee Crosstab_3]. [Sep-
04]) AS [Sep-4], Sum([Monthly Employee Crosstab_3].[To Be
Resch]) AS [SumOfTo Be Resch], Sum([Monthly Employee
Crosstab_3].[Total Of circuitid]) AS [SumOfTotal Of
circuitid]
FROM [Monthly Employee Crosstab_3];

.