Hi All...
I have a problem here and I wish some of you can help me out.
I'm at very new stage in MS Access and my English is not that good.
I've created a table name tblentitlement ( table - entitlement )
Fields :
Jobgred 0to3 3to5 5to10 - (this is year service)
JG1 12 15 17
JG2 14 16 18
Then I have a staff table
StaffNo Jobgred DateJoint
0001 JG1 1-6-2011(d-mm-yyyy)
0002 JG2 1-3-2007(d-mm-yyyy)
0003 JG2 1-7-2001(d-mm-yyyy)
What I want to do is a query that can give me a correct figure related to the jobgred and datejoint
The basis of year calculation will be early of the year example 1-1-2011(d-mm-yyyy) and later can be change to 1-1-2012(d-mm-yyyy) and so on when the time comes
Base on calculation
For staffno 0001 related to JG1 and less 3 years service (since he joint 1 june 2011 and the year starts at 1 Jan 2011) he will entitle 12days leave but in the same time his leave will be prorated since he joint in May so the result will show 7 days [(7/12)*12=7] - 7month divide by 12months times 12day equal to 7 days
For staffno 0002 related to JG2 and less 5 and more than 3 years service he will get 16days ( no need prorated since he serve more than 1 year)
For staffno 0003 related to JG2 and less 10 and more than 5 years service he will get 18days ( no need prorated since he serve more than 1 year)
Can anyone help and in the same time I am zero knowledge about VBA ( still reading the Dummies Book to improved)
Your time reading and helping is highly appreciated