Append Access Table With Dates Programmatically

Adding fields to a table that can easily be calculated in a query is poor practice.  I don't follow your intent here.  Can you give more details on what it is you're trying to accomplish?

Yes, I am confused too...

What is a sample input, and what is the exact desired output?

Sorry for the confussion. I completely understand what the concern is when you talk about the calculated fields. With that said, I agree, so I am throwing out the "workDayName" field. So assuming my base table looks as followed.

workDate - workDayYearId - departmentID - allowedHours

To help explain myself better I have attached an excel file that shows a very simple diagram of what I am trying to do. If anyone has suggestions on how to do this differently by all means please share. If I need to change my logic I am willing to do it. Thanks everyone for your time.
MyIntention.xlsx

TBL_DEPARTMENTS, TBL_EMPLOYEES, and TBL_EMPLOYEEVACATION all are fine.  I don't see the need for TBL_VACATION.  As for TBL_ALLOWEDHRS, this is highly inefficient.  Are the allowed hours different for each department?  Do the allowed hours change each day?

Well, now that I really think about it TBL_VACATION (I misspelled the table should be TBL_VACATIONYEAR) doesnt make that much sense when considering how they are ulitimatly joined.

1) Are allowed hours different for each department? Unfortunatly they are because depending on the number of people in the department the hours/people off at one given time could be different. For example, if you know you need 10 people to do a job and you have 14 in the department then you dont want to schedule more than 4 off.

2) Do the allowed hours change each day? Yes and no. For example in December we will begin to fill out the allowed hours for 2014 and rarely do we edit the list. On the other hand based on historical data we know that for lets say the day after a holiday we need less people off so in that sense they do change. When requests start to come in by different people from different departments we will know wheather we have enough people staffed for the day that its being requested.

Again thank you for looking into this. I can see where your thought process is going so thank you.

So are you saying that Department 1 could have 28 hours allotted for each day of the year and Department 2 could have 36 hours each day, etc...  In other words, could your TBL_DEPARTMENTS have a column specifying the allowed hours each day?

Can you describe how exactly you want the user to enter data and what it is you want to show them?

So now that you have an idea of what I am trying to accomplish I can clearify my question a bit more. At years end a member of the resource time will begin inputing what they think is the max number of hours allowed for each workday and each department.

At first I said, I dont want to mess with this so I will just pre-create the allowed hours table with all the workdays for all the departments for the next five years. Then I realized that if I later want to query the data (really its not that much) I would have to contend with ensuring that I show only this year of that year so I stopped. Also I plan to connect the database to and Infopath form later and using Access over a network just stinks. To unperdictable.

Correct. Each department has a different number of hours allowed based on workload hours coming in.

I re-read your last comment again. I am thinking that the Dept table wouldnt be the table that should hold the allowed hours for each day and department. The department table is a reference table so I will be using it later to create queries for reporting so I dont want to have to "SELECT DISTINCT departmentName FROM TBL_DEPARTMENT" to do this.

So are you trying to make a form for the member of the resource time to input data once a year?

Why would you have to use DISTINCT?  I thought you said that the hours will not change for each department each year.  So if you add 1 column to hold the number of hours, you will still have 20 records in there, 1 for each department.

So are you saying that the table would like like:

departmenID  --  workDate  -- allowedHours   ?

No, it would be:
DepartmentID -- DepartmentName -- AllowedHours

I'm still waiting on your responses to the following:

I am sorry. I think thats where you missunderstood me and thus why I sent you an additional message after I re-read your message about the TBL_DEPARTMENT table. The hours do change each day for each department.

•Can you describe how exactly you want the user to enter data and what it is you want to show them? So are you trying to make a form for the member of the resource time to input data once a year?

The user (resource member) at the year end wants to begin entering allowed hours for the upcoming year. I envision a form, linked to the allowed hours table, containing a button (add next years allowed hours) they click. That could initiate an input box that that requires the user to enter the year and the department. Based on the input the code generates all workdays for the the year and applies the department ID to each row. From there the user is now ready to input the hours for each day.

I'm confused again.  So how would the allowed hours change each day of the upcoming year?  When the User enters next years data, they would somehow already know what the various hours are for each day of 2014 for each department?

They would. So if they know who many hours they need for each day for each department then will will need a way to reference the date that the hours apply to. So user says that department1 on 1/1/2014 will allow 48 hours, department2 on 1/2/2014 will allow 18 hours.
So based on your proposed table structure how would they be able to do that if there is no reference to the day that the hours apply to?

I'm going to see if I can get some of the other Experts to post their ideas.

Thank you sir.

May be you can use table like this sample (for each department you can fill only days where vacation hours were used adn calculate allowed hours):
In this case you can have problems if hours will not be allocated sequentally (you can allocate hours in june before may, for example) - is it critical? If it is possible, you had to use query to get sum of already allocated hours in this year each time.

We do vacation day bidding so it would be critical since we would schedule days sequentally. That example that you have is what i am looking to do but in a query. I have a table that holds all of the hours scheduled for each employee. So, I would Join the Allowed Hours table to my aggregated Employee Vacation table. Thats where the usedhours column that you have would come into play. The only thing I need now is just a bit of code to create all the workdays for each department.

"The only thing I need now is just a bit of code to create all the workdays for each department."
You have approximately 250 working days in a year. Why do you like to have all days in your table? May be on a form you can fill table selecting vacation day? In sample above you should have only few records in table.

Why would I like to have all the days table? Because we have to assign allowed hours to each working day. If we don't and someone asks for it off we won't know what is permitted.

To reiterate. We would have to know on any given day how many hours we are allowing for the department which the employee is requesting off. This ensures that we don't schedule more hours off then we need to do the work.

Would you consider something like having a default number of allowed hours for each department along with an exemption table that shows days where the hours stray from the default?  

If so, then your default hours for each department can be in the TBL_DEPARTMENTS table, and you would create a table to store the exemptions.  This table would have 3 fields, DT, DEPT_ID, and ALLOWED_HRS.  So if the day the employee is requesting off is not in this table, you would use the default hours from the departments table.  

A user interface could look something like this.  Here, the default hours are listed under each department.  Whenever a new date is added, for instance, 1-Apr, the default hours for that day show up in a light color.  The user can then enter the allowed hours which would show up bold.
The advantage here is less data entry as well as a smaller table size.

Hey I like it. Great idea! I am fuzzy on what I need to do for days where it strays from the default.

So when someone asks for a certain day off, your code should add up all the hours of anyone in that department with that particular day off and subtract that from the Allowed Hours.  To get Allowed Hours, it should look first in the Exemption table for that day and that department, if not there, it will then look at the Departments table for the default.

I think I understand. Could you provide me with an example of the tables

This should work perfectly. How could I add multiple records to the table by providing a date range? This should take care of everything I need to accomplish my goal

You don't add multiple records at a time.  You would add a record to the TBL_ALLOWEDHRS table every time an exception is added for a department for that particular date.  Likewise, you would edit a single record when a change is made to an existing exemption AllowedHours.

I just tried it out and it works perfect. Thank you so much for all your assistance.

Your welcome.

Ron