1. ## Array?

Dear All,

I have a database that that prints out a delivery schedule for deliveries. Each vehicle has a starting point and has to collect from a particular depot, make a delivery, go to the nearest depot to collect, then deliver etc. This schedule is organised manually and it takes a lot of experience and time to do it.

I would like access to assist in planning the delivery schedule by suggesting the shortest route.

I think this could be done by loading the results of a query in to an array and then performing calculations on the info in the array and then marking the delivery record as 1st 2nd 3rd etc.

I would be very grateful if you could direct me to a site or publication that covers this type of problem.

2. ## Re: Array?

It sounds like you're reinventing the critical path method. I haven't seen an algorithm for that in VBA, but there probably is one out there somewhere if you do a web search on critical path method.

3. ## Re: Array?

Dear Charlotte,
I have had a look on the web at

4. ## Re: Array?

A bubble sort is a fair approximation of what it sounds like you're trying to do. To get a table into an array, you would load the table values into a recordset and then stuff them into an array. But since you would already have a recordset, what would be the purpose of the array except to hold the most current values for comparison?

You spoke of doing some "simple arithmetic" on the values, but you haven't said what that entails. Is the math part of the comparison or are you actually changing values in the array? Arrays are handy but somewhat rigid, so why don't you provide more detail on the comparisons you need to make and the calculations and maybe we can come up with a method.

5. ## Re: Array?

Some thoughts:
I think that the bubble sort is the least of your worries, first you need some data to sort.
Does each vehicle always start from the same depot?
How is the locality for each depot and each delivery address stored? are we talking grid references, street addresses, radial distance from depot?
You will also have to account for the urgency of the delivery, because by delivering on the closest path (minimising distance travelled) you will upset your outlying customers.
How often is the database updated, and how live is the data? Can you assume that the delivery data for the day is fixed (and that further orders will be delivered tomorrow?)

I suspect that the data you need to collate is something like this:
for the depot: next_closest_delivery

Then you need to build a path and minimise it. It may be that you will use a sort to minimise that path, or maybe not.

I am sure you will be aware that the first closest delivery won't necessarily be the best choice. This will be true for every single pickup and delivery.

You will also learn (if you haven't already) that humans are rational and that computers are logical. Sometimes it's very efficient being rational. Have fun!!

6. ## Re: Array?

I have a very simple suggestion regarding the actual problem, altough it won't help you understand arrays in Access.

Some time in the past it was beaten into my head that if you couldn't solve a problem on paper there was no point trying to code it. So how exactly does the manual process work? What are the steps required to generate the delivery schedule? This should give you a lot of information as to how the problem should be solved.

It isn't obvious to me that this is a problem that will be solved by sorting.

More physical questions:
Can each driver pickup more than one delivery at a time?
How exactly are the jobs split between drivers?

7. ## Re: Array?

Dear Charlotte & Marty,
Thank you for your responses to my question. As suggested I have looked at the problem from the point of view of solving it on paper, and as I believe you guessed, a lot of experience goes in to the process which would be difficult, if not impossible to reproduce in code. I think the best approach is for me to develop a simple sorting procedure to aid the operator and hopefully improve it over time.

8. ## Re: Array?

It may not be that bad! If your simple sorting procedure offers the user the next closest deliveries, you're halfway there (assuming there isn't a dirty great river through town and only one bridge over it!).
My aim was to clarify the problem, not eliminate the project!

9. ## Re: Array?

Rupert: The problem you are describing is a clasic that we use to solve when I was in school (back in the dark ages) using a technique called Linear Programming (nothing to do with computer programming). There have probably also been other techniques developed for this problem since I was in school. There are also commercial products available for solving this type of problem (you might search for route management systems if you are interested). What I am trying to say is that you might come up with some program steps to help in the process of developing the delivery routes, but that is all you will have when you are done. If you really want to come up with a solution to the whole problem, you will need to either buy a solution, or analyze the whole problem and use a technique like Linear Programming.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•