I have three tables DriversName, Route and Orderlines. My job, if I should accept, is to create a form for the dispatcher to assign a driver and a route to each order. Preferably in a query so I can create reports based on Route and Driver, for the loaders & production, and Driver and Route for the driver.

I have a nice query already built to capture the order lines to be delivered daily, averaging around 800 lines. My problem is relationship. There is no relationship to Orders from either the DriversName or Route tables. Unfortunately the Orders table cannot be added to as the next data download would just remove the fields. I have tried just to slam in the tables in my query but of course I get a repeat data line for every driver name and Route letter.

I need this form to have the dispatcher call up the order by number and have two comboboxes for them to lookup and assign order numbers to Driver and Route. Thanks in advance for any suggestions.

My question is, does someone have a suggestion as to how I could build a relationship of Drivers and Routes to the Order query?

Skip:

Even though you said not to say it, I'm going to say you need to revise the Orders table to include the foreign key for the driver's name and the route (An alternative is if the route and driver don't change often, assign the driver to the route and that way you only have to store either the key to the driver or the key to the route in the orders table).

you also should revise how you get the orders in the database. You don't need to recreate the table each time. You can import into an existing table and therefore your structure does not need to go away.

Thanks for responding Bob,

After thinking about the untouchable orders file I may be mistaken. I will have to ask my customer. If I understand you correctly I could create a Driver Name field and a Route field right in the Orders Table? That would really simplify my life.

Thanks for responding Bob,

After thinking about the untouchable orders file I may be mistaken. I will have to ask my customer. If I understand you correctly I could create a Driver Name field and a Route field right in the Orders Table? That would really simplify my life.

Thanks for responding Bob,

After thinking about the untouchable orders file I may be mistaken. I will have to ask my customer. If I understand you correctly I could create a Driver Name field and a Route field right in the Orders Table? That would really simplify my life.
Remember you don't have a driver NAME field, nor a ROUTE field -

You use a DriverID field and RouteID field which is the foreign key from a drivers table and routes table. You don't store the text, you store the ID number.

When I add the Primary key for the Driver and Route tables and try to use the tables in my query I lose sight of all my data. What did I do wrong?

When I add the Primary key for the Driver and Route tables and try to use the tables in my query I lose sight of all my data. What did I do wrong?

You have to use all of the applicable tables in the query so you can use the text intead of the ID's.

I am trying to create a way to assign a Driver, a Route and a vehicle to lines from my open order file that require delivery. Bob Larson has tried to help me out on this but I am either not understanding what he is telling me or I don’t know how to apply what he is telling me. I have 5 tables Order header with Order number Customer number etc., Order lines the lists a line for each part number ordered, how much to send etc., a Driver Name table, a Route table and a vehicle table.

The Order header and Line tables have the order number in common; the Driver Name, Route and Vehicle have nothing in common. If I understood Bob correctly I put a field DriverID (Number long integer), RouteID, (Number, long integer) and the same for Vehicle. I have a query pulling the Order information needed but if I put any one or all of the other tables into my query I get nothing in datasheet view. I don’t even have to put any of the fields in the query and this is what happens. I delete the tables from my query and I get all my lines back.

I would like to add three columns to my query so I would be able to make and use a form to assign a Driver, Route and Vehicle to each order line. I have roughly 850 lines per day average.

Sorry to be so long winded here so I wonder if maybe I should be in Excel rather than Access. Any clarification and help get would be greatly appreciated.

skip, have a think about this - i definitely would not consider storing ANY route details directly in the order table.

i designed a logistics system recently

i actually have tables among others for

route, AND
routedetails as well as
order details

i also have tables for driver, driver mates, driver agencies, trailers, vehicles, courier firms, etc

the route table stores things like driver, vehicle, trailer, time out, night/day trips/ changover details etc. it also debriefs mileage, hours worked, driver expenses etc - [you can even have a flag in the vehicle table, so if its a rigid say, it doesnt let you pick a trailer or forces you to pick a trailer of the correct type]

the routedetails table is the important one that manages the link to the routes - it stores things like drop order, deliverytime, pallets supplied, and orderreference, drivers notes etc -since these are related to the route and route item, but not necessarily directly related to the order. i store success/failure debrief info in the routedetails table also.

Having said that i dont even store the route details reference in the ordertable, but the orderid IS stored in the routedetails table. Therefore any unmatched order, (order table compared with routedetails table) is therefore unrouted, and needs sorting.

I also provide for extra facilities such as backloads, collections and such like

i suppose you could put this routedetails stuff in the order table, but it works really well in a separate routedetails table. it does also mean that if a delivery fails and you need to redeliver you can have another routedetail item linked to the same order, which you couldnt easily do if you only had space in the order detail table for one routing section

hence

you get

normally
route_table 1 ----------- n route_details 1 ------------ 1 order_file

but possibly
route_table 1 ----------- n route_details n ------------ 1 order_file


------------
with regard to excel, they were using excel previously, and there are/were real issues regarding user acceptance/learning curve/educational aspect with changing to access, but access is far far better for this job, once you get it going


hope this helps

Thanks for sharing. This gives me a different way of looking at my issue.