Add records depending on count of 15 min interval

[Gareth Jones]

Hi all,

Is it possible using a function or query, to group records by 15 minutes, count how many records there are in this interval and add a percentage of these (e.g. 5%) records to uplift the amount. I have tried a different methods however none appear to be working.

Thanks
Gareth

[Rabbit]

Take the minutes, calculate the mod 15 of that number, subtract that many minutes from the value. Group by this new field.

[Gareth Jones]

Thanks for answering. Is there a way of doing this on mass? I wasn't very clear in the OP, as there are approx 800k records.

Thanks

[Rabbit]

The algorithm I described works en masse. It's just a formula, I didn't mean you had to calculate it by hand.

[Gareth Jones]

No problem, just to check is this to be done in a function or a query?

Thanks

[Rabbit]

Yes it can. It's just a formula, any formula can be typed in a query.

[jforbes]

I'm not sure what you mean by Uplifting a percentage, but this might get you close:

SELECT 
  CInt(Format([SomeDate],'n')/15) AS TimeGroup
, COUNT([SomeNumericField]) AS CountOfSomeNumericField
FROM SomeTable
GROUP BY CInt(Format([SomeDate],'n')/15);

[Rabbit]

That will do it regardless of what the hour and date is. However, I believe the actual goal is to round the date time value down to the nearest 15 minute interval.

[Gareth Jones]

Thanks both. So essentially what I am looking for is to count how many records are in every 15 min interval in my table, and insert/add an additional 5% of this count.

So for example, if there are 55 records in a 15 min interval, I need to add an additional 2.75 (3) records to the original amount of 55. These can be 'dummy' records and the content of the record is irrelevant. The reason I cant just add 5% to totals is that this raw data table is the feed for other related tables. The table is quite large so thought a function or update table query would be needed.

[Rabbit]

Did you try the formula I posted?

[Gareth Jones]

Yes however I wasnt entirely sure if I was doing right. Am I right in saying I need to calculate the mod 15 on the date/time, or the count of the date/time per 15 mins? When I just try the mod 15 on the date/time, the results arent right.

I have tried the below which gets me 50% there

SELECT CDate(Format([time1],"Short Date"))+(CDate(Format([time1],"Short Time"))*1440\15)*15/1440 AS TimePeriod, Count(table1.time1) AS CountOftime1
FROM table1
GROUP BY CDate(Format([time1],"Short Date"))+(CDate(Format([time1],"Short Time"))*1440\15)*15/1440;

I then would need to multiply the count by 0.05 to get the % and then add the amount of records thats in the count field per 15 mins

[Gareth Jones]

Got it :) I inserted all the rows I needed in Excel using the below function once I got the count of rows per 15 mins using the above queries.

Public Sub CopyData()
    ' This routing will copy rows based on the quantity to a new sheet.
    Dim rngSinglecell As Range
    Dim rngQuantityCells As Range
    Dim intCount As Integer

    ' Set this for the range where the Quantity column exists. This works only if there are no empty cells
    Set rngQuantityCells = Range("B1", Range("B1").End(xlDown))

    For Each rngSinglecell In rngQuantityCells
        ' Check if this cell actually contains a number
        If IsNumeric(rngSinglecell.Value) Then
            ' Check if the number is greater than 0
            If rngSinglecell.Value > 0 Then
                ' Copy this row as many times as .value
                For intCount = 1 To rngSinglecell.Value
                    ' Copy the row into the next emtpy row in sheet2
                    Range(rngSinglecell.Address).EntireRow.Copy Destination:= _
                            Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
                Next
            End If
        End If
    Next
End Sub

Thanks all for your help.