CPMC User
asked on
Need help solving how to programmatically assign positions to employees
I have a need to assign employees to positions. Lets say I have table1 with EmpID and PositionCat
Table1:
EmpID PositionCat
1 A
2 A
3 A
4 B
5 C
6 C
Then we have Table2 with PositionID, PositionCat, Position
Table2:
PositionID PositionCat Position
1 A A1
2 A A2
3 A A3
4 B B1
5 C C1
6 C C2
What I want to do is programmatically assign the employee to the position in Table3.
Table 3:
EmpID PositionID
1 1
2 2
3 3
4 4
5 5
Any ideas on the best way to handle this?
Table1:
EmpID PositionCat
1 A
2 A
3 A
4 B
5 C
6 C
Then we have Table2 with PositionID, PositionCat, Position
Table2:
PositionID PositionCat Position
1 A A1
2 A A2
3 A A3
4 B B1
5 C C1
6 C C2
What I want to do is programmatically assign the employee to the position in Table3.
Table 3:
EmpID PositionID
1 1
2 2
3 3
4 4
5 5
Any ideas on the best way to handle this?
ASKER
Basically I just want to increment the the Positions for anyone in that position category.
If there are 3 employees who are assigned to position category A. I want their position assignments to be as follows: one of them to be A1, one to be A2 and one to be A3. It doesn't matter who's assigned to which position. I was thinking that it might just be a loop that looks at the count of positions for that category and then just sets the position with the position name and concatenate a sequence number. The sequence number should be reset after each category.
If there are 3 employees who are assigned to position category A. I want their position assignments to be as follows: one of them to be A1, one to be A2 and one to be A3. It doesn't matter who's assigned to which position. I was thinking that it might just be a loop that looks at the count of positions for that category and then just sets the position with the position name and concatenate a sequence number. The sequence number should be reset after each category.
OK, then you can use my RowNumber function from my article and demo:
Sequential Rows in Microsoft Access
It allows for a GroupKey which would be PositionCat, and then the Position could be created this way:
Sequential Rows in Microsoft Access
It allows for a GroupKey which would be PositionCat, and then the Position could be created this way:
Position: PositionCat & CStr([FieldNameReturningRowNumber])ASKER
I'm sorry, but could use give me the usage if used in a query.
The GroupKey would be [LCATID], Reset is True. The guess the field name returning the row number would be the autonumber field which is PositionID.
How would I call your function in a query, because this doesn't seem to be working:
Position: RowNumber([EmpLCAT]![LCAT]
The GroupKey would be [LCATID], Reset is True. The guess the field name returning the row number would be the autonumber field which is PositionID.
How would I call your function in a query, because this doesn't seem to be working:
Position: RowNumber([EmpLCAT]![LCAT]
Both keys must be strings, so perhaps:
Else, feel free to upload a database with your sample data (anonymised, of course).
Position: RowNumber(CStr([EmpLCAT]![LCAT]),[EmpLCAT]![LCATID],True) Else, feel free to upload a database with your sample data (anonymised, of course).
ASKER
I tried that and Position is still returning 0's
And you modelled the query after the example in the article and code?
You can also use builtin dcount function:
SELECT Table1.PositionCat, Table1.EmpID, DCount("EmpID","Table1","EmpID <= " & [EmpID] & " AND PositionCAT = " & Chr(34) & [PositionCAT] & Chr(34)) AS [Position]
FROM Table1
ORDER BY Table1.PositionCat, Table1.EmpID;ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry for the delay in getting back to you. Great solutions to my issue. Thanks for taking the time to help me out.
You are welcome!
But it would rely on the rules for assigning the values which you don't specify in detail.