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?
CPMC UserAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
There could be several.
But it would rely on the rules for assigning the values which you don't specify in detail.
CPMC UserAuthor Commented:
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.
Gustav BrockCIOCommented:
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:

Position: PositionCat & CStr([FieldNameReturningRowNumber])

Open in new window

Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

CPMC UserAuthor Commented:
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],[EmpLCAT]![LCATID],True)
Gustav BrockCIOCommented:
Both keys must be strings, so perhaps:

Position: RowNumber(CStr([EmpLCAT]![LCAT]),[EmpLCAT]![LCATID],True) 

Open in new window


Else, feel free to upload a database with your sample data (anonymised, of course).
CPMC UserAuthor Commented:
I tried that and Position is still returning 0's
Gustav BrockCIOCommented:
And you modelled the query after the example in the article and code?
als315Commented:
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;

Open in new window

Gustav BrockCIOCommented:
I've tested this here.

Sample data:

EmpCat.PNG
Using this query:

SELECT 
    EmpID, 
    RowNumber(CStr([EmpID]),"") AS PositionID, 
    PositionCat, [PositionCat] & RowNumber(CStr([EmpID]),[PositionCat]) AS [Position]
FROM 
    EmployeeCategory
WHERE 
    RowNumber(CStr([EmpID])) <> RowNumber("","",True)

Open in new window

- basically as the example here (paragraph Implementation):

Example SQL

Result:

EmpPos.PNG

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CPMC UserAuthor Commented:
Sorry for the delay in getting back to you.  Great solutions to my issue.  Thanks for taking the time to help me out.
Gustav BrockCIOCommented:
You are welcome!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.