davejdavies
asked on
Accumalitive time spent on different days ?
I am designing a report (or trying to) and I need to add up the time spent on a varying number of jobs in a given day, given a start time and completion time.
The first column indicates the date, the second a job number, third is time taken (this is a calculation in a query). The question is how can I put a total figure of time taken in a forth column which increses for every job completed per day. The figure is not to be displayed unless the accumulative time exceeds 7 hours. It must also only include jobs started upto 7.00pm in the evening. A further twist to the problem is in the sixth column a "y" is to be displayed for every total in the previous column exceeding 7 hours.
The first column indicates the date, the second a job number, third is time taken (this is a calculation in a query). The question is how can I put a total figure of time taken in a forth column which increses for every job completed per day. The figure is not to be displayed unless the accumulative time exceeds 7 hours. It must also only include jobs started upto 7.00pm in the evening. A further twist to the problem is in the sixth column a "y" is to be displayed for every total in the previous column exceeding 7 hours.
Dedushka,
if yiu want, send me a copy of the table & query & the report (so far) and I will see what I can do
perove
oh..i forgot:
email:
perove@krdata.no
if yiu want, send me a copy of the table & query & the report (so far) and I will see what I can do
perove
oh..i forgot:
email:
perove@krdata.no
perove,
i do not need help, i want that we together helps davejdavies :-)
My best wishes,
Dedushka
i do not need help, i want that we together helps davejdavies :-)
My best wishes,
Dedushka
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Dedushka:
Opps...slip of the finger..sorry.. (thank god its weekend soon..)
Ill try again:
DAVEJDAVIES:
if you want, send me a copy of the table & query & the report (so far) and I will see what I can do
perove
oh..i forgot:
email:
perove@krdata.no
Opps...slip of the finger..sorry.. (thank god its weekend soon..)
Ill try again:
DAVEJDAVIES:
if you want, send me a copy of the table & query & the report (so far) and I will see what I can do
perove
oh..i forgot:
email:
perove@krdata.no
ASKER
Thanks for the assistance, your idea will help me complete half the problem but I still need to know the following:
Brief example of report:
Date Job Number Time taken Over 7 hours Worked
01/01/99 120 1:00
01/01/99 121 4:00
01/01/99 122 2:30
01/01/99 123 1:20 7:30
01/01/99 124 1:00 8:50
02/01/99 125 3:00
02/02/99 126 4:10
02/01/99 127 1:33 7:10
I need to be able to group each date and calculate the time taken and display it as above, on the next date the time starts from 0:00. Onlt times over 7:00 arfe to be displayed and any times over 7:00 must be able to be displayed.
With your help I am now able to display or not display the relevant data but the calculations are still giving me problems.
Dave....
Brief example of report:
Date Job Number Time taken Over 7 hours Worked
01/01/99 120 1:00
01/01/99 121 4:00
01/01/99 122 2:30
01/01/99 123 1:20 7:30
01/01/99 124 1:00 8:50
02/01/99 125 3:00
02/02/99 126 4:10
02/01/99 127 1:33 7:10
I need to be able to group each date and calculate the time taken and display it as above, on the next date the time starts from 0:00. Onlt times over 7:00 arfe to be displayed and any times over 7:00 must be able to be displayed.
With your help I am now able to display or not display the relevant data but the calculations are still giving me problems.
Dave....
Dave you could make a temp field and place it in the header for the date group tempfield = 0 ; in the detail make it tempfield = tempfield + time take field. now you can use the accumulated value to do tests if tempfield > 7 then
place your Y
make the line visible
end if
header make the tempfield = 0 so you can start over for the next date..
hope this helps..
alcapps@yahoo.com
place your Y
make the line visible
end if
header make the tempfield = 0 so you can start over for the next date..
hope this helps..
alcapps@yahoo.com
Dave:
Like alcapps, I'd recommend a grouping on your date field (I'm guessing you might already have this in place?). Actually, though, once you've established that, it's a little easier than alcapps' suggestion. For your TimeTaken text box, set the Running Sum property to Over Group; the code you already have in place will be fine, and Access will automatically set the value to 0 at the start of each new group/date.
brewdog
Like alcapps, I'd recommend a grouping on your date field (I'm guessing you might already have this in place?). Actually, though, once you've established that, it's a little easier than alcapps' suggestion. For your TimeTaken text box, set the Running Sum property to Over Group; the code you already have in place will be fine, and Access will automatically set the value to 0 at the start of each new group/date.
brewdog
GREETINGS!
This question was awarded, but never cleared due to the JSP-500 errors of that time. It was "stuck" against userID -1 versus the intended expert whom you awarded. This corrects the problem and the expert will now receive these points; points verified.
Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them. If you are an EE Pro user, you can also choose Power Search to find all your open questions.
This is the Community Support link, if help is needed, along with the link to All Topics which reflects many TAs recently added. Also in the Community Support link is a question on how experts can help, if they wish, on the cleaning of old and abandoned questions.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
https://www.experts-exchange.com/jsp/zonesAll.jsp
Thank you,
Moondancer
Moderator @ Experts Exchange
This question was awarded, but never cleared due to the JSP-500 errors of that time. It was "stuck" against userID -1 versus the intended expert whom you awarded. This corrects the problem and the expert will now receive these points; points verified.
Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them. If you are an EE Pro user, you can also choose Power Search to find all your open questions.
This is the Community Support link, if help is needed, along with the link to All Topics which reflects many TAs recently added. Also in the Community Support link is a question on how experts can help, if they wish, on the cleaning of old and abandoned questions.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
https://www.experts-exchange.com/jsp/zonesAll.jsp
Thank you,
Moondancer
Moderator @ Experts Exchange
Please, post here test data from your table and sample of report.
Dedushka