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: -
SQLrs = "SELECT [Time], AVG(Data_1) AS 'AVG(Data_1)'"
-
SQLrs = SQLrs & " INTO T_Average FROM T_Export"
-
SQLrs = SQLrs & " GROUP BY (time_in_seconds/" & AvNum & "/60)"
-
-
DoCmd.RunSQL SQLrs
-
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
3 2232
The actual function is a bit more complicated: -
Function Av_Calc(AvNum As Integer, TableNme As String)
-
Dim db As DAO.Database
-
Dim rs As DAO.Recordset
-
Dim SQLrs As String
-
Dim ShowField As String
-
Dim Diff As Long
-
-
Set db = CurrentDb
-
DoCmd.SetWarnings False
-
DoCmd.RunSQL "DROP TABLE T_Average" 'delete existing T_Average Table
-
'create a new table with the same columns as T_Export
-
DoCmd.TransferDatabase acExport, "Microsoft Access", db.Name, acTable, "T_Export", "T_Average", True
-
-
Set rs = db.OpenRecordset(TableNme, dbOpenSnapshot)
-
' create a string containing the names of the fields in the table
-
For i = 1 To rs.Fields.Count - 1
-
ShowField = ShowField & ", AVG(" & rs.Fields(i).Name & ") "
-
ShowField = ShowField & "AS 'AVG(" & rs.Fields(i).Name & ")'"
-
Next
-
-
Diff = DateDiff("s", "01/01/2010 00:00:00", rs![Time]) / 60 / AvNum
-
SQLrs = "SELECT " & Diff & " AS 'Time'" & ShowField & ""
-
SQLrs = SQLrs & " INTO T_Average FROM T_Export GROUP BY " & Diff & ""
-
DoCmd.RunSQL SQLrs
-
-
rs.Close
-
db.Close
-
Set rs = Nothing
-
Set db = Nothing
-
DoCmd.SetWarnings True
-
End Function
-
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
fixed the problem!
here's the code: -
Function Av_Calc(AvNum As Integer, TableNme As String)
-
Dim db As DAO.Database
-
Dim rs As DAO.Recordset
-
Dim SQLrs As String
-
Dim ShowField As String
-
Dim Diff As Long
-
-
Set db = CurrentDb
-
DoCmd.SetWarnings False
-
DoCmd.RunSQL "DROP TABLE T_Average" 'delete existing T_Average Table
-
'create a new table with the same columns as T_Export
-
DoCmd.TransferDatabase acExport, "Microsoft Access", db.Name, acTable, "T_Export", "T_Average", True
-
-
Set rs = db.OpenRecordset(TableNme, dbOpenSnapshot)
-
' create a string containing the names of the fields in the table
-
For i = 1 To rs.Fields.Count - 1
-
ShowField = ShowField & ", AVG(" & rs.Fields(i).Name & ") "
-
ShowField = ShowField & "AS 'AVG(" & rs.Fields(i).Name & ")'"
-
Next
-
-
SQLrs = "SELECT Min([Time]) "
-
SQLrs = SQLrs & "AS 'Time'" & ShowField & " INTO T_Average FROM T_Export GROUP BY"
-
SQLrs = SQLrs & " Int(DateDiff('s', '01/01/2010 00:00:00', [Time])/60/" & AvNum & ")"
-
DoCmd.RunSQL SQLrs
-
-
rs.Close
-
db.Close
-
Set rs = Nothing
-
Set db = Nothing
-
DoCmd.SetWarnings True
-
End Function
-
NeoPa 32,556
Expert Mod 16PB
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: GregM |
last post by:
Hi,
I'm hoping that someone can point me in the right direction with this.
What I would like to do is calculate the average time it takes to load
a page. I've been searching the net and reading...
|
by: vee_kay |
last post by:
Ihave a written aprogram in C which implements _beginthread(to create a
thread) and _endthread(to end a thread).The program need to write a
string of date n time to a file for each succesful thread...
|
by: sho_nuff |
last post by:
Hello all,
I have several DateTime objects and i want to get the average of all of them.
What would be the easiest way to do this?
Thanks
SN
|
by: Joseph Lee |
last post by:
Hi,
I am trying to track the time taken for a process to complete.
for loop //for multiple same Process
{
//Do Process
//Get Time
}
|
by: Saradhi |
last post by:
Is there any way to estimate the time required to execute a T-SQL statement?
I need to set the command time out by calculating the time for T-SQL
statement. Can any one give an example?
|
by: richardson |
last post by:
Hi all i need ur help on how to calculate the avg time of two time stamps like start_time and end_time .
The ideal need is to know the average time taken by each job from a table .
i tried ...
|
by: desktop |
last post by:
In the C++ standard sec 23.1.2 table 69 it says that erase(q) where q is
a pointer to an element can be done in amortized constant time.
I guess that is not worst case since std::set is...
|
by: brnkstyle |
last post by:
I figured out how to calculate the work day given two values but all of my records have dates and i want to be able to calcuate the average time it takes to do a job per month so basically take all...
|
by: p3rk3le |
last post by:
So, I'm about to do a sequential search on a table (n contents) of random numbers.
I have to print
the average between the number of comparisons and the contents of the table (n)
and the...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
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: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
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: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |