Hi All,
I’m new to MS Access. I am working on a project to improve an internal quotation process.
My company is a manufacturer and reseller of products to the industrial sector. We often have customers from all over the globe asking for quotations on our products. A typical request may have anywhere from 1 to 5 products. These quotations are simple in nature but their recording and processing is all done manually. I would like to get away from using excel based forms and quotations and build a simple database to capture the essential information that can be mined later for other purposes.
The business/transaction for a quotation request is like this:
1. An Enduser (company that will use our product) will request their supplier to find manufacturers like us.
2. The Supplier will come to my company asking for a quotation and provide details about their company and their Enduser.
3. This information is used to produce the quotation and is sent to the supplier company who then forwards it to the Enduser.
Based on this I have decided that the following tables are an efficient design:
1. Products
2. Orders
3. Order Details
4. Customers
5. Endusers
Table 1: Stores product information (essentially list price, product names and model codes).
Table 2: Stores information about the order (order number, date requested/produced, and other administrative information). It functions like a connecting table to connect all the other tables together.
Table 3: Stores details about the products related to the order. Since there can be multiple products, this table will be ran through a query and used as a subform on the Order’s form. Also, since prices may change, I believe it's best to use a Dlookup function to feed this information through to the forms. Please tell me if this is the best approach.
Table 4 and 5: This is the source of my problem. Both Customers and Endusers have essentially the same information, except we want to know whether the record is a Customer or an Enduser. Both must appear on the form for selection and on the final quotation.
Question:
1. Should I use two different tables to store Customers and Endusers information? I feel like this goes against database design principles because both tables essentially store the same information.
2. Should I store the information for these two tables into one table and then use a MtoM relationship with the Orders table?
Please let me know what you think about how I should design this.
Thank you,
I’m new to MS Access. I am working on a project to improve an internal quotation process.
My company is a manufacturer and reseller of products to the industrial sector. We often have customers from all over the globe asking for quotations on our products. A typical request may have anywhere from 1 to 5 products. These quotations are simple in nature but their recording and processing is all done manually. I would like to get away from using excel based forms and quotations and build a simple database to capture the essential information that can be mined later for other purposes.
The business/transaction for a quotation request is like this:
1. An Enduser (company that will use our product) will request their supplier to find manufacturers like us.
2. The Supplier will come to my company asking for a quotation and provide details about their company and their Enduser.
3. This information is used to produce the quotation and is sent to the supplier company who then forwards it to the Enduser.
Based on this I have decided that the following tables are an efficient design:
1. Products
2. Orders
3. Order Details
4. Customers
5. Endusers
Table 1: Stores product information (essentially list price, product names and model codes).
Table 2: Stores information about the order (order number, date requested/produced, and other administrative information). It functions like a connecting table to connect all the other tables together.
Table 3: Stores details about the products related to the order. Since there can be multiple products, this table will be ran through a query and used as a subform on the Order’s form. Also, since prices may change, I believe it's best to use a Dlookup function to feed this information through to the forms. Please tell me if this is the best approach.
Table 4 and 5: This is the source of my problem. Both Customers and Endusers have essentially the same information, except we want to know whether the record is a Customer or an Enduser. Both must appear on the form for selection and on the final quotation.
Question:
1. Should I use two different tables to store Customers and Endusers information? I feel like this goes against database design principles because both tables essentially store the same information.
2. Should I store the information for these two tables into one table and then use a MtoM relationship with the Orders table?
Please let me know what you think about how I should design this.
Thank you,
