[Student_101]

Hi Experts,

I am working on Time-Frequency Convter tool.

I need some help sorting out this challenge.

I have data for about 10-25 years, which is in a text file, and is space delimated.

The first part has been completed, which was to import the text file using VB6 into an Access Database.

Now in the second part, I am using that file from the Access database, to find out different frequencies from the time range.

For example the user selects the parements, such as:
-the measurement paramater
-the time range
-the kind of operation to be performed, such as Hourly/Daily/Monthly/Seasonal/Yearly Averages.

All the data is in different columns in the Access database, now with accurate grouping and different selections made from the combo box, I need to get the desired output as selected by the user.

I am not sure how to go about with this, for example using grouping to trace back every min to its hour, while being in the same year,month, day and hour.

Please advice me as to what an efficient solution can be.

This is an example of the input format from the text file

YEAR MM DD HH mm Value to be averaged
1998 07 23 14 35 -7.877
1998 07 23 14 35 -7.879
1998 07 30 14 14 -7.804
1998 08 06 16 11 -7.721
1998 08 06 16 11 -7.727
1998 08 13 14 44 -7.685
1998 08 13 14 44 -7.693

Can I use structures such as heaps, or dictionaries, or many loops, or 3D arrays(matrices) to solve this efficiently?
Which would be the most efficient way to go about this?

Or should I just use group based Queries in MS-Access ??

I tried using this Query, I'm not sure if this is the right way to do it.

SELECT Avg(tbl_Import_Data.Value) AS AvgOfValue
FROM tbl_Import_Data
GROUP BY tbl_Import_Data.cYear, tbl_Import_Data.cMonth, tbl_Import_Data.cDay, tbl_Import_Data.cHour;

NEED HELP !!

P.S. This is the code I have so far

[code]

Private Sub cmd_Load_Click()
OpenConnection
Dim cmd_Load As New ADODB.Command
Dim RS_Load As New ADODB.Recordset

With cmd_Load
.ActiveConnection = m_Conn
.CommandText = "SELECT * from tbl_import_data;"
.CommandType = adCmdText
End With

With RS_Load
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmd_Load
End With


If cbo_OperationParam.Text = "Hourly Mean" Then
' Year, Month, Day and Hour must be the same, then find Hourly average for Values

ElseIf cbo_OperationParam.Text = "Daily Mean" Then
' Year, Month and Day must be the same, then find Daily average for Values


ElseIf cbo_OperationParam.Text = "Monthly Mean" Then
' Year and Month must be the same, then find Monthly average for Values

ElseIf cbo_OperationParam.Text = "Yearly Mean" Then
' Year must be the same, then find Yearly average for Values

End If



End Sub

This post has been edited by Student_101: 12 January 2009 - 08:05 PM

[iimememine]

Hello and welcome.

I recommend using Microsoft's solution (JET? Query engine), since you already using VB and Access.

I'd bet the performance would be much improved and the SQL simpler if you converted the values you have to an actual Date/Time field. (I think Access has a built in function dateserial for doing this, maybe VB6 also).

With just the date and the rate to worry about, I think Jet should handle many many records efficiently.

[Student_101]

iimememine, on 12 Jan, 2009 - 01:44 PM, said:

Hello and welcome.

I recommend using Microsoft's solution (JET? Query engine), since you already using VB and Access.

I'd bet the performance would be much improved and the SQL simpler if you converted the values you have to an actual Date/Time field. (I think Access has a built in function dateserial for doing this, maybe VB6 also).

With just the date and the rate to worry about, I think Jet should handle many many records efficiently.

hmmm.. as you see in the input.. all the dates are in seperate columns.. n thats why that date/time field will not work well.

I did however try grouping in MS Access, and I was able to get some results, I still need to verify these results.

Student_101, on 12 Jan, 2009 - 06:52 PM, said:

iimememine, on 12 Jan, 2009 - 01:44 PM, said:

Hello and welcome.

I recommend using Microsoft's solution (JET? Query engine), since you already using VB and Access.

I'd bet the performance would be much improved and the SQL simpler if you converted the values you have to an actual Date/Time field. (I think Access has a built in function dateserial for doing this, maybe VB6 also).

With just the date and the rate to worry about, I think Jet should handle many many records efficiently.

hmmm.. as you see in the input.. all the dates are in seperate columns.. n thats why that date/time field will not work well.

I did however try grouping in MS Access, and I was able to get some results, I still need to verify these results.

and Yes, I am using the Jet search engine.

The OpenConnection is my connection module which uses a JetEngine to connect to the MS Access Database

[iimememine]

To be clear, you can add a new date/time field to the imported data. Then you can populate it with an update query using the dateserial and timeserial built in VB(A) functions.

i.e.

?dateserial(1933,8,9)+timeserial(14,34,22)
8/9/1933 2:34:22 PM 

so

UPDATE tbl_Import_Data SET myDateField = DateSerial(tbl_Import_Data.cYear, tbl_Import_Data.cMonth, tbl_Import_Data.cDay) + TimeSerial(tbl_Import_Data.cHour, tbl_Import_Data.cMinute);

Then your queries become simple SELECT Avg(Value) FROM tbl_Import_Data WHERE myDateField Between #1/1/08# and #1/7/08#

etc.

[Student_101]

Thanks for the Help.

I used Aggregate function with grouping and it solved the problem

Regards