a pointer needed

[SDB]

morning all,

I have 59 employees working a seven day week rota over a four week period, so at the end of the fourth week there rota reverts back to the first week for another four weeks.

my question is how to i put this into a table with out having 25000+ records eg
employeeName
rotadate
Attendance, 25000 + times???


the reason its like this is beacuse we need to know whos working, holidays etc through out the year.

cheers for any pointers

 

[john471]

Im not sure what a "rota" is. Can you please elaorate on that a little. Also, are you wanting to determine the hour (or time) at which a given employee should commence work on any nominated day? Or are you trying to track whether or not individuals actually start, and on time, for every day?

 

[SDB]

i have a staff rota to see whos working and whos day off / on holiday etc, all I need to do is show who's working/day off for a certain day or week. which i can do via a query its just how to put the data into a table without having 25000 plus records that leaving me stumped.

 

[john471]

I'm thinking that adding in individual staff member's holidays adds an extra degree of difficulty; unless they are somehow built into this "rota" thingy. Can you paint a bit of a picture? e.g. if I were to guess a it (not including holidays)....

Line | Mon | Tue | Wed | Thu | Fri | Sat | Sun
---------------------------------------------
001 | off | off | wrk | wrk | wrk |wrk | wrk
002 | wrk | wrk | off | off| wrk | wrk | wrk
003 | wrk | off | wrk | wrk | off | wrk | wrk
004 | wrk | wrk | wrk | wrk | wrk | off | off

Does that resemble what you are working with?

If so, you have one roster. The roster has 28 "cells". Each employee begins their assignment to the roster on a particular cell on a specified date. There after, their position on the roster on any nominated date is a function of the date and cell they started on, the number of cells in the roster, and the nominated date.

You would need a table to track the assignment date/cell/employee details (make sure you don't assign someone to a "Wednesday" cell on a date that is not a "Wednesday", or everything will go amuck.) and two other tables to hold the roster data. Assume you already have a tblEmployee with EmployeeID.

tblRoster
--------
RosterID (PK)
Roster Name (Text)
(you will only have one entry in this table)

tblRosterCell
------------
RosterCellID (PK)
RosterID (FK)
RosterCellIndex (Integer - between 1 and 28, in your case, to record its position on the grid. 1-7 are on line 1, 2-14 are on line 2 etc)
WorkOrOff (text)

tblRosterCellAssignment
-------------------
RosterCellAssignmentID (PK)
RosterCellID (FK)
EmployeeID (FK)
AssignmentDate (Date/Time)

I would also use a table to project data out for a year. Assuming you want to only ever project forwards, this table would have 366 records in it, with the numbers 0 to 365

tblOffset
---------
Offset (Integer)


The rest can be done with queries and probably a custom function.

HTH

Regards

John

 

[SDB]

cheers john, thats given me some ideas, the problem I was making was basic..Instead of using multiple tables I was trying to put the info into one(schoolboy error)

again cheers

Steve..