How to get Leave Entitlement Day with MIN & MAX working years in Query 1

[RaffiqEddy]

Hi,

I wanted to captured number of day for leave entitlement, based on years working, below are some examples:

=>1 and <2 years working = 15 day leave entitlement
=>2 and <4 years working = 17 day leave entitlement
=>4 and <5 years working = 21 day leave entitlement

I want the above condition to be stored into Table and use SQL Statement to get the right entitlement (after passing value of years working.)

The reason why I want to store the value above is because, I want the user able to change the year or the entitlement value in table without need to recoding the program.

To try this solution, I created a table “LeaveEntitlement” with structure like so:

LeaveType MinMonth MaxMonth LeaveEntitlement
Annual 12 23 15
Annual 24 47 17
Annual 48 59 21

Using Query I want to retrieve the info from a table where I can just pass the value simple as this:

Select all the fields
From table LeaveEntitlement
Where Between MinMonth and MaxMonth = MonthOfWork(example 26)

The result should be 17..


Is this possible?
Am I in the right path?

Please help!

TIA

 

[PHV]

Something like this ?
SELECT * FROM LeaveEntitlement WHERE 26 BETWEEN MinMonth And MaxMonth

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RaffiqEddy]

PH,

You make my day!

Thanks a million..

Regards.

Ps: A STAR FOR U TOO