Rotating list of names

[bkvisler]

Hi all. Sorry if this is a super easy question, but I'm a newbie. I'm trying to keep track of a list of names of people and there associated information. I would like the list to be able to rotate...by that i mean when a name or some names are removed from the top of the list, the names below and there associated information moves up and now populates the begining of the list. Is this a situaion for access, or am I barking up the wrong tree? how should I go about making this?
Thanks in advance for the help.


Brian

[June7]

Would have to update a field with some value that would be used to sort records. Perhaps a full date/time value. Assumes never more than one name gets hit on in a second. Sort by ascending, newest date goes to end. Would need code to update the field of existing record. A new record could default to Now().

[rpeare]

Can you give an example of what you want to do with before and after samples?

it would be much easier to guage what you're trying to do with an example.

[bkvisler]

Ok...More specifically, I have 40 employees who are assigned based on a rotating list. Example:
1 Bob
2 Tom
3 Jane
4 Sandy
5 Johnny
When an assignment comes in, one or many of these peope will be assigned, usually off the top of the list (but not always). They will be taken off the "Available" list and moved to an "Assigned" list. Then, all the people below those who were just assigned will move up on the list to fill the spaces left by those assigned.
When the assigned people return from their assignment, they return to the "Available" list, but on the bottom, in the same order that they left. There also would need to be a way to take people on and off the list without rotating to the bottom for if they have a day off or go on an assignment that gets cancelled.
Hopefully that helps clarify the question. Thanks in advance for the help everyone.

[June7]

I hope you are not actually moving records back and forth. Need a field that would be AvailableStatus (Yes/No) and also the date field somewhere. You have a separate table of Assignments and then a junction table to record StaffAssigned?

Why would 'go on an assignment' cancel the rotation?

[bkvisler]

What I meant by that was, someone may be assigned, but may get cancelled on the way there or something, or some assignments will not cause rotation, and a person will stay in their same place on the "available" list when they return. In that case, they would return to the list in their old position.

[June7]

Getting complicated.

Criteria:
Available - Yes/No
Rotate - Yes/No

Exclude Not Available from the recordset, then maybe sort by Rotate and appropriate Assignment Date.

If that makes sense, trick is how to accomplish.

Need to know more about your data structure. If you can provide project for review, attach to post, zip if large. Remove confidential info and run Compact & Repair first.

[bkvisler]

I wish I had a project to post. I am at square one...I dont even really know where to start. I am making a table with everyone's names now..

[bkvisler]

ok..I was able to get the tables from another database with much of the relevant information. I have attached them. Now, I just need to figure out how to take the names of people on the "personell_tbl" and put them in a rotating list as described above.

[June7]

Here is something to get you thinking. This query accomplishes:

1. excludes non-active personnel

2. determines latest assignment date

3. tests availability by checking if current day is between last assignment arrival and return dates, because all records have a return date, am assuming the return date is anticipated and entered before personnel actually return, if this is not the case then can exclude all personnel where return date is null

4. sorts personnel by last assignment arrival time

I have no idea how to consider the LastDayOff data.
To determine whether or not an assignment should be considered latest, would need something for criteria to restrict (incident type or a Yes/No field).

SELECT Personnel_tbl.JumperName, Personnel_tbl.LastDayOff, IncPersonnel_tbl.ArrivalDate, IncPersonnel_tbl.ReturnDate
FROM (Personnel_tbl LEFT JOIN (SELECT IncPersonnel_tbl.PersonnelID, Max(IncPersonnel_tbl.ArrivalDate) AS MaxOfArrivalDate FROM IncPersonnel_tbl GROUP BY IncPersonnel_tbl.PersonnelID) AS MaxAssignment ON Personnel_tbl.PersonnelID = MaxAssignment.PersonnelID) INNER JOIN IncPersonnel_tbl ON Personnel_tbl.PersonnelID = IncPersonnel_tbl.PersonnelID
WHERE (((IIf(Date() Between [ArrivalDate] And [ReturnDate],False,True))=True) AND ((IncPersonnel_tbl.ArrivalDate)=[MaxOfArrivalDate]) AND ((Personnel_tbl.ActiveStatus)=-1))
ORDER BY IncPersonnel_tbl.ArrivalDate;

Curiosity, two records in tbl_Personnel for Canceled, No smoke and Canceled, in flight.

[rpeare]

I have a very basic question

Are there rules in deciding who gets the next assignment. By rules I mean rules that do not change, are not subject to any human intervention and you could literally have a program assign people.

OR

Are you looking to have access offer the best possible options and have the user decide which people to send out?

If it's the former we'd need to know all the possible rules

if it's the latter, that's easier to handle (probably) but you still need some general rules

For instance
Priority 1 is when did the last assignment end
Priority 2 is when was their last day off

so let's say we had two people

Person A's last assignment ended on 1/1/2011 and their last day off was 12/1/2010 (mm/dd/yyyy)
Person B's last assignment ended on 1/1/2011 and their last day off was 11/1/2010 (mm/dd/yyyy)

Who gets the priority in your listing system?

I would assume it would be person A because they had a more recent day off.

Have you got a list of rules that will help you prioritize who appears on the top of your list other than assignment date ending and last day off?

[bkvisler]

Thanks so much for the responces.
@June7:
"Curiosity, two records in tbl_Personnel for Canceled, No smoke and Canceled, in flight. "
I'm not really sure why these are in here. The database where these tables came from was designed and is maintained by someone at the university of montana. I guess your guess is as good as mine.
@rpeare: We do have a list of rules that control our rotation however they are subject to constant human intervention. In the example you gave and in all situations, people go back on the list in the order they returned to the base and there place on the list is not influenced by their days off (only availability is affected by days off.) If two people go on the same assignment and return at the same time, they go back on the list in the order they left. If two people go on the same assignment, (or different assignments for that matter) and return at different times on the same day, the person who returned earlier goes on the list first.
To simplify...the list order is determined by the date and time you get back from assignment... but this database must have a mechanism for basically unlimited human intervention to deal with special situations.

Hope that helps... and thanks again for all the help.

[bkvisler]

one mor thing @ June7.. you wrote "I have no idea how to consider the LastDayOff data."
We track last day off because we have to manage days off. We try to ensure everyone gets a day off every 14 days and we make certain that people have 2 days off if they work 21 days in a row. This date is in the database because we use it to produce a report that shows when people need to be given a day off.

[June7]

Okay, easy enough to determine if that date is within 14 days but that doesn't confirm that they had 2 days off nor if they worked 21 days in a row prior to that day off.

This is going to require a lot more effort than I can spare. I suspect VBA code will be required to manipulate the data and check for all these 'rules'. The VBA could be functions called within the query that would return a Yes or No. Then sort or filter based on these values.

[rpeare]

I'm not in love with the data structure but is June's query giving what you want or do you need something else in that query.

I added this field

ActiveTime: DateDiff("d",[lastdayoff],Date())

Which shows the number of days since the person last had a day off. It's just a quick check rather than having to figure in your head when their last day off was.

A lot of the assignments in your IncPersonnel_tbl are very short durations (1 - 5 days). Is a person consider to be working even if they don't have activity assigned in this table every available day since their last day off?

Your Personnel_tbl is also not keeping track of the duration of their last time off which makes it next to impossible to perform any sort of query based on anything other than the last day off recorded.

Do you have any control over the structure of this database? If so I would recommend a sub table for your personnel_tbl that just tracks days off, all you'd have to have is their unique identifier and the date they had off (you can add a reason if you want too, sick, vacation, etc.) Then it would be far easier to calculate how many days they actually had off if it's important.