UNION and Crosstab query

E

elena

Hi, All
I have 2 crosstab queries, which output the same result, the difference
between them is tables (identical tables with the same name fields).
crosstab 1:
TRANSFORM Sum(LookUp1.fld3) AS SumOffld3
SELECT LookUp1.LongName
FROM LookUp1
GROUP BY LookUp1.LongName
PIVOT Format([fld2],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

crosstab 2:
TRANSFORM Sum(LookUp2.fld3) AS SumOffld3
SELECT LookUp2.LongName
FROM LookUp2
GROUP BY LookUp2.LongName
PIVOT Format([fld2],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

output:
Jan Feb Mar Apr .....
Lena 30 20 50 35

How can i merge (UNION) both crosstab in one, but output fields will look
like this "Jan (tab1)" "Jan(tab2)" "Feb(tab1)" "Feb(tab2)".
I need result like
Jan(tab1) Jan(tab2) Feb(tab1) Feb(tab2) .... Mar Apr .....
Lena 30 20 50 35



Is it possible at all?
Please, help
 
M

Michel Walsh

SELECT a.*, b.*
FROM crosstab1 AS a LEFT JOIN crosstab2 AS b
ON a.LongName = b.LongName





which assumes all LongName is the second crosstab appears ALSO in the first
one.

The order of the fields may not be like you want, but use a form to
'reorder' the fields, for the presentation, if required.

You can also list explicitly all the fields, rather than using *, and alias
them as it fits your needs.

Vanderghast, Access MVP
 
E

elena

Hi, Michel
Thank you for reply;
Is there a union query for two crosstabs?
I am sorry, what is a.*?

Please, help

Michel Walsh said:
SELECT a.*, b.*
FROM crosstab1 AS a LEFT JOIN crosstab2 AS b
ON a.LongName = b.LongName





which assumes all LongName is the second crosstab appears ALSO in the first
one.

The order of the fields may not be like you want, but use a form to
'reorder' the fields, for the presentation, if required.

You can also list explicitly all the fields, rather than using *, and alias
them as it fits your needs.

Vanderghast, Access MVP

elena said:
Hi, All
I have 2 crosstab queries, which output the same result, the difference
between them is tables (identical tables with the same name fields).
crosstab 1:
TRANSFORM Sum(LookUp1.fld3) AS SumOffld3
SELECT LookUp1.LongName
FROM LookUp1
GROUP BY LookUp1.LongName
PIVOT Format([fld2],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

crosstab 2:
TRANSFORM Sum(LookUp2.fld3) AS SumOffld3
SELECT LookUp2.LongName
FROM LookUp2
GROUP BY LookUp2.LongName
PIVOT Format([fld2],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

output:
Jan Feb Mar Apr .....
Lena 30 20 50 35

How can i merge (UNION) both crosstab in one, but output fields will look
like this "Jan (tab1)" "Jan(tab2)" "Feb(tab1)" "Feb(tab2)".
I need result like
Jan(tab1) Jan(tab2) Feb(tab1) Feb(tab2) .... Mar Apr .....
Lena 30 20 50 35



Is it possible at all?
Please, help
Click to expand...
Click to expand...
 
K

karl dewey

Try these queries --
LookUp1
SELECT Tab1.fld2 AS [Y], Tab1.fld3 AS [A], NULL AS , Tab3.LongName
FROM Tab3 INNER JOIN Tab1 ON Tab3.ShortName = Tab1.fld1
UNION ALL SELECT Tab2.fld2 AS [Y], NULL, Tab2.fld3 AS , Tab3.LongName
FROM Tab3 INNER JOIN Tab2 ON Tab3.ShortName = Tab2.fld1;

LookUp2
SELECT LookUp1.LongName, IIf([A] Is Null,"Tab2 " & Format([Y],"mmm"),"Tab1 "
& Format([Y],"mmm")) AS Tab_Mon, IIf([A] Is Null,,[A]) AS Tab_Val
FROM LookUp1;

LookUp2_Crosstab
TRANSFORM Sum(LookUp2.[Tab_Val]) AS SumOfTab_Val
SELECT LookUp2.[LongName], Sum(LookUp2.[Tab_Val]) AS [Total Of Tab_Val]
FROM LookUp2
GROUP BY LookUp2.[LongName]
PIVOT LookUp2.[Tab_Mon] In("Tab1 Jan","Tab2 Jan","Tab1 Feb","Tab2 Feb","Tab1
Mar","Tab2 Mar","Tab1 Apr","Tab2 Apr","Tab1 May","Tab2 May","Tab1 Jun","Tab2
Jun","Tab1 Jul","Tab2 Jul","Tab1 Aug","Tab2 Aug","Tab1 Sep","Tab2 Sep","Tab1
Oct","Tab2 Oct","Tab2 Nov","Tab1 Dec","Tab2 Dec");

--
KARL DEWEY
Build a little - Test a little


elena said:
Hi, Michel
Thank you for reply;
Is there a union query for two crosstabs?
I am sorry, what is a.*?

Please, help

Michel Walsh said:
SELECT a.*, b.*
FROM crosstab1 AS a LEFT JOIN crosstab2 AS b
ON a.LongName = b.LongName





which assumes all LongName is the second crosstab appears ALSO in the first
one.

The order of the fields may not be like you want, but use a form to
'reorder' the fields, for the presentation, if required.

You can also list explicitly all the fields, rather than using *, and alias
them as it fits your needs.

Vanderghast, Access MVP

elena said:
Hi, All
I have 2 crosstab queries, which output the same result, the difference
between them is tables (identical tables with the same name fields).
crosstab 1:
TRANSFORM Sum(LookUp1.fld3) AS SumOffld3
SELECT LookUp1.LongName
FROM LookUp1
GROUP BY LookUp1.LongName
PIVOT Format([fld2],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

crosstab 2:
TRANSFORM Sum(LookUp2.fld3) AS SumOffld3
SELECT LookUp2.LongName
FROM LookUp2
GROUP BY LookUp2.LongName
PIVOT Format([fld2],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

output:
Jan Feb Mar Apr .....
Lena 30 20 50 35

How can i merge (UNION) both crosstab in one, but output fields will look
like this "Jan (tab1)" "Jan(tab2)" "Feb(tab1)" "Feb(tab2)".
I need result like
Jan(tab1) Jan(tab2) Feb(tab1) Feb(tab2) .... Mar Apr .....
Lena 30 20 50 35



Is it possible at all?
Please, help
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

SELECT a.*, b.*


means to take all columns from table aliased as a, and all columns from all
columns from table aliased as b.

The query is a standard join on the common LongName field.


Vanderghast, Access MVP


elena said:
Hi, Michel
Thank you for reply;
Is there a union query for two crosstabs?
I am sorry, what is a.*?

Please, help

Michel Walsh said:
SELECT a.*, b.*
FROM crosstab1 AS a LEFT JOIN crosstab2 AS b
ON a.LongName = b.LongName





which assumes all LongName is the second crosstab appears ALSO in the
first
one.

The order of the fields may not be like you want, but use a form to
'reorder' the fields, for the presentation, if required.

You can also list explicitly all the fields, rather than using *, and
alias
them as it fits your needs.

Vanderghast, Access MVP

elena said:
Hi, All
I have 2 crosstab queries, which output the same result, the difference
between them is tables (identical tables with the same name fields).
crosstab 1:
TRANSFORM Sum(LookUp1.fld3) AS SumOffld3
SELECT LookUp1.LongName
FROM LookUp1
GROUP BY LookUp1.LongName
PIVOT Format([fld2],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

crosstab 2:
TRANSFORM Sum(LookUp2.fld3) AS SumOffld3
SELECT LookUp2.LongName
FROM LookUp2
GROUP BY LookUp2.LongName
PIVOT Format([fld2],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

output:
Jan Feb Mar Apr .....
Lena 30 20 50 35

How can i merge (UNION) both crosstab in one, but output fields will
look
like this "Jan (tab1)" "Jan(tab2)" "Feb(tab1)" "Feb(tab2)".
I need result like
Jan(tab1) Jan(tab2) Feb(tab1) Feb(tab2) .... Mar Apr .....
Lena 30 20 50 35



Is it possible at all?
Please, help
Click to expand...
Click to expand...
Click to expand...
 
E

elena

Thank you Michel and Karl,
It works great!
Michel Walsh said:
SELECT a.*, b.*


means to take all columns from table aliased as a, and all columns from all
columns from table aliased as b.

The query is a standard join on the common LongName field.


Vanderghast, Access MVP


elena said:
Hi, Michel
Thank you for reply;
Is there a union query for two crosstabs?
I am sorry, what is a.*?

Please, help

Michel Walsh said:
SELECT a.*, b.*
FROM crosstab1 AS a LEFT JOIN crosstab2 AS b
ON a.LongName = b.LongName





which assumes all LongName is the second crosstab appears ALSO in the
first
one.

The order of the fields may not be like you want, but use a form to
'reorder' the fields, for the presentation, if required.

You can also list explicitly all the fields, rather than using *, and
alias
them as it fits your needs.

Vanderghast, Access MVP

Hi, All
I have 2 crosstab queries, which output the same result, the difference
between them is tables (identical tables with the same name fields).
crosstab 1:
TRANSFORM Sum(LookUp1.fld3) AS SumOffld3
SELECT LookUp1.LongName
FROM LookUp1
GROUP BY LookUp1.LongName
PIVOT Format([fld2],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

crosstab 2:
TRANSFORM Sum(LookUp2.fld3) AS SumOffld3
SELECT LookUp2.LongName
FROM LookUp2
GROUP BY LookUp2.LongName
PIVOT Format([fld2],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

output:
Jan Feb Mar Apr .....
Lena 30 20 50 35

How can i merge (UNION) both crosstab in one, but output fields will
look
like this "Jan (tab1)" "Jan(tab2)" "Feb(tab1)" "Feb(tab2)".
I need result like
Jan(tab1) Jan(tab2) Feb(tab1) Feb(tab2) .... Mar Apr .....
Lena 30 20 50 35



Is it possible at all?
Please, help
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

Custom Format for crosstab headings 2
Crosstab returns blank fields 6
Group by Range in Crosstab Query 4
Crosstab Returns Empty Fields 2
CrossTab Query Format 2
Crosstab qry not adding up 1
Showing Crosstab Totals Column in Chart 1
Sorting Crosstab Query 2

Top