Select second, third, etc. record with aggregate functions

[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]);

[June7]

Be careful with First and Last, Max and Min are more reliable.

What is the issue, why doesn't work - error message, wrong results, nothing? I suspect the return from each First function is the empty string. I did some testing. Didn't matter what the alternate value was, that is what First or Last returned. Yeah, didn't make sense to me either, but as I said First and Last are not usually a good idea, I avoid them. However, Min and Max behaved differently. Try those and tell us if that is better.

[djhambly]

Unfortunately, Max and Min won't work in this case as the field HlyWeather contains text. There is not error message; as you found in your testing, 2ndHrType, 3rdHrType, 4thHrType, and 5thHrType just come up empty, as the First function will not work in these cases (nor will the IIf function without being contained in something else). 1stHrType, i.e., the 'first' First, works fine, and brings back the expected value. So does 6thHrType, which uses the Last function. My problem is that there is no such thing as a Second, Third, Fourth, or Fifth function, so I need to find a way to replicate such a thing.

[June7]

Show sample of data in HlyWeather. Are the values a limited set?

The only alternative I can offer is VBA code. This is a fairly common topic. Google: select nth record

[djhambly]

Here is an example dataset that I created to help illustrate what I am trying to do...field names are of course different in my actual database - this is just meant to be illustrative; however, the HlyWeather variable is the same as 'Weather code' in the image. Unfortunately, there are more than 100 distinct possible values for HlyWeather, so no, they are not that limited.

[June7]

Then that kills a train of thought. VBA is route left. You are trying to denormalize the Weather Code data (report horizontally, left to right). Review this thread and the links in it https://www.accessforums.net/showthr...lias-Variables

and http://allenbrowne.com/func-concat.html