QUERY: Match fields in 2 tables side by side, see if they are exact 1

[13badar]

I have two Excel tables that I imported into an Access Database. [blue]I want to check a certain column[/blue] "Customer_ID" (Maybe even two) [blue]in Table1 with Table2[/blue]. [blue]Make sure its all the same Line by Line,[/blue] If not, a third column in my Query named "MATCH" can say a Yes/No.
I was thinking about using an expression (If Statement) for this situation, dont know if I'm headed in the right direction. Any helpful hints will be greatly appreciated, Thanx. So far I wrote:

MATCH: IIf([CUST_ID]=[CUST_ID2],Null)

p.s., I was going to use the same name CUST_ID for Table2 too, but it doesn't accept it, is there a way that it could, so I wont have to manually change the Column Name each time?

 

[traingamer]

try

MATCH: IIf(tbl1.[CUST_ID]=tbl2.[CUST_ID2],Null)

Where tbl1 and tbl2 are your table names

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[PHV]

Line by Line
SELECT A.[Line# field], A.Customer_ID, B.Customer_ID, IIf(A.Customer_ID=B.Customer_ID,"Yes","No") AS MATCH
FROM Table1 AS A LEFT JOIN Table2 AS B ON A.[Line# field] = B.[Line# field]
UNION SELECT B.[Line# field]], A.Customer_ID, B.Customer_ID, "No"
FROM Table1 AS A RIGHT JOIN Table2 AS B ON A.[Line# field] = B.[Line# field]
WHERE A.Customer_ID Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[13badar]

Thanx for all your help Traingamer and PHV. PHV, your code is exactly what I was looking for, but it's giving me some problems. When I ran that Query, Two Dialog boxes appear before it takes me to the Datasheet view of the Query:
[blue]Enter Parameter Value A.Line# field:[/blue]
[blue]Enter Parameter Value B.Line# field:[/blue]

Table1 and Table2 are very long tables. They are supposed to be the exact copy of eachother but there's no way of knowing that unless someone painstakingly goes through every record line by line and checks it. I want Access to automatically check Customer_ID from Table1 and from Table2 and return it in the Match column if its exactly the same or different as a YES/NO. This way I'll know that the import process is working OK. Its only for QA purposes.
[blue]e.g. Customer_ID=1, in Table1
and Customer_ID=2, in Table2 in the top 1st row --Match No[/blue]
and so on for a 100 or so records.

I wrote this, but it doesnt work either as I would want it to work.
SELECT tbl1.CUST_ID, tbl2.CUST_ID
FROM tbl1, tbl2
WHERE tbl1.CUST_ID=tbl2.CUST_ID;

[COLOR=red yellow]Something wierd happens to my first column of CUST_ID column which comes from Table1. If CUST_ID=1 It repeats that 1 in the CUST_ID column 19 times because I have a total of 19 records in Table2. The CUST_ID colum from Table2 displays fine e.g. 1,2,3,.... and so on[/color]

 

[PHV]

You have to replace [Line# field] by the name of the row# field in your tables.
You should have one as you posted this sentence: in the top 1st row

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[13badar]

Ok this statement works but I'm still having the problem where my CUST_ID column from Table1 repeats 19 times. I dont know how to fix that. The code below is working fine and Match Column is saying YES/NO and is working properly:
[blue]SELECT tbl1.CUST_ID, tbl2.CUST_ID, IIf(tbl1.CUST_ID=tbl2.CUST_ID,"Yes","No") AS [MATCH]
FROM tbl1, tbl2;[/blue]

 

[PHV]

Take a look at Cartesian product (aka Cross join).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[13badar]

By the Top First Row, I meant First record in Table1 should match exactly the top 1st record in Table2.
[blue]Table1 Table2
CUST_ID CUST_NAME CUST_ID CUST_NAME Match
01 Mike 01 Mike YES
02 Joe 03 Nick No[/blue]

I dont have any Row Names, Only Column Names. Rows are all just records that I want ACCESS to automatically check as I run this Query. I know its very simple. I probably made it sound complicated. Im sorry. I just cant figure it out. Please Help.

 

[PHV]

So you don't have any sequence #, I guess.
SELECT A.CUST_ID, A.CUST_NAME, B.CUST_ID, B.CUST_NAME, IIf(A.CUST_NAME=B.CUST_NAME,"Yes","No") AS Match
FROM tbl1 AS A LEFT JOIN tbl2 AS B ON A.CUST_ID = B.CUST_ID
UNION SELECT Null, Null, B.CUST_ID, B.CUST_NAME, "No"
FROM tbl1 AS A RIGHT JOIN tbl2 AS B ON A.CUST_ID = B.CUST_ID
WHERE A.CUST_ID Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[13badar]

YAY.....
thanx it works, ill keep u posted when i do more with it.
appreciate all yr help

 

[13badar]

One last Question: U mentioned Cross Join, but over here U used Outer Join right...can u Explain Y

 

[PHV]

LEFT JOIN: All records of 1st table with unmatching fields of 2nd table set to null
RIGHT JOIN: All records of 2nd table with unmatching fields of 1st table set to null
CROSS JOIN: All records of 2nd table with EACH record of 1st table (number of rows returned = number of 1st table's rows * number of 2nd table's rows)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[13badar]

Thnx a bunch PH, I understand more about Joins and how/when to use them. I could've used a Cross Join in this case and I did use it at first but It was simply going to Join every Record of the two tables. I wanted to link each Record of Table2 to Table1 and check for accuracy thats Y a Left Join is perfect for this situation because I am joining the Right Table(Table2) with the Left Table(Table1). Thanx again for explaining.