Solved

# Matching query in Access

Posted on 2007-04-03
515 Views
Hi,
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.

0
Question by:Bear2
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 18

Expert Comment

ID: 18841735
this would be easier to achieve in Excel, with a VLOOKUP call, and the final argument of TRUE to indicate an approximate match....
0

LVL 50

Expert Comment

ID: 18841741
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
0

LVL 50

Accepted Solution

Gustav Brock earned 500 total points
ID: 18842227
Here is one method with a subquery:

SELECT
tblA.*,
tblB.*
FROM
tblA,
tblB
WHERE
tblB.ID=
(Select Top 1
B.ID
From
tblB As B,
tblA As A
Where
A.ID = tblA.ID
Order By
Abs(A.ColumnA - B.ColumnB));

The IDs are the unique keys for the tables.

/gustav
0

Author Comment

ID: 18842877
Perfect solution
0

LVL 50

Expert Comment

ID: 18842917
Thanks!

/gustav
0

LVL 50

Expert Comment

ID: 18870064
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.

/gustav
0

Author Comment

ID: 18880684
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!
0

LVL 50

Expert Comment

ID: 18880745
Very good, just what I needed. Thanks!

/gustav
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
###### Suggested Courses
Course of the Month5 days, 22 hours left to enroll