Solved

Accumalitive time spent on different days ?

Posted on 1999-01-14
9
592 Views
Last Modified: 2012-05-04
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.
0
Comment
Question by:davejdavies
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 7

Expert Comment

by:Dedushka
ID: 1973606
Hi, davejdavies!

Please, post here test data from your table and sample of report.

Dedushka
0
 
LVL 9

Expert Comment

by:perove
ID: 1973607
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
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 1973608
perove,
i do not need help, i want that we together helps davejdavies :-)

My best wishes,
Dedushka
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 10

Accepted Solution

by:
brewdog earned 200 total points
ID: 1973609
As a start, here's what I'd think:

1. For your text box where you want the cumulative time, set these properties:
       Running Sum:       Over All
       Control Source:     [Time Taken Text Box]

2. For the code on the Format event of the Detail section, do this:
       If [CumulativeTimeTextBox] < 7 Then
           CumulativeTimeTextBox.Visible = false
       Else
           CumulativeTimeTextBox.Visible = true
       End If

I don't know how you're planning to do the "y" in the sixth column. I might do that through code, too, with either the visible property or setting the value to "Y" or "".

Hope that helps.

brewdog
0
 
LVL 9

Expert Comment

by:perove
ID: 1973610
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

0
 

Author Comment

by:davejdavies
ID: 1973611
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....
0
 
LVL 1

Expert Comment

by:alcapps
ID: 1973612
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
0
 
LVL 10

Expert Comment

by:brewdog
ID: 1973613
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
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6875546
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.

http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
http://www.experts-exchange.com/jsp/zonesAll.jsp
 
Thank you,
Moondancer
Moderator @ Experts Exchange
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question