Programmatically load products from table in correct order

[htdenver]

I had posted this a while back but I still haven't been able to figure it out. I need to get products loaded in a truck for delivery in correct order based on what type of product it is. Frozen needs to be grouped with Referated WHEN POSSIBLE according to the Drop sequence. Drop 3 would be last stop and so on. This is because we insert divider to keep frozen and Refrig cold. For Instance if you had the following.

Drop 3 Frozen
Drop 3 Refrig
Drop 3 Dry
Drop 2 Refrig
Drop 2 Dry
Drop 1 Dry

It would need to be grouped like so..

Load Drop 3 Frozen (section of truckcold)
Laad Drop 3 Refrig (section of truckcold)
Insert Divider
Load Drop 3 Dry
Load Drop 2 Dry
Insert Divider
Load Drop 2 Refrig (section of truck cold)
Insert Divider
Load Drop 1 Dry

That way we can load so that when we unload at the stops the goods are in order form back to front. They can't unload and reload to get to the drops in back if they werent sequenced correctly.. I know this is a pain. That is why Im asking.. Im just pulling my hair out.

Thanks!

 

[Dor100]

I think it would help a lot if you posted your basic table(s) design as well.

 

[htdenver]

Yes you are right, the simple table consists of the following.

LoadNumber DropNumber FreightState
6771 3 Frozen
6771 3 Dry
6771 3 Refrigerated
6771 2 Dry
6771 2 Refrigerated
6771 1 Frozen
6771 1 Refrigerated

The sequence changes for each load number to a particular destination. Could be all dry which is easy just load truck starting with load 3 , 2, 1, etc.. Hard part is figuring out if you need to load Frozen, Refrig and Dry. I hope this helps Im really struggling with this. I'm using:
ORDER BY Drop DESC, Nz(Switch(State='Frozen',1,State='Refrigerated', 2, State='Dry', 3), 9) ASC

But that is not giving me the right sequence only sorting drop numbers together.

Thanks for any help.

 

[htdenver]

Sorry FreightState column heading is DropDesc as in the SQL.

 

[Remou]

This is very, very clumsy and more full of holes than a sieve, but there may be the germ of an idea:

Sub try()
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb

sql1 = "Select * from [drops] where [state]='Frozen' Or [state]='Refrigerated' order by [drop] Desc"
sql2 = "Select * from [drops] where [state]='Dry' order by [drop] Desc"

Set rs1 = db.OpenRecordset(sql1)
Set rs2 = db.OpenRecordset(sql2)

Do While Not rs1.EOF()
If rs1!drop >= rs2!drop Then
    vdrop = rs1!drop
Else
    vdrop = rs2!drop
End If
    Do While rs1!drop = vdrop
        Debug.Print rs1!drop; rs1!state
        rs1.MoveNext
        If rs1.EOF Then
            Exit Do
        End If
        If rs2!drop = vdrop And rs1!drop < vdrop Then
            Exit Do
        End If
        If rs2!drop < vdrop And rs1!drop < vdrop Then
            vdrop = rs1!drop
        End If
    Loop
    
    Do While Not rs2.EOF()
        If rs1.EOF And Not rs2.EOF() Then
            vdrop = rs2!drop
        End If
        
        If rs2!drop = vdrop Then
            Debug.Print rs2!drop; rs2!state
            rs2.MoveNext
            If rs2.EOF Then
                Exit Do
            End If
                If rs2!drop = rs1!drop Then
                    Debug.Print rs2!drop; rs2!state
                    rs2.MoveNext
                Else
                    Exit Do
                End If
        Else
            Exit Do
        End If
    Loop
Loop
End Sub

 

[SkipVought]

FYI:

The ORDER of loading data is irrelevant.

You Order data when you run your queries.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}