Employee Availability and Scheduling... Excel and Access

[arfox]

Hello there!

I am a computer science major new to database development. I have volunteered to develop a database for my employer to store employee availability and allow supervisors to view employee availability and schedule them accordingly.

Right now, they are using an excel workbook... one spreadsheet per employee with one week represented on each page. (days are the columns, times are the rows- 24/7 ) An employee is "available" if the cell is not colored, "preferred" if the cell is colored yellow, and "unavailable" if the cell is colored red.

I have had great success representing all other employee data (contact info, etc) with MS Access... but can't for the life of me figure out how to represent the employee's availability! I think I'm thinking in terms of Excel and not Access.

Any ideas?
Much thanks!!

 

[abezuide]

Hello there!

I am a computer science major new to database development. I have volunteered to develop a database for my employer to store employee availability and allow supervisors to view employee availability and schedule them accordingly.

Right now, they are using an excel workbook... one spreadsheet per employee with one week represented on each page. (days are the columns, times are the rows- 24/7 ) An employee is "available" if the cell is not colored, "preferred" if the cell is colored yellow, and "unavailable" if the cell is colored red.

I have had great success representing all other employee data (contact info, etc) with MS Access... but can't for the life of me figure out how to represent the employee's availability! I think I'm thinking in terms of Excel and not Access.

Any ideas?
Much thanks!!

You can do the same in access as in excell. Make a textbox next to the name and code the background property to change color on availability, if you want some code, just ask we can send

 

[arfox]

You can do the same in access as in excell. Make a textbox next to the name and code the background property to change color on availability, if you want some code, just ask we can send

Thanks for the advice... that sounds very helpful!

My biggest source of confusion is how to store the data in the underlying tables... I'm hung up on the idea of the spreadsheet and am not sure how to represent the same data in a simple table. Do I store a list of availabilities, or have a single record for each employee with a field corresponding to each day/time... nothing seems to intuitive to me.

 

[abezuide]

Thanks for the advice... that sounds very helpful!

My biggest source of confusion is how to store the data in the underlying tables... I'm hung up on the idea of the spreadsheet and am not sure how to represent the same data in a simple table. Do I store a list of availabilities, or have a single record for each employee with a field corresponding to each day/time... nothing seems to intuitive to me.

You store name, address and everything that is unique to one employee in one table - employee name, address, phone

availability gets stored in another table with a link on the employee number to the employee table - for this you need a course in relational databasis I would suggest. It is an art to be able to store data wisely and usable in a database. See if you can find a book on normalisation.

 

[arfox]

You store name, address and everything that is unique to one employee in one table - employee name, address, phone

availability gets stored in another table with a link on the employee number to the employee table - for this you need a course in relational databasis I would suggest. It is an art to be able to store data wisely and usable in a database. See if you can find a book on normalisation.

I actually already have tables forms and queries for the employee contact info I need... pretty comfortable with database design basics. I'm just getting hung up on how to represent the -=availability=- data in a table.

In the spreadsheet I'm cross-referencing the rows(times) with columns(days of the week) and the corresponding cells are the availability (yes/no/preferred).

The only way I can think to do this is a table with an employeeID(pk) and fields for each time/day... which in half hour increments is 337 fields! Surely there's a better way to do this

 

[abezuide]

I actually already have tables forms and queries for the employee contact info I need... pretty comfortable with database design basics. I'm just getting hung up on how to represent the -=availability=- data in a table.

In the spreadsheet I'm cross-referencing the rows(times) with columns(days of the week) and the corresponding cells are the availability (yes/no/preferred).

The only way I can think to do this is a table with an employeeID(pk) and fields for each time/day... which in half hour increments is 337 fields! Surely there's a better way to do this

Yes, to put the employee number as fk in the availability table would be better, cause one availability can be shared by many employees.

You can have a form that looks just like a spread sheet, or you might want something else, give me 10, we did something like that a couple of years ago, I'll quickly go look for it.

 

[abezuide]

Hi, Sorry my 10 took a little long. In our case it was easy, our schedules were only for either mornings or afternoons. We have checkboxes for Monday through Friday morning and afternoon, one checkbox each. The name of the employee is a dropdown box. You pick a name and check the availibilty and save the employee number with the availability in the availibilities table.

This might be helpful or might not be, all depends on how short your periods of availiblity should be. You could also set up a popup calendar to pick availability per employee and then save this to the availibilities table. Display it in a listbox changing for each employee.

Hope this helps.

 

[abezuide]

Another method is to make three listboxes. One with the employee names, one with all the times of the day that you need. Than pick from the first and second listbox and transfer over to the third. Save the third table to an availabilities table that can display in the third listbox upon picking an employee name.