Employee Roster weekly view

[BamaBlast]

Hello All,
I 'm trying to create a database for tracking time off from work and print a weekly roster. I have built several databases in Access 2003 and have now transitioned to 2010 and it is seems to be going well. My past databases have been rather simple data in data out not really that big of a deal. However on this database they need a report that will show them a week view that shows them all the employees regular days off and any additional time off they have schedule in that week. I will try to explain what I have as best as I can in hopes of getting some direction or insight so please bear with me as I go into detail. My VB skills are limited but I’m working on that, just need a little help to get over this hurdle.

I have an employee info table that has employee basic info, Emp name, Emp number, Work Week code (which identifies which days off the employee has) Hire Dates (for seniority sorting) and then I have 7 fields listed as D1, D2, D3 and so on until D7 (I will try to explain these fields later).
I have another table (Time Requests) that has 3 fields, Date, Emp number, and Time code (which identifies why they are off work or unavailable to be assigned).
The last table I will refer to has 7 fields, they are Date1, Date2, Date3, Date4, Date,5 Date6, Date7

I now have a form. This form has the 7, fields Date1, Date2.... Date7.
On my form Date1 field is updated by a manager with a date that is a Sunday then Date2 = [Date1]+1,
Date3 = [Date1]+2 and so on until I have all 7 fields showing dates from Sunday – Saturday. These fields are lined across the top in a vertical direction positioned above a subform of the employee info table. So directly below the 7 Date fields are the D1, D2, D3, D4, D5, D6, D7 fields then to the right is the Emp name and Emp number, This gives me a Week view of who is working. I have been able to get the D1, D2….. fields to show their respective Work Week codes by writing very simple “If Then” statements . So here is where I AM STUCK…When I am showing this week view how do I get D1 –D7 to identify what date they are supposed to be in reference to Date1 – Date7 then compare themselves to the table “Time Requests” to see if they have a match and then set the value of D1, D2 …. to the “Time code” in that table. D1 – D7 need to auto populate and do this for about 50 employees.


I hope this was understandable. I have been researching this for about a week on the net and forums and have not found anything that is helping me get over this hurdle.


Any direction would be gladly appreciated.

Here is a screen shot of what I have so far and where I am stuck: flickr.com/photos/lnrw2011/11309570694


I am aware there are a lot databases out there that do similar stuff but none of them meet my needs and requirements.

[Dal Jeanis]

Please post the format of each significant table. Explain what the fields mean or what they are used for. Here's a start, so you can use this kind of format:

tblEmployees
   EmpID      Autokey
   EmpName    Text
   WorkWeekID Number  (FK to tblWorkWeek)

tblWorkWeek
   WorkWeekID  Autokey
   WorkSun     Yes/No  (No means off that day)
   WorkMon     Yes/No
   WorkTue     Yes/No

or alternately 

tblWorkWeek
   WorkWeekID  Autokey
   DayOff1     Number  (1 for sunday thru 7 for Saturday)
   dayOff2     Number  (1 for sunday thru 7 for Saturday)

tblTimeRequests
   TRID        AutoKey
   EmpID       Number (FK to tblEmployees)
   DateOff     Date
   TimeCode    Number (FK to tblTimeOff)

tblTimeOff
   TimeCode    Autokey
   TimeDesc    Text (Description of Time off reason)

If you put the word CODE in square braces [] before your code, and /Code in square braces [] after your code, then the forum won't scrunch up your formatted code.

Please change the names of the tables and fields to exactly match the tables and fields in your database. If you have spaces in any field names, then please include square braces around the table or field name.

[My Time Off Table]
   TimeCode    Autokey
   [Time Desc] Text (Description of Time off reason)

[BamaBlast]

I'm gonna have to chew on this a bit! Thanks for your reply!!!