problem with stopwatch in time tracker database

[BigJohn89]

Hi

I need to create "time tracker" for production. We have 4 working spots where workers produce different part for the same order.

1. Profile Cutting --->tblProfile
2. Fabric Cutting --->tblFabric
3. Montage --->tblMontage
4. Packing --->tblPacking

They need to track time through entire proccess on each spot.

I came up with the idea: make tables and forms for every action and add stopwatch. Then they can scan order number and save it (with time they spent on producing, name of the worker, current date).

I managed to create working login form. I found vba for stopwatch and it's working great:

Private Sub Form_Timer()
Dim lngNumOfHrs As Long
Dim lngNumOfMins As Long
Dim lngNumOfSecsRem As Long


lngNumOfSecs = lngNumOfSecs + 1


Select Case lngNumOfSecs
Case Is > 86400 '>1 day - not equipped for that
Case Is >= 3600 '>1 hour
lngNumOfHrs = lngNumOfSecs \ 3600
lngNumOfMins = ((lngNumOfSecs - (lngNumOfHrs * 3600)) \ 60)
lngNumOfSecsRem = lngNumOfSecs - ((lngNumOfHrs * 3600) + (lngNumOfMins * 60))
Case Is >= 60 '>1 minute
lngNumOfMins = ((lngNumOfSecs - (lngNumOfHrs * 3600)) \ 60)
lngNumOfSecsRem = lngNumOfSecs - ((lngNumOfHrs * 3600) + (lngNumOfMins * 60))
Case Is > 0 '< 1 minute
lngNumOfSecsRem = lngNumOfSecs - ((lngNumOfHrs * 3600) + (lngNumOfMins * 60))
Case Else 'shouldn't happen, but who knows?
End Select


Me![lblTime].Value = Format$(lngNumOfHrs, "00") & ":" & Format$(lngNumOfMins, "00") & _
":" & Format$(lngNumOfSecsRem, "00")
End Sub


But I need to have summary in another form. I tried something like this:

Private Sub Sum_Click()
Me.txtSum = Val(Me.time_tblProfile) + Val(Me.time_tblMontage) + Val(Me.time_tblFabric) + Val(Me.time_tblPacking)
End Sub


But it always return value "0".

I think this can be a problem with format of time 00:00:00 (hrs:min:sec)




Maybe someone could help me with this.

Thank you in advance!

[June7]

Format() function returns a string, not a number or date/time value.

Sum the time values as total seconds first then do calculation to show in hrs:min:sec format.


lblTime is a textbox? 'lbl' prefix is usually used in the name of a label control.

[BigJohn89]

Hey,

I needed to change it to textbox because I couldn't pass value from label to the table (and save it with a new record):

Me.time_tblProfile.Value = Me.lblTime.Value

[June7]

Then you might want to change the control name and adjust the expression appropriately.

[BigJohn89]

Ok, I've managed with passing value from label to texbox. I don't know exactly why that didn't worked before...

For sum values I've used:

Hrs: Val(Left([tblFabric]![time_fabric];2))
Mins: Val(Mid([tblFabric]![time_fabric];4;2))
Secs: Val(Mid([tblFabric]![time_fabric];7;2))
TotalSecs: Round([Hrs]/60+[Mins]+[Secs]*0,016)/60 --> I need it that way "sum of minutes/60 = MINHRS"

And I have another question:
If there is a situation when I have 2 duplicate records in tblFabric (sometimes it can happen). First with time 00:22:00 and second with time 00:30:00. How can I change query to sum that and get one result 00:52:00?

[June7]

Sounds like you need an aggregate (GROUP BY) query.

[BigJohn89]

Hi June7,

Can you help me with it? I need to use something like this?

SELECT [tblFabric Kwerenda].zlecenie
FROM [tblFabric Kwerenda]
GROUP BY [tblFabric Kwerenda].zlecenie;

It doesn't work

[June7]

An aggregate query needs an aggregate function, such as Sum. Or design a report that groups records and in group section have a textbox with Sum(TotalSecs) expression.

[BigJohn89]

So far I have:
1. Working stopowatch in every form (frmFabric, frmMontage, frmPacking, frmProfile)
-users can scan order number and save it with user id, date, quantity and time spending on producing

2. Queries - where time from format 00:00:00 is changing separatly to hours, minutes and seconds

Hrs: Val(Left([tblFabric]![time_fabric];2))
Mins: Val(Mid([tblFabric]![time_fabric];4;2))
Secs: Val(Mid([tblFabric]![time_fabric];7;2))

-and total time (in my case sum of minutes/60)
TotalSecs: Round([Hrs]/60+[Mins]+[Secs]*0,016)/60

Now in a situation when 2 different users are working with the same order (e.g. there are few pieces to produce in one order).
User 1 is cutting fabric and scan order 12345 and save it with time 00:20:00
User 2 is also working with fabric on the second spot a he also scan order 12345 and save it with time 00:15:00

Query is showing 2 results:
12345 time 00:20:00 TotalSecs 0,333333333333333
12345 time 00:15:00 TotalSecs 0,25

At the end I need to sum all TotalSecs results concerns one order number from all tables. That's a main goal in this project.

June7 can you help me with accomplish this? My knowledge in this is too low.

[June7]

Aggregate query would be like:

SELECT OrderID, Sum([TotalSecs]) AS SumSecs FROM queryname GROUP BY OrderID;

If you include other fields in the SELECT then they must also be included in the GROUP BY and data will aggregate by that compound grouping criteria.

Otherwise, alternative is to do grouping and aggregate calcs in report design. Report allows display of detail data as well as aggregate calcs.

[BigJohn89]

Where should I put these criteria?

[June7]

I think you will have to use that query as the source for another query - an aggregate query.

Again, designing this in report might be better.

[BigJohn89]

ok, I'm give up
June would you be so kind to check my database? If you have time and willing to create this query. I'm trying everything and it's too much for me.

Many thanks in advance

[June7]

What needs fixing? The report is summing the TotalSecs, which is the preferred solution I recommended. Why do you want to do the same thing in query object?

[BigJohn89]

It's working well when I need report from one query (in this case "queryCord").
I need to group and aggregate information from all queries together - show unique values of "zlecenie" with sum of "totalSecs and if there are duplicate records of "zlecenie" - group them and also sum "totalSecs".
I tried with report as you said and subreports - it's still not working