showing 24 hour period and 5 hour data segments

[Karl Piper]

Greetings,

Here is a whale of a problem for the experts:

I have timed data for loading and unloading product in a warehouse. For
example, StartDate, StartTime when loading begins, then StopDate, StopTime
when the loading ends. So I now have a starting date and time and ending
date and time (since we load 24 hours it could span days). I then calculate
the number of minutes used loading by subtracting the StopTime from the
StartTime AND I calculate the amount of product loaded by Subtracting the
weight of product BEFORE being loaded that's in the warehouse from the
weight of produce left in the warehouse at the END of loading. OK so far.
The problem to solve is this:

I need a running average of amount of product loaded over 5 hr windows
beginning each day at 12:00 midnight. IOW, at 12:00 AM I want to look BACK
over the past 5 hours and 1) see if any product has been loaded and 2) if it
has, what was the average amount loaded. I need to do this for entire 24
hours periods within certain dates. So I may want to see what my running 5
hour average loading amount was over a 4 day period. The final output may
look something like:

TIME AVG PRODUCT LOADED
7:00 PM - 12:00 AM 505
8:00 PM - 1:00 AM 234
9:00 PM - 2:00 AM 765
10:00 PM - 3:00 AM 0

But the data can be like StartTime=7:45 PM, EndTime=9:30 PM and so on with
many different loadings within a 5 hour period.

Data fields are StartDate, StartTime, EndDate, EndTime, BegWT, EndWT. Again
I'm ok with calculating the amount of time and product for each loading
session, but there could be time spans with NO DATA. So my first question
is how to I show an entire 24 hour period if there is no data to fill the
entire period. Question 2 is how do I look back over the five hour periods
to calculate the running average?

Thanks in advance and sorry if the description is too long.

Karl

 

[Michel Walsh]

Hi,


If I understand, a same loading may be included up to 5 time (say it occur
from 8:00 PM to 9:00PM, it will be assigned to five slots, such as :
5:00 PM to 10:00 PM and 6:00 PM to 11:00 PM. Since the loading will occur
in 5 slots, would it be that it would appear you have loaded it five times?
But not necessary all loading would? A loading having taken 5 hours would
occur in just one slot?

Loading Start End Weight
A 04:00 05:00 10 tons
B 00:00 05:00 10 tons
.... and nothing else...

so,

FromTo Total ton
00:00-05:00 20 ( A + B)
01:00-06:00 10 ( A, since it occurred
between 1 AM and 6 AM )
02:00-07:00 10
03:00-08:00 10
04:00-09:00 10
-----------------
60 tons ...

but you loaded only 20 tons!


There is some "math" here that seems to have been overlooked, and which
required further analysis about how the thing would really be done.



Vanderghast, Access MVP

 

[Karl Piper]

Michel,

You are correct. I was not very clear in that what I am looking for is an
average over each 5 hour period. So, in your example:

FromTo AVG ton
00:00-05:00 4 Avg per hour: 20 tons/5hrs
01:00-06:00 3.6 Avg per hour: 18 tons/5hrs
02:00-07:00 3.2 Avg per hour: 16 tons/5hrs
03:00-08:00 2.8 Avg per hour: 14 tons/5hrs
04:00-09:00 2.0 Avg per hour: 10 tons/5hrs

Very sorry for the misunderstanding. What I'm trying to do is run a report
that basically shows the above information over an entire 24 hour period
even if there is no data --in other words I need a report that shows the
five hour segments like above but if there is no data for a certain time, it
would simply show:

05:00-10:00 0 Avg per hour: 18 tons/5hrs

Hope this helps and thank you for your interest.

Karl

 

[Michel Walsh]

Hi,


If you only need the record, then the trick is to have a table with all
the values, already (ie, with the 24 ranges From-To) and make an outer join
out of it:


SELECT AllRanges.Starting, AllRanges.Ending, Nz(MyTable.MyAverage, 0) As
AVG_ton
FROM AllRanges LEFT JOIN MyTable
ON AllRanges.Starting = MyTable.Starting



Note I assumed the field Starting is supplying the Starting time, in both
tables (or in the table with all the ranges and the actual query you use);
I also use Nz( fieldName, 0 ) to replace the Null with zeros for the
missing records in the actual table (query) you use to produce the result.


Hoping it may help,
Vanderghast, Access MVP