Do...Loop Help

[chucklloyd]

I have a table that has data based on 15 minute increments. The table has an
ID field, a time field, and a total field. I would like to identify the
highest total hour for the data which typical spans 12 hours. How do I
create the loop to evaluate the total for four 15 minute increments, store
the value and then evaluate the next total fo four 15 minute increments. The
output would ideally identify the range of the 4 15 minute increments that
have the highest total.

 

[John Spencer]

So, does ID identify a specific record or is it an identifier for a specific
group of records?

Do you have more than one period of time (more than one day) in your table?

Does your time field only have the time in it or does it also include the
date?

Perhaps this will get you started. It assumes only times in the field and
only one days worth of data

SELECT A.YourTimeField, Sum(B.TotalField) as RollingTotal
FROM A.YourTable = B.YourTable
WHERE A.YourTimeField >= B.YourTimeField
AND A.YourTimeField <= DateAdd("n",65,B.YourTimeField)
GROUP BY A.YourTimeField

SELECT Top 1 Q.YourTimeField, RollingTotal
FROM TheAboveQuery as Q
ORDER BY RollingTotal Desc

If you care to post the actual field and table names, perhaps that can all
be rolled up into one query. Also answer the questions about your data.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[George Nicholson]

When you say highest total hour, can that cross hh:00? (i.e., 12:30 am,
12:45 am, 1:00 am, 1:15 am, making 12x4 total groupings) or do you just want
to evaluate all hh:00, hh:15, hh:30, hh:45 groupings as 12 separate
entities?

If the former, you'd need a fair amount of code. I might create an array to
hold all 48 values and then a loop to total each group of 4, comparing the
Current4Total to the previous High4Total and storing High4Time in a variable
each time a new High4Total is identified. Probably an easier way, that's
just off the top of my head.

If the latter, you can simply create a totals query, sorted Descending, that
only returns the Top record:

Something like:

SELECT TOP 1 Hour([MyDateTime]) AS FullHour, Sum([MyTotal]) AS TotalValue
FROM MyTable
GROUP BY Hour([MyDateTime])
ORDER BY Sum([MyTotal]) DESC;

 

[chucklloyd]

My table has the following fields
TrafficCountID (unique values for each record)
StartTime (6:00,6:15,6:30....)
Total (integer)

It is only for a 12 hour period, or 48 records.
Just the time in 15 minute increments

 

[John Spencer]

SELECT Top 1 Q.StartTime, Q.RollingTotal
FROM
(
SELECT A.StartTime, Sum(B.Total) as RollingTotal
FROM YourTable as A INNER JOIN YourTable As B
ON A.StartTime>= B.StartTime
AND A.StartTime<= DateAdd("n",65,B.StartTime)
GROUP BY A.StartTime
) as Q
ORDER BY RollingTotal Desc

--Copy the above SQL statement.
--Open a new query
-- Don't add any tables when asked, just close the dialog
-- Select VIEW : SQL from the menu
-- Paste the above into the window.
-- Replace YourTable with the name of your table.

I don't know what I was thinking of earlier, but somehow I really, really
messed up the query I posted.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[George Nicholson]

Very, very slick John

--
HTH,
George

SELECT Top 1 Q.StartTime, Q.RollingTotal
FROM
(
SELECT A.StartTime, Sum(B.Total) as RollingTotal
FROM YourTable as A INNER JOIN YourTable As B
ON A.StartTime>= B.StartTime
AND A.StartTime<= DateAdd("n",65,B.StartTime)
GROUP BY A.StartTime
) as Q
ORDER BY RollingTotal Desc

--Copy the above SQL statement.
--Open a new query
-- Don't add any tables when asked, just close the dialog
-- Select VIEW : SQL from the menu
-- Paste the above into the window.
-- Replace YourTable with the name of your table.

I don't know what I was thinking of earlier, but somehow I really, really
messed up the query I posted.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

 

[chucklloyd]

Works like a champ.
Thanks so much for your help.