UNION and Crosstab query

  • Thread starter Thread starter elena
  • Start date Start date
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
 
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, 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...
 
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...
 
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...
 
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...
 
Back
Top