Advice on Tables & Relationships in Access 2010

[sliz]

Good evening all.

I'm a big Access noob and I'm putting together a little Database to plan transport runs. I've been pondering this for a while now, and I still can't decide the best way to handle the information. Essentially any customer can place numerous orders, and each delivery vehicle will carry numerous orders on its delivery run, but obviously each order can only be delivered on one van to one customer. My tables and relationships are currently these:

Currently the user selects a delivery date when the Order Record is created. A Transport Planner then filters the Order Table by Delivery Date and assigns Delivery Vehicles and Drop Numbers to individual Orders to "create" each delivery run. This is all done via a Form so the user is kept away from datasheet views, etc.

The scope of the database isn't huge - we deliver no more than 7 or 8 orders per delivery van, and we only have 5 vans.

My head is telling me that I should have a Delivery Run Table which would contain:

Delivery Run No (PK)
Delivery Date
Job Number -> From the Order Table
Registration -> From the Vehicle Table
Driver Id -> From an as-yet-uncreated Drivers Table

amongst other things, to better manage individual delivery runs. It strikes me as being both logically and theoretically best practice to store the information in this way, but I can't think of the best way to implement it.

The original user must specify a delivery date upon creation of the order, as this delivery date drives all processes before it, including manufacture. I can't picture a way that this can still happen but the table above could be created/implemented. I think I could programmatically create the delivery runs if the Delivery Date stays in the Order Table (by the user filtering by Date, assigning vehicles and then clicking a "Create Runs" button that creates the new Delivery Run records by looping through each job on the specified date, and grouping them by Delivery Vehicle). However this takes the Delivery Date variable away from the Delivery Run Table, which doesn't seem right to me.

Any advice would be appreciated!

[NeoPa]

This is more of a design question rather than a technical one (so I see this as being mainly your responsibility - presumably what you're being paid for), however, the question is pretty well asked, which is always a bonus, so I would comment that you really do need to ask yourself the question about what exactly drives what. This won't necessarily be easy to answer, but you must nevertheless, if you ever hope to come up with a workable solution (and be under no illusion, this is a problem that many have struggled with).

The main issue from my (very limited) perspective, is that you are considering storing the van as part of the Order data. That seems misplaced to me. The Transport Manager is not going to be entering orders so why have a Sales Clerk making that decision? Just a thought.

My main advice though, is to take a step back and, however complicated this may be, get yourself a very clear understanding of what goes where from what you know is ultimately required. I can tell you, that any effort put in at this stage will more than compensate you later. This is certainly the most important stage of the project. Good luck.

[sliz]

Hi, thanks for your time.

You're correct; it is a design issue, but unfortunately I'm not being paid for this!

Initially I had this small database set up so the Transport Manager could see each new Order created by the Sales Clerk (as the two were located in different offices). Within each new Order the SC would select the Delivery Date, and the Delivery Vehicle and Delivery Booked fields would default to "Unassigned" and "False". A sub-form displayed all "unbooked" orders, so the TM could arrange his delivery runs (using the filtering process mentioned above), assign a Vehicle and Drop Number to each Order, and declare it "Delivery Booked". This formed the basis of printing Run Sheets and Delivery Notes by the TM filtering filtering the Order Table by Delivery Date and then by Vehicle.

However, our TM has now left the company. I further developed the database to use the Google Static Maps API to plot all the drops for any given day on a map, to help both the SC and the interim TM who could look at each day's jobs group them geographically using the following Form:

edit - it appears the picture doesn't work - here is the direct link - http://i.imgur.com/qeuGB.png

I then went a step further by writing a routine that lets the user find the optimal delivery order but passing the postcodes of each Delivery Date/Vehicle combination into the Google Directions API.

Unfortunately, some error in my VBA led to Access deleting my project. I do have a backup, but I thought I'd take this opportunity to start again from scratch and try and redesign my database correctly.

[sliz]

I guess my main query is to how best implement the SC needing to define the Delivery Date when creating the order, but then somehow integrating this to use the (not-yet-created) Delivery Runs table.

[NeoPa]

I'm afraid to help with that would involve a lot more time and effort understanding where you're coming from than I like to devote to a single question. That, and my previous comment about the design being down to you. We can generally help with specific technical questions, but design choices, other than very basic conceptual ideas, rarely fit within that category. I'm sure when you've managed to handle that overall question you'll have a good appreciation of how much it entails. Not typically something you can handle in a few minutes.

Best of luck anyway.

[sliz]

I understand - thanks for your response.

I'll be sure to let you know how I get on!