Advice needed - Employee shifts database (1 Viewer)

[diberlee]

Hi,

I'm looking for a little advice on how to set up part of a database. I've been asked to make one to keep details of around 300 employees, including their shifts. Their shifts is the main thing that we will be pulling from the database as this is to be used for resource planning.

The problem is that not everyone works a regular shift (e.g start at 08:00, lunch at 12:00 home at 16:00) so the database needs to be able to store irregular shifts (e.g start at 10:45, have 20 minutes of their lunch break at 13:30 and the other 40 minutes at 15:30 then go home at 6:30) We also have some staff who work a part shift in the morning, then a part shift in the evening of the same day.

We ideally want to output staff shifts in a useful format so they can be used in our Excel sheets to show which staff are available at certain times. The Excel sheets show staff shifts in a grid, so for each 30 minute interval of the working day they will have a 1 if they are available, a L if they are on lunch and some other codes to show otherwise unavailable.

I've got a few ideas for how we can do this but I'm not sure if any of them are very good or easy to work with.

1. Store the shifts for each agent on a per day basis in a text field in a format like:
"SS10:45 LS13:30 LE13:50 LS15:30 LE16:10 SE18:30" (SS = shift start, LS = LS etc...)
2. Store the shifts for each day like this:
"0000000AAAAAAAALAAAALLAAAA0000" (0 = not working, A = available, L = lunch)
3. Have several fields for each day like [MondayStart], [MondayLunchStart],[MondayLunchEnd],[MondayLunchStart2], etc...

I can see a reason for doing it any of the 3 ways, but I don't think any of them are ideal. I'm hoping somebody has already had to make something like this and has some better ideas for how to achieve what I need. How would you do it?

I should add that I'm ok with VBA code, so exporting the info to Excel isn't a big issue. The main thing is being able to store these weird staff shifts and display/update them without too much trouble.

Thanks in advance
Duane

 

[Frothingslosh]

How about a Shifts or Workday table (something like that) tied to the Employees table, containing the employee, the date, the time in, the time out, and any other things specific to that shift. Then join it to a Breaks table, where you might have just ShiftID, BreakType, BreakStart, and BreakFinish. You would either use a BreakID pk or an index over shiftID and the break times as a pk.

Then for reporting, you can just determine the total time covered by the shift, and subtract the total time covered by unpaid breaks. You would need to include a routine that checks the breaks when they're entered to make sure they don't overlap, just to play it safe.

There may well be other, more efficient methods - this is just what sprang to mind while I wait for the caffeine to make it through my system.

 

[diberlee]

Thanks for that, I think a separate table is definately the way to go and your idea there is definitely better than anything I've come up with so far.

Just want to make sure I've understood you right...

So for a simple member of staff I would have a an entry in the staffList table, and 1 entry in the staffShifts table showing he works 9-5 every day. Then a separate staffBreaks table would show he takes his lunch at 1pm every day.

For a more complicated user I would again have them listed in the staffListing but 5 entries in the staffShifts detailing a differing shift on 5 days of the week, and 5 entries in the breaks table showing he takes a break at whatever time on each of those 5 days.

Is that the sort of set up you had in mind?

Cheers

 

[Frothingslosh]

Now that I'm more awake, what I'd do would be this:

Then in the Shifts table, I'd include a Shift ID, the employee ID (thus linking it to each employee), a DayOfWeek field (restricted to 1-7 for Sun-Sat or 1-5 for Mon-Fri), and StartTime and EndTime.

In the Breaks table, I'd link it to the shiftID, and add BreakType, BreakStart, and BreakFinish (that allows for any kinds of breaks you may need - lunch, unpaid break, splitting of shift, etc). BreakType would be another small table with just an ID field, the name of the break type, and whether it's paid or unpaid.

If break type isn't needed, just ignore it completely.

The result would be that each employee would get one record in the shifts table for each day they are scheduled to work. During creation, you could include a function that duplicates a completely created shift to one more more other days of the week, or you could assume that any shifts entered only once are actually M-F. That's up to you. I'm paranoid enough to prefer the multiple listings, but that DOES take up more space.

If you wanted a history of what shifts someone was scheduled for, you could even replace the DayOfWeek field with a Date field (and maybe add pay rate), but that would have to be maintained weekly, and it doesn't sound like you need that kind of data.

Am I making sense? It's early enough and I'm distracted enough running reports that I would be far from surprised if I were babbling.

 

[diberlee]

Yes, it makes perfect sense, and I think it'll work perfectly for our needs.

I made the tables already and they're pretty much exactly as you described so that's good - I've decided to add a Mon-Fri checkbox in the Shifts table, since most people work the same shifts each day of the week I'll use this to indicate that the shift entered applies to all days.

I like your idea to treat the period between split shifts as an unpaid break... That was the main thing that had me flummoxed, but it's going to be really simple to work with if we do it like that.

We won't need any historical data - maybe a comparison of new shift to current shift at the time we consider if we can allow a shift change - but beyond that it will be redundant.

Thanks a lot for your help, I'm feeling a lot more confident about getting this done than I did this morning!

 

[Frothingslosh]

Glad to hear it!

You know, to be honest, there's no reason you can't have multiple shifts scheduled for the same day in that shifts table. You'd just need to insert a check to make sure that there's no overlap, but that wouldn't be hard.

It might make your reporting easier, as well, as you don't need to do a second check for a split shift when determining availability or staffing requirements.

 

[diberlee]

I'll keep that in mind, but for now I think I'll go with your initial suggestion as I think it will be easier to update the shifts if/when they change. All that will have to be through forms and as simple as possible as non-technical people will be maintaining it... Scary, I know.

 

[diberlee]

Quick (hopefully) question if you wouldn't mind. I'm just tinkering with how I'd get an FTE figure and I've got a bit of a hitch. I've set the tables up as you described and added the relationships. I've added a person to the TeamListing table, added shift details for a Monday, and added a 20 minute break and a 40 minute break for Monday.

I set up the query to calculate shift length in one field, unpaid break length in another and then one more to do a sum on the 2 figures. (I will join to one statement in time, just tinkering for now)

The problem is the query returns 2 separate lines for the same person rather than joining them to one entry. One shows the 40 minute break and the other shows the 20, when what I need is one record showing 60.

I've tried adding totals but that causes my calculated fields to ask for an input so I think I'm doing something wrong somewhere.... Any ideas what it is?

 

[Frothingslosh]

Off the top of my head, and without seeing the query, it sounds like you just linked it to the breaks table. You're actually going to need a subquery that adds up all the unpaid breaks for each ShiftID, and then use THAT query rather than the Breaks table.

Edit: Remember, domain functions tend to be slow, so avoid them if you can. That's why I suggested stacking queries instead.

Also, I assume by 'sum' you mean 'difference'? You're not going to want to add the unpaid breaks to the shifts, since the former happen at the same time as the latter.

 

[Frothingslosh]

Okay, what I think you'd be looking for would be something like this. Obviously, you'd need to adapt it to your actual system, but it works right in the test db I just threw together.

SELECT tblShifts.ShiftID, DateDiff("n",[ShiftStart],[ShiftEnd]) AS ShiftLength, DateDiff("n",[BreakStart],[BreakEnd]) AS UpaidTime
FROM tblShifts INNER JOIN tblBreaks ON tblShifts.ShiftID = tblBreaks.ShiftID
GROUP BY tblShifts.ShiftID, DateDiff("n",[ShiftStart],[ShiftEnd]), DateDiff("n",[BreakStart],[BreakEnd]);

Obviously, as written it won't list any shifts that don't have breaks, so if you have no-break shifts at all, you'll need to change it from an inner join to a left join.

 

[diberlee]

Thanks for taking another look at that - I left work just after I posted my last message so I didn't check back for replies until now. I'll have another stab at it later on and come back to you either way. Cheers

 

[diberlee]

Hi again,

The query you posted works great for some scenarios - but not so well on days where I put in more than one break. In those cases it counts the logged in time twice. I've tinkered with some things but haven't managed to get it working how I need it to so far.

It's clear that I need to do a bit more reading before I tackle this. I've done some databases before but only fairly simple ones. I'm sure this counts as a simple one for most people on this board... I've no idea what JOINS are in SQL statements, or domain functions/stacking queries which you mentioned earlier.

I'll attach a copy of my database if you wouldn't mind having a look to identify what the issue with that query is

 

[Frothingslosh]

I will have to check it out this weekend - I can't actually download files to review them at work. It's odd that you're having that problem, however - when I made the query, I actually built a small test database with Employees, Shifts, and Breaks, and made a point to test shifts with multiple breaks. You should be getting one line for each shift, with the total shift length and total break length in minutes.

Edit: Or I thought I did... Try this. Make a select query and add Shift ID, BreakID, and a calculated field, BreakLength. For BreakLength, use DateDiff to determine the length of each break.

Save that query, then make a second one, using the first as a source. For THAT one, turn on grouping, then add ShiftID and BreakLength. You want to Group By ShiftId, and Sum BreakLenght.

Save THAT query, and create third query. In THAT one, your sources are tblShifts and Query#2. At a minimum, you need to add the ShiftID, a calculated field using datediff to determine the lenght of the shift, and the SumOfBreakLength field from Query #2.

If that doesn't work, I really will have to check it out this weekend.

 

[diberlee]

That's done the trick up to a point, I still get duplicates on my final query due to having data in for more than one day, but I see now how you would tackle that, and what you meant by stacking queries.

I'll carry on tinkering, don't waste your weekend trying to clean up my mess

I'll post back if I hit any more snags, if you don;t mind... ?

 

[Frothingslosh]

Sure thing, and don't worry about taking up time over the weekend. If I didn't like working with Access, I wouldn't be posting here.