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
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.
11 3301
Take the minutes, calculate the mod 15 of that number, subtract that many minutes from the value. Group by this new field.
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
The algorithm I described works en masse. It's just a formula, I didn't mean you had to calculate it by hand.
No problem, just to check is this to be done in a function or a query?
Thanks
Yes it can. It's just a formula, any formula can be typed in a query.
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);
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.
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.
Did you try the formula I posted?
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
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Rich Protzel |
last post by:
Hello,
If I run an action SP from MS Access using ADO:
...
cmd.execute
where the SP is something like Create...
Update tbl1 set fld1 = 'something' where...
how can I retrive the count of...
|
by: PaulMac |
last post by:
This seems so easy....change the join to show all records, but the
zero records still do not show
I want to join 2 tables....basically Customers and Orders....get the
total number of orders for...
|
by: John |
last post by:
I've got a single table I need to query to return records that have no
"related records."
Table dataset example:
1. John, Biology
2. Dave, Math
3. Susan, Biology
4. Betty, Sociology
The...
|
by: DataFreakFromUtah |
last post by:
Hello!
No question here, just a procedure for the archive.
Search critera:
count records imported count data imported count number of rows
imported
count number of records imported record import...
|
by: Anuradha |
last post by:
Dear Experts
How can I count records in a data reader?
Thx
Anuradha
|
by: Rich |
last post by:
Hello,
Is there a way to capture the Records Affected count when performing a table
Update on a Sql Server table using a DataAdapter? How is this done?
Thanks,
Rich
|
by: Justin |
last post by:
I'm looping through my dataset and when I find a match I need to update a
second field. However with this code ONLY the last record in the entire
set/table is updated with ALL the records data:
...
|
by: veaux |
last post by:
Might not have explained this correctly in subject, but query results
look like below:
Name ID Phone
Bill 001 123
Bill 001 234
Bill 001 ...
|
by: andy143vivi |
last post by:
hi, my first time here, great community site, found lots of help for my access development. So here is first question:
how do i merge a multiple record and shows the count as well, im making a...
|
by: fran7 |
last post by:
Hi, I wonder if anyone can help with this query.
I have a table with categories as follows
profileid, categories
1224 abstract
234 illustration
2345 abstract
4 ...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |