Solved

Get the closest number

Posted on 2010-08-25
24
688 Views
Last Modified: 2013-11-29
Hi,
Need to find the closest number to numbers in one table to the list of numbers in another table (ms access).  For instance, I have 2 tables:
TABLE A
ID       NAME        PRICE
1          ABC             200
2         KSS              280
3         LKI                390
3         LKI                375
3         LKI                250
4         POY              600
4         POY              576
5         JIK                 200

But table B has uniquie ID and single row for every Price.
TABLE B
ID       NAME        PRICE
1         ABC             300
2         KSS              260
3         LKI                379
4         POY              492
5         JIK                 280
                 
So when I join these 2 tables using the ID, I would like to get the closest Price in Table A.  In other words, If Table A has multiple Prices then I want to see only the Price that is closest to the Price from Table B.  So here is what I would like to see in the final results:
FINAL RESULTS
ID       NAME        PRICE
1         ABC             200
2         KSS              280
3         LKI                375
4         POY              576
5         JIK                 200

Thanks for your help
0
Comment
Question by:karinos57
  • 11
  • 9
  • 4
24 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
What would you do in this case?TableAID     NAME          PRICE6      XYZ              2506      XYZ              350TableBID     NAME          PRICE6      XYZ              300In this case, there are two records that could stake a claim to being "closest"...
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
Is it safe to assunme that TableB has at most one record for any given ID?
0
 
LVL 10

Expert Comment

by:dwe761
Comment Utility
what do you want if one of the IDs has a price higher that is the same difference as one lower.

For instance:

TABLE A
ID       NAME        PRICE
3         LKI                300
3         LKI                100

TABLE B
ID       NAME        PRICE
3         LKI                200
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
dwe761,I see we think alike :)Patrick
0
 
LVL 10

Expert Comment

by:dwe761
Comment Utility
matthewspatrick:
Except that you usually type 2 minutes faster than I do.

(-:
0
 
LVL 10

Expert Comment

by:dwe761
Comment Utility
select x.ID, x.Name, MIN(x.Price)
 FROM
(
      SELECT     b.ID, b.Name, a.Price, MIN(ABS(b.Price - a.Price)) AS diff
      FROM         TableA AS a INNER JOIN
                                                 TableB AS b ON a.ID = b.ID
      GROUP BY b.ID, b.Name, a.Price
) x
INNER JOIN
(
      SELECT     b.ID, b.Name, MIN(ABS(b.Price - a.Price)) AS diff
      FROM         TableA AS a INNER JOIN
                                                 TableB AS b ON a.ID = b.ID
      GROUP BY b.ID, b.Name
) y
ON x.ID = y.ID and x.Name = y.Name and x.diff = y.diff
group by x.ID, x.Name
0
 

Author Comment

by:karinos57
Comment Utility
thanks guys, both of you had asked good question.  If that is the case then i will always go by the lesser price.  thanks for your help.
0
 
LVL 10

Expert Comment

by:dwe761
Comment Utility
Then my original suggestion works as it is.
0
 
LVL 10

Expert Comment

by:dwe761
Comment Utility
I noticed that your question was asked in the Access zone.  My apologies, I gave you a query in SQL.  If you had problems with my original query, try pasting this one into a new Access query.  Of course, you'll have to change the name of TableA and TableB to your own table names.


SELECT x.ID, x.Name, Min(x.Price) AS MinOfPrice

FROM 

[

SELECT     b.ID, b.Name, a.Price, MIN(ABS(b.Price - a.Price)) AS diff

FROM         TableA AS a INNER JOIN

 TableB AS b ON a.ID = b.ID

GROUP BY b.ID, b.Name, a.Price

]. AS x 

INNER JOIN

[SELECT     b.ID, b.Name, MIN(ABS(b.Price - a.Price)) AS diff

FROM         TableA AS a INNER JOIN

 TableB AS b ON a.ID = b.ID

GROUP BY b.ID, b.Name

]. AS y 

ON (x.diff = y.diff) AND (x.Name = y.Name) AND (x.ID = y.ID)

GROUP BY x.ID, x.Name;

Open in new window

0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
Here is what I was working on before getting distracted by a call.  Seems to work, and in case of a tie will return the lower price.
SELECT a1.ID, a1.NAME, Min(a1.PRICE) AS Price

FROM (TableA a1 INNER JOIN 

    TableB b1 ON a1.ID = b1.ID) INNER JOIN 

    (SELECT a2.ID, Min(Abs([a2].[Price] - [b2].[Price])) AS MinDiff

    FROM TableA AS a2 INNER JOIN 

        TableB AS b2 ON a2.ID = b2.ID

    GROUP BY a2.ID) AS q ON b1.ID = q.ID

WHERE Abs(a1.Price - b1.Price) = q.MinDiff

GROUP BY a1.ID, a1.NAME

Open in new window

0
 

Author Comment

by:karinos57
Comment Utility
thanks guys.  both of you have provided the correct solution but i am having a little problem when i try to change the field names.  I am getting an error in both queries.  Pls. see attached.  Also, is there a way to include the Tier Desc in the output?  thanks
Test.zip
0
 
LVL 10

Expert Comment

by:dwe761
Comment Utility
Sorry, I don't have as new of a version of Access so could not open your file.  Could you just paste your query (in SQL form)  into the post?  We can probably figure it out from that.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:karinos57
Comment Utility
here it is.  It says "enter prameter value" for x.last price
SELECT x.[LINE NUMBER], x.[Formatted NDC], Min(x.[Last Price]) AS MinOfPrice



FROM (SELECT     b.[LINE NUMBER], b.[Formatted NDC], a.[INCENTIVE PRICE PKG], MIN(ABS(b.[Last Price] - a.[INCENTIVE PRICE PKG])) AS diff



FROM         Final_Combined_Table AS a INNER JOIN

 Member_Data AS b ON a.[LINE NUMBER] = b.[LINE NUMBER]



GROUP BY b.[LINE NUMBER], b.[Formatted NDC], a.[INCENTIVE PRICE PKG]

)  AS x INNER JOIN (SELECT     b.[LINE NUMBER], b.[Formatted NDC], MIN(ABS(b.[Last Price] - a.[INCENTIVE PRICE PKG])) AS diff



FROM         Final_Combined_Table AS a INNER JOIN

 Member_Data AS b ON a.[LINE NUMBER] = b.[LINE NUMBER]

GROUP BY b.[LINE NUMBER], b.[Formatted NDC]

)  AS y ON (x.diff = y.diff) AND (x.[Formatted NDC] = y.[Formatted NDC]) AND (x.[LINE NUMBER] = y.[LINE NUMBER])

GROUP BY x.[LINE NUMBER], x.[Formatted NDC];

Open in new window

0
 
LVL 10

Expert Comment

by:dwe761
Comment Utility
x is the name given for the first query.  So if you reference a field from x ([Last Price] it has to be in the query output.  So try adding that field as I've done and see if that fixes your problem.
SELECT x.[LINE NUMBER], x.[Formatted NDC], Min(x.[Last Price]) AS MinOfPrice



FROM (

SELECT     b.[LINE NUMBER], b.[Formatted NDC], a.[INCENTIVE PRICE PKG], a.[Last Price], MIN(ABS(b.[Last Price] - a.[INCENTIVE PRICE PKG])) AS diff



FROM         Final_Combined_Table AS a INNER JOIN

 Member_Data AS b ON a.[LINE NUMBER] = b.[LINE NUMBER]



GROUP BY b.[LINE NUMBER], b.[Formatted NDC], a.[INCENTIVE PRICE PKG]

)  AS x 



INNER JOIN (



SELECT     b.[LINE NUMBER], b.[Formatted NDC], MIN(ABS(b.[Last Price] - a.[INCENTIVE PRICE PKG])) AS diff

FROM         Final_Combined_Table AS a INNER JOIN

 Member_Data AS b ON a.[LINE NUMBER] = b.[LINE NUMBER]

GROUP BY b.[LINE NUMBER], b.[Formatted NDC]

)  AS y 



ON (x.diff = y.diff) AND (x.[Formatted NDC] = y.[Formatted NDC]) AND (x.[LINE NUMBER] = y.[LINE NUMBER])

GROUP BY x.[LINE NUMBER], x.[Formatted NDC];

Open in new window

0
 
LVL 10

Expert Comment

by:dwe761
Comment Utility
Oops.  It must be in the Group By as well.
SELECT x.[LINE NUMBER], x.[Formatted NDC], Min(x.[Last Price]) AS MinOfPrice



FROM (

SELECT     b.[LINE NUMBER], b.[Formatted NDC], a.[INCENTIVE PRICE PKG], a.[Last Price], MIN(ABS(b.[Last Price] - a.[INCENTIVE PRICE PKG])) AS diff



FROM         Final_Combined_Table AS a INNER JOIN

 Member_Data AS b ON a.[LINE NUMBER] = b.[LINE NUMBER]



GROUP BY b.[LINE NUMBER], b.[Formatted NDC], a.[INCENTIVE PRICE PKG], a.[Last Price]

)  AS x 



INNER JOIN (



SELECT     b.[LINE NUMBER], b.[Formatted NDC], MIN(ABS(b.[Last Price] - a.[INCENTIVE PRICE PKG])) AS diff

FROM         Final_Combined_Table AS a INNER JOIN

 Member_Data AS b ON a.[LINE NUMBER] = b.[LINE NUMBER]

GROUP BY b.[LINE NUMBER], b.[Formatted NDC]

)  AS y 



ON (x.diff = y.diff) AND (x.[Formatted NDC] = y.[Formatted NDC]) AND (x.[LINE NUMBER] = y.[LINE NUMBER])

GROUP BY x.[LINE NUMBER], x.[Formatted NDC];

Open in new window

0
 

Author Comment

by:karinos57
Comment Utility
i am getting this one now: "enter prameter value" for a.last price
0
 
LVL 10

Expert Comment

by:dwe761
Comment Utility
My bad.  Should be b.[Last Price]
SELECT x.[LINE NUMBER], x.[Formatted NDC], Min(x.[Last Price]) AS MinOfPrice



FROM (

SELECT     b.[LINE NUMBER], b.[Formatted NDC], a.[INCENTIVE PRICE PKG], b.[Last Price], MIN(ABS(b.[Last Price] - a.[INCENTIVE PRICE PKG])) AS diff



FROM         Final_Combined_Table AS a INNER JOIN

 Member_Data AS b ON a.[LINE NUMBER] = b.[LINE NUMBER]



GROUP BY b.[LINE NUMBER], b.[Formatted NDC], a.[INCENTIVE PRICE PKG], b.[Last Price]

)  AS x 



INNER JOIN (



SELECT     b.[LINE NUMBER], b.[Formatted NDC], MIN(ABS(b.[Last Price] - a.[INCENTIVE PRICE PKG])) AS diff

FROM         Final_Combined_Table AS a INNER JOIN

 Member_Data AS b ON a.[LINE NUMBER] = b.[LINE NUMBER]

GROUP BY b.[LINE NUMBER], b.[Formatted NDC]

)  AS y 



ON (x.diff = y.diff) AND (x.[Formatted NDC] = y.[Formatted NDC]) AND (x.[LINE NUMBER] = y.[LINE NUMBER])

GROUP BY x.[LINE NUMBER], x.[Formatted NDC];

Open in new window

0
 

Author Comment

by:karinos57
Comment Utility
dwe761,
thanks man, the query is wokring now but the result are wrong.  it is producing all the price in the member_data table.  it is not showing the closest number.  The member_data should be like Table B in my previous post.  
Final Combined table
Line Number      Formatted NDC      INCENTIVE PRICE PKG      Tier Desc
1      00944293201      $0.69      tier1
2      15584010102      $200.00      tier4
3      00049399063      $484.42      tier1
4      00004035234      $524.00      tier3
5      61958070105      $365.00      tier5
6      59148001016      $542.00      tier8
7      00002442037      $800.28      tier2
7      00002442037      $775.01      tier1
7      00002442037      $825.55      tier9
8      00002441538      $581.26      tier8
8      00002441538      $600.21      tier10
8      00002441538      $619.16      tier5
9      00004035009      $254.00      tier2
10      00004035010      $58.00      tier9

memeber data table
Line Number      Last Price      Formatted NDC
1      $825.00      00944293201
2      $1,384.00      15584010102
3      $394.00      00049399063
4      $1,592.00      00004035234
5      $887.00      61958070105
6      $545.00      59148001016
7      $729.00      00002442037
8      $542.00      00002441538
9      $398.00      00004035009
10      $358.00      00004035010
0
 
LVL 10

Accepted Solution

by:
dwe761 earned 500 total points
Comment Utility
OK.  Try this.


SELECT x.[LINE NUMBER], x.[Formatted NDC], Min(x.[INCENTIVE PRICE PKG]) AS MinOfPrice



FROM (

SELECT     b.[LINE NUMBER], b.[Formatted NDC], a.[INCENTIVE PRICE PKG], MIN(ABS(b.[Last Price] - a.[INCENTIVE PRICE PKG])) AS diff



FROM         Final_Combined_Table AS a INNER JOIN

 Member_Data AS b ON a.[LINE NUMBER] = b.[LINE NUMBER]



GROUP BY b.[LINE NUMBER], b.[Formatted NDC], a.[INCENTIVE PRICE PKG]

)  AS x 



INNER JOIN (



SELECT     b.[LINE NUMBER], b.[Formatted NDC], MIN(ABS(b.[Last Price] - a.[INCENTIVE PRICE PKG])) AS diff

FROM         Final_Combined_Table AS a INNER JOIN

 Member_Data AS b ON a.[LINE NUMBER] = b.[LINE NUMBER]

GROUP BY b.[LINE NUMBER], b.[Formatted NDC]

)  AS y 



ON (x.diff = y.diff) AND (x.[Formatted NDC] = y.[Formatted NDC]) AND (x.[LINE NUMBER] = y.[LINE NUMBER])

GROUP BY x.[LINE NUMBER], x.[Formatted NDC];

Open in new window

0
 

Author Comment

by:karinos57
Comment Utility
works like a charm.  thanks
0
 

Author Closing Comment

by:karinos57
Comment Utility
thanks
0
 

Author Comment

by:karinos57
Comment Utility
dwe761,
this last query works fine except it does not solve this issue that you raised before.  See below.  when i run the query it shows both values from Table A as suppose to show only the lesser price.  I think your initial query worked fine and i think something went wrong when we changed the field names.  thanks again for your help.
TABLE A
ID       NAME        PRICE
3         LKI                300
3         LKI                100

TABLE B
ID       NAME        PRICE
3         LKI                200
0
 
LVL 10

Expert Comment

by:dwe761
Comment Utility
Are you sure you're using my last query sent?  I created your tables with your data.  And I added two rows to Final_Combined_Table
Line Number      Formatted NDC      INCENTIVE PRICE PKG      Tier Desc
1      00944293201      $0.69      tier1
2      15584010102      $200.00      tier4
3      00049399063      $484.42      tier1
4      00004035234      $524.00      tier3
5      61958070105      $365.00      tier5
6      59148001016      $542.00      tier8
7      00002442037      $775.01      tier1
7      00002442037      $800.28      tier2
7      00002442037      $825.55      tier9
8      00002441538      $581.26      tier8
8      00002441538      $600.21      tier10
8      00002441538      $619.16      tier5
9      00004035009      $254.00      tier2
10      00004035010      $58.00      tier9
11      11111111111      $100.00      tier99
11      11111111111      $300.00      tier99

and one row to Member_Data
Line Number      Last Price      Formatted NDC
1      $825.00      00944293201
2      $1,384.00      15584010102
3      $394.00      00049399063
4      $1,592.00      00004035234
5      $887.00      61958070105
6      $545.00      59148001016
7      $729.00      00002442037
8      $542.00      00002441538
9      $398.00      00004035009
10      $358.00      00004035010
11      $200.00      11111111111

These are my results when I ran my last query:

LINE NUMBER      Formatted NDC      MinOfPrice
1      00944293201      $0.69
2      15584010102      $200.00
3      00049399063      $484.42
4      00004035234      $524.00
5      61958070105      $365.00
6      59148001016      $542.00
7      00002442037      $775.01
8      00002441538      $581.26
9      00004035009      $254.00
10      00004035010      $58.00
11      11111111111      $100.00

Isn't this correct?
0
 

Author Comment

by:karinos57
Comment Utility
i think what is happening is that i added the tiier desc in there and the tier desc are different for the same line number so when group them then it shows both prices.  thanks
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

762 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

12 Experts available now in Live!

Get 1:1 Help Now