Join query to get ranking ofan employee

[Khatuaaccess]

Hi,

i have a master table where i have data for each employee on a monthly basis.

another table as job level below. i want to get the results in terms of access queries as per the below scenario

i want to check if there is a level change for an employee for a particular month, then if its a promotion or demotions.



scenario 1 - For IC category, if the level increasing from 1 to 11, or level changes from IC to PM, its a promotion
Scenario 2 - For IC, if the level decreasing from 11 to 1, then its a demotion.

Scenario 3 - for PM, if the level increasing from 1 to 8, or level changes from PM to IC with sr no uptp 7 in IC category then its a promotion.
scenario 4 - form PM, if the level decreasing from 8 to 1 with category PM then its a demotion

LSrNO	LCategory
1	IC
2	IC
3	IC
4	IC
5	IC
6	IC
7	IC
8	IC
9	IC
10	IC
11	IC
1	PM
2	PM
3	PM
4	PM
5	PM
6	PM
7	PM
8	PM

[orange]

Please add a link to the other post when you post the same question on multiple forums.
Cross posted at http://www.utteraccess.com/forum/ind...ic=2038503&hl=

Cross posting is fine, just let readers know---add a link.

Here's the rationale for this.