Summing over a date range

[GeraintA]

Hi All,

I am new to access but am trying to get to grips with it as it could help me solve some problems at work! I am trying to create an access database to allocate people to projects and show the total time spent on projects. As peoples time can be allocated to different projects I want to add up the proportions of their times per day and show the results in a graph.

The data I am entering is the date they start working on a project (Start date), the date they stop (End date) and their time allocation (e.g. 50%). How can I use this data to sum for date ranges?

I have attached an example database showing the problem. In the report the graph shows the type of thing I want for the output, clearly showing the total time spent on a project is more than 100% on one day and less on another day. Do you know how I can do this?

Thanks,

Ger

 

[BigHappyDaddy]

This is my opinion, but I wouldn't try to record 'percentages' of date range. I would record for each day, how many hours (minutes?) worked. Try to get to the most specific measurement you need.

Second, you can't add percentages (as in your example) and get the results you are looking for. For example, I spent 100% of my day working on Project 1 and you spent 0% of your day on the same project. If you 'add' the percentages it is 100%. What does that mean? It is just saying that 100% of a typical day was spent on the project. What you are not seeing is how many 'days' were available. My one day and your one day.

So I would change your Timesheets table to something like this:

Timesheets
TimeSheet_ID
SAP_ID (Consider change to same name in table People)
Project_ID
DateWorked (Date)
HoursWorked
​

Now (at least to me) it becomes clear how much time was spent on each project.

Make sense?

 

[GeraintA]

Thanks for your response.

I have changed the Person ID to SAP ID to match the timesheets table.

We measure people’s time as a percentage. 100% corresponds to a person’s full time, so each person should not have more or less than 100% allocated at any one time. Projects can have more than 1 person full time so for example a project with 350% time allocated has the equivalent of 3.5 people working on it.

I can change the percentage to hours without any problem but I still have the same issue that dates are entered in ranges but I want to be able to pick an individual day, or time period and see the time allocations. I don’t want to enter a value for each day as this will be used for recording all the projects (about 20) for 300 people over the course of about 18 months. Data entry would be too difficult to repeatedly type a date and enter the time allocation, then enter tomorrows date and do the same. It’s much easier to set the date range from January to June as half time on project X.

I suppose it’s converting a start date and end date to a range that I am struggling with, or even if that is the correct thing to do.

Regards,

Ger

 

[BigHappyDaddy]

1) Renaming Person ID to SAP ID: Thumbs up!

2) Percentages on Graph: I must of misunderstood your original post. I thought you were confused why some projects had >100% and others were < 100%. As long as you understand what the graph represents, then no problem.

3) I still prefer hours, but if you can make percentages work for you, then go for it.

4) Data Entry: I understand your concern of having to enter multiple timecards per day. My first thought was, "Can you guarantee that in a given date range, Person1 wil always spend the same percentage of the day on the same project?" To me that seems unlikely event (based on similar project I built). But if it is true, then there is a way around it.

On your time sheet entry form, enter the person, project, date start, date end and percentage. (All unbound) Then upon submitting the data, build a loop from date start to date end. For each date, have VBA enter a new record. Person, project and percentage won't change, just the date. That way, there isn't anymore manual data entry and the table structure I suggested still works.

 

[GeraintA]

Thanks for the reply. That seems to be what I need. This database is used for scheduling so people may not end up working exactly as allocated but it allows us to predict costs, sometimes months in advance.

Just to clarify, option 4 will automatically populate a table based on the start and end date given which for a person working on a project from 1/1/12 to 3/1/12 will look like this:

Person, Project Date Percentage
PersonA Project1 1/1/12 100
PersonA Project1 2/1/12 100
PersonA Project1 3/1/12 100

Manually creating this table to test allows me to do what I need with the data, but I have no idea about VBA. How do you recommend I create the loop?

Thanks,

 

[BigHappyDaddy]

I modified and attached your original database.

I removed the recordsource (table Timesheets) from the form Timesheets. I also removed all the bounding from controls in that same form.

I added a Submit button. To view the code behind the button, put the form into design view, select the button and click "View Code" in the ribbon.

I also changed the first two combo boxes to display the names of the people and projects rather than the index.

When the form is filled out and the submit button is clicked, the code will create one record for each day between Start Date and End Date.

One entry, multiple records.

Just FYI, you might want to modify the code to exclude records being created for days in the date range where the person didn't work. Like weekends, holidays, etc.

Does that help?

 

[BigHappyDaddy]

Another piece of advice is to follow a naming convention. Having the same name for a table and a form is asking for issues in the future.