Matching query in Access

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.

Thanks in advance!

   

Bear2Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Gustav BrockConnect With a Mentor CIOCommented:
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
 
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....
0
 
LowfatspreadCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Bear2Author Commented:
Perfect solution
0
 
Gustav BrockCIOCommented:
Thanks!

/gustav
0
 
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.

/gustav
0
 
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!
0
 
Gustav BrockCIOCommented:
Very good, just what I needed. Thanks!

/gustav
0
All Courses

From novice to tech pro — start learning today.