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
 
Ad

Advertisements

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
 
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
 
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
 
Ad

Advertisements

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
 
Ad

Advertisements


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

Top