Trying to build a scheudling access database!

[CarrieFisher]

Good Morning Everyone,

I am about to start a new project and would love some advice before I start. I am using Acess 2010, and very junior at working with access.

The goal of this project is to build a database that manages employees who are working on a rostering system. We want it to display people who are "onsite" and people who are on "break," we also have multiple locations. So if they are on and what location they were at. It would be great if it would keep a history as to where they have been and where they are going.

So....ya...I am in rut and in need some great advice.

I have started the following tables:

• tblemployeeinfo (holds first and last names)
• tbllocations (holds a list of possible locations an employee could work at
• tblworkhistory (holds effective date, a link to "tbllocations" to chose the location that they are at, and employee number to link to "tblemployeeinfo"

What I would like to see - possibly something that looks like a calender that shows whos on shift for the week?!?! Or possible a simple report that shows who is working today?

I guess my biggest stump is how to I tell it to say find the people who are working today?

One last note: Everyone is on a roster..meaning when they are hired they are either on Roster A or Roster B. So If I am roster A I work week 1&2 and then week 5&6 - I dont know if that would help?

Thank you for all of your help in advance!!!

 

[CBrighton]

Well, none of what you said is impossible, though the calendar type view is more likely to be a list of dates rather than a grid.

Your table design is simple but functional, though I find it useful to have a calendar table with dates too.
You can easily get a long list of dates in Excel by using the fill feature (tbh I have no idea what this feature is called, the little square in the bottom right corner of the selected cell that lets you extend the data/formula to additional cells). With this it's just a short SQL statement to get a list of dates between a start & end date, which could be used for all sorts of things.

You may also need another table to hold the possible work patterns. That way you can link that to your main table too and you will know when each member of staff is expected to be present, this gives you an expected result to check against.


It looks like you know what you are doing, but if you have any specific queries I'd be glad to help.

 

[CarrieFisher]

Thank you for your feedback!!