Sophisticated Many-To-Many problem

[hinesbrad]

Hi all,

I'm encountering some special issues on an Access 2007 VBA application I am writing, and having some issues and challenges correctly creating the tables to relate my data. I believe at the end of this I should have four tables total, but we'll see.

In a nutshell: I work for a warehouse that has drivers making deliveries to grocery stores. We have a numbering system that we use to refer to routes our delivery drivers run. We also have a customer table that identifies a customer number and details related to the customer.

I have a third table identifying a many to many relationship. Some stores receive deliveries many times per week across multiple stores. Some stores only receive one delivery per week. I'm using this third table to identify the relationships between stores and routes.

Here's the problem I am running into:
Suppose I have stores A, B, C and D on a route. The defaults table lists all of the possible deliveries, and let's say "C" has decided it wants to skip this particular delivery. Since I am tracking case loads and the time that a driver spends at each store, my calculations would be corrupt if I attempt to include a delivery time here since the truck was never loaded with product to deliver to this store!

So here's what I'd love to have answered:
- It seems the table defining the many to many relationship would be useful as a set of "defaults". So if I had a form in access a user could identify what stores are actually going to receive deliveries and the number of products assigned to the store, we can avoid a lot of tedious data entry by having the default stores load into a form.
- Sometimes we have a special delivery to a customer that normally isn't on a route, and gets slotted in, so suppose we have delivieres to A, B, X, C then D, how can I change this?
- What visual basic code do I need to write to make the 4th form useful for all of this?
- Finally - how do I avoid an explosive amount of data from erupting? I have 190 deliveries to make per week on average. At an average of 10 stores a route, this database could get huge quickly.

Thanks for your input and wisdom.

 

[Atomic Shrimp]

Deliveries seem key to all of this - although there are many deliveries, each delivery only belongs to one store - so that's a one to many relationship (many deliveries per store, but only one store per delivery).

Are the routes fluid, or do they consist of a tour of a predefined set of stores (albeit missing some out in instances where there is no delivery)?
If the route is a persistent property of the store, then it should be possible to start from orders/deliveries, link in the stores, then group/sort by route - that way, only those stores with orders/deliveries will even be included.

 

[gemma-the-husky]

i have an app that does something very similar

first - how do you have a table with a many to many relationship. (what is it used for?) this is an error - there shoulds be no many-to-many realtionships - break them down into two 1-many realtionships

------
one way of soving this

set up a table of master routes for each day.
so master route 1, monday(day1) vistis stores 12,13,14,23,28 etc.

two tables -
master routes
master route detail

now when you ACTUALLY do your monday route 1,

a) copy the master route 1 into the active route 1 for 13/7/09 (details as well)
b) have facilites to remove unnecessary drops
c) have facilities to add extra drops
d) have facilities to change the drop order


---------
we actually STARTED this way

but we ended up doing it a different way

we prepare a daily spreadsheet of the routes/drops we want
we import this into access
we use this to preapre the daily routes

its easier for the staff to use, because its easier for them to set out the spreadsheet.

 

[hinesbrad]

Thanks for responding!

I have the tables set in a many to many relationship because it would seem that I'd break data integrity rules if I didn't.

One customer has many deliveries. Each customer (store) has a customer number associated with them. So, if I want to say, pull a query that lists one customers deliveries, then I'd need to have that relationship exist, right?

Conversely, one route can have many stores on them. Therefore, if I wanted to see all of the customers, stores, on a route, this also implies a relationship. So, I'm having a hard time of breaking my paradigm of many to many here.

The store routes are mostly static with pre-defined deliveries. However, sometimes a store simply won't need any additional product delivered, and won't request a delivery and will simply be skipped on a route. We also have situations that arise where a customer that receives only one delivery per week has an awesome sales week and will need three. We often tack that customers deliveries onto a pre-defined route.

 

[Atomic Shrimp]

Each (one) route has many stores
Each (one) store has many deliveries

On any route, there will be many deliveries to many stores, but that's just an outcome of the above pair of one-to-many relationships, it's not a relationship in itself. Or at least that's how it looks to me.

If you want to see the deliveries on a route, you just need to join routes to stores, then stores to deliveries (in the same query).

 

[hinesbrad]

Would it be possible for you to send me a copy of the relationships table of the database under discussion? Maybe a screenprint of the relationships window of the database in question would make things a bit clearer....

Hines Dot Brad at gmail dot com

 

[hinesbrad]

Would it be possible for me to email a copy of the relationships I have set up to verify my integrity in question?

 

[Atomic Shrimp]

Could you post the screenshot here in the thread? (click 'go advanced' then use the 'manage attachments' bit at the bottom...

 

[hinesbrad]

As Requested.

 

[gemma-the-husky]

a couple of things

(minor note re stores details)
a) does a customer have 1 store only
if a customer has several outlets, can you handle this?

(mayor discussion of routes etc)
b) personally i am confused by the tables

defaultroutes
saproutes

this is ok to define the standard route - but don't you have another file for the actual route on a given day

we have something like this

tblcustomers
tblstores (so that 1 customer has several stores)
tblroute (routeid, routedate, routenameornumber, driverno, driversmate, vehicleid, debrief info, etc etc)
tblroutedrops (deliveryid, routeid, storeid, droporder, palletcount, successflags, debrief info etc)

so that 1 route= many drops
1 store = many drops

------
now as i say to populate the route, we either have a separate system of tables

masterroute
masterroutedrops

(1 to many Link)

to identify the standard "normal" route on a given day, that we can modify when the route changes. This is copied into the main database each day, so you build up a history of daily routes over time.

you cant do all this (as far as i can see) with only 1 routes table (well maybe you can with some special flags) but it seems to me you HAVE to distinguish between the master plan, and the actual route that happened.

-----------
as I also say, in use this proved too cumbersome - the master routes were being revised, or varied too frequently. The master routes had standard pallet quantities, but these were being varied all the while - so instead we ended up entering the drop details on a daily spreadsheet, ie routnameornumber, storenumber, drop info etc - and used that to generate the daily routes.

--------
this particular system was developed for a major logistics company, and was actually more complex than i have described - because the many of the actual routes were long distance and included trailer handovers etc - so we are managing two vehicles per route in some cases etc, and added in a lot of extra functionality.

But this was a big system and took 30-40 days or more to complete, using a lot of pre-existing routines.

---------
don't underestimate what is involved - when you get this working, you will almost certainly be asked to add extra stuff to it - maybe debrief info for delivery failures, mileage details, fuel used, delivery times, driver hours etc, and statistical summary information. We have even tried to build in some google mapping stuff to estimate delivery times.


I hope you find this useful

 

[hinesbrad]

Hi team,

I don't think I am doing a very good job at explaining my problem or outlining what I am trying to achieve. I'll try and do a better job on that the second go-around. Some of this information might not be value added. If it's not, please allow me to momentarily bow my head in shame. Here we go:

I've been assigned to create a performance enhancement model around a distribution facility. The facility serves about 190 grocery stores within 22 grocery chains across 5 Southwestern US States: New Mexico (headquartered), Western Texas panhandle, Arizona, Colorado and Utah. Since we have a huge geographic area covered by our distribution center, and the smallest number of customers served, our facility is under a lot of pressure to improve its performance operations. We’re trying to implement a six sigma data collection program to do exactly that. We’re interested in tracking delivery times, time spent at stores, and to constantly refine our delivery routes and store relationships to increase efficiency and throughput.

We currently have three systems in place: SAP handles our accountancy and invoicing, a web based system called Xata handles our GPS, fuel and efficiency numbers. The third system, voice to pick, takes information from Xata and gives a warehouse team the information they need to efficiently load trucks.

The company is considering getting rid of SAP. Since our facility is a pilot on this program, we need to track numbers separately from the other systems so we don’t lose the data over time / core system changes. All three systems are essentially incompatible, and are aged to the extent that we need to collect data from all of them and link them together in a useful format. Management also wants to implement a CRM tool on top of the operations improvement project, so we’ve decided that Access, and later an IIS solution, is probably the best way to go.

The facility has two departments: Sales and Logistics. The sales team is responsible for maintaining delivery times and rapport with our customers and their receiving bays. The logistics team is responsible for making the deliveries safely, quickly and accurately. Each salesperson can be associated with many stores (one to many). We’d like to be able to see if one salespersons stores are more efficient than others about receiving product. We’d also like to see if certain franchises do better than others.

SAP Doesn’t have a mechanism to identify a different franchise of store. So I can’t uniquely identify a type of store. Instead, we use a customer number and a customer name to identify each customer. (Example: Fry’s Foods Store 0923, 2300 Paseo Del Norte NE, Albuquerque customer #2525259)

We’re using the SAP Customer numbers and SAP route numbers to model the data within the database. This matches the data being placed into Xata each day.

We process the invoices, and assign a team member to pick the load
* (Voice to pick gives us some efficiency information, but it doesn’t save anything beyond three days – nor does it have the facilities to track trends)

Each time a store is assigned to a route, a customer number is linked to a stop. So, for instance:
Route #1400
Stop 1 – Customer #123456
Stop 2 – Customer #234567
As previously mentioned, some stores receive a delivery once per week. Other stores receive deliveries many times per week. The Route Numbers and customer numbers are defined in SAP. So, we have a table defining the store name, the franchise of the store, address and phone number, along with deliveries.
(This is the rationale behind a many to many relationship between the stores and the SAP Routes table under discussion)

Each day when a driver comes in, a driver should receive a header sheet telling them what truck they are driving, the stores they are going to on the route (sometimes stores won’t have a delivery despite having a scheduled time. Other times we’ll add on a store that normally isn’t on the route. This happens on about 15% of routes). The drivers should also have their most recent performance numbers, show the names of the salespersons, receivers, and the average/best store turnaround time. We’d like to use these as points of data to plot trends for the six sigma improvement project.

Our goals are:

• To track the picker efficiency – how fast the warehouse team picks loads into trucks
  (Note that our facility drivers and pickers are the same people, but we’re considering adding warehouse only team members in the near future)
• Track the amount of time, and the number of cases being sent to each store, to arrive at a units per hour figure for the stores
• Create header sheets for
• Create positive coaching tools for performance
• And finally – succeed with data collected to start a six sigma hoshin program.

So, in the previous examples given, it was suggested that each store be listed individually as a stop. Since we’re interested in tracking deliveries by each store, and the number of queries involved, I don’t understand why a join is more practical.

 

[gemma-the-husky]

(This is the rationale behind a many to many relationship between the stores and the SAP Routes table under discussion)

i think its just that access (indeed a realtional database) doesnt lend itself to a many to many relationship

ie if table a is related to table b in 2 different ways - a query will actually (wrongly) show a cross product of both relationships (probably)

to model such a relationship in access (eg) you HAVE to construct this as two 1 to many joins

as an example you need a

stores table
routes table
routedrops table

so that the routedrops table is the link that joins the routes to the stores. You could duplicate the route information (eg route1, 7/12/09) in the routedrop table, but this denormalizes the data

so you end up with a structure that looks like

routes table
routeid     routename        date        vehicleid .... etc
17           route 1            7/12/09    28
18           route 2            7/12/09    31

routedrops table
routeid    storeno   other details
17          12345     12 pallets etc  
17          A42        14 pallets etc
17          G28        17 pallets


stores table
storeid     customer   address etc
12345      other details etc
A42         other details etc
G28         other details etc

perhaps you already have this, and we are just at cross purposes