Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    jsmccloud is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    14
    Can you give us a little more detail/example of what sort of things make up an order and Load?
    I will try.

    1. A driver calls in saying he needs to go from point A to point B with a load.
    2. Permit agent creates a new order
    3. The order states is marked as "New" meaning it has not yet been worked.
    4. Permit agent asks driver to verify truck number
    5. Permit agent adds the Truck, Driver and Trailer via the Assignment ID
    6. If a truck, driver, trailer doesn't exist, one must be created and properly assigned.
    7. If adding a truck, we'll need to get information like the truck number, year, make, model, number of axles, plate, plate state and vin (all this goes in the Trucks Table)
    8. If adding a trailer, we'll need to get information like the trailer number, year, make, plate, plate state, vin, trailer length, kinpin setting, and currently active number of axles and axle spacings ( all this goes in the Trailers Table)
    9. If adding a driver, we'll get his first and last name, phone and email
    10. The Assignments Table includes the TruckID, and the DriverID and the Trailer ID that truck is assigned to
    11. I created the assignments table that links all this together, but also included a field "Status" which tells us if the assignment is "Active" or "Inactive". So for example, in the rare case a new driver, truck or trailer is changed, a new record would be created in the Assignments table with the new change, and the previous record is mark "Inactive".... meaning, do not use for future orders because it is not the current assignment. I did it this way because I did not know of any other way to safely change a piece of the assignement, without altering the assignements of previous orders (hope that makes since)
    12. The agent may verify other information to make sure nothing has changed for the Assignments, such as the number of active axles (some axles on trailers can be dropped or raised in order to distribute weight properly for a load), different trailer, new truck.... etc...
    13. Agent gets load/equipment information from the driver. This information goes into the Loads/Equipment table and tells us a description of the load, the load make/model and serial. The Load relates to the Order and not the trailer because it's always something different.
    14. Agent gets the Starting Address and the Ending Address for travel (Where the load is picked up and will be dropped off)
    15. The agent determines the route later and what state permits will be needed for travel. (Although a the driver may request a specific route)
    16. The states needed are recorded in the order.
    17. The agent gets from the driver the overall width, length and height for the load with truck and trailer. (right now I have this in the Loads/Equipment table, but I'm thinking it more pertains to the order since it is an overall calculation)
    18. The agent gets from the driver the overall weight of everything that's recorded in the order.
    19. The call ends. The agent continues to add new orders as needed.
    20. Later, the agent goes back to view the order details.
    21. The agent goes back to the order cue.
    22. The agent reviews all the new orders.
    23. The agent pulls up a map to help determine the best route for a specific order.
    24. The agent goes to each state website to review travel restrictions and requirements.
    25. If needed, the agent will order a police escort and/or pilot cars
    26. THe agent submits an order to each state to obtain a permit for travel
    27. Once all states are submitted, the order status is now "Submitted"
    28. Once the permits come back, they are uploaded to the database (right now I plan on attaching these files in the StatePermits table)
    29. The StatePermits table includes an OrderID field, so we know which order has what states. (There can be one to many state permits so this seemed to be the best way to do this)
    30. The permit fee is recorded in the StatePermits table as well as our service fee for each state.
    31. If pilot cars or escorts are needed, they prices only, will be recorded in the ORders Table. In paperwork pertaining to these two will be uploaded and attached to the order as well.
    32. I believe the permits are sent out (emailed or faxed to the driver) as soon as they are available, I'll have to double check.
    33. Once all permits are received, and the driver has all permits, the order is marked "completed"
    34. After this, the agent has to "Invoice" the order. That is as soon as the order is complete or when she is able, she sends a copy to the Trucking Company's dropbox.
    35. The order is marked "Invoiced" and no further action needs to be taken.




    Some extra information, our company is just two people, me (sort of working as IT, Finances and now Database Admin) and my wife that does everything else to communicating with drivers, getting permits, etc.

    We are only contracted to work for one trucking company. We don't expect to work for any others, but there could be a possibility. If we were to take on another trucking company, we'd need to start hiring other employees to act as permitting agents.

    So if I were to sum this down I'd say the following....

    An order will show it's status, it's Assignment (Truck, Driver and Trailer from the Assignments Table) it's Load(from the Load's Table), Permits needed for each state (from the StatePermits table), Overall Width, Length and Height, Overall Weight, Paper work for any Pilot Cars or Police Escorts (State Permits are attached to the StatePermits Table), PermitFee Total, ServiceFee Total, PilotCarCost, PoliceEscortCost, AdminCost(for any other fee), and then Grand Total Cost of everything.

    Another note, driver some times call to see how much a particular past order was. Or might need to quickly pull up an order to resend a permit that was lost or didn't receive.

    I might be missing something, but that should be a good bit of the process. Sorry it's so lengthy, but it's how I could best describe the process.

  2. #17
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    That's a very good start.
    What are some samples of LOADs?
    Also, Mike asked for some sample data in a post --I think some sample data values, even mock ups, would help.

    What exactly do you mean by Order status? What values are possible?
    What steps and things are involved in identifying the Permit(s) needed for a State? Is it the same for every State?
    Do you use GoogleMaps (or other mapping software) to identify routes etc?

    From someone who doesn't know the permitting business, it seems to me an Order is conceptually similar to a Delivery of an Item from a Starting Location to a Destination. Is that an over simplification?
    Seems Delivery involves driver, vehicle (truck and trailer), load(item), route(s), permits, additional support(escort, pilot), insurance?.......

    "If adding a driver, we'll get his first and last name, phone and email" Who records his license and license status?

    Are you aware of any commercial off the shelf(COTS) packages that have the functionality you are describing?
    What do other Permit Servicing companies you are aware of use for their processing and record keeping?

    I'm not trying to be picky. I'm trying to ensure your pieces fit together by having you tell us some of the details.

    Good luck with your project.

  3. #18
    jsmccloud is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    14
    Honestly I didn't think things would get so complicated. The more questions that are asked the more confusing and complicated things are becoming. I wanted to replace a simple spreadsheet that would give more functionality and I've looked at some solutions online that track and organize information but haven't come across anything we liked or can do exactly what we need it to. Please don't get me wrong, I want to build a good system, I just wasn't prepared for all the planning and it's starting to feel a little overwhelming at the moment.

    Thank you for asking the questions and getting me to think about different things but I think I'd like to stop here for a moment to process all the new information and go through the tutorials you were suggesting. At this point I just want to start over, follow the tutorials and do things one small step at a time.

    Once I'm caught up on everything, I'll update the forum and see how things are looking and maybe get some ideas of where to go from there.

  4. #19
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    No problem -- a pause is a good thing here.
    Good luck.

  5. #20
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by jsmccloud View Post
    I'm sorry but I'm a little lost to what you mean here. I have a Permits table now, which list all the states a permit is needed for an order. It has an OrderID field so when I go to the orders table, I can see all the permits the order relates to..
    My post was based on your opening post - and on image linked there. Now I did find the example DB you posted later - and I see that you have taken of this issue there. (But I would drop StateID there and make StateShort the primary key instead. For some 50+ states there and no massive increase of their numbers foreseen in next 10 or 20 years, I see no advantage in having an autonumeric PK for this table! Maybe you even don't need a form to update States table in your DB. When it happens, that US gets a new state at some time, this is the case where you simply can add it into table manually. Or you create special DB for this kind of information with Sates table being there, and any your databases which need a list of US sates having it linked!)

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Which relationships to use in my design?
    By mbenton in forum Database Design
    Replies: 11
    Last Post: 07-18-2016, 03:51 PM
  2. help with database design and relationships
    By Jen0dorf in forum Access
    Replies: 8
    Last Post: 08-26-2015, 11:57 AM
  3. Database Design: Many-to-Many relationships
    By rmohaisen in forum Database Design
    Replies: 11
    Last Post: 02-13-2014, 12:23 PM
  4. Replies: 2
    Last Post: 12-14-2012, 02:53 PM
  5. Database Design/Relationships
    By sloft21 in forum Access
    Replies: 1
    Last Post: 10-31-2012, 09:07 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums