Compare two tables

[ncalcaterra]

hi - love this website and use it often, but first time posting a question i have to tables (A & B) that have the exact same 6 fields:

ItemNo, Description, Unit, UnitCost, Quantity & Total

i would like to compare the two tables (based on ItemNo) and return the results from both tables. heres an example of what i would like my results to look like side by side:

A.ItemNo A.Qty A.Total B.ItemNo B.Qty B.Total
02.1011 5 2000 02.1011 13 6200
02.1011 2 120
02.1011 5 400
02.1013 4 800
02.1014 4 500

02.1015 3 100 02.1015 1 50
02.1015 4 70
02.1018 6 500
02.1019 2 10000

i will be totaling each side (A & B), and it's important for there to be blanks when B has more of the same ItemNo then A.

i would really appreciate some great advice on this and would sincerly like to thank you in advance for your help.

thanks so much.

 

[crobg]

I would create a union of the two tables into one query. Making sure I add a new column to identify the table the data is from.

SELECT A.ItemNo, A.Qty, A.Total, "A" AS [Group]
FROM A;

UNION ALL SELECT B.ItemNo, B.Qty, B.Total, "B" AS [Group]
FROM B;

Then use a crosstab to get the totals.

TRANSFORM Sum(Query1.Total) AS SumOfTotal
SELECT Query1.ItemNo
FROM Query1
GROUP BY Query1.ItemNo
PIVOT Query1.Group;

I have not totaled two values yet in a cross tab. But here is a FAQ if you need to sum both QTY and Total:faq701-4524

 

[JJeremy]

You can create this in the query side by putting 2 tables and linking them together.

 

[crobg]

That will duplicate values from table A.

 

[ncalcaterra]

crobg - thank you for your reply... i do like your suggestion and it will work for my summary, but is there a way to do the crosstab without suming and having each record display? hopefully that makes sense.

 

[ncalcaterra]

jjeremy - thanks for your reply as well, and yes, i did try it your way and it does return the duplicate values from table a as crobg says.

 

[crobg]

Why do you need to present the data in this fasion? Is it for a report or form?

 

[ncalcaterra]

its for a report. i need to have the report in a fashion like i listed in my initial thread.

 

[ncalcaterra]

oh ya, the reason why it has to be in this fashion is because it's an ItemNo comparison and it needs to be in ItemNo order. As you can see, sometimes theres more "B" ItemNos then "A" and it's important that we know that and see them listed out. but the kicker is not listing duplicate values on the "A" side just to match up with the additional items that might be on the "B" side. thanks for all your help - this is great.

 

[crobg]

In your report group by item number, then by [group]. It doesn't look exactly like you are looking for though. I'm still thinking this one through...hold on, someone smarter will have an answer.

 

[ncalcaterra]

this is a tough one, i know, and it seems so easy in theory, but not so much! i really do appreaciate all your advice thus far and hopefully there's an answer out there somewhere!

 

[crobg]

You could just report on the union query. Using a IIF statement of =IIf([GROUP]="A",[Qty],"" for a QTYA column and =IIf([GROUP]="B",[Qty],"") for a QTYB colomn. Repeat for Totals. Output would be as follows:
ItemNo QTYA TotalA QTYB TotalB
02.1011 5 2000
02.1011 13 6200
02.1011 2 120
02.1011 5 400

 

[ncalcaterra]

crobg - thanks for the report idea... after struggling forever, i gave it a try and it came out pretty nice, so hopefully it will be accepted. thanks again for all your help - greatly appreciated.

 

[crobg]

I'm glad you figured it out. Sometimes the easiest sounding things can be the hardest.