karinos57
asked on
Get the closest number
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
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
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"...
Is it safe to assunme that TableB has at most one record for any given ID?
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
For instance:
TABLE A
ID NAME PRICE
3 LKI 300
3 LKI 100
TABLE B
ID NAME PRICE
3 LKI 200
dwe761,I see we think alike :)Patrick
matthewspatrick:
Except that you usually type 2 minutes faster than I do.
(-:
Except that you usually type 2 minutes faster than I do.
(-:
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
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
ASKER
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.
Then my original suggestion works as it is.
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;
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
ASKER
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
Test.zip
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.
ASKER
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];
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];
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];
ASKER
i am getting this one now: "enter prameter value" for a.last price
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];
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
works like a charm. thanks
ASKER
thanks
ASKER
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
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
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?
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?
ASKER
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