Union of two crosstabs?

G

Guest

I have tried to take the SQL generated by two crosstab queries and UNION them
together but I am getting Errors...

TRANSFORM Sum([Test-Apps].Applications) AS SumOfApplications
SELECT [Test-Apps].Type
FROM [Test-Apps]
GROUP BY [Test-Apps].Type
PIVOT [Test-Apps].Eligibility;

I want to Union with...
TRANSFORM Sum([Test-students].Students) AS SumOfStudents
SELECT [Test-students].Type
FROM [Test-students]
GROUP BY [Test-students].Type
PIVOT [Test-students].Eligibility;

How do I accomplish this?

Each returns 1 row, so the result should be 2rows, Each have the same field
names. I was able to do a make table on each, but I need this to run
seamlessly by executing a report that calls the UNION query.
 
G

Guest

"but I am getting Errors... " doesn't help much. Have you tried entering all
the possible column headings in the Column Headings property? What is the SQL
view of the union query?
 
G

Guest

*SQL view of the UNION query:
TRANSFORM Sum([Test-Apps].Applications) AS SumOfApplications
SELECT [Test-Apps].Type
FROM [Test-Apps]
GROUP BY [Test-Apps].Type
PIVOT [Test-Apps].Eligibility
UNION
TRANSFORM Sum([Test-students].Students) AS SumOfStudents
SELECT [Test-students].Type
FROM [Test-students]
GROUP BY [Test-students].Type
PIVOT [Test-students].Eligibility;
*end of SQL view

*Gets this error...
Syntax error (missing operator) in query expression '[Test-Apps].Eligibility
UNION
TRANSFORM Sum([Test-students].Students) AS SumOfStudents
SELECT [Test-students].Type
FROM [Test-students]
GROUP BY [Test-students].Type
PIVOT [Test-students].Eligibility.
*End of error text

Each of the crosstabs run seperately produces the exact data-row I am
wanting. Each produces results with 1 row and 4 fields (Same field names on
each). I am trying to make the results of this UNION query produce 2 rows
with 4 fields.
There are 2 different Type(s) and 3 different Eligibility(s) in each of the
initial tables (Test-Students & Test-Apps). The data points in field are
simple counts that require no further manipulation than accomplishing the
transpose rows to columns.
--
Regards,
John


Duane Hookom said:
"but I am getting Errors... " doesn't help much. Have you tried entering all
the possible column headings in the Column Headings property? What is the SQL
view of the union query?
--
Duane Hookom
Microsoft Access MVP


John Keith said:
I have tried to take the SQL generated by two crosstab queries and UNION them
together but I am getting Errors...

TRANSFORM Sum([Test-Apps].Applications) AS SumOfApplications
SELECT [Test-Apps].Type
FROM [Test-Apps]
GROUP BY [Test-Apps].Type
PIVOT [Test-Apps].Eligibility;

I want to Union with...
TRANSFORM Sum([Test-students].Students) AS SumOfStudents
SELECT [Test-students].Type
FROM [Test-students]
GROUP BY [Test-students].Type
PIVOT [Test-students].Eligibility;

How do I accomplish this?

Each returns 1 row, so the result should be 2rows, Each have the same field
names. I was able to do a make table on each, but I need this to run
seamlessly by executing a report that calls the UNION query.
Click to expand...
Click to expand...
 
G

Guest

You can't union two crosstabs in all in a single query. You would need to
save each crosstab and then create a third query to do the union.

--
Duane Hookom
Microsoft Access MVP


John Keith said:
*SQL view of the UNION query:
TRANSFORM Sum([Test-Apps].Applications) AS SumOfApplications
SELECT [Test-Apps].Type
FROM [Test-Apps]
GROUP BY [Test-Apps].Type
PIVOT [Test-Apps].Eligibility
UNION
TRANSFORM Sum([Test-students].Students) AS SumOfStudents
SELECT [Test-students].Type
FROM [Test-students]
GROUP BY [Test-students].Type
PIVOT [Test-students].Eligibility;
*end of SQL view

*Gets this error...
Syntax error (missing operator) in query expression '[Test-Apps].Eligibility
UNION
TRANSFORM Sum([Test-students].Students) AS SumOfStudents
SELECT [Test-students].Type
FROM [Test-students]
GROUP BY [Test-students].Type
PIVOT [Test-students].Eligibility.
*End of error text

Each of the crosstabs run seperately produces the exact data-row I am
wanting. Each produces results with 1 row and 4 fields (Same field names on
each). I am trying to make the results of this UNION query produce 2 rows
with 4 fields.
There are 2 different Type(s) and 3 different Eligibility(s) in each of the
initial tables (Test-Students & Test-Apps). The data points in field are
simple counts that require no further manipulation than accomplishing the
transpose rows to columns.
--
Regards,
John


Duane Hookom said:
"but I am getting Errors... " doesn't help much. Have you tried entering all
the possible column headings in the Column Headings property? What is the SQL
view of the union query?
--
Duane Hookom
Microsoft Access MVP


John Keith said:
I have tried to take the SQL generated by two crosstab queries and UNION them
together but I am getting Errors...

TRANSFORM Sum([Test-Apps].Applications) AS SumOfApplications
SELECT [Test-Apps].Type
FROM [Test-Apps]
GROUP BY [Test-Apps].Type
PIVOT [Test-Apps].Eligibility;

I want to Union with...
TRANSFORM Sum([Test-students].Students) AS SumOfStudents
SELECT [Test-students].Type
FROM [Test-students]
GROUP BY [Test-students].Type
PIVOT [Test-students].Eligibility;

How do I accomplish this?

Each returns 1 row, so the result should be 2rows, Each have the same field
names. I was able to do a make table on each, but I need this to run
seamlessly by executing a report that calls the UNION query.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks for the quick replies :)

The two crosstabs are saved as seperate queries. How do I reference the
crosstabs in the new 3rd query to UNION them together?

By "save", do you mean create a table from each crosstab result?...
Is there a way to delete table & create table (or just replace) from
crosstab all in a query, or do I have to use macro's to sequence the steps.


--
Regards,
John


Duane Hookom said:
You can't union two crosstabs in all in a single query. You would need to
save each crosstab and then create a third query to do the union.

--
Duane Hookom
Microsoft Access MVP


John Keith said:
*SQL view of the UNION query:
TRANSFORM Sum([Test-Apps].Applications) AS SumOfApplications
SELECT [Test-Apps].Type
FROM [Test-Apps]
GROUP BY [Test-Apps].Type
PIVOT [Test-Apps].Eligibility
UNION
TRANSFORM Sum([Test-students].Students) AS SumOfStudents
SELECT [Test-students].Type
FROM [Test-students]
GROUP BY [Test-students].Type
PIVOT [Test-students].Eligibility;
*end of SQL view

*Gets this error...
Syntax error (missing operator) in query expression '[Test-Apps].Eligibility
UNION
TRANSFORM Sum([Test-students].Students) AS SumOfStudents
SELECT [Test-students].Type
FROM [Test-students]
GROUP BY [Test-students].Type
PIVOT [Test-students].Eligibility.
*End of error text

Each of the crosstabs run seperately produces the exact data-row I am
wanting. Each produces results with 1 row and 4 fields (Same field names on
each). I am trying to make the results of this UNION query produce 2 rows
with 4 fields.
There are 2 different Type(s) and 3 different Eligibility(s) in each of the
initial tables (Test-Students & Test-Apps). The data points in field are
simple counts that require no further manipulation than accomplishing the
transpose rows to columns.
--
Regards,
John


Duane Hookom said:
"but I am getting Errors... " doesn't help much. Have you tried entering all
the possible column headings in the Column Headings property? What is the SQL
view of the union query?
--
Duane Hookom
Microsoft Access MVP


:

I have tried to take the SQL generated by two crosstab queries and UNION them
together but I am getting Errors...

TRANSFORM Sum([Test-Apps].Applications) AS SumOfApplications
SELECT [Test-Apps].Type
FROM [Test-Apps]
GROUP BY [Test-Apps].Type
PIVOT [Test-Apps].Eligibility;

I want to Union with...
TRANSFORM Sum([Test-students].Students) AS SumOfStudents
SELECT [Test-students].Type
FROM [Test-students]
GROUP BY [Test-students].Type
PIVOT [Test-students].Eligibility;

How do I accomplish this?

Each returns 1 row, so the result should be 2rows, Each have the same field
names. I was able to do a make table on each, but I need this to run
seamlessly by executing a report that calls the UNION query.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I think I hit on the correct SQL...

SELECT *
FROM [Test-Apps_Crosstab]
UNION
SELECT [Test-students_Crosstab].*
FROM [Test-students_Crosstab];

returns the result I was looking for.
Thanks for your help!
--
Regards,
John


Duane Hookom said:
You can't union two crosstabs in all in a single query. You would need to
save each crosstab and then create a third query to do the union.

--
Duane Hookom
Microsoft Access MVP


John Keith said:
*SQL view of the UNION query:
TRANSFORM Sum([Test-Apps].Applications) AS SumOfApplications
SELECT [Test-Apps].Type
FROM [Test-Apps]
GROUP BY [Test-Apps].Type
PIVOT [Test-Apps].Eligibility
UNION
TRANSFORM Sum([Test-students].Students) AS SumOfStudents
SELECT [Test-students].Type
FROM [Test-students]
GROUP BY [Test-students].Type
PIVOT [Test-students].Eligibility;
*end of SQL view

*Gets this error...
Syntax error (missing operator) in query expression '[Test-Apps].Eligibility
UNION
TRANSFORM Sum([Test-students].Students) AS SumOfStudents
SELECT [Test-students].Type
FROM [Test-students]
GROUP BY [Test-students].Type
PIVOT [Test-students].Eligibility.
*End of error text

Each of the crosstabs run seperately produces the exact data-row I am
wanting. Each produces results with 1 row and 4 fields (Same field names on
each). I am trying to make the results of this UNION query produce 2 rows
with 4 fields.
There are 2 different Type(s) and 3 different Eligibility(s) in each of the
initial tables (Test-Students & Test-Apps). The data points in field are
simple counts that require no further manipulation than accomplishing the
transpose rows to columns.
--
Regards,
John


Duane Hookom said:
"but I am getting Errors... " doesn't help much. Have you tried entering all
the possible column headings in the Column Headings property? What is the SQL
view of the union query?
--
Duane Hookom
Microsoft Access MVP


:

I have tried to take the SQL generated by two crosstab queries and UNION them
together but I am getting Errors...

TRANSFORM Sum([Test-Apps].Applications) AS SumOfApplications
SELECT [Test-Apps].Type
FROM [Test-Apps]
GROUP BY [Test-Apps].Type
PIVOT [Test-Apps].Eligibility;

I want to Union with...
TRANSFORM Sum([Test-students].Students) AS SumOfStudents
SELECT [Test-students].Type
FROM [Test-students]
GROUP BY [Test-students].Type
PIVOT [Test-students].Eligibility;

How do I accomplish this?

Each returns 1 row, so the result should be 2rows, Each have the same field
names. I was able to do a make table on each, but I need this to run
seamlessly by executing a report that calls the UNION query.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Union Query with two crosstabs 2
How to Union Crosstabs... 1
Union Queries 1
Date Limit in Crosstab from Union query 2
UNION and Crosstab query 5
SUM in a UNION query 2
Combining Crosstab Queries 1
joining crosstab queries 1

Top