MS Access从两列之间的范围匹配

Question

我有下面的表T1

RL  OR  FVALU   TVALU
R1  O1  3291    
R1  O1  3002    
R1  O1  3010
R2  O1  2000

另一个表T2如下：

RL  OR  FVALU   TVALU
R1  O1  3291    
R1  O1  3000    3005
R1  O1  5000    
R2  O1  *

预期输出应如下：

RL  OR  FVALU   TVALU   MATCHORDOESNOT
R1  O1  3291            MATCH
R1  O1  3002            MATCH
R1  O1  3010            DONOTMATCH
R2  O1  2000            MATCH

===============================================================如何匹配FVALU和TVALU字段中从T1到T2的值？

在上述情况下，3002和3291也应在T1和T2表之间匹配*值应与2000匹配。前两列可以有联接。此外，注意TVALU有时有值，有时是空

我尝试了以下查询，但不起作用：SELECT T1.RL, T1.[OR], T1.FVALU, T1.TVALU, IIf(InStr(([T2]![FVALU]),"*")>0,"MATCH",IIf([T1]![FVALU] Between ([T2]![FVALU]) And (IIf([T2]![FVALU] Is Null,[T2]![FVALU],[T2]![FVALU])),"MATCH","DONOTMATCH")) AS MATCHORDOESNOT FROM T1 INNER JOIN T2 ON (T1.[OR] = T2.[OR]) AND (T1.RL = T2.RL);

Answer 1

使用LEFT联接表和ON子句中的所有条件：

SELECT DISTINCT T1.*,
IIF(T2.RL IS NULL, 'DONOTMATCH', 'MATCH') AS MATCHORDOESNOT
FROM T1 LEFT JOIN T2
ON T2.RL = T1.RL AND T2.[OR] = T1.[OR]
AND (T2.FVALU = T1.FVALU OR (INSTR(T2.FVALU, '*') > 0) OR (T1.FVALU BETWEEN T2.FVALU AND T2.TVALU))

结果：

RL  OR  FVALU   TVALU   MATCHORDOESNOT
R1  O1  3002            MATCH
R1  O1  3010            DONOTMATCH
R1  O1  3291            MATCH   
R2  O1  2000            MATCH