Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012

    Unanswered: Select second, third, etc. record with aggregate functions

    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).


    [6hly_E6-E9].[DateSerial] AS [Date],
    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]);

  2. #2
    weejas is online now Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 7
    The only thing that comes to mind would be to write a VBA function that takes a table or query name, a field name and an integer as arguments. Then use a recordset to hold the named field within the table or query, and return the specified row's value. That would probably be a massive performance hit, though.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 1

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    you could find the hours elapsed from a specific date, then divide that by 6 and group by the integer of that result.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts