Schedule planing procedure

Question

My family owns a medium sized transport company and when i came in the business 3 years ago we had no software to manage all the transports we had to do. With 20 drivers this was a problem, so i sat down, learned the basics of VBA and made an app trough excel to manage/dispatch the different trips by email to our different drivers. It "works" for now but we are planing a future expansion so i started learning Xojo (im on a mac, closest thing to VBA)

We receive a Excel file to tell us which trips we have to do one day ahead (we transport people). Basically, its a sheet with all the different customers. I import this sheet in a "week file" to use the data afterwards trough different macros. There is lot of irrelevant information in this sheet but the column we will be interested too are the Type, Number and Hour.

So basically, i have to take all my rows (100+), group them by type and number, then order them by hour.

Heres a quick example of what my sheet looks like when sorted (the different colours are different drivers):
sorted sheet

I think my procedure to get this result is not really that good. I loop trough all the rows in a data sheet (which is hidden) with a If statement checking if its a new type or trip number, save the time and row reference (first row, last row) in an array, then loop trough the array to export the ranges on the display sheet. Keep in mind that i wrote this 3 weeks after learning that VBA existed. It "works" but id like to have a better process.

I will be using SQLite to store all the information in the application im starting to write. Id like to have suggestion as to how i could sort all my data faster using SQL. Im looking for a procedure, i can figure out a way to code it.

Heres a sample of the code i made.

    For RowSearch = 2 To RowCount
        If Sheets(DataSheetName).Cells(RowSearch, 2).Value <> Sheets(DataSheetName).Cells(RowSearch - 1, 2).Value _
        Or Sheets(DataSheetName).Cells(RowSearch, 3).Value <> Sheets(DataSheetName).Cells(RowSearch - 1, 3).Value Then
            Blocks(TripCount, 1) = Position
            Blocks(TripCount, 2) = RowSearch - 1
            Blocks(TripCount, 3) = Format(Sheets(DataSheetName).Cells(Position, 4).Value, "hh:mm")
            TripCount = TripCount + 1
            Position = RowSearch
        End If
    Next RowSearch

    Blocks(TripCount, 1) = Position
    Blocks(TripCount, 2) = RowSearch - 1
    Blocks(TripCount, 3) = Format(Sheets(DataSheetName).Cells(Position, 4).Value, "hh:mm")

'Sorts the blocks by time, loops trought the trips row range to sort the trips by time and type and writes the blocks
    RowSelect = 1

    For BlockSearch = 1 To TripCount
        TempHour = "99:99"

        For RowOrder = 1 To TripCount
            If Blocks(RowOrder, 3) <= TempHour Then
                TempHour = Blocks(RowOrder, 3)
                Trips(BlockSearch, 1) = Blocks(RowOrder, 1)
                Trips(BlockSearch, 2) = Blocks(RowOrder, 2)
                RowChange = RowOrder
            End If
        Next RowOrder

        RowRange = Trips(BlockSearch, 2) - Trips(BlockSearch, 1) + 1
        FieldValue = Sheets(DataSheetName).Range("A" & Trips(BlockSearch, 1) & ":" & "R" & Trips(BlockSearch, 2))
        Sheets(SheetName).Range("A" & RowSelect & ":" & "R" & RowSelect + RowRange - 1) = FieldValue
        Sheets(SheetName).Rows(RowSelect + RowRange).Insert Shift = xlDown
        RowSelect = RowSelect + RowRange + 1
        Blocks(RowChange, 3) = "99:99"
    Next BlockSearch

You've made a good choice to use a database to store your data. I get the feeling you are not very familiar with database queries, though? SQL queries are an easy, straightforward way to get a view of the database sorted/grouped/filtered. Your VBA code would likely be replaced by a single SQL query. — Justin, Jul 07 '14 at 20:24
Be sure and visit Famous Canuck and Database Genius Tony Teows at http://msmvps.com/blogs/access/archive/tags/Granite+Fleet+Manager/default.aspx ... he uses a database to manage a truck fleet! — Smandoli, Jul 07 '14 at 20:44

score 2 · Answer 1 · answered Jul 07 '14 at 20:30

In SQL, "grouping" is an operation that not only partitions the rows into groups, but also aggregates all a group's rows to create a single output row for each group.

In your example, the rows are simply sorted by type, number, and hour, which would require a query like this:

SELECT *
FROM MyTable
ORDER BY Type, Number, Hour

Schedule planing procedure

1 Answers1