SELECT Query - VBA needed? 1

[AnonGod]

posted this in access queries with no response yet, thought I'd try here as well. Hopefully not bugging too many people
-----------------------
I'm sure this is a simple one, I just can't figure it out yet...

I am trying to build an application that matches up records between 2 tables.
ex: (Row 1 in each table is an ID field that I want matched up to Row 1 in the other table.)

Table1
Row1, Row2, Row3, Row4
1, AAA, BBB, CCC
2, 123, 456, 789
3, QWE, RTY, UIO

Table2
Row1, Row2, Row3, Row4
1, 123, 456, 789
2, AAA, BBB, CCC
3, UIO, PQW, ERT

Now what I would like is an output saying that Table1.Row(1) = Table2.Row(2), Table1.Row(2) = Table2.Row(1), and Table1.Row(3) AND Table2.Row(3) do not match anything.

Any ideas?

Thanks so much.
-Andrew

 

[lupins46]

Do you have 'rows' and columns the right way round? I can't make sense of the question .

You are saying that row1 (that is record 1) contains 1 2 3 in both tables. Is that what you mean? If so why are you showing the rows as columns?

 

[AnonGod]

Whoops, yes, swapped rows and columns. Here's corrected data. :
Table1
Col1 Col2 Col3 Col4
1 AAA BBB CCC
2 123 456 789
3 QWE RTY UIO

Table2
Col1 Col2 Col3 Col4
1 123 456 789
2 AAA BBB CCC
3 UIO PQW ERT

-Andrew

 

[lupins46]

Add both tables to a new query.
Link the tables on col2--cl2, Col3--col3 and col4--col4.
Add Columns 2,3 & 4 from table 1 to the grid, then add col1 from table1 and col1 from table2
Make sure the table row in the grid shows the correct table.

Now modify the field names to show:
tbl1Col1:col1
for the fieldname for table1.

Then rename col1 from table2 :
tbl2col1:col1

Run the query.

 

[AnonGod]

Exactly what I was looking for, thanks lupins46.

Any idea for another query to report those that did NOT match up? ... my thought is to run this guy, then run another query on both the tables on id's that did NOT come up.

Anything easier?

-Andrew

 

[PHV]

query to report those that did NOT match up
A starting point:
SELECT T1.*,T2.*
FROM Table1 T1 LEFT JOIN Table2 T2
ON T1.Col2=T2.Col2 AND T1.Col3=T2.Col3 AND T1.Col3=T2.Col3
WHERE T2.Col1 IS NULL
UNION
SELECT T1.*,T2.*
FROM Table1 T1 RIGHT JOIN Table2 T2
ON T1.Col2=T2.Col2 AND T1.Col3=T2.Col3 AND T1.Col3=T2.Col3
WHERE T1.Col1 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

 

[AnonGod]

Great stuff, thank you.
-Andrew

[TAG]
... If you find my posts helpful, rate me up! ...
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'