Select second, third, etc. record with aggregate functions (1 Viewer)

[djhambly]

Hello, I am having trouble with Access aggregate functions, as I would like to select a second, third, etc. record, similar to a First or Last function, but clearly such functions do not explicitly exist.


In a nutshell, what I am trying to do is the following: I have hourly climate records (for hours from 0 to 23) that I need to group into 6-hour periods (beginning at hours 0, 6, 12, and 18), as some of my data are only available in 6-hourly increments. I am using an aggregate function to do this, and have had no trouble with this method; for each 6-hour period, I have created a number of summary variables using different Count, Avg, Max, functions, etc.


However, I have realized that I also required additional information about the hourly weather conditions throughout each 6-hourly period, i.e., the conditions experienced in the first, second, third hour (of the 6-hour period), and so on. For the first and last hours I can do this, but am completely stuck on the others. I am working with massive amounts of data, so it is not feasible to simply go back to the hourly records and manually look up the conditions throughout an aggregated 6-hour period.


Perhaps someone out there has some ideas as to how I could go about this, perhaps by nesting another aggregate function within this one ? (I can't seem to wrap my head around that).


(Note that I have extracted the below code for example purposes from a much longer query that I have been successfully using, so if a few commas etc. are missing or my syntax looks to be incomplete, that should not likely be a problem – my issue is with the new functions I have added to produce the variables 1stHlyType, 2ndHlyType, ..., 6thHlyType. Note also that it works perfectly as expected for First and Last).


Thanks!
Derrick

SELECT
[6hly_E6-E9].[DateSerial] AS [Date],
[6hly_E6-E9].[6hPer],
[6hly_E6-E9].[6hlyPrecip],
[6hly_grouped].[6hlyType],
[6hly_grouped].[1stHrType],
[6hly_grouped].[2ndHrType],
[6hly_grouped].[3rdHrType],
[6hly_grouped].[4thHrType],
[6hly_grouped].[5thHrType],
[6hly_grouped].[6thHrType],
[6hly_grouped].[TotalHlyRainfall],
[6hly_grouped].[MaxHlyRainfall],
[6hly_grouped].[AvgHlyRainfall],
[6hly_grouped].[HOR],
[6hly_grouped].[AvgHlyDryTemp]
INTO [6hly_climate_summary-left]
FROM [6hly_E6-E9] LEFT JOIN (SELECT [Date],[6hPer],
IIf(Sum(IIf([HlyType]="M2",1,0))>0,"M2",IIf((Sum(IIf([HlyType]="M1",1,0))>0 Or (Sum(IIf([HlyType]="R",1,0))>0 And Sum(IIf([HlyType]="S",1,0))>0)),"M1",IIf(Sum(IIf([HlyType]="R",1,0))>0,"R",IIf(Sum(IIf([HlyType]="S",1,0))>0,"S","")))) AS 6hlyType,
First(IIf(([Hly_climate_summary].[Hour]=0) OR ([Hly_climate_summary].[Hour]=6) OR ([Hly_climate_summary].[Hour]=12) OR ([Hly_climate_summary].[Hour]=18),[Hly_climate_summary].[HlyWeather],"")) AS 1stHrType,
First(IIf(([Hly_climate_summary].[Hour]=1) OR ([Hly_climate_summary].[Hour]=7) OR ([Hly_climate_summary].[Hour]=13) OR ([Hly_climate_summary].[Hour]=19),[Hly_climate_summary].[HlyWeather],"")) AS 2ndHrType,
First(IIf(([Hly_climate_summary].[Hour]=2) OR ([Hly_climate_summary].[Hour]=8) OR ([Hly_climate_summary].[Hour]=14) OR ([Hly_climate_summary].[Hour]=20),[Hly_climate_summary].[HlyWeather],"")) AS 3rdHrType,
First(IIf(([Hly_climate_summary].[Hour]=3) OR ([Hly_climate_summary].[Hour]=9) OR ([Hly_climate_summary].[Hour]=15) OR ([Hly_climate_summary].[Hour]=21),[Hly_climate_summary].[HlyWeather],"")) AS 4thHrType,
First(IIf(([Hly_climate_summary].[Hour]=4) OR ([Hly_climate_summary].[Hour]=10) OR ([Hly_climate_summary].[Hour]=16) OR ([Hly_climate_summary].[Hour]=22),[Hly_climate_summary].[HlyWeather],"")) AS 5thHrType,
Last(IIf(([Hly_climate_summary].[Hour]=5) OR ([Hly_climate_summary].[Hour]=11) OR ([Hly_climate_summary].[Hour]=17) OR ([Hly_climate_summary].[Hour]=23),[Hly_climate_summary].[HlyWeather],"")) AS 6thHrType,
Sum([Hly_climate_summary].[HlyRainfall]) AS TotalHlyRainfall,
Max([Hly_climate_summary].[HlyRainfall]) AS MaxHlyRainfall,
(Sum([Hly_climate_summary].[HlyRainfall])/Count([Hly_climate_summary].[HlyRainfall])) AS AvgHlyRainfall,
Sum([Hly_climate_summary].ObsRain) AS HOR,
Avg ([Hly_climate_summary].[HlyDryTemp]) AS AvgHlyDryTemp
FROM [Hly_climate_summary] GROUP BY [Hly_climate_summary].[Date], [Hly_climate_summary].[6hPer]) AS 6hly_grouped ON ([6hly_E6-E9].[DateSerial]=[6hly_grouped].[Date]) AND ([6hly_E6-E9].[6hPer]=[6hly_grouped].[6hPer]);