Updating records based on different criteria 1

[angflem]

Hi!
I am new to VB and really don't know that much about it. I need create a module that will assign agent schedules based on seniority numbers and shift bid choices. For example, 13 shifts are available, agent with seniority number 1 gets their first choice of shift #2. Agent with seniority number 2 chooses shift#2 as their first choice and shift #6 as their second choice. They would be assigned shift #6, since shift#2 was already assigned to a higher seniority number. And so on through all available shifts. This is the table I have to work with, it shows the choice number (BidNo) for each seniority (SenNum) and the shift (SchedNo) for each.

SenNum BidNo SchedNo
1 1 1
2 1 3
2 2 5
3 3 8
3 2 11
3 1 13
4 1 1
4 2 4
4 4 11
4 3 13
5 5 1
5 1 2
5 2 3
5 3 6
5 4 8
6 6 3
6 5 5
6 4 7
6 3 9
6 2 11
6 1 13

I'm sure this is probably something pretty easy to do but I am clueless! Please help!! Thank y ou!!!

 

[Remou]

Do you have a shift assigned field or shift assigned table that shows, or can show, which shifts have been assigned?

It seems to me that you need a number of queries that select each seniority level and a shift where the shift has not previously been assigned.

 

[angflem]

I was thinking this same table could be used (or a new one created) and a field added to it for shift assignement. For seniority 1, the shift assignment would be 1, sennum 2 the shift would be 3, sennum3, shift would be 8, etc...and each time it goes to the next sen num, it will look to previous records to see what shift assignment has already been given. Are you saying that I need to have multiple queries in the database to do this instead of writing it in a module somehow?

 

[Remou]

The queries can be part of the module. Perhaps something like:

strSQL = "UPDATE tblT SET Assigned = SchedNo " _
& "WHERE SenNo=1 AND BidNo=1"
CurrentDb.Execute strSQL

strSQL = "SELECT DISTINCT SenNo FROM tblT " _
& "WHERE SenNo>1"

Set rs1 = CurrentDb.OpenRecordset(strSQL)

Do While Not rs1.EOF
    strSQL = "SELECT * FROM tblT " _
    & "WHERE SenNo=" & rs1!senno _
    & " AND SchedNo Not In ( " _
    & "SELECT Assigned FROM tblT " _
    & "WHERE SenNo<" & rs1!senno _
    & " AND Assigned Is Not Null) " _
    & "ORDER BY BidNo"
    
    Set rs2 = CurrentDb.OpenRecordset(strSQL)
    rs2.Edit
    rs2!Assigned = rs2!SchedNo
    rs2.Update
    
    rs1.MoveNext
Loop

 

[angflem]

This is great thanks!!!
Seems to be working with one exception, when I get to this step:
Do While Not rs1.EOF
I get a Do without Loop error?
It seems that it's only doing the first and second SenNo's and not going through the rest of the list.

 

[Remou]

Please post the code as modified for your application.

 

[angflem]

I was running through it in the immediate window and would get that error, when I assigned a macro to the function and ran it worked fine. I really do appreciateit, you've been a great help!! Thank you!!!