Append Access Table With Dates Programmatically

I'm trying to create append a table programmatically using VBA with dates based on some parameters passed by the user. My base table looks as followed.

workDate - workDayName - workDayYearId - departmentID - allowedHours

My objective is to only provide the user with the year of dates the need when they need it making the table smaller and more effificent when used in a query.

The paramenters the user would need to enter is the year and department id. The base table is already linked to two reference tables: workYear table (linked to workDayId) and the department table (linked to department id). Those two fields would just repeat based on the parameters inputed so doesnt need to do anything fancy there. Also the allowedHours defaults to zero so again nothing fancy required there. Just mainly need the dates for the year excluding weekends and the name of the day.

Any assistance would be much appreciated. Thank you.
LVL 1
spaced45Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IrogSintaCommented:
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?
0
Jeffrey CoachmanMIS LiasonCommented:
Yes, I am confused too...

What is a sample input, and what is the exact desired output?
0
spaced45Author Commented:
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
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

IrogSintaCommented:
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?
0
spaced45Author Commented:
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.
0
IrogSintaCommented:
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?
0
spaced45Author Commented:
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.
0
spaced45Author Commented:
Correct. Each department has a different number of hours allowed based on workload hours coming in.
0
spaced45Author Commented:
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.
0
IrogSintaCommented:
So are you trying to make a form for the member of the resource time to input data once a year?
0
IrogSintaCommented:
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.
0
spaced45Author Commented:
So are you saying that the table would like like:

departmenID  --  workDate  -- allowedHours   ?
0
IrogSintaCommented:
No, it would be:
DepartmentID -- DepartmentName -- AllowedHours

I'm still waiting on your responses to the following:
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?
0
spaced45Author Commented:
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.
0
IrogSintaCommented:
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?
0
spaced45Author Commented:
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?
0
IrogSintaCommented:
I'm going to see if I can get some of the other Experts to post their ideas.
0
spaced45Author Commented:
Thank you sir.
0
als315Commented:
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):
used and allowed hoursIn 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.
0
spaced45Author Commented:
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.
0
als315Commented:
"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.
0
spaced45Author Commented:
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.
0
spaced45Author Commented:
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.
0
IrogSintaCommented:
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.
exemption UIThe advantage here is less data entry as well as a smaller table size.
0
spaced45Author Commented:
Hey I like it. Great idea! I am fuzzy on what I need to do for days where it strays from the default.
0
IrogSintaCommented:
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.
0
spaced45Author Commented:
I think I understand. Could you provide me with an example of the tables
0
IrogSintaCommented:
Some slight changes to the tables you posted eariler:

TBL_EMPLOYEEVACATION
Emp_ID
Vacatoin_Dt
Hours

TBL_EMPLOYEES
Emp_ID
Emp_Fname
Emp_Lname
Dept_ID
etc…

TBL_DEPARTMENTS
Dept_ID
Dept_Name
Allowed_Hrs

TBL_ALLOWEDHRS
Work_Dt
Dept_ID
Allowed_Hrs
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
spaced45Author Commented:
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
0
IrogSintaCommented:
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.
0
spaced45Author Commented:
I just tried it out and it works perfect. Thank you so much for all your assistance.
0
IrogSintaCommented:
Your welcome.

Ron
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.