Question Freight Quotes (1 Viewer)

[brianborn]

Hi,

First off I want to admit that I'm a student and also an aspiring database developer, but still in the beginning stages. So the help I'm requesting is a combination of a project to help me do my job more efficiently and with the bonus of gaining more knowledge of how a database works. Part of my daily responsibilities at work is providing freight quotes to Account Managers. Currently I keep the data like weights and the weight class needed in a spreadsheet and when I receive a quote request, it requires a lot of manual work.

My question is how do I start this database? My thoughts are that I need the following 3 tables.

1) tbl_SKU (this is a list of all the products we ship)
2) tbl_QuoteLog (to store the main details like the client, shipping cost, packaging, and total cost)
3) tbl_QuoteDetail (to store the multiple items being shipped that is linked to the QuoteLog)

The relationship from (tbl_QuoteLog - one) to (tbl_QuoteDetail - many) is easy, but I'm not sure how to tie in (tbl_SKU) to this mix. I have control of the fields that are in the tables (QuoteLog & QuoteDetail), but the SKU table comes from my company's internal database.

I'm not really sure how to explain what I need help with. If I can just get started, I'm confident that I can have fun building this database and it being useful in my daily tasks.

My vision is to have a multi-part form where the one-side table shows at the top where I enter the client's ID and other details I need and then a subform where I can click a drop down list to select items in the SKU table that will not only show the SKU, but also the description, weight and weight class.

This is only the first step. If I get this part working, I will be taking the calculated weights and classes and manually entering the details into a web form to get a shipping quote from the carrier. Sometime in the future, sending data to the carrier's server to automatically retrieve the quote is one of my goals.

Hopefully what I wrote is not too confusing.

BTW... I'm familiar with Access and find my way around, but still consider myself a novice getting close to moving to the next level.

Thanks!

Brian

 

[pbaldy]

Welcome! I see the SKU table as having a one-to-many relationship with the details table, as I assume each record there would be a different SKU. This may help when you get to the SKU dropdown:

http://www.baldyweb.com/Autofill.htm

I assume there will also be a clients table, related one-to-many with tbl_QuoteLog.

 

[brianborn]

Welcome! I see the SKU table as having a one-to-many relationship with the details table, as I assume each record there would be a different SKU. This may help when you get to the SKU dropdown:


I assume there will also be a clients table, related one-to-many with tbl_QuoteLog.

I reviewed the webpage and will try to follow it when I get to work tomorrow. Tonight I'm working on a C++ assignment.

You are correct about the one-to-many relationship and there may or may not be a separate table for clients. There are a couple of reasons why with the main reason being that usually I only have to quote once per client. It will be something to consider.

Thanks for the tip and I will see how it works tomorrow.

 

[pbaldy]

I realize it's homework, but this is the operative word:

usually.

I'd have a clients table. In real life there almost certainly would be one.

 

[brianborn]

I realize it's homework, but this is the operative word:

I'd have a clients table. In real life there almost certainly would be one.

Thanks for the help Paul and I will take your advise on the client's table. As for homework, the Access database is enjoyable for me and will be used at my job. The C++ assignment is my homework and it's not real enjoyable.

Thanks again and I will let you know how the tip works out.

 

[gemma-the-husky]

ok - the order detail links to the products table

assuming that each order, consists of order lines, and each orderl ine consists of a single product that is in the product table.

so 1 product = many order_details

As Paul says , the kjey word is your word "usually" - your data structure needs to cope with any eventuality. if usually implies there is some other possibiliity, then this structure is not correct. design the structure that 100% matches the rquirements of the system you are modelling. If you don't you get into difficulties/redesign down the road.

Having re-read, the usually seemd more concerned with single/mulpile quotes per client rather than anuthnig to do with the products

However, even if you only quote once per client, you arestill better with a separate table - you may want to extend this dbs for other uses, such as contact management - and this will be easier using a clientid in a separate table, rather than entering entering full names each time

having a separate table lets you set up clients first, helps avoids spelling errors, avoids inadvertent duplication of clients, gives more control over data integrity, and avoids the need for cascading updates, as well as allowing a more efficient key.

all good, no bad

 

[brianborn]

ok - the order detail links to the products table assuming that each order, consists of order lines, and each orderline consists of a single product that is in the product table.

so 1 product = many order_details

As Paul says , the key word is your word "usually" - your data structure needs to cope with any eventuality. if usually implies there is some other possibiliity, then this structure is not correct. design the structure that 100% matches the rquirements of the system you are modelling. If you don't you get into difficulties/redesign down the road.

Having re-read, the usually seemd more concerned with single/mulpile quotes per client rather than anuthnig to do with the products

However, even if you only quote once per client, you arestill better with a separate table - you may want to extend this dbs for other uses, such as contact management - and this will be easier using a clientid in a separate table, rather than entering entering full names each time

having a separate table lets you set up clients first, helps avoids spelling errors, avoids inadvertent duplication of clients, gives more control over data integrity, and avoids the need for cascading updates, as well as allowing a more efficient key.

all good, no bad

Sorry it took so long to respond, but got sidetracked with schoolwork and had to put this project on the back burner.

Without going into too much detail, I would be the only one using this database. Our system at work is SQL on the backend with an Access frontend; at least that is my understanding. We have one programmer and his attention is towards the clients which bring in revenue so that I have a job. I completely understand this. I've been with the company over 5 years requesting features that would make my job easier, but nothing has materialized. My hope is that if I development something useful that works and I can show it is beneficial, at the very least the programmer can incorporate it into our company database. What would be better is to have a door open for me to ease my way into helping the programmer with developing tools that would benefit my current job position. I would much rather work on real projects while going to school that on theoretical assignments that I soon forget after the class has been completed.

Anyway, I just wanted to thank those that offered assistance with this question. After looking into the autofill and some of the links, this part of the project might be a little advanced for me right now. My first project is working smoothly with shipping and creating Bill of Ladings and labels for the shipping crates.

Thanks again!