Min/Max Time of data in different format

[eHigh]

I have several blocks of data in the format below:
This is the datasheet view of a MS Access table.

Block Time Route
004-02 06:29 4
004-02 06:39 4
004-02 06:43 4
004-02 06:49 4
004-02 06:53 4
004-02 07:00 18
004-02 07:07 18
004-02 07:12 18
004-02 07:16 18
004-02 07:19 18
004-02 07:24 18
004-02 07:36 18
004-02 07:44 18
004-02 07:47 18
004-02 07:51 18
004-02 07:56 4
004-02 08:06 4
004-02 08:12 4
004-02 08:17 4
004-02 08:23 4
004-02 08:27 4
004-02 08:39 4
004-02 08:43 4
004-02 08:49 4
004-02 08:53 4
004-02 09:00 18
004-02 09:07 18
004-02 09:12 18
004-02 09:16 18
004-02 09:19 18
004-02 09:24 18
004-02 09:33 18
004-02 09:41 18
004-02 09:44 18
004-02 09:48 18
004-02 09:53 4
004-02 10:03 4
004-02 10:09 4
004-02 10:14 4
004-02 10:20 4
004-02 10:24 4
004-02 10:54 4
004-02 10:58 4
004-02 11:05 4
004-02 11:10 4

Would like the data in the format below.

MinTime MaxTime Route
6:29 6:53 4
7:00 7:51 18
7:56 8:53 4
9:00 9:48 18
9:53 11:10 4

Can I use a Group By or Totals query to do this? Is there a function or procedure that might rearrange the Min/Max time in the format above?

 

[randy700]

Group by should work....

SELECT MinTime:Min(Time), MaxTime:Max(Time), Route FROM YourTable GROUP BY Route

You might, however, consider renaming the Time field to avoid using any of Access reserved words.


Randy

 

[cmmrfrds]

The Partition Function might do what you need. Here is an example. Please lookup in Access Help.

SELECT DISTINCTROW Partition(datepart('n',[dbo_Invoices].[orderdate]),0,60,10) AS Range, Count([dbo_Invoices].[orderdate]) AS [Count]
FROM dbo_Invoices
GROUP BY Partition(datepart('n',[dbo_Invoices].[orderdate]),0,60,10);

 

[MichaelRed]

I do not see any (easy) way to do this in straight sql. A relativel painless approach might be to write a procedure to add a field/value (call it BolckSet?). The procedure would then loop through the table and assign the value, maintaining the same value until the route changes, the 'incrementing' it. When done, you could then at least inspect the block-route groups independently.

On the other hand, if you have to go into procedure lane anyway, it doesn't seem like much more effort to calc the min max of each set as you wend your way through the morass.

a-la

 Public Function basBlockMinMax()

    Dim dbs As DAO.Database
    Dim rstSrc As DAO.Recordset
    Dim rstDest As DAO.Recordset

    Dim strSQL As String

    Dim LclMin As Date
    Dim LclMax As Date
    Dim LclRte As Integer

    Set dbs = CurrentDb
    Set rstSrc = dbs.OpenRecordset("BlockRoute", dbOpenDynaset)
    
    strSQL = "Delete * from tblBlockMinMax;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

    Set rstDest = dbs.OpenRecordset("tblBlockMinMax", dbOpenDynaset)

    With rstSrc

        LclMin = !dtTime
        LclMax = !dtTime
        LclRte = !Route

        While Not .EOF
            If (!Route <> LclRte) Then
                'Write the New Record
                rstDest.AddNew
                    rstDest!tmMin = LclMin
                    rstDest!tmMax = LclMax
                    rstDest!Route = LclRte
                rstDest.Update

               'Reset Locals
               LclMin = !dtTime
               LclMax = !dtTime
               LclRte = !Route
            Else
               'Check / Update Local Min & Max
               If (!dtTime < LclMin) Then
                   LclMin = !dtTime
                End If

                If (!dtTime > LclMax) Then
                    LclMax = !dtTime
                End If

            End If

            .MoveNext
        Wend

        'Write Last Record
        rstDest.AddNew
            rstDest!tmMin = LclMin
            rstDest!tmMax = LclMax
            rstDest!Route = LclRte
        rstDest.Update

    End With

End Function

MichaelRed
mlred@verizon.net

 

[eHigh]

The "basBlockMinMax" function works great. Thank you!

 

[eHigh]

What if the Route does not change, but the Block changes? For example, the data below changes from Block 7-92 to 7-93 but the Route remains 7.

How could I adjust the "basBlockMinMax" function to handle this?

BLOCK TIME ALT_SORT Route

007-92 18:21 65760 27
007-92 18:38 65760 27
007-92 18:47 65760 27
007-92 18:57 65760 7
007-92 19:06 65760 7
007-92 19:11 65760 7
007-92 19:23 65760 7
007-92 19:35 65760 7
007-92 19:50 65760 7
007-93 14:25 53100 7
007-93 14:45 53100 7
007-93 14:56 53100 7
007-93 15:07 53100 27
007-93 15:11 53100 27
007-93 15:20 53100 27
007-93 15:30 53100 27
007-93 15:40 53100 27
007-93 15:57 53100 27
007-93 16:06 53100 27

 

[eHigh]

Modified if statement to handle problem above, but stuck on the problem when TIME remains the same but Route changes. For example, the third line below creates two entries and I only want one.

Any assistance would be appreciated.
Thanks.

BLOCK TIME ALT_SORT Route

007-92 18:21 65760 27
007-92 18:38 65760 27
007-92 18:47 65760 27
007-92 18:47 65760 7
007-92 19:06 65760 7
007-92 19:11 65760 7
007-92 19:23 65760 7
007-92 19:35 65760 7
007-92 19:50 65760 7
007-93 14:25 53100 7
007-93 14:45 53100 7
007-93 14:56 53100 7
007-93 15:07 53100 27
007-93 15:11 53100 27
007-93 15:20 53100 27
007-93 15:30 53100 27
007-93 15:40 53100 27
007-93 15:57 53100 27
007-93 16:06 53100 27