Question for CDrake and the VIP guys... (1 Viewer)

[adamrick]

Hi Dave, Bob et al,

Many many thanks for helping me out with my Excel export issue. You are all scholars and a gentlemen! Can I beg your assistance on one tiny issue which is driving me nuts? Sorry, I am a newbie to this and any help given is always appreciated!

I have created a new form in my database frmTimesheet which captures time used for my service engineers. It's extremely basic at this point (created from examples and demo's scrounged from this great site and the web) with just the module to calculate elapsed time, the form, the query and the associated tables. I have yet to design the reports and excel export (easy enough to do last). The query qryTimesheet simply adds up the hours used by all engineers to be displayed in a report, or exported to Excel.

The tricky bit is this:

I have a form frmJobs where the engineer manually insert the hours used in a text box UnitHourUsed. What I would like to do is have the time sheet automatically add up all the hours used for a specific job (based on the job number selected in the form frmTimesheet) and insert the result in the text box UnitHoursUsed in frmJobs. I know I can use a SUM function in the text box UnitHoursUsed, but I am unsure how to add all the time for a particular job first (from the table tblTimesheet) before inserting the result in the UnitHoursUsed field. I know I have to first add the hours up, but where? In another query or in another table? How can I add up all the hours used?

I have linked a combo box in frmTimesheet to the table tblClientUnits (the column UnitJobNo) to pick up the job number. But how do I setup the field UnitHoursUsed in the form frmJobs to add up the hours and display the total for that particular job number? e.g. my engineer works four times (on different days) on the same job. How can I add up all those four time periods and show the total in the field UnitHoursUsed on the form frmJobs?

I can't upload the whole database here (zipped it is nearly 4Mb - unzipped it's 27Mb) for you to see, but I have attempted to upload all the forms and tables etc. related to the timesheet for you all to study.

Your most kind help is appreciated!

 

[DCrake]

For a start you would get a better response if your sample was pre 2007. I and others do not have 2007 and as such cannot view it.

However from your post it seems to me quite a simple exercise if the data is stored correctly in the first instance. On your form where you collec tthe unit hours worked let the user type it in as a normal time, eg 03:30 however in your table store the time in minutes, in this case 210. then when you want to sum the times you will get the total minutes. This can then be recalculated back into hours and mins.

Public Function MinsToTime(Mins As Integer) As String

If Mins < 60 Then
  MinToTime = "00:" & Format(Mins,"00")
Else
  MinsToTime = Format(Int(Mins/60) ,"00") & ":" & Format(Mins - (Int(Mins/60)*60),"00")
End If

End Function

Then on your form

Me.TotalTime = Nz(MinsToTime(DSum("HoursBooked","Table","Field=" & Expression)),"00:00")

Don't forget to use the actual table/field names not the ones used for brevity.

David

 

[adamrick]

Hi Dave,

Sorry for the version conflict. Please see attached an Access 2000 version

 

[adamrick]

Just to add to my initial post...

Is there some kind of VLOOKUP which I can use in the query/table/whatever to add up these hours?

 

[DCrake]

VLookup is an Excel function, if you see my earier post it uses the DSum() function to get you the answer.

Using DLookup Only looks for something whereas DSum does as it says sums.

Having had a quick glance at your tables You need to change the way in which the user enters the time spent on a job to be in small time format hh:mm. Then you can store this as a whole number in your table (after converting it to minutes.)

David

 

[adamrick]

Thanks for the help David, but I am still a little confused

In my table tsTimesheet, there is a column tsWorkedTime which is supposed to show the worked time, but I am unsure how to get this column populated with the calculated work time. I can easily format it to show the time in minutes as you suggest, but right now, it's not showing any calculated results.....

In the query, I have inserted the expression to calculate this and it shows the time worked correctly. This is also true for the form frmTimesheet.

Where I am stuck is on how to pick out all the specific records where the job number is the same and then add up all the hours for the job number, and then display that in the UnitHoursUsed field on the form frmJobs.

 

[adamrick]

Having had a quick glance at your tables You need to change the way in which the user enters the time spent on a job to be in small time format hh:mm. Then you can store this as a whole number in your table (after converting it to minutes.)

David

Do you mean the tables which refer to the current field in question i.e. UnitHoursUsed (linked to tblClientUnits etc...)?

I understand your point on using DSum, but how can I use DSum to pick out the specific jobs/records from the tblTimesheet/qryTimesheet where the job number is the same?

 

[DCrake]

DSum works just like any other domain agrregate function.

In your table you will have a field that contains many records for one job that record the unit hours used.

Table

Id:1000
Job No 10
Hours 120 (02:00)

Id:1001
Job No 10
Hours 210 (03:30)

Id:1002
Job No 10
Hours 240 (04:00)

So we use the Dsum function to sum the hours field in the table where the field Job no = 10

Which wil give us 570. We then use the MinsToTime Function to convert 570 into 09:30 which is displayed on the form.

We would do this on either the after update of the job id field or the on current event of the form, and also on the after update of the unti hours used text box.

David

 

[adamrick]

Hi Dave,

Many thanks for your advice. Sorry to be a pain but I have a few last questions (I hope..!)

I have seen many examples of DSum on the internet and forums where it's adding up very simple table data and I understand the simple concept. But where I get confused is how do I get the table to calculate the hours worked in the first place, even before trying to get the DSum to work (see tblTimesheet in my attached example where the field/column tsWorkedTime is blank)?

I am so confused

I think I need to use some kind of Me!tsWorkedHours= xxxxx function/expression in VBA to calculate and update the column in the table... But this is also a bit beyond me at this stage...!

 

[DCrake]

Adam

You do not need a tsworked time as this can be calculated

TotalMins: DateDiff("n",[tsstarttime],[tsendtime])



So create a query like such

SELECT tsJobNo, Sum(DateDiff("n",[tsstarttime],[tsendtime])) AS TotalMins
FROM tblTimesheet
WHERE tsDate Between [B]GetDateLower()[/B] And [B]GetDateUpper()[/B]
GROUP BY tsJobNo
HAVING tsJobNo Is Not Null;

Then in your form you can do a DLookup on the TotalMins field as the times have already been calculated and summed.

David