Union query

[hoachen]

I have two cosstab query and i want to combine them together as well as merge
them with the same pubname. The structure of these two tables are same. Bothe
column contains: Pubname, Item1, Item2, Item3 and a total. I tried to do a
union on these two tables but it did not merge the same pubname with the
different item total or same item total. Also, how can put a row at the
bottom to sum all the item?

crosstab Query1
pubname item1 item2 Item3 Total
aaa 2 1 1 4
bbb 1 1
ddd 1 1
eee 1 1

crosstab query2
pubname item1 item2 Item3 Total
iii 3 10 13
ooo 1 1
aaa 1 1
ddd 1 1 2

Output should look like:
pubname item1 item2 Item3 Total
aaa 3 1 1 5
bbb 1 1
ddd 1 2 3
eee 1 1
iii 3 10 13
ooo 1 1
Total 8 4 12 24

 

[Duane Hookom]

I would create a union query and use it as the record source of a report. Add
a PubName group header (or footer) and a report footer with sums of the
Items. Hide the detail section.

 

[hoachen]

I created a union query,but when i run the report, the access not responding
and take 15 minutes still not generate the report. The union table on have 15
rows and 4 columns! any suggestion?

 

[Duane Hookom]

15 rows and 4 columns shouldn't take too much time. I expect the crosstabs
might be holding this up. Did you enter the Column Names into the crosstab
query properties? Did you use UNION or UNION ALL?

 

[hoachen]

Yes, the crosstab query is from very large table and then group by each
pubname. each crosstab have the heading row. Pubname, item1, item2, item3 and
a total. I am using uion all. May i ask what is different between union all
and just a union? So, you see why cause my access not responding?

 

[Duane Hookom]

UNION ALL is generally faster than just UNION.

Please answer my question regarding entering of Column Headings. If you
don't understand, post the SQL of your crosstabs.

 

[hoachen]

here is one of the crosstab query and the other one is the same.
TRANSFORM Count([information].[Item#]) AS [CountOfItem#]
SELECT IIf([information].Sub=PubSub.Pubs,[information].Sub,"OTHER") AS
PubName, Count([information].[Item#]) AS Totals
FROM [information] LEFT JOIN PubSub ON [information].Sub = PubSub.Pubs
WHERE ((([information].Author) Not Like "*[*]*") AND
(([information].Pub)="LS")) OR ((([information].Author) Not Like "*[*]*") AND
(([information].Pub)="VT"))
GROUP BY IIf([information].Sub=PubSub.Pubs,[information].Sub,"OTHER")
PIVOT [information].Pub In ("CM","VT","LS");

 

[Duane Hookom]

I would make sure you have indexes on all fields used in joins, criteria, and
grouping. Some times it works more efficiently to append from the crosstab
into a temporary table.
--
Duane Hookom
Microsoft Access MVP

here is one of the crosstab query and the other one is the same.
TRANSFORM Count([information].[Item#]) AS [CountOfItem#]
SELECT IIf([information].Sub=PubSub.Pubs,[information].Sub,"OTHER") AS
PubName, Count([information].[Item#]) AS Totals
FROM [information] LEFT JOIN PubSub ON [information].Sub = PubSub.Pubs
WHERE ((([information].Author) Not Like "*[*]*") AND
(([information].Pub)="LS")) OR ((([information].Author) Not Like "*[*]*") AND
(([information].Pub)="VT"))
GROUP BY IIf([information].Sub=PubSub.Pubs,[information].Sub,"OTHER")
PIVOT [information].Pub In ("CM","VT","LS");

UNION ALL is generally faster than just UNION.

Please answer my question regarding entering of Column Headings. If you
don't understand, post the SQL of your crosstabs.

 

[Daryl S]

UNION ALL is used when the records returned by each select statement are
unique, but will not work for the case where you have duplicate records in
each select statement that need to be combined.

I would suggest re-writing the two crosstabs so that each one contains all
the pubnames (even though there may be no data for those records). Then
instead of a UNION query, create a new query with both subqueries (looking
like tables) selected, and individually sum each of the three fields (e.g.
Item1, Item2, and Item3) and the total.