Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

Get the closest number

Posted on 2010-08-25
Medium Priority
736 Views
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

0
Question by:karinos57
[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
• 11
• 9
• 4

LVL 93

Expert Comment

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 93

Expert Comment

ID: 33525050
Is it safe to assunme that TableB has at most one record for any given ID?
0

LVL 10

Expert Comment

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 93

Expert Comment

ID: 33525078
dwe761,I see we think alike :)Patrick
0

LVL 10

Expert Comment

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

(-:
0

LVL 10

Expert Comment

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

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

ID: 33525465
Then my original suggestion works as it is.
0

LVL 10

Expert Comment

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;
0

LVL 93

Expert Comment

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
0

Author Comment

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

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

Author Comment

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];
0

LVL 10

Expert Comment

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];
0

LVL 10

Expert Comment

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];
0

Author Comment

ID: 33535230
i am getting this one now: "enter prameter value" for a.last price
0

LVL 10

Expert Comment

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];
0

Author Comment

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

dwe761 earned 2000 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];
0

Author Comment

ID: 33536161
works like a charm.  thanks
0

Author Closing Comment

ID: 33536170
thanks
0

Author Comment

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

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

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

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Backups and Disaster RecoveryIn this post, weâ€™ll look at strategies for backups and disaster recovery.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a qâ€¦
Suggested Courses
Course of the Month5 days, 18 hours left to enroll