Solved

Matching query in Access

Posted on 2007-04-03
8
519 Views
Last Modified: 2008-02-01
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!

   

0
Comment
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
  • Learn & ask questions
8 Comments
 
LVL 18

Expert Comment

by:Sham Haque
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

by:Lowfatspread
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 51

Accepted Solution

by:
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:Bear2
ID: 18842877
Perfect solution
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 18842917
Thanks!

/gustav
0
 
LVL 51

Expert Comment

by:Gustav Brock
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

by:Bear2
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 51

Expert Comment

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

/gustav
0

Featured Post

PowerShell Core for Advanced Linux Administrators

Understand advanced principals around Powershell Core with a focus on the Linux Administrator.  This course covers how to administer numerous environments across multiple platforms including Linux, Azure, AWS, and Google Cloud from a single shell instance.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question