Making a Delivery Schedule

[Wictolia]

Hello all you brilliant people! I know this is probably very basic and elementary for you, but it has me scratching my head.

My boss wants me to create a database or spreadsheet (?) that allows a trucking delivery schedule to be made from one distribution center to many different stores. We want to use a minimum of three trucks and each store should have 1-3 deliveries per week.

I have entered all the data but cannot seem to wrap my brain around how to get a report that I can "play" with. I have even entered mileage to and from each store from the distribution centers and mileage to and from each store.

This is how it should work: Monday: Delivery from DC1 to Store 24223, 23885 and 24387. I need to calculate round trip mileage from the DC to each store and back to the DC again. I need to be able to change the variables for other stores for different days.

Is there a simple way to do this?

Thanks so much for your help!!

[Wictolia]

By the way, I have checked other threads and this one is close, but not what I need:

http://www.excelforum.com/excel-gene...rom-table.html

That has SKU numbers of products. I need to be able to choose up to 3 destinations and see the mileage associated with those locations.

[alansidman]

Do you want to do this in Excel or Access? I ask because you posted an excel spreadsheet and referenced an excel solution but posted in the Access Forum.

[Wictolia]

Either way is fine with me. I would like to use the method with the best user interface. It's pretty easy to import or export from one into the other right?

[alansidman]

Look at this link. I saw a couple of schema's that may be a good starting point if you decide to go the Access route.

Look in the Maritime Section and the Travel and Transport Section and Employees Works Schedule (An idea on how to set up a rota for deliveries, perhaps)

http://www.databaseanswers.org/data_models/index.htm

Tables needed:
Locations
Trucks
Distances between each location

You would also need a junction table because you have a many to many relationship between the locations to calculate the distances.

Off the top of my head, this is how I would start.

Need to think about what is the maximum number of stops can a truck do in one day. This will be a key factor in setting this up. Is there a company policy for this (something for you to think about as you set this up.)

Most important question though --- are you comfortable working in an RDBMS environment? If not, then Excel will be your only choice.

[Wictolia]

Wow! Great site! There is definitely some stuff in there I can use. Thank you so much!

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.