Managing a Seniority List - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Managing a Seniority List

Adam Deliso

2 years ago

I am trying to manage a seniority list for an upcoming job pick for 350 employees. My question is there a easier way to renumber the list when people retire? Also can access automatically add time intervals for each employee to pick over the 5 day pick. Lets say each employee gets 7 mins to choose and each day should start at 8:30 A.M. and end at 6:00 P.M. With two 15 min breaks and a 1 hour lunch during the day. Any ideas how I can do this thanks.

Adam Schwanz @Reply

2 years ago

The first part could be done with a recordset and a second field. This below and images show what I mean.

    CurrentDb.Execute "Update Table1 Set NewNumber=Null"

    

    Dim RS As Recordset

    Set RS = CurrentDb.OpenRecordset("Select * From Table1 Order By MyNumber")

    Dim Counter

    Counter = 1

    

    While Not RS.EOF

        RS.Edit

        RS!NewNumber = Counter

        RS.Update

        Counter = Counter + 1

    RS.MoveNext

    Wend

    

    RS.Close

    Set RS = Nothing

    

    CurrentDb.Execute "Update Table1 Set MyNumber=NewNumber"

	Adam Schwanz @Reply 2 years ago

	Adam Schwanz @Reply 2 years ago
The second part would be a little more difficult. When are the breaks and lunch time? Are they always the same? You could do it with a loop but you would need to exclude the times. Something like Total Time Spent might help you with excluding times from a calculation and might give you some pointers to doing it.

	Richard Rost @Reply 2 years ago
I do not understand either question. :(

	Adam Deliso OP @Reply 2 years ago
Yes the breaks are at the same time each day. Lunch is 12pm-1pm and a 15min break at around 3:40pm. I am trying to find an old list so I can snip part of it out to show. The seniority list part I think I have down with just using DCOUNT which seems to work for what I need. Thank you for your help.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View