英文:
Access match a value to closest value in another table
问题
以下是翻译好的内容：
期望的输出：
姓名 百分比 价值
克里斯 56% £3
简 67% £4
最好使用SQL而不是VBA，并且我们希望从Python运行数据库，而VBA无法正确运行。
如果您需要进一步的帮助，请随时提问。
英文:
Seen some things close but not exact, ill keep it basic.
2 tables
Table1
NAME PERCENTAGE
Chris 56%
Jane 67%
Table 2
PERCENTAGE VALUE
50% £3
60% £4
70% £5
I want to return the value from table 2 where the percentage from table 1 matches to the closest value lower than the original value, not above.
Expected output:
NAME PERCENTAGE VALUE
Chris 56% £3
Jane 67% £4
Ideally in SQL not VBA and we want to run the DB from python and VBA won't run correctly.
Many thanks if you can help.
We can do this by creating a massive table and matching to every possible number but this is not ideal.
答案1
得分: 1
你可以使用子查询来获取所需的数值。类似这样的方式：
SELECT
M.FirstName,
M.Percentage,
(SELECT TOP 1 L.DataValue FROM tblLookup AS L WHERE L.Percentage<M.Percentage ORDER BY L.Percentage DESC) AS ReturnValue
FROM tblMain AS M;
你需要确保在第二个表格中存在一个负百分比的值，以满足第一个表格中有百分比为0的情况。
英文:
You can use a subquery to return the values needed. Something like:
SELECT
M.FirstName,
M.Percentage,
(SELECT TOP 1 L.DataValue FROM tblLookup AS L WHERE L.Percentage<M.Percentage ORDER BY L.Percentage DESC) AS ReturnValue
FROM tblMain AS M;
You will need to ensure that there is a negative percentage value on the second table to cater for anybody having a percentage of 0 in the first table.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论