Software

Ask something
Discussion Locked

Access aggregation formula

0 helpful
2 replies

by cwlipke

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have been asked to produce a table of values which has me stumped. The table has daily quantities for multiple meters and they want to see three-day quantities for groups of meters. Meters are assigned a group code so group-by on each day is easy. Any way I can get sum of a group of meters for three days, record that sum, roll forward one day and record the sum of that three days, etc for each meter-group?

2 replies

by slator.turner

This will give you the 3-day sum of all the meter groups for any 3-day period the user enters into the parameter. If you create a form with a combo box to select Day-1 of the three period, the AfterUpdate event for the combo box could generate the Day-3 for the parameter and run the query.
If the user(s) want a dynamic/rolling 3-Days, such as in a Cross Tab query, that would require a much more involved solution.

SELECT [First] & " - " & [Last] AS 3Days,
tblMeterReading.MeterGroup,
Sum(tblMeterReading.MeterValue) AS SumValues,
First(tblMeterReading.DateRead) AS [First],
Last(tblMeterReading.DateRead) AS [Last]
FROM tblMeterReading
WHERE (((tblMeterReading.DateRead)
Between [enter start date] And [enter end date]))
GROUP BYtblMeterReading.MeterGroup;

The table in my query had 3 fields:
- MeterGroup (Text)
- MeterValue (Number)
- DateRead (Data/Time)
If you create a table with these field names and add some data, you should be able to Cut/Paste the query.

Hope this helps.

by cwlipke

Thanks for taking the time to look at this. Unfortunately, as you feared, I need the dynamic/rolling 3-day sums. What they want to do is identify the highest three-day period during the year and on what days that occurred. For that, I need to get the rolling three-day sums. Manually, I would pick Jan 1-3, add them together, record that value, move to Jan 2-4, add them together, record that value, etc. Would a VB procedure loop accomplish this? I'm fairly new to VB although I've done quite alot of Excel macros in VB.

This discussion is closed to new replies.