Matching query in Access

I need some help with a matching query in Access:

I have 2 tables: TableA and TableB which are not connected in the database. In TableA I have a column
ColumnA and want to find the closest values in ColumnB in Table B (not exact match).

I want the result of the query to display all values in ColumnA in one column and the closest values to each value in ColumnA in ColumnB in another column.

Thanks in advance!


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sham HaqueSenior SAP CRM ConsultantCommented:
this would be easier to achieve in Excel, with a VLOOKUP call, and the final argument of TRUE to indicate an approximate match....
what are the data type of the columns ?
define closest match...?

give an example of the data and what you'd expect...

have you considered

select a.column,max(b.column)
 from tablea as a
 left outer join tableb as b
 on a.column > b.column
 group by a.column
 order by 1
Gustav BrockCIOCommented:
Here is one method with a subquery:

      (Select Top 1
        tblB As B,
        tblA As A
        A.ID = tblA.ID
      Order By
        Abs(A.ColumnA - B.ColumnB));

The IDs are the unique keys for the tables.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Bear2Author Commented:
Perfect solution
Gustav BrockCIOCommented:

Gustav BrockCIOCommented:
I'm looking for good examples for use of this method.

Would you mind telling me what this solution solved for you? Just the headline, no detailed data is needed.

Bear2Author Commented:
Very simple. If you have created a table with input values and want to compare them with a table with standard values this solution helps with the grouping.

Thanks again Gustav!
Gustav BrockCIOCommented:
Very good, just what I needed. Thanks!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.