I have created a database that I intend to use in the field of logistics. I have done all I can to take my time and normalize the data as I have gone along. Here are the tables I have:
I would LIKE to create ONE form with 2 tabbed subforms. Using this form, I would like to enter orders complete with the details of the order. With a second tab ON that subform, I would like to have the Purchase order details. There I would like to have a button that I can press to "print" the purchase order to send to the carrier hauling the load.
I am NOT a database guru. I understand SQL language of SELECT, FROM, WHERE but beyond that please give me some grace and patience. I try to use the wizards when possible to ensure I don't mess something up.
Thank you for your help!
Katie
- Carriers - PK is an auto number and the rest of the table contains details pertinant to that carrier in particular.
- Commodity - PK is an auto number. Table lists commodity hauled on any particular load (aka inventory) ex. drums, pipes, etc.
- Customers - PK is an auto number. Right now I only have the PK and company name. I don't need to track other customer detail in this system since the DB will primarily serve the needs of the freight line - not the customer.
- Fleet - PK is an auto number. Table lists different types of trucks used for hauling. This serves as the foreign key on the carriers table (3 times) and on the orderline table.
- Insurance Carriers - PK is an auto number. Table lists insurance providers for carriers since many freight lines use same insurance companies.
- Load Status - PK is an auto number. I created this table to update each load within an order for status. Sometimes you will have 5 trucks in one order and I need to know at a glance what is pending, confirmed, delivered, etc.
- Order - PK is an auto number. This table is essentially the header of an order. It is intended to track OUR customer details pertaining to the order. Who ordered it, order date, the customer's PO number if applicable.
- OrderLine - PK is the FK of the Order Table AND FK of the Fleet Table. This has caused problems because when I link this to a PO for the freight carrier, it is redundant and more than one can be available. What can I do to link this to the PO for the carrier? Is there a better option (autonumber & Order FK maybe?) This table maintains the origin, destination, commodity, requirements, qty, quote, pick up number and dates for ready/required delivery.
- Purchase Orders - PK is auto number. I think I need to create a header as I did for orders. This ultimately is for the freight carrier ONLY. It will have their info on top as my order table houses my customer info. The details would hold the info of the orderline table and include rate for transit. There is one column that is calculated in case there is unforeseen detention, or other charges. I am still trying to figure out how to best capture this because ultimately on my actual print out, it would appear only if populated. Any suggestions here? I would also love to have a check box that resulted in the "fee type" that prints out. ex. Truck Not Used, Detention, etc....
- Shipping Addresses - PK is an auto number. This table houses all of the address to and from which we ship. It is used in the Orderline table and ultimately, I want the full address to print on the purchase order for the carriers.
I would LIKE to create ONE form with 2 tabbed subforms. Using this form, I would like to enter orders complete with the details of the order. With a second tab ON that subform, I would like to have the Purchase order details. There I would like to have a button that I can press to "print" the purchase order to send to the carrier hauling the load.
I am NOT a database guru. I understand SQL language of SELECT, FROM, WHERE but beyond that please give me some grace and patience. I try to use the wizards when possible to ensure I don't mess something up.
Thank you for your help!
Katie
