Access 2019 Query through 2 tables

user_2136407 0
Viewed 24

Table A

ID      Name        Pay     Colleage
1       aaa       15,000        1
2       bbb       25,000        2

Table B : Deduction Money

ID      Min      Max     Colleage1   Colleage2   Colleage3  Colleage4
1     10,001   20,000      1,000       2,000       3,000      4,000
2     20,001   30,000      2,000       3,000       4,000      5,000

I have to take each employee's deduction money in Table B.

Given Table B says, aaa's deduction money will be 1,000.

Because its range of pay is between 10,001~20,000 and it has 1 colleage.

So if i follow it on Table B. I can find the amount is 1,000.

As same way, bbb will be 3,000.

But I can't get the proper value.

I want query syntax to use for access 2019.

So help me pz.

ms-access join sql
1 Answers

You can join the tables and use CHOOSE() to select the correct deduction:

SELECT a.*, CHOOSE(a.Colleage, b.Colleage1, b.Colleage2, b.Colleage3, b.Colleage4) AS Deduction
FROM TableA AS a INNER JOIN TableB AS b
ON a.Pay >= b.Min AND a.Pay <= b.Max
user_104988280
Related