Conditional Reports Help

[TroxlSha]

I have a table with a [date/time stamp]as the key and eight [readings] fields. The data if linked to a .csv file coming from equipment that takes readings every 15 mins, but I need to create a report for the state that takes each day for a given month and shows only the highest [readings] for each 4 hour period.

How do I get a report to sort through the data for specific date/time frames, and display the highest value for these 4 hour blocks?

[June7]

Do you want the 4-hour periods to overlap (1-5, 2-6, 3-7, etc) or separate (8-12, 12-4, 4-8, 8-12, 12-4, 4-8)?

[TroxlSha]

Sorry, I thought I replied to this already but I guess it didn't work... Anyway, no the time periods would not overlap they would be 0000-0400, 0401-0800, 0801-1200, etc...

[June7]

Create fields in query that will extract the date and time components:

Format(datefield, "Short Time")
Format(datefield, "Short Date") or Int(datefield)

Use the timepart field to calculate a period ID for each record. This could be a long, complicated expression and might exceed length limit. Roughly, something like:

IIf(timepart Between "00:00" And "04:00", 1, IIf(timepart Between "04:01" And "08:00", 2, etc)

or

Switch(timepart Between "00:00" And "04:00", 1, timepart Between "04:01" And "08:00", 2, etc)

Might want to build a custom function and call that function from query.

Use that timeperiod field along with the datepart field to group records in an aggregate (totals) query to return the Max reading for each period of each date.

[TroxlSha]

Thank you so much for the quick reply, I will give this a shot when I get a free moment and let you know how it worked.