multiple queries and crosstab report

[March]

Hello,


I would like to know how to combine multiple queries in crosstab table.

For example,

query1: listed

Field1 qty
A 8
B 3
C 2
D 5

query2: listed

Field1 qty
A 20
B 23
C 42
D 58
E 88
Z 89

From query1 and query2, I would like to have a result below

Field1 xqty mqty
A 8 20
B 3 23
C 2 42
D 5 58
E 88
Z 89

then I will take this to add into report view.


Thank you,

March

 

[Daniel.Ran.XU]

Hello,

I would like to know how to combine multiple queries in crosstab table.

For example,

query1: listed

Field1      qty
A                8
B                3
C                2
D                5

query2: listed

Field1      qty
A              20
B              23
C              42
D              58
E              88
Z              89

From query1 and query2, I would like to have a result below

Field1      xqty     mqty
A                8          20
B                3          23
C                2          42
D                5          58
E                             88
Z                             89

then I will take this to add into report view.

Thank you,

March

you may need to create another query which join your two queries, and
then crosstab it.

Cheers
Daniel

 

[March]

If you mean join with "Union" the records will be

Field1 qty
A 8
A 20
B 3
B 23
C 2
C 42
D 5
D 58
E 88
Z 89


that I have no idea how to do the crosstab into the form below

Field1 xqty mqty
A 8 20
B 3 23
C 2 42
D 5 58
E 88
Z 89


Through this point, "Union" might not be a good solution. I 'm not really
sure that my idea to merge both tables with "union".

Please give me suggestions.

Thank you,

March

 

[John Spencer]

I'm not positive this will work, but I would try the following.

First query to get a list of all values in field1

SELECT Field1
FROM Query1
UNION
SELECT Field1
FROM Query2

SELECT U.Field1, Query1.qty, Query2.Qty
FROM (UNIONQuery as U LEFT JOIN Query1
ON U.Field1 =Query1.Field1)
LEFT JOIN QUERY2 ON U.Field1 = Query2.Field1

If your field and query names obey the naming conventions for fields and
tables, you could try to do that all in one query.

SELECT U.Field1, Query1.qty, Query2.Qty
FROM ((SELECT Field1
FROM Query1
UNION
SELECT Field1
FROM Query2) as U LEFT JOIN Query1
ON U.Field1 =Query1.Field1)
LEFT JOIN QUERY2 ON U.Field1 = Query2.Field1

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County