By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,747 Members | 1,808 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,747 IT Pros & Developers. It's quick & easy.

Average time

P: 27
Hi,
I have a function in Access VBA which uses an SQL code to create an average table from an existing table.
The original table may look something like this:

Time Data_1
03/08/2010 12:41:20 10
03/08/2010 12:41:30 13
03/08/2010 12:41:40 15
03/08/2010 12:41:50 23
.
.
.

with many thousands of rows.
I'm trying to create a function that outputs a new table containing the average values of data_1 (in segments the user chose) - for 10 minutes:

Time AVG(Data_1)
03/08/2010 12:41:20 10
03/08/2010 12:51:20 23
03/08/2010 13:01:20 13
03/08/2010 13:11:20 25
.
.
.

so far I have:
Expand|Select|Wrap|Line Numbers
  1. SQLrs = "SELECT [Time], AVG(Data_1) AS 'AVG(Data_1)'"
  2. SQLrs = SQLrs & " INTO T_Average FROM T_Export"
  3. SQLrs = SQLrs & " GROUP BY (time_in_seconds/" & AvNum & "/60)"
  4.  
  5.  DoCmd.RunSQL SQLrs
  6.  
the function is supposed to average the values in data_1 and group them by the time (converted into seconds) divided by the user picked number of minutes (the 60 is for seconds)

Can any one help?

Thanks in advance,

Izhar
Aug 8 '10 #1
Share this Question
Share on Google+
3 Replies


P: 27
The actual function is a bit more complicated:
Expand|Select|Wrap|Line Numbers
  1. Function Av_Calc(AvNum As Integer, TableNme As String)
  2.     Dim db As DAO.Database
  3.     Dim rs As DAO.Recordset
  4.     Dim SQLrs As String
  5.     Dim ShowField As String
  6.     Dim Diff As Long
  7.  
  8.     Set db = CurrentDb
  9.     DoCmd.SetWarnings False
  10.     DoCmd.RunSQL "DROP TABLE T_Average" 'delete existing T_Average Table
  11.     'create a new table with the same columns as T_Export
  12.     DoCmd.TransferDatabase acExport, "Microsoft Access", db.Name, acTable, "T_Export", "T_Average", True
  13.  
  14.     Set rs = db.OpenRecordset(TableNme, dbOpenSnapshot)
  15.     ' create a string containing the names of the fields in the table
  16.     For i = 1 To rs.Fields.Count - 1
  17.         ShowField = ShowField & ", AVG(" & rs.Fields(i).Name & ") "
  18.         ShowField = ShowField & "AS 'AVG(" & rs.Fields(i).Name & ")'"
  19.     Next
  20.  
  21.     Diff = DateDiff("s", "01/01/2010 00:00:00", rs![Time]) / 60 / AvNum
  22.     SQLrs = "SELECT " & Diff & " AS 'Time'" & ShowField & ""
  23.     SQLrs = SQLrs & " INTO T_Average FROM T_Export GROUP BY " & Diff & ""
  24.     DoCmd.RunSQL SQLrs
  25.  
  26.     rs.Close
  27.     db.Close
  28.     Set rs = Nothing
  29.     Set db = Nothing
  30.     DoCmd.SetWarnings True
  31. End Function
  32.  
I'm stuck with the SQL part (which I'm quite new to).

What I want to do is create a new table (T_Average) containing average values of all the fields from the original table (T_Export), in segments which appear in AvNum in minutes.
If the user chooses AvNum=3, then I want to GROUP each 3 minutes worth of data and output a row containing the time of the beginning of the segment and the average values of all the fields in the table during that time.

Thanks in advance,
Izhar
Aug 8 '10 #2

P: 27
fixed the problem!

here's the code:
Expand|Select|Wrap|Line Numbers
  1. Function Av_Calc(AvNum As Integer, TableNme As String)
  2.     Dim db As DAO.Database
  3.     Dim rs As DAO.Recordset
  4.     Dim SQLrs As String
  5.     Dim ShowField As String
  6.     Dim Diff As Long
  7.  
  8.     Set db = CurrentDb
  9.     DoCmd.SetWarnings False
  10.     DoCmd.RunSQL "DROP TABLE T_Average" 'delete existing T_Average Table
  11.     'create a new table with the same columns as T_Export
  12.     DoCmd.TransferDatabase acExport, "Microsoft Access", db.Name, acTable, "T_Export", "T_Average", True
  13.  
  14.     Set rs = db.OpenRecordset(TableNme, dbOpenSnapshot)
  15.     ' create a string containing the names of the fields in the table
  16.     For i = 1 To rs.Fields.Count - 1
  17.         ShowField = ShowField & ", AVG(" & rs.Fields(i).Name & ") "
  18.         ShowField = ShowField & "AS 'AVG(" & rs.Fields(i).Name & ")'"
  19.     Next
  20.  
  21.     SQLrs = "SELECT Min([Time]) "
  22.     SQLrs = SQLrs & "AS 'Time'" & ShowField & " INTO T_Average FROM T_Export GROUP BY"
  23.     SQLrs = SQLrs & " Int(DateDiff('s', '01/01/2010 00:00:00', [Time])/60/" & AvNum & ")"
  24.     DoCmd.RunSQL SQLrs
  25.  
  26.     rs.Close
  27.     db.Close
  28.     Set rs = Nothing
  29.     Set db = Nothing
  30.     DoCmd.SetWarnings True
  31. End Function
  32.  
Aug 9 '10 #3

NeoPa
Expert Mod 15k+
P: 31,769
It hardly seems worth answering now, but in case the concept is of interest (rather than some specific code) then the GROUP BY clause is the issue. How to group records together in batches of X minutes?

The trick is to divide (integer divide to lose any fractional part) the date/time value by X (working in minutes or X * 60 working in seconds).

Moving on. I noticed this languishing in the SQL Server forum. I expect this is related to your newness dealing with SQL. SQL =/= SQL Server. Your question is related to MS Access and Jet-SQL. I will move it across for you.

BTW. This probably explains the lack of interest in your question. You were asking a set of experts who largely have little to do with the subject of your question.
Aug 9 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.