Create database based on 24 hour time; 15 minute increments

  • Follow


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?

0
Reply Utf 2/29/2008 5:35:00 PM

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?
>

0
Reply Utf 2/29/2008 11:27:00 PM


1 Replies
766 Views

(page loaded in 0.046 seconds)


Reply: