Calculation Help (1 Viewer)

[sondriven]

Hi I have a form with three calulations to get the final amount.

Fields
1. Start Time
2. End Time
3. Calculates the Time inbetween (Start Time & End Time)

4. Pcs Made
5. Calculates Pcs Per Hour

I can enter up to five of these fields so I can get an average

6. Total Pcs / Hour Made
7. Average Pcs / Hour

In my query I need a formula or calulation so when I open my report only the Avg Pcs Per Hour will show up.

I dont know how to put this into my query. But it all boils down to one calculated field that I want.

Thanks.

 

[sondriven]

Is it possible?

 

[raskew]

(1) tblTimes probably needs four fields.
Everything else will be calculations
which should not be stored but
rather calculated each time the
query is run.
<table>
MyDate StartTime EndTime PCSMade
7/26/02 10:45:00 AM 11:50:00 AM 112
7/26/02 12:10:00 PM 1:20:00 PM 81
7/26/02 1:30:00 PM 2:33:00 PM 84
7/26/02 2:45:00 PM 4:02:00 PM 105
7/26/02 4:14:00 PM 5:00:00 PM 109
7/27/02 11:30:00 AM 2:23:00 PM 131
</table>
(2) First need a query to determine time spent, in minutes (starting with hours
would return very vague results.
Query116:<code>
SELECT tblTimes.MyDate, tblTimes.StartTime, tblTimes.EndTime, tblTimes.PCSMade, timediff([starttime],[endtime],"n") AS TimeSpent
FROM tblTimes
ORDER BY tblTimes.MyDate, tblTimes.StartTime;
code>
…which returns
<table>
MyDate StartTime EndTime NumSecs PCSMade
7/26/02 10:45:00 AM 11:50:00 AM 3900 112
7/26/02 12:10:00 PM 1:20:00 PM 4200 81
7/26/02 1:30:00 PM 2:33:00 PM 3780 84
7/26/02 2:45:00 PM 4:02:00 PM 4620 105
7/26/02 4:14:00 PM 5:00:00 PM 2760 109
</table>
(2) Then a second query (Query117) to return the average pieces per hour (per day)
<code>
SELECT Query116.MyDate, Int(60*(Sum([Query116].[PCSMade])/Sum(Val([TimeSpent])))) AS AvgPerHr
FROM Query116
GROUP BY Query116.MyDate;
</code>
…which returns
<table>
MyDate AvgPerHr
7/26/02 91
7/27/02 45
</table>

In your reports record source, you should be able to tie Query117 to the date reported on to return
the specific AvgPerHr.

Function TimeDiff called in (2) above is (place this in a new module)
<code>
Function timediff(ByRef starttime As Date, ByRef endtime As Date, interval As String) As Variant
'*******************************************
'Name: timediff (Function)
'Purpose: Return the number of intervals (hours, minutes, seconds) between
' two times, dates or date/times
'*******************************************

timediff = DateDiff(interval, starttime, endtime)

End Function
</code>

 

[sondriven]

Thanks for your help.