Solved

Get the closest number

Posted on 2010-08-25
24
698 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
ID: 33525042
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
ID: 33525050
Is it safe to assunme that TableB has at most one record for any given ID?
0
 
LVL 10

Expert Comment

by:dwe761
ID: 33525063
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
ID: 33525078
dwe761,I see we think alike :)Patrick
0
 
LVL 10

Expert Comment

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

(-:
0
 
LVL 10

Expert Comment

by:dwe761
ID: 33525330
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
ID: 33525426
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
ID: 33525465
Then my original suggestion works as it is.
0
 
LVL 10

Expert Comment

by:dwe761
ID: 33525567
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
ID: 33525643
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
ID: 33534896
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
ID: 33534949
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:karinos57
ID: 33535046
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
ID: 33535149
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
ID: 33535166
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
ID: 33535230
i am getting this one now: "enter prameter value" for a.last price
0
 
LVL 10

Expert Comment

by:dwe761
ID: 33535270
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
ID: 33535458
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
ID: 33535820
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
ID: 33536161
works like a charm.  thanks
0
 

Author Closing Comment

by:karinos57
ID: 33536170
thanks
0
 

Author Comment

by:karinos57
ID: 33536843
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
ID: 33537220
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
ID: 33542696
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

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.

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 …
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

947 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

18 Experts available now in Live!

Get 1:1 Help Now