Taking 2 tables from a Access DB and Comparing them

[Coreyc]

Here's what I'm looking to do and I can't figure out how to do it.
I have 2 tables in an access database. The user will enter a part # that is listed in table one. Many specifications are also listed with the part # in Table one. Then I want it to compare all the specs from the part # in table 1 and select the best matching part in table 2. There are about 30 difference columns of specs listed. Both tables are set up the same way with the same columns and everything. Is there anyway to do what I am asking.. I'm hoping it's clear.

Thank you,
Corey

[dhansen]

What kind of comparison are you wanting to do? See if a column from one table is the same as a column from the other table? Or look for similar values? Or ??

[Coreyc]

Yeah I want to see if the column in one is equal to the one in the other and then display the item with the most column matches.

[dhansen]

That could get a little tricky. Here is something I thought of that may work.

You would need to get the values from the first table/column that the user is accessing into variables. Then do a query like

Select * from table2 where column1 LIKE '*" & variable1 & "*' OR column2 LIKE '*" & variable2 & "*' ......

Have a long string like that. It will return all the recordsets that have at least one match. Then, you could loop through the recordset to see how many matches each one has and show the user the one with the most.

Will that work for you?