Design question (function) 1

[htdenver]

I need to design a function that will help me load a truck in the appropriate order. I have a SQL call that will bring back the following.

Drop 3 Frozen
Drop 3 Refrigerated
Drop 2 Dry
Drop 2 Frozen
Drop 1 Dry
Drop 1 Frozen

I need to load the truck in this order based on that information. Drop 3 gets unloaded first, Drop 2 second etc... These instructions need to be generated. As you can see they need to be grouped together based on their state. Frozen, Refrigerated, Dry etc…. Because we cool the sections of truck based on this. Im not sure how to read through and base decisions on records I have not looped through yet. Cant make it work! HELP.. It seems simple but it is not. I tried an array, but the sequence changes for each load. Its never the same.. You could have all dry, or any combination.

Load Drop 3 Frozen
Load Drop 3 Refrigerated
Load Drop 2 Frozen
Load Drop 2 Dry
Load Drop 1 Dry
Load Drop 1 Frozen

 

[PHV]

In the SQL you may add and order by clause:
SELECT ...
ORDER BY Drop DESC, IIf(state='Frozen',1,IIf(state='Refrigerated',2,IIf(state='Dry',3,9))) ASC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VBslammer]

[tt]
ORDER BY Drop DESC, Nz(Switch(State='Frozen', 1, State='Refrigerated', 2, State='Dry', 3), 9) ASC
[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[htdenver]

Thanks for the help! But I still need to get in correct order based on state. 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!