Solved

Matching query in Access

Posted on 2007-04-03
8
501 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
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 49

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
 

Author Comment

by:Bear2
ID: 18842877
Perfect solution
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 18842917
Thanks!

/gustav
0
 
LVL 49

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 49

Expert Comment

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

/gustav
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

929 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now