Timesheet "discretionary" time

Hi,

I have exported a heap of data from our company timesheet system...

I need to be able to identify staff working over 7.5 hours a day (think this should be ok)

I then need to identify what projects they have worked on, on those days they have worked over 7.5 hours (think i should be ok here too...)

I then need to be able to split their additional time in a relative manner to the hours worked on those projects within the 7.5 hours (does that even make sense???)

I'm not sure if the last point is possible or where I'd start...

I plan to set a query up tomorrow to try and achieve point 1 and maybe point 2, but can't think how I'd start to think about point 3...

I'll then need to be able to roll up to project level and report actual hours booked then be able to minus off this 'discretionary' time...

Any thoughts - grateful for any assistance...

I think what you need to do is to add an additional column to your query that is the difference between 7.5 hours and the total hours grouped by project. This should give you the additional time

To do this convert the time into minutes and do a subtraction from 450 mins (7.5 hours)

So if the total time is 8 hours (480 mins) the overtime wil be 30 mins (480-450)

David

Ok - I may be doing this a long way round...

I've created one query "qryIndvDailyHoursbyProject"


And then a follow on query "qryIndvDailyHours"


Using these I can give myself both a breakdown of hours per project a day and also total hours work against the 7.5 required to calculate disctretionary time...

What I need to achieve somehow is then calculating the percentage of time by project for each day and apply these percentages to the calculated discretionary time to get discretionary time per project per day...

Then I can minus this time off the actual hours booked to create a reportable time per project...

Does that help now that I've started to get my own head around this??

I can't through this element...

I have a query that calculates the time spent for named member of staff across a specified time frame by project per day:

SELECT qryIndvSelect.[Entry Date], Sum(qryIndvSelect.[Total Hrs]) AS [Project Hours], qryIndvSelect.[Project Name]
FROM qryIndvSelect
WHERE (((qryIndvSelect.[Entry Date]) Between [forms]![frmQuery]![txtStart] And [forms]![frmQuery]![txtFinish]))
GROUP BY qryIndvSelect.[Entry Date], qryIndvSelect.[Project Name], qryIndvSelect.[User Name];

I have another query that totals this time per day:

SELECT qryIndvDailyHoursbyProject.[Entry Date], Sum(qryIndvDailyHoursbyProject.[Project Hours]) AS [Daily Hours], [Daily Hours]-7.5 AS [Discretionary Hours]
FROM qryIndvDailyHoursbyProject
GROUP BY qryIndvDailyHoursbyProject.[Entry Date];

I think in order to achieve what i need then I need to calculate a percentage of the time per project divided by the total for the day...

I keep getting a circular reference error as these two queries are linked...

I'm stuck!!

Once I've calculated the % I can multiply this by the discretionary time for each project to give a reduced total hours and reportable discretionary time for each project...

Can anyone make sense of that enough to help??

If you were to write down the actual formula to give you the desired results do the elements of the equasion use both queries at the same time?

Post the equasion for inspection, and if needed send an updated copy of your mdb to test on. (I deleted your last version)

David

I know I'm not explaining this well - I've only just got my head around it...

Ok say I work a 10 hour day against a 7.5 hour contract across 4 projects

Project A = 5 hrs
Project B = 2 hrs
Project C = 2 hrs
Project D = 1 hr

I need to calculate the total additional time (that's the easy bit) by taking 7.5 away from 10 to give me 2.5 hours 'discretionary' time.

For the purpose of reporting we need to be able to assign this discretionary time pro rata against the hours booked and reduce the 'actual hours' down...

Project A = 50% of time
Project B = 20% of time
Project C = 20% of time
Project D = 20% of time

Project A = 2.5 x 50% = 1.25 hours
Project B = 2.5 x 20% = 0.5 hour
Project C = 2.5 x 20% = 0.5 hour
Project D = 2.5 x 10% = 0.25 hour

Project A = 5 - 1.25 = 3.75 Actual Hours & 1.25 Discretionary Hours = 5 Total Hours
etc
etc

I hope you get the gist...

I need to be able to do this for individuals (chosen by [forms![frmQuery]![txtStaff])

And between two dates [txtStart] and [txtFinish] on the same form

As I said previously I can get the breakdown across projects for each day and then on a seperate query I can total these up to give me a daily total...

What I can't do is feed these into eachother to calculate the percentage and then the follow on work after this...

Hope that helps...


Thanks for your time!

I have attached a spreadsheet that gives you what you want in the way of formulae.

In this case I would be tempted to get the minimum data requirements from Access and export them to Excel and apply the formulae there. Trying to get Access to do it is rathr converlooted, however Excel can cope easily also you can present the data more professionally and is easily transportable.

David

Thanks David,

My data when exported is in this format...

Entry Date Project Hours Project Name
02/02/2009 7.25 Project A
02/02/2009 2 Project B
03/02/2009 2.5 Project A
03/02/2009 5.17 Project c
03/02/2009 1 Project D
Etc
Etc
Etc

I'm trying to get this to cleanly export and calculate with as little human involvement after clicking a command button as possible...

I've created another worksheet tab with your excel structure (thanks for this by the way) but cannot work out if there is a way I can get the data out in a better structure in order to facilitate the populating of the discretionary section to further populate an excel table in the right columns...

Current query:

SELECT qryIndvSelect.[Entry Date], Sum(qryIndvSelect.[Total Hrs]) AS [Project Hours], qryIndvSelect.[Project Name]
FROM qryIndvSelect
WHERE (((qryIndvSelect.[Entry Date]) Between [forms]![frmQuery]![txtStart] And [forms]![frmQuery]![txtFinish]))
GROUP BY qryIndvSelect.[Entry Date], qryIndvSelect.[Project Name], qryIndvSelect.[User Name];

Thanks again!