How to automated this query problem and output the result?

[changeable]

Hi all! I have a question on how to automate this process.

i have some data which looks like these:
Time Day Y X Speed
6:12:21 Monday 3.137623456 101.6953814 57
6:12:27 Monday 3.136615051 101.6952216 71
6:12:32 Monday 3.135641022 101.6951024 75
6:12:37 Monday 3.134624022 101.6949792 75
6:12:42 Monday 3.133702706 101.6947838 75
6:12:47 Monday 3.132911451 101.6942716 71
6:12:53 Monday 3.132395216 101.6934099 61
. . . . .
. . . . .

and i need to do some filtering on it which is based on the following criteria:

Day Time Segment
Monday 7:00:00 3.137263<=Y<=3.138149
Tuesday 7:05:00 3.136371<=Y<=3.137263
Wednesd 7:10:00 3.135472<=Y<=3.136371
Thursday 7:15:00 3.134575<=Y<=3.135472
Friday 7:20:00 3.133716<=Y<=3.134575
Saturday 7:25:00 3.13297<=Y<=3.133716
Sunday 7:30:00 Y<=3.13297AND101.693463<=X
7:35:00 101.692597<=X<=101.693463

i.e. I need to have the average speed and standard deviation of a group of data that fall in:
certain "Day" (monday....sunday) and within certain "time" (7:00:00 to 7:05:00, 7:05:00 to 7:10:00, up to 20:55:00 to 21:00:00) and satisfy certain criteria "segment " (3.137263<=Y<=3.138149.and so on....)

The results of the query may need to output to different table for each "Day".

It is a tedious work if i have to obtain the query by manually define the filter criteria as i have to repeatly select those criteria one by one which may results to 7(days) x 169 (duration) x 127 (segment criteria) = 150241 trials!!!!

Please, anyone, show me how to solve it by vb or macro or other better way...., thanks a lot.

 

[mdmilner]

Re: How to automated this query problem and output the resul

Step 1:

Try creating a small table with each of the values you're presenting as a sample in it as a separate record.

Step 2:

Depends on what you need to do. If you need ALL of them used, my suggestion is to write a VBA routine that creates/recreates a query as many times are are needed. If each query is only run once, then I'd recommend NOT creating a new table for each day. Just use the query. If it's multiple queries for each given day, you may end up writing your procedure to 'delete' all the records in the existing daily table and then build append queries to write all the data to it.

Here's a basic 'how to grab data out of a table'

Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL, strVal, strVal2 As String

Set dbs = CurrentDb()

strSQL = "SELECT * FROM tblName"
Set rs = dbs.OpenRecordSet(strSQL, dbOpenSnapshot)

With rs
  Do Until rs.EOF
    
    ' place code here to read data out of the table
    ' can save/hold the data as needed
    strVal = .Fields(1).Value  'How to reference by position
                                        ' By Default, the first field is 0
                                        ' this gets the 2nd column
    strVal2 = !Speed    ' Another way to reference fields by name

    .MoveNext
  Loop
End With

Code to create a new query would look like this:

Dim qdf As QueryDef
strSQL = "PROCEDURE Split; Select * from tblName"

Set qdf = dbs.CreateQueryDef("name_of_query", strSQL)

And you can get more complex by using fields (grabbed above) to use within queries.

strSQL = "PROCEDURE Split; Select * FROM " & strVal2
                                       ' this inserts the value in strVal2 in place of
                                       ' the tablename.
Set qdf = dbs.CreateQueryDef(strVal, strSQL)
                                       ' And here I'm using the contents of strVal
                                       ' to define the name of my query

I realize this isn't a complete solution. It isn't every step, but that's most of the fun of learning to work with access. Figuring it out for yourself so next time you don't have to ask, or next time somebody asks you.

Mike