If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Create database based on 24 hour time; 15 minute increments
I need to set up a MS Access Database that stores test data that was
performed at certain times (rounded to the nearest 15 minutes). I need to produce a query that shows each 15 minute interval within a 24 hour period. Any 15 minute increment that doesn't have test data needs to be represented by a -1. Any suggestions on how to do this? |
#2
|
|||
|
|||
Create database based on 24 hour time; 15 minute increments
If you first create a table which has one row for every 15 minute interval
over a suitable period you can then LEFT JOIN this to your table of test results to return a row for every time regardless of whether there is test data for that time. As it happens I have a VBA function which will create such a table (it was originally written for scheduling appointments). The function uses ADOX as well as ADO do you'll need to create a reference to the Microsoft ADO Extensions for DDL and Security library if you don't already have one (Tools | References on the VBA menu bar). Then paste the following function into any standard module: Public Function MakeSchedule(strTable As String, _ dtmStart As Date, _ dtmEnd As Date, _ dtmDayStart As Date, _ dtmDayEnd As Date, _ intMinuteInterval As Integer, _ ParamArray varDays() As Variant) ' Accepts: Name of schedule table to be created: String. ' Start date for calendar: DateTime. ' End date for calendar: DateTime. ' Time when first 'time-slot' starts each day: DateTime ' Time when last 'time-slot' starts each day: DateTime ' Length of each 'time-slot' in schedule in minutes: Integer ' Days of week to be included in calendar ' as value list, e,g 2,3,4,5,6 for Mon-Fri ' (use 0 to include all days of week) Dim cmd As ADODB.Command Dim cat As ADOX.Catalog Dim tbl As ADOX.Table Dim strSQL As String Dim dtmDate As Date Dim dtmTime As Variant Dim varDay As Variant Set cmd = New ADODB.Command cmd.ActiveConnection = CurrentProject.Connection cmd.CommandType = adCmdText Set cat = New Catalog cat.ActiveConnection = CurrentProject.Connection ' does table exist? If so delete it On Error Resume Next Set tbl = cat(strTable) If Err = 0 Then strSQL = "DROP TABLE " & strTable cmd.CommandText = strSQL cmd.Execute End If On Error GoTo 0 ' create new table strSQL = "CREATE TABLE " & strTable & _ "(StartTime DATETIME," & _ "CONSTRAINT PrimaryKey PRIMARY KEY (StartTime))" cmd.CommandText = strSQL cmd.Execute ' fill table with dates of selected days of week For dtmDate = dtmStart To dtmEnd For Each varDay In varDays() If Weekday(dtmDate) = varDay Or varDay = 0 Then For dtmTime = dtmDate + dtmDayStart To dtmDate + _ dtmDayEnd Step intMinuteInterval / 1440 cmd.CommandText = strSQL strSQL = "INSERT INTO " & strTable & "(StartTime) " & _ "VALUES(#" & Format(dtmTime, "mm/dd/yyyy hh:nn:ss") & "#)" cmd.CommandText = strSQL cmd.Execute Next dtmTime End If Next varDay Next dtmDate Set cmd = Nothing End Function To create a table called FifteenMinuteSchedule covering from the start of this year to the end of 2010 for instance call the function with: MakeSchedule "FifteenMinuteSchedule", #01/01/2008#, #12/31/2010#, #00:00:00#,#23:45:00#, 15, 0 The above will probably have split over two lines in your newsgroup reader but should be entered as a single line (in the debug window, aka the Immediate pane, for instance – press Ctrl+G to open the window). Assuming your table of test data is called Tests and has a column TestDateTime of date/time data type and a column TestResult a query to return rows for a 24 hour period, the start time of which you are prompted for as a parameter) would be: PARAMETERS [Enter start date/time:] DATETIME; SELECT StartTime, IIF(TestResult IS NULL,-1,TestResult) AS Result FROM FifteenMinuteSchedule LEFT JOIN Tests ON FifteenMinuteSchedule.StartTime = Tests.TestDateTime WHERE StartTime = [Enter start date/time:] AND StartTime DATEADD("d",1,[Enter start date/time:]) ORDER BY StartTime; So if you enter 02/28/2008 03:00 (assuming a US short date format is in use) you'd get results from 3:00 AM on 28 February 2008 to 2:45 AM on 29 February 2008. If you are using the query as the RecordSource for a report omit the ORDER BY clause and use the report's own internal sorting mechanism to order the rows. Ken Sheridan Stafford, England "DavisGail" wrote: I need to set up a MS Access Database that stores test data that was performed at certain times (rounded to the nearest 15 minutes). I need to produce a query that shows each 15 minute interval within a 24 hour period. Any 15 minute increment that doesn't have test data needs to be represented by a -1. Any suggestions on how to do this? |
Thread Tools | |
Display Modes | |
|
|