Ideas for a 'Time Sheet' application needed

[Kactus]

Hello folks,

I’m trying to build an application in ASP and MS Access for my local volunteer fire brigade which allows firefighters to logon and indicate their availability over the coming time X number of days. Tracking down people to make up crews over the last Christmas and New Year period highlighted some issues that a more automated way of generating lists of available people could resolve.

I have a ‘vision’ of something on the screen like a spreadsheet with days on one axis and time slots (morning, afternoon, evening) on the other but then in the database a third dimension indicating the firefighter’s name. I can’t seem to come up with a satisfactory way to achieve this, however.

I had thought that functionally-speaking the personnel officer would ‘create’ a range of dates he was interested in getting firefighters availability for but some kind of automatic, rolling schedule might also be good.

Obviously the personnel officer would need to be able to collate the data into some kind of report from which he could put together a crew during a fire.

I have a ‘firefighter’ table with various contact details: I didn’t know whether to go for an ‘availability’ table to hold the date ranges and then have a junction table to build the links or what.

Any help with this problem concerning any approach would be appreciated.

Thanks,
Dave

 

[ChrisHirst]

I would go for two extra tables, one with the date ranges and one that linked personnel IDs to the ranges

Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[emozley]

I have been working on a boardroom booking system that is fairly similar. I would have thought your best bet would be to have the individual firemen's names across the top and the day broken down into chunks on the Y axis. This would make it easier to identify periods that needed extra resources allocated.

In this example instead of people's names I have got 10 boardrooms and in the table I have designed it to display from 8am until 6pm. Because this period of 10 hours is divided into 15 minute sections the array we have 40 slots in the array.

<%

Dim RoomsTaken(10,40)

For Room=1 to 10
TBL.Open "SELECT StartTime, EndTime, MeetingID FROM Meetings WHERE Date=#" & ViewingDate & "# AND RoomID=" & Room & " ORDER BY StartTime ASC", DB

Do While Not TBL.EOF

StartHour=DatePart("h", TBL("StartTime"))
StartMinute=DatePart("n", TBL("StartTime"))
EndHour=DatePart("h", TBL("EndTime"))
EndMinute=DatePart("n", TBL("EndTime"))


start=((StartHour*4)+Int(StartMinute/15))-32
finish=((EndHour*4)+Int((EndMinute-1)/15))-32

For i=start to finish
RoomsTaken(Room-1,i)="T"
Next

TBL.MoveNext
Loop

TBL.Close
Next

%>

So, if a meeting goes from 8am-10:30am we get the following values:

StartHour=8
StartMinute=0
EndHour=10
EndMinute=30

start =((8*4)+Int(0/15))-32 = 0
finish=((10*4)+Int((30-1)/15))-32 = 8

This means slots 0-8 in the array can be marked as "T" then all you have to do is use a nested loop to draw the table. Use counters to keep track of what cell in the table you are drawing then look up that value in the array to decide whether or not to mark the cell in the table. Don't forget arrays start at 0 not 1.

You could make it so that the user can view the same information in different ways. For example you might want names across the top and time down the side and that view would be for a particular day or you could make it so you could have days across the top, time down the side and each cell would have the number of people in at that time.

All you have to do is run through the database perform a calculation for the data given and then put that value into the array.

Good luck!

Ed

 

[emozley]

Instead of having a room ID you would have a user ID which would tie in with the second table that ChrisHirst suggested.

 

[Sheco]

Suppose, in addition to firefighting, you have 2 other specialized "skills" needed for every shift: Truck Driver and EMT...

Now suppose that some volunteers have one of these skills, some have both, but some have none and that each shift should have at least one person with each special skill...

 

[emozley]

How about...

In the Personnel table include two yes/no fields - one for truck driving and one for EMT. Change the SQL select statement to to include whether that firefighter can do either of those skills.

If go through the recordset writing who is on call for that time period to one array then write the truckdriving skills value to one array and the EMT value to another array.

Once that's all sorted start building the table and in each cell try something along the lines of:

Response.Write("<td>")
If TruckDriving(a,b)="0" Or EMT(a,b)="0" Then
Response.Write("!")
Response.Write("</td>")

a and b would be part of a nested loop - so that a) it's actually displaying the HTML table and b) you can keep track of where you actually are in the table.

How does that sound? Or is there a way of storing more than one value in an array so you could have three values in one array which would make it quite a lot more efficient...

 

[Sheco]

Oh, another idea... since it is a volunteer thing you might want to add some logic to even the work out between the firefighters... such that the first people chosen for each shift are the ones that have had the most time since last shift or somesuch...

 

[theniteowl]

It sounds as if you are going to have to rely on the individuals to tell you when they may be available.
My recommendation is to only track when they are NOT available. This way would require far less entries into the database and far less work by the individuals to give you the information. They just provide you with dates/times they can NOT be assigned a shift which should be a much smaller amount of data to have to enter and search through when generating your lists.

If you track the amount of time they do work in a database then it will be easy to use that information to bubble names up to the top of who should be called to fill in a shift so you get more balanced shift assignments like Sheco is saying above although he talks about time in between shifts rather than just overall time worked, using both would be beneficial so people that just worked recently are lower on the list and people that have worked the most hours YTD would be lower on the list.

I think emozley's method of displaying should work well for you if you want to see a visual representation of everyones availability. It is the same approach we have taken here to do the same type of room booking application and seems to work well.

If all you want though is to list names of those that are available to work and rank them by who should be called first based on past work efforts then you will not need anything that complex, just a list of names for a specific date.

You may want to account for differences between availability for part of a day rather than an entire day as well so if you listed names out it would show availability time frames for the date in question beside the name.


Stamp out, eliminate and abolish redundancy!

 

[Kactus]

Thanks for all your thoughts and suggestions.

As Sheco says there are a number of skills/qualifications that have to be considered eg. each crew needs someone qualified as Crew Leader (CL) and someone who is a response-driver (RFD) – then there’re optional CABA (compressed air breathing apparatus) operators for entering buildings, First Aiders, specialist tool operators etc, etc, etc. While not preferable some of these functions can be doubled up, which makes it harder to code the sort of logic Theniteowl suggests. Eventually you need a person (the Personnel Officer) to sort out the crews.

Theniteowl’s suggestion of tracking when people AREN’T available was very interesting but after some thought I’m going to lean towards an ‘Opt-in’ system rather than an ‘Opt-out’ system. That way while we’re secretly hoping people will be available we don’t just assume it and people have to explicitly say yes, I’ll be there. Thankfully volunteers are very generous with their time.

Emozley, thank you for your sample code but I think I’ve talked myself out of using arrays and will go to a simpler DB structure:

[u]Availability Table[/u]
PK  Date      (Generated by the page when viewed)
PK  ID        (PK From the Firefighter table)
    Morning   (Y/N, default N)
    Afternoon (Y/N, default N)
    Evening   (Y/N, default N)

150 people in the Brigade by 90 days into the future – the table shouldn’t be too unwieldy so long as I build some record keeping functionality into it.

Thanks again for your help,
Dave