Compare two tables

[hoachen]

HI Everyone,



Thank you in advance for reading this post. I am not getting the result at the bottom. Please help me how to get below bottom result I want.
SELECT Order2.OrderQty, Order2.Company, Order2.[Order#], Order1.OrderQty, Order1.Company
FROM Order1 RIGHT JOIN Order2 ON (Order1.Company = Order2.Company) AND (Order1.[Order#] = Order2.[Order#])
GROUP BY Order2.OrderQty, Order2.Company, Order2.[Order#], Order1.OrderQty, Order1.Company;

OrderQty	Company	Order#
8	Oop	BBS
12	abc	AAA
3	abc	AAA

OrderQty	Company	Order#
7	Oop	BBS
12	abc	AAA
3	abc	AAA

Order2.OrderQty	Order2.Company	Order#	Order1.OrderQty	Order1.Company
3	abc	AAA	3	abc
3	abc	AAA	12	abc
7	Oop	BBS	8	Oop
12	abc	AAA	3	abc
12	abc	AAA	12	abc

The result I need. How to get this?

Order2#Company	Order#	Order1#OrderQty	Order1#Company
3	abc	AAA	3
7	Oop	BBS	8
12	abc	AAA	12

[ranman256]

join the 2 tables on [company] and [order#]
then append (or make table) this join qry to a table with the new field names

[hoachen]

Nope, it still get the same result

3	abc	AAA	3	abc
3	abc	AAA	12	abc
7	Oop	BBS	8	Oop
12	abc	AAA	3	abc
12	abc	AAA	12	abc