Employee Schedule/Database (1 Viewer)

J Sandman

New member
Local time
Today, 03:55
Joined
Mar 29, 2020
Messages
6
Hey everyone. This is my first post so forgive me if I confuse anyone (feel free to put me on blast).

I'm a crew scheduler at a private aviation company with over 170 pilots. I'm trying to use MS Access to develop a pilot database/scheduling system. I have a table of all the pilots that work for our company and I have a table that I've been trying to use to create a schedule for each individual pilot.

The biggest problem I have right now is that I don't know what the best way is to schedule a pilot to a day (or rotation of days) of work. My original thought was that I could have a table that assigns dates to fields; however, Access limits the number of fields to 255 (obviously 365 days in a year so that won't work).

My second thought was to assign dates to rows (records) on a table, then somehow link a pilot from the pilot table to the schedule table field that matched his ID.

I may be getting ahead of myself with some of the notes below, but so that you can gain some perspective of what I'm trying to do, here are some of the things I'm trying to accomplish with this project:

1. I only care about what dates pilots are working - they don't have set hours (8am-5pm etc...) so time is not relevant in the scope of what I'm trying to use this database for.

2. Because we have 170+ pilots, it doesn't make sense for me to have a calendar view that includes the name of every pilot. My eventual plan is to create a form or query that populates a calendar with the days an individual pilot is scheduled to work, then email that pilot with their schedule any time a change has been made.

3. Excel has some pretty simple shortcut features that allow you to copy and paste large amounts of data rather quickly but, so far, I have not been able to find any of this functionality in Access. It would be helpful if there was a way to quickly import what days a pilot will be working into the database so that I don't have to go 1 day at a time and type yes/no (Pilot is/isn't working).

4. Because our pilots can choose from two schedules (8 days on/6 days off or 15 days on/13 days off), I can forecast a pilot's schedule through the end of the year and make corrections as time progresses (when a pilot takes leave or calls in sick or works on his scheduled off days).

Please let me know if you have any thoughts or if you can help get me going in the right direction.
 

namliam

The Mailman - AWF VIP
Local time
Today, 09:55
Joined
Aug 11, 2003
Messages
11,543
1. if you have the times, it is a bad idea to destroy data on a general rule of thumb

2. fine whats the question? A continuation from 1 ?

3. It should be pretty simple to enter a start date and end date, then enter a schedule and automaticaly fill the table using a do while - loop construction.

4. is a continuation of 3?
 

J Sandman

New member
Local time
Today, 03:55
Joined
Mar 29, 2020
Messages
6
1. if you have the times, it is a bad idea to destroy data on a general rule of thumb

2. fine whats the question? A continuation from 1 ?

3. It should be pretty simple to enter a start date and end date, then enter a schedule and automaticaly fill the table using a do while - loop construction.

4. is a continuation of 3?
1. I don't already have times - just trying to emphasize the lack of a need for that data

2. No question - just trying to give perspective to whomever is reading

3. I have yet to learn about "do while - loop" I assume you're referring to macros

4. Maybe I shouldn't have used numbering
 

vba_php

Forum Troll
Local time
Today, 02:55
Joined
Oct 6, 2019
Messages
2,884
1. if you have the times, it is a bad idea to destroy data on a general rule of thumb
there's nothing wrong with destroying data permanently. the reason corporations store the stuff forever is of course because they're of getting their a$$e$ sued for one reason or another. if you do not have that issue, or for any other reason you don't need the data, ditch it. what could be the probem?
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:55
Joined
Sep 21, 2011
Messages
5,787
The biggest problem I have right now is that I don't know what the best way is to schedule a pilot to a day (or rotation of days) of work. My original thought was that I could have a table that assigns dates to fields; however, Access limits the number of fields to 255 (obviously 365 days in a year so that won't work).
You are thinking with your Excel head on, not an Access one.?
You would likely have one record for each date for each pilot, or perhaps a From/To setup.
In the first scenario, if a pilot works 250 days in a year, there would be 250 records. in the DaysWorked table.?

Certainly that would be my approach, not being that experienced. Others might recommend the From/To approach
 

namliam

The Mailman - AWF VIP
Local time
Today, 09:55
Joined
Aug 11, 2003
Messages
11,543
there's nothing wrong with destroying data permanently. the reason corporations store the stuff forever is of course because they're of getting their a$$e$ sued for one reason or another. if you do not have that issue, or for any other reason you don't need the data, ditch it. what could be the probem?
data can only be destroyed once, assuming it is the only source offcourse. Too often one finds, I have chosen not to process this data, however now I need it.
 

vba_php

Forum Troll
Local time
Today, 02:55
Joined
Oct 6, 2019
Messages
2,884
data can only be destroyed once, assuming it is the only source offcourse. Too often one finds, I have chosen not to process this data, however now I need it.
that is a consequence of poor planning. but yes, you are right. so the bottom line is that this person *should* store data because almost everyone in the entire world is uncertain about everything. that will end when the point gets reached when humans do not have to work anymore, if it does come to pass, but as of yet it has not.
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 02:55
Joined
Feb 28, 2001
Messages
16,843
The biggest problem I have right now is that I don't know what the best way is to schedule a pilot to a day (or rotation of days) of work.
Given its preference, Access "prefers" to list things sparsely. That means it only stores a record when it needs it. So your schedule is basically a list of records that might say "Chesley, 4-Feb-2020" and "Brad, 9-Feb-2020" and "Suzanne, 10-Feb-2020." Obviously you might include more info such as a flight number or a flight-plan number or something like that. But that is all that Access needs to say that pilot X is busy on day Y. And if there is no such entry, that pilot is available.

Your problem is that you want to devise a better interface to show you when a given pilot is flying OR maybe you want a list of pilots who could be assigned to take a flight on 9-Feb-2020. In my list of scheduled flights, you would want to rule out Brad because he's already scheduled, and you might wash to rule out Suzanne because that might require her to work more than x hours in a 48-hour period. (I have a pilot friend named Brad who tells me there are rules about consecutive flights depending on the size of the aircraft and you didn't specify sizes.)

This means that your issue is really two-fold - storage of data and presentation of data. Treat them independently. First decide what you need to store for any single scheduled pilot on a given day. Then as a separate issue, you can design your interface.

For example, let's say that my friend Brad's FAA rule apples. So what you would do is, for Suzanne, you would schedule her for three days - the day of the flight as a flight day, and the days before and after as regulation days. Three entries, not one. But for pilots where the rule doesn't apply (perhaps because of the size of the plane), you would only schedule a single day. So then, scheduling someone becomes easier. in any case, you look for pilots who DON'T have something scheduled on the day you want to schedule a flight.

Selectivity is something you haven't discussed yet - like, can the pilot only handle single-engine prop-driven? Or two-engine prop-driven? Or small private jets? In other words, qualification on flying a particular fuselage. That complicates the selection process but is no big deal, really.

Other factors you want to consider might include fairness, such as trying to rotate among all pilots with similar classifications, in which case you might wish to include the factor of picking a pilot with the greatest number of days since their most resent flight.

It is up to you to decide what you want to do. However, the concept of "scheduling" has come up on this forum many times. If you use the forum search facility (see right side of menu bar), search for "Scheduling" to get all sorts of ideas. NOTE that if you do, some of what you see will not be human scheduling but rather, motel room scheduling, or fleet vehicle scheduling, or classroom scheduling. But they are all surprisingly the same.
 

J Sandman

New member
Local time
Today, 03:55
Joined
Mar 29, 2020
Messages
6
Don't know if it will help, Its part of my Employee example you could make it so you could days etc
This was my original goal - to have a table like this. However, Access limits the number of fields to 255 (or is it 265?). I need to be able to track what a pilot is scheduled to do for every day of the year (365).
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:55
Joined
May 21, 2018
Messages
3,160
However, Access limits the number of fields to 255 (or is it 265?). I need to be able to track what a pilot is scheduled to do for every day of the year (365).
The data gets stored in rows with only a few fields and not columns. If you want to display it in columns you would transpose with a crosstab query or some other means.
The data gets stored as something like
pilotID
WorkdDate
StartShift
EndShift
other fields related to a work shift for that pilot
 

J Sandman

New member
Local time
Today, 03:55
Joined
Mar 29, 2020
Messages
6
Given its preference, Access "prefers" to list things sparsely. That means it only stores a record when it needs it. So your schedule is basically a list of records that might say "Chesley, 4-Feb-2020" and "Brad, 9-Feb-2020" and "Suzanne, 10-Feb-2020." Obviously you might include more info such as a flight number or a flight-plan number or something like that. But that is all that Access needs to say that pilot X is busy on day Y. And if there is no such entry, that pilot is available.

Your problem is that you want to devise a better interface to show you when a given pilot is flying OR maybe you want a list of pilots who could be assigned to take a flight on 9-Feb-2020. In my list of scheduled flights, you would want to rule out Brad because he's already scheduled, and you might wash to rule out Suzanne because that might require her to work more than x hours in a 48-hour period. (I have a pilot friend named Brad who tells me there are rules about consecutive flights depending on the size of the aircraft and you didn't specify sizes.)

This means that your issue is really two-fold - storage of data and presentation of data. Treat them independently. First decide what you need to store for any single scheduled pilot on a given day. Then as a separate issue, you can design your interface.

For example, let's say that my friend Brad's FAA rule apples. So what you would do is, for Suzanne, you would schedule her for three days - the day of the flight as a flight day, and the days before and after as regulation days. Three entries, not one. But for pilots where the rule doesn't apply (perhaps because of the size of the plane), you would only schedule a single day. So then, scheduling someone becomes easier. in any case, you look for pilots who DON'T have something scheduled on the day you want to schedule a flight.

Selectivity is something you haven't discussed yet - like, can the pilot only handle single-engine prop-driven? Or two-engine prop-driven? Or small private jets? In other words, qualification on flying a particular fuselage. That complicates the selection process but is no big deal, really.

Other factors you want to consider might include fairness, such as trying to rotate among all pilots with similar classifications, in which case you might wish to include the factor of picking a pilot with the greatest number of days since their most resent flight.

It is up to you to decide what you want to do. However, the concept of "scheduling" has come up on this forum many times. If you use the forum search facility (see right side of menu bar), search for "Scheduling" to get all sorts of ideas. NOTE that if you do, some of what you see will not be human scheduling but rather, motel room scheduling, or fleet vehicle scheduling, or classroom scheduling. But they are all surprisingly the same.
Thank you for this! A very comprehensive, helpful, and complete response.

Your statement: "you want a list of pilots who could be assigned to take a flight on 9-Feb-2020." almost hits the mark. "Available" in my world, only refers to a pilots rotation (in my case, the 8 or 15 days that a pilot is scheduled to work).

We have several categories that describe what a pilot did on a given day:
  • TRAINING (Simulator training)
  • LAYOVER (stayed in a hotel or waited at the airport all day without a flight assignment)
  • HOME STANDBY (self explanatory)
  • DUTY DAY (when a pilot sends in a flight log)
  • TRAVEL (Refers to a pilot's travel to/from their assigned aircraft)
  • LEAVE (This breaks down in to several other categories. i.e. BEREAVEMENT, PTO, SICK, UNPAID LEAVE)
Side note: I also need a BONUS DAY category that would indicate when one of the above categories are considered to be eligible for overtime. I could simply assign a code to each one of these categories and then modify that code to show its overtime eligible. For example a normal duty day would be "DD" but a bonus day duty day is "BD"

There's two phases to my scheduling process:
1. What days are the pilots scheduled to be working?
2. What did they actually do on a given day?

So for my purposes, I don't need to include flight information because we have a separate software that we use that encompasses all flight operations. That particular software was not designed to handle 170+ pilots which is why I have resorted to Access for my pilot scheduling needs. In terms of FAA regulations, the company operations software handles regulation compliance so there's no need to track flight activity other than to say "Pilot X flew" Which is why we use the DUTY DAY category.

My primary issue is formatting. What format should be used to store the dates that a pilot is scheduled to work? Should each available day be recorded individually or should I create a single cell entry that is a range of dates. My current line of thinking is leading me towards two fields such that:
RotationStart
RotationEnd

My concern here is that phase 2 of my scheduling process becomes more difficult. For example. Pilot X agreed to extend his rotation which was normally scheduled from 2FEB-9FEB, now he wants to work 2 days of overtime and I have to go digging to find the 2FEB-9FEB entry and change 9FEB to 11FEB. I think this concern is born of ignorance as I'm sure that there's probably a way to quickly find and edit information within Access.

Per your recommendation, I'm going to do some reading on this forum (using the search function). I really appreciate your feedback!
 

vba_php

Forum Troll
Local time
Today, 02:55
Joined
Oct 6, 2019
Messages
2,884
my goodness richard. my "likeableness" is certainly going down around here! maybe you should come follow me? =) (those are not my words, I'm sure you realize)
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 02:55
Joined
Feb 28, 2001
Messages
16,843
What format should be used to store the dates that a pilot is scheduled to work? Should each available day be recorded individually or should I create a single cell entry that is a range of dates
If you are using a "rotation" system, there are ways in Access to place pilot records, one per day, with a fixed status. You might find it conceptually easier to have a little bit of code to append records to a recordset for the fixed number of days. Each record would contain pilot's name/designation, date, and a flag AVAILABLE (separate status independent of your listed statuses) and then put nothing at all - or a status of NO ASSIGNMENT or whatever else is appropriate in the list from YOUR list of statuses. I might store entries like this:

Pilot - text or number, whichever you use to identify the pilot
WrkDate - the single date to which this status applies
Availability - flag or status, two obvious choices: TRUE = available, FALSE = not available
Assignment - one of the items from your list of possible statuses. It is up to you whether this is raw text or is a code-number for the statuses.

For sake of argument, call this "PilotStatus" table.

You will need a form to populate this only because you need a framework from which to run code, not because you would enter this by hand through some calendar interface.

You need three things on the form to set up a pilot's rotation. The Pilot's ID info, the starting date, and the ending date. Then you could make a button-click routine for a command button that you might call SETROTN ("set rotation", but who wants to type that much?) What I'm going to show you is the basic skeleton; you would need to flesh it out for error checking like "dates are in wrong order" or "one of the dates was blank" or "piolt already assigned" or other stuff like that. Let's also say that your list of statuses includes "NO ASSIGNMENT YET" or similar.

Code:
Private Sub SETROTN_Click()

Dim WkDate as Date
Dim WkRS as DAO.Recordset
    WkDate = Me.StartDate
    Set WkRS = CurrentDB.OpenRecordset( "PilotStatus", dbOpenTable )
    Do
        WkRS.AddNew
        WkRS!Pilot = Me.PilotID
        WkRS!WrkDate = WkDate
        WkRS!Availability = vbTRUE
        WkRS!Assignment = "NO ASSIGNMENT YET"   'or whatever code corresponds to this status if you do not use literal text statuses
        WkRS.Update
        WkDate = DateAdd( "d", 1, WkDate )
    Loop Until WkDate > Me.EndDate
    WkRS.Close
    Set WkRS = Nothing
End Sub



Once that runs without error, you now have a set of continuous days where the pilot is available with no assignment (yet). Then, to set up a schedule, it becomes a matter of writing a query to find pilots with AVAILABILITY status of TRUE on a given day. If you select a pilot, change the status to SCHEDULED (i.e. AVAILABILITY=FALSE) and select the type of event for which they are scheduled (from YOUR list of things they can do).

NOTE: The above code DOES NOT CHECK for the pilot already being scheduled. You might want to test that before allowing the click to do anything. What I have just shown you is a IDEA of how to proceed, but you would have to fill in the bells and whistles yourself.

You asked how you might extend a pilot's rotation. Simple. Use this method with the dates of the EXTENSION (not the whole rotation). In other words, place a second rotation adjacent to the first one.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:55
Joined
May 21, 2018
Messages
3,160
This may give you some ideas, and shows another interface. This is one of the most extreme forms I have built in Access.

YearView.jpg
As you can see in each day you can have multiple activities each with a different color code. To add or edit double click on that day. The underlying data is stored in some table similar to @MickJav version. The code is very encapsulated so building of this takes very little editing. I built this with @oxicottin and it has been real popular on multiple Access forums. I see it referenced often.
 

Attachments

J Sandman

New member
Local time
Today, 03:55
Joined
Mar 29, 2020
Messages
6
This may give you some ideas, and shows another interface. This is one of the most extreme forms I have built in Access.

View attachment 80328
As you can see in each day you can have multiple activities each with a different color code. To add or edit double click on that day. The underlying data is stored in some table similar to @MickJav version. The code is very encapsulated so building of this takes very little editing. I built this with @oxicottin and it has been real popular on multiple Access forums. I see it referenced often.
This looks like exactly what I needed for one aspect of scheduling. Thanks!
 

J Sandman

New member
Local time
Today, 03:55
Joined
Mar 29, 2020
Messages
6
I thought it might be helpful if I included some screenshots of what I'm currently using to schedule pilots. Its basically a combination of proprietary software developed by our IT department and google sheets.

Screenshot (46).png

On the left-hand side is the proprietary software that we use. I need to duplicate this type of record in Access without necessarily visualizing the data as the primary method of input.

You can see in google sheets that I have multiple tabs/sheets that include pilot names for each fleet.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom