If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Quotes to Orders to Invoice design - Help!!
Thanks for your time. I will take your info home with me tonight and
implement it. Can you please check this thread out in the morning in case I have questions? (No news is good news.) Thank you very much. "mnature" wrote: I'm going to put all the table together here, along with comments about them. You have dealers and customers: DEALERS Dealer_ID (Primary) DealerInfo CUSTOMERS Customer_ID (Primary) CustomerInfo You have quotes, based on specific dealers and customers, containing specifics about the quote: QUOTE Quote_ID (Primary) Dealer_ID (foreign) Customer_ID (foreign) MiscQuoteInfo You have part(s) that belong to a particular quote: QUOTE_PARTS Quote_Parts_ID (Primary) Quote_ID (foreign) Part_ID (foreign) PartQuantity These are your parts: PARTS Part_ID (Primary) PartNumber PartName Sales orders are drawn up once the quote is accepted, with the ability to have more than one sales order per quote: SALES ORDERS Sales_Order_ID (Primary) Quote_ID (foreign) Sales_Order_Info Once the sales order is drawn up, invoices can be created, with the ability to have more than one invoice per sales order. The serial numbered part is actually in a 1:1 relationship with the invoice table, but that allows all of the information about the part to be kept separate from your invoice information. INVOICE Invoice_ID (Primary) Sales_Order_ID (foreign) Invoice_Info Serial_Number (foreign) SERIAL_NUMBERED_PARTS Serial_Number (Primary Key) Serial_Numbered_Part_Info WARRANTY_CLAIMS Claim_Number (Primary Key) Misc Warranty Info Serial_Number You will, of course, have to go into the relationships window and connect all of the foreign keys back to the primary ones. This should reduce the number of tables you have, but the database shouldn't be so confused about what relates to what. Start out with queries on related tables. You will probably want to use the form wizard, and allow it to create some subforms for you. |
#12
|
|||
|
|||
Quotes to Orders to Invoice design - Help!!
I have made the changes to my database to reflect what you have said. I
still have two questions: 1. How do I bring in more than one line item from Quote Parts into a Sales Order? It keeps generating a new Sales Order number for each line item. 2. How do I bring in more than 1 line item from a Sales Order into an Invoice? "mnature" wrote: I'm going to put all the table together here, along with comments about them. You have dealers and customers: DEALERS Dealer_ID (Primary) DealerInfo CUSTOMERS Customer_ID (Primary) CustomerInfo You have quotes, based on specific dealers and customers, containing specifics about the quote: QUOTE Quote_ID (Primary) Dealer_ID (foreign) Customer_ID (foreign) MiscQuoteInfo You have part(s) that belong to a particular quote: QUOTE_PARTS Quote_Parts_ID (Primary) Quote_ID (foreign) Part_ID (foreign) PartQuantity These are your parts: PARTS Part_ID (Primary) PartNumber PartName Sales orders are drawn up once the quote is accepted, with the ability to have more than one sales order per quote: SALES ORDERS Sales_Order_ID (Primary) Quote_ID (foreign) Sales_Order_Info Once the sales order is drawn up, invoices can be created, with the ability to have more than one invoice per sales order. The serial numbered part is actually in a 1:1 relationship with the invoice table, but that allows all of the information about the part to be kept separate from your invoice information. INVOICE Invoice_ID (Primary) Sales_Order_ID (foreign) Invoice_Info Serial_Number (foreign) SERIAL_NUMBERED_PARTS Serial_Number (Primary Key) Serial_Numbered_Part_Info WARRANTY_CLAIMS Claim_Number (Primary Key) Misc Warranty Info Serial_Number You will, of course, have to go into the relationships window and connect all of the foreign keys back to the primary ones. This should reduce the number of tables you have, but the database shouldn't be so confused about what relates to what. Start out with queries on related tables. You will probably want to use the form wizard, and allow it to create some subforms for you. |
#13
|
|||
|
|||
Quotes to Orders to Invoice design - Help!!
Keith -
OK, I may have misunderstood how this is setup, so please be patient with me. You create a quote. This quote is made up of parts. Are there any other items that go into a quote? Does a sales order have every item that is in a quote? Does a sales order sometimes have other items than what are in the quote? "Keith" wrote: I have made the changes to my database to reflect what you have said. I still have two questions: 1. How do I bring in more than one line item from Quote Parts into a Sales Order? It keeps generating a new Sales Order number for each line item. 2. How do I bring in more than 1 line item from a Sales Order into an Invoice? "mnature" wrote: I'm going to put all the table together here, along with comments about them. You have dealers and customers: DEALERS Dealer_ID (Primary) DealerInfo CUSTOMERS Customer_ID (Primary) CustomerInfo You have quotes, based on specific dealers and customers, containing specifics about the quote: QUOTE Quote_ID (Primary) Dealer_ID (foreign) Customer_ID (foreign) MiscQuoteInfo You have part(s) that belong to a particular quote: QUOTE_PARTS Quote_Parts_ID (Primary) Quote_ID (foreign) Part_ID (foreign) PartQuantity These are your parts: PARTS Part_ID (Primary) PartNumber PartName Sales orders are drawn up once the quote is accepted, with the ability to have more than one sales order per quote: SALES ORDERS Sales_Order_ID (Primary) Quote_ID (foreign) Sales_Order_Info Once the sales order is drawn up, invoices can be created, with the ability to have more than one invoice per sales order. The serial numbered part is actually in a 1:1 relationship with the invoice table, but that allows all of the information about the part to be kept separate from your invoice information. INVOICE Invoice_ID (Primary) Sales_Order_ID (foreign) Invoice_Info Serial_Number (foreign) SERIAL_NUMBERED_PARTS Serial_Number (Primary Key) Serial_Numbered_Part_Info WARRANTY_CLAIMS Claim_Number (Primary Key) Misc Warranty Info Serial_Number You will, of course, have to go into the relationships window and connect all of the foreign keys back to the primary ones. This should reduce the number of tables you have, but the database shouldn't be so confused about what relates to what. Start out with queries on related tables. You will probably want to use the form wizard, and allow it to create some subforms for you. |
#14
|
|||
|
|||
Quotes to Orders to Invoice design - Help!!
Mnature -
A quote is made up of 1 or more parts (some serial numbered, some not) A sales order usually has most items that are on the quote but some parts may be added or omitted that were in the quote. Also quantities may change from the quote to the sales order. Or there may be multiple sales orders for the same quote because of sale of the multiple units over a period of time. A sales order sometimes does have items other than what are in the quote. Likewise the invoice can, in some limited instances, have other than what is in the sales order. The reason that the changes to the sales order from the quote; and to the invoice from the quote, is that the original quoted item is a $30,000 item. This item is built to order for attachment to a combine and some needed accessories may or may not have been in the original quote because of changes to the unit to which it will be attached from the time of the quote to sales order or from the time of the sales order to the invoice. "mnature" wrote: Keith - OK, I may have misunderstood how this is setup, so please be patient with me. You create a quote. This quote is made up of parts. Are there any other items that go into a quote? Does a sales order have every item that is in a quote? Does a sales order sometimes have other items than what are in the quote? "Keith" wrote: I have made the changes to my database to reflect what you have said. I still have two questions: 1. How do I bring in more than one line item from Quote Parts into a Sales Order? It keeps generating a new Sales Order number for each line item. 2. How do I bring in more than 1 line item from a Sales Order into an Invoice? "mnature" wrote: I'm going to put all the table together here, along with comments about them. You have dealers and customers: DEALERS Dealer_ID (Primary) DealerInfo CUSTOMERS Customer_ID (Primary) CustomerInfo You have quotes, based on specific dealers and customers, containing specifics about the quote: QUOTE Quote_ID (Primary) Dealer_ID (foreign) Customer_ID (foreign) MiscQuoteInfo You have part(s) that belong to a particular quote: QUOTE_PARTS Quote_Parts_ID (Primary) Quote_ID (foreign) Part_ID (foreign) PartQuantity These are your parts: PARTS Part_ID (Primary) PartNumber PartName Sales orders are drawn up once the quote is accepted, with the ability to have more than one sales order per quote: SALES ORDERS Sales_Order_ID (Primary) Quote_ID (foreign) Sales_Order_Info Once the sales order is drawn up, invoices can be created, with the ability to have more than one invoice per sales order. The serial numbered part is actually in a 1:1 relationship with the invoice table, but that allows all of the information about the part to be kept separate from your invoice information. INVOICE Invoice_ID (Primary) Sales_Order_ID (foreign) Invoice_Info Serial_Number (foreign) SERIAL_NUMBERED_PARTS Serial_Number (Primary Key) Serial_Numbered_Part_Info WARRANTY_CLAIMS Claim_Number (Primary Key) Misc Warranty Info Serial_Number You will, of course, have to go into the relationships window and connect all of the foreign keys back to the primary ones. This should reduce the number of tables you have, but the database shouldn't be so confused about what relates to what. Start out with queries on related tables. You will probably want to use the form wizard, and allow it to create some subforms for you. |
#15
|
|||
|
|||
Quotes to Orders to Invoice design - Help!!
Keith, the design you have in mind and what mnature has suggested is
essentially the right approach. I think the question you are now asking is, "How can I track the items through this process? So, a quote item turns up as one or more SalesOrder entries, the SalesOrder entries turn up on invoices, etc." If that is the question, consider this approach: Quote table: QuoteID pk (primary key) ClientID fk (foreign key to Client table) QuoteDate date/time EmployeeID fk (or DealerID?) QuoteDetail table: QuoteDetailID pk QuoteID fk PartID fk Quantity number UnitPrice currency TaxRate Double (percent) SalesOrder table: SalesOrderID pk ClientID fk SalesOrderDate date/time EmployeeID fk SalesOrderDetail: SalesOrderDetailID pk SalesOrderID number PartID fk Quantity number UnitPrice currency TaxRate Double (percent) QuoteDetailID fk (not required) This lets you tie an entry in SalesOrderDetail to an entry in QuoteDetail. It copes with things like: - a quote item that was bought over time (many SalesOrderDetail records for one QuoteDetail); - a change in quantity or price since the quote; - a sales order item that never was part of the quote (i.e. SalesOrderDetail.QuoteDetailID is null.) Yet, it still allows you to track which quotes or which quote items are actually being turned into sales. A similar thing happens with the invoicing, i.e. the InvoiceDetail table contains a SalesOrderDetailID foreign key field. Invoice table: InvoiceID pk ClientID fk InvoiceDate date/time EmployeeID fk InvoiceDetail table: InvoiceDetailID pk InvoiceID fk PartID fk Quantity number UnitPrice currency TaxRate Double (percent) SalesOrderDetailID fk (not required) SerialNumber Text (not required, but perhaps unique.) Now you can track each stage of the process, splitting as many times as needed (e.g. where a sales order is filled by multiple orders.) The SerialNumber may well identify the records where that is appropriate, without the need for a SerialNumberedParts table. Similarly, you may not need the DealerCustomersJunction table if you can just make the customer and dealer foreign keys in each of the header tables. Personally, I would be tempted to put the suppliers, customers, dealers, employees and others all into one Client table. It makes it easier later when you need to make payments to/receive payments from them. HTH -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Keith" wrote in message ... I have tried to resubmit my table structure with the foreign key specified correctly. DEALERS Dealer_ID (Primary) Dealer Info CUSTOMERS Customer_ID (Primary) Customer Info DEALER_CUSTOMERS JUNCTION Dealer_ID (Primary) (foreign) Customer_ID (Primary) (foreign) Quote ID (foreign) QUOTE Quote_ID (Primary) Misc. Quote Info QUOTE_DETAILS Quote_Detail_ID (Primary) Quote ID (foreign) Part_ID (foreign) Quantity PARTS Part_ID (Primary) Part Number Part Name After this I get confused as to what I should do: SALES_ORDER_DETAIL Sales_Order_Detail_ID (Primary) Quote_Detail_ID (foreign) SALES ORDERS Sales_Order_ID (Primary) Quote ID (foreign - unsure if this is needed) Sales_Order_Detail_ID (foreign) INVOICE Invoice_ID (Primary) Sales_Order_ID (foreign) INVOICE_DETAILS Invoice_Details_ID (Primary Key) Invoice ID (foreign) Serial_Number (not sure what to call this field but it has a 1:1 to Serial_Number in Serial Numbered Parts, the serial number is tied to the quote at the time of invoice.) SERIAL NUMBERED PARTS Serial_Number (Primary Key ) Misc. Job Number, Lot number, etc. WARRANTY_CLAIMS Claim_Number (Primary Key) Misc Warranty Info Serial_Number (foreign) |
#16
|
|||
|
|||
Quotes to Orders to Invoice design - Help!!
I can see now why your original database design was so convoluted. A quote
is similar to a sales order, but does not necessarily have a one-to-one correspondence. I was over-simplifying the problem in my approach. If you don't mind, I'd like to take a little time to think this over. What I'm considering is some system where the quote details are transferred over (thus maintaining the original quote details), and allowed to be edited for the actual sales order. Your quote relates only loosely to the sales order, and a database works with absolutes. You really have two databases, one that starts an order (as an estimate), and another that finishes the order, with specific charges. And, as you say, the same quote should be able to be used for multiple sales orders, which could each be modified differently. Tempting to just go out and find some canned software that would do this for you. Not nearly as straightforward as I first thought. "Keith" wrote: Mnature - A quote is made up of 1 or more parts (some serial numbered, some not) A sales order usually has most items that are on the quote but some parts may be added or omitted that were in the quote. Also quantities may change from the quote to the sales order. Or there may be multiple sales orders for the same quote because of sale of the multiple units over a period of time. A sales order sometimes does have items other than what are in the quote. Likewise the invoice can, in some limited instances, have other than what is in the sales order. The reason that the changes to the sales order from the quote; and to the invoice from the quote, is that the original quoted item is a $30,000 item. This item is built to order for attachment to a combine and some needed accessories may or may not have been in the original quote because of changes to the unit to which it will be attached from the time of the quote to sales order or from the time of the sales order to the invoice. "mnature" wrote: Keith - OK, I may have misunderstood how this is setup, so please be patient with me. You create a quote. This quote is made up of parts. Are there any other items that go into a quote? Does a sales order have every item that is in a quote? Does a sales order sometimes have other items than what are in the quote? "Keith" wrote: I have made the changes to my database to reflect what you have said. I still have two questions: 1. How do I bring in more than one line item from Quote Parts into a Sales Order? It keeps generating a new Sales Order number for each line item. 2. How do I bring in more than 1 line item from a Sales Order into an Invoice? "mnature" wrote: I'm going to put all the table together here, along with comments about them. You have dealers and customers: DEALERS Dealer_ID (Primary) DealerInfo CUSTOMERS Customer_ID (Primary) CustomerInfo You have quotes, based on specific dealers and customers, containing specifics about the quote: QUOTE Quote_ID (Primary) Dealer_ID (foreign) Customer_ID (foreign) MiscQuoteInfo You have part(s) that belong to a particular quote: QUOTE_PARTS Quote_Parts_ID (Primary) Quote_ID (foreign) Part_ID (foreign) PartQuantity These are your parts: PARTS Part_ID (Primary) PartNumber PartName Sales orders are drawn up once the quote is accepted, with the ability to have more than one sales order per quote: SALES ORDERS Sales_Order_ID (Primary) Quote_ID (foreign) Sales_Order_Info Once the sales order is drawn up, invoices can be created, with the ability to have more than one invoice per sales order. The serial numbered part is actually in a 1:1 relationship with the invoice table, but that allows all of the information about the part to be kept separate from your invoice information. INVOICE Invoice_ID (Primary) Sales_Order_ID (foreign) Invoice_Info Serial_Number (foreign) SERIAL_NUMBERED_PARTS Serial_Number (Primary Key) Serial_Numbered_Part_Info WARRANTY_CLAIMS Claim_Number (Primary Key) Misc Warranty Info Serial_Number You will, of course, have to go into the relationships window and connect all of the foreign keys back to the primary ones. This should reduce the number of tables you have, but the database shouldn't be so confused about what relates to what. Start out with queries on related tables. You will probably want to use the form wizard, and allow it to create some subforms for you. |
#17
|
|||
|
|||
Quotes to Orders to Invoice design - Help!!
Keith, you might also consider using a canned software such as Microsoft's
Small Business Accounting software, which allows for quotes (which can be memorized and reused) being turned into sales orders, and from there into invoices. "Keith" wrote: Mnature - A quote is made up of 1 or more parts (some serial numbered, some not) A sales order usually has most items that are on the quote but some parts may be added or omitted that were in the quote. Also quantities may change from the quote to the sales order. Or there may be multiple sales orders for the same quote because of sale of the multiple units over a period of time. A sales order sometimes does have items other than what are in the quote. Likewise the invoice can, in some limited instances, have other than what is in the sales order. The reason that the changes to the sales order from the quote; and to the invoice from the quote, is that the original quoted item is a $30,000 item. This item is built to order for attachment to a combine and some needed accessories may or may not have been in the original quote because of changes to the unit to which it will be attached from the time of the quote to sales order or from the time of the sales order to the invoice. "mnature" wrote: Keith - OK, I may have misunderstood how this is setup, so please be patient with me. You create a quote. This quote is made up of parts. Are there any other items that go into a quote? Does a sales order have every item that is in a quote? Does a sales order sometimes have other items than what are in the quote? "Keith" wrote: I have made the changes to my database to reflect what you have said. I still have two questions: 1. How do I bring in more than one line item from Quote Parts into a Sales Order? It keeps generating a new Sales Order number for each line item. 2. How do I bring in more than 1 line item from a Sales Order into an Invoice? "mnature" wrote: I'm going to put all the table together here, along with comments about them. You have dealers and customers: DEALERS Dealer_ID (Primary) DealerInfo CUSTOMERS Customer_ID (Primary) CustomerInfo You have quotes, based on specific dealers and customers, containing specifics about the quote: QUOTE Quote_ID (Primary) Dealer_ID (foreign) Customer_ID (foreign) MiscQuoteInfo You have part(s) that belong to a particular quote: QUOTE_PARTS Quote_Parts_ID (Primary) Quote_ID (foreign) Part_ID (foreign) PartQuantity These are your parts: PARTS Part_ID (Primary) PartNumber PartName Sales orders are drawn up once the quote is accepted, with the ability to have more than one sales order per quote: SALES ORDERS Sales_Order_ID (Primary) Quote_ID (foreign) Sales_Order_Info Once the sales order is drawn up, invoices can be created, with the ability to have more than one invoice per sales order. The serial numbered part is actually in a 1:1 relationship with the invoice table, but that allows all of the information about the part to be kept separate from your invoice information. INVOICE Invoice_ID (Primary) Sales_Order_ID (foreign) Invoice_Info Serial_Number (foreign) SERIAL_NUMBERED_PARTS Serial_Number (Primary Key) Serial_Numbered_Part_Info WARRANTY_CLAIMS Claim_Number (Primary Key) Misc Warranty Info Serial_Number You will, of course, have to go into the relationships window and connect all of the foreign keys back to the primary ones. This should reduce the number of tables you have, but the database shouldn't be so confused about what relates to what. Start out with queries on related tables. You will probably want to use the form wizard, and allow it to create some subforms for you. |
#18
|
|||
|
|||
Quotes to Orders to Invoice design - Help!!
Keith, I thought of another way of approaching your problem. If we look at a
quote, sales order, and invoice as all being just stages of the same order, then we can just pile them all into the same table. Look at this: tbl_Dealers DealerID (PK) DealerInfo tbl_Customers CustomerID (PK) CustomerInfo tbl_Order OrderID (PK) DealerID (FK) CustomerID (FK) QuoteInfo SalesOrderInfo InvoiceInfo OrderParts OrderPartID (PK) OrderID (FK) PartID (FK) PartSerialNumber (only to be used when the part actually has a serial number) QuoteQuantity SalesOrderQuantity InvoiceQuantity OrderPartInfo tbl_Parts PartID (PK) PartNumber PartName For any particular part that gets tied into the Order table, you can show different quantities (if need be) for the quote, the sales order, or the invoice. So a part can be added to the order, with a quantity in the sales order and invoice quantities, but not in the quote quantity, which shows that it was not in the original quote, but will be in the final order. "Keith" wrote: Mnature - A quote is made up of 1 or more parts (some serial numbered, some not) A sales order usually has most items that are on the quote but some parts may be added or omitted that were in the quote. Also quantities may change from the quote to the sales order. Or there may be multiple sales orders for the same quote because of sale of the multiple units over a period of time. A sales order sometimes does have items other than what are in the quote. Likewise the invoice can, in some limited instances, have other than what is in the sales order. The reason that the changes to the sales order from the quote; and to the invoice from the quote, is that the original quoted item is a $30,000 item. This item is built to order for attachment to a combine and some needed accessories may or may not have been in the original quote because of changes to the unit to which it will be attached from the time of the quote to sales order or from the time of the sales order to the invoice. "mnature" wrote: Keith - OK, I may have misunderstood how this is setup, so please be patient with me. You create a quote. This quote is made up of parts. Are there any other items that go into a quote? Does a sales order have every item that is in a quote? Does a sales order sometimes have other items than what are in the quote? "Keith" wrote: I have made the changes to my database to reflect what you have said. I still have two questions: 1. How do I bring in more than one line item from Quote Parts into a Sales Order? It keeps generating a new Sales Order number for each line item. 2. How do I bring in more than 1 line item from a Sales Order into an Invoice? "mnature" wrote: I'm going to put all the table together here, along with comments about them. You have dealers and customers: DEALERS Dealer_ID (Primary) DealerInfo CUSTOMERS Customer_ID (Primary) CustomerInfo You have quotes, based on specific dealers and customers, containing specifics about the quote: QUOTE Quote_ID (Primary) Dealer_ID (foreign) Customer_ID (foreign) MiscQuoteInfo You have part(s) that belong to a particular quote: QUOTE_PARTS Quote_Parts_ID (Primary) Quote_ID (foreign) Part_ID (foreign) PartQuantity These are your parts: PARTS Part_ID (Primary) PartNumber PartName Sales orders are drawn up once the quote is accepted, with the ability to have more than one sales order per quote: SALES ORDERS Sales_Order_ID (Primary) Quote_ID (foreign) Sales_Order_Info Once the sales order is drawn up, invoices can be created, with the ability to have more than one invoice per sales order. The serial numbered part is actually in a 1:1 relationship with the invoice table, but that allows all of the information about the part to be kept separate from your invoice information. INVOICE Invoice_ID (Primary) Sales_Order_ID (foreign) Invoice_Info Serial_Number (foreign) SERIAL_NUMBERED_PARTS Serial_Number (Primary Key) Serial_Numbered_Part_Info WARRANTY_CLAIMS Claim_Number (Primary Key) Misc Warranty Info Serial_Number You will, of course, have to go into the relationships window and connect all of the foreign keys back to the primary ones. This should reduce the number of tables you have, but the database shouldn't be so confused about what relates to what. Start out with queries on related tables. You will probably want to use the form wizard, and allow it to create some subforms for you. |
#19
|
|||
|
|||
Quotes to Orders to Invoice design - Help!!
Dear mnature,
I have been away from my office for a few days. Thank you for your time in this matter. I really appreciated it. It seems as though Allen Browne's suggestions are more in line to help me at this point. "mnature" wrote: Keith, I thought of another way of approaching your problem. If we look at a quote, sales order, and invoice as all being just stages of the same order, then we can just pile them all into the same table. Look at this: tbl_Dealers DealerID (PK) DealerInfo tbl_Customers CustomerID (PK) CustomerInfo tbl_Order OrderID (PK) DealerID (FK) CustomerID (FK) QuoteInfo SalesOrderInfo InvoiceInfo OrderParts OrderPartID (PK) OrderID (FK) PartID (FK) PartSerialNumber (only to be used when the part actually has a serial number) QuoteQuantity SalesOrderQuantity InvoiceQuantity OrderPartInfo tbl_Parts PartID (PK) PartNumber PartName For any particular part that gets tied into the Order table, you can show different quantities (if need be) for the quote, the sales order, or the invoice. So a part can be added to the order, with a quantity in the sales order and invoice quantities, but not in the quote quantity, which shows that it was not in the original quote, but will be in the final order. "Keith" wrote: Mnature - A quote is made up of 1 or more parts (some serial numbered, some not) A sales order usually has most items that are on the quote but some parts may be added or omitted that were in the quote. Also quantities may change from the quote to the sales order. Or there may be multiple sales orders for the same quote because of sale of the multiple units over a period of time. A sales order sometimes does have items other than what are in the quote. Likewise the invoice can, in some limited instances, have other than what is in the sales order. The reason that the changes to the sales order from the quote; and to the invoice from the quote, is that the original quoted item is a $30,000 item. This item is built to order for attachment to a combine and some needed accessories may or may not have been in the original quote because of changes to the unit to which it will be attached from the time of the quote to sales order or from the time of the sales order to the invoice. "mnature" wrote: Keith - OK, I may have misunderstood how this is setup, so please be patient with me. You create a quote. This quote is made up of parts. Are there any other items that go into a quote? Does a sales order have every item that is in a quote? Does a sales order sometimes have other items than what are in the quote? "Keith" wrote: I have made the changes to my database to reflect what you have said. I still have two questions: 1. How do I bring in more than one line item from Quote Parts into a Sales Order? It keeps generating a new Sales Order number for each line item. 2. How do I bring in more than 1 line item from a Sales Order into an Invoice? "mnature" wrote: I'm going to put all the table together here, along with comments about them. You have dealers and customers: DEALERS Dealer_ID (Primary) DealerInfo CUSTOMERS Customer_ID (Primary) CustomerInfo You have quotes, based on specific dealers and customers, containing specifics about the quote: QUOTE Quote_ID (Primary) Dealer_ID (foreign) Customer_ID (foreign) MiscQuoteInfo You have part(s) that belong to a particular quote: QUOTE_PARTS Quote_Parts_ID (Primary) Quote_ID (foreign) Part_ID (foreign) PartQuantity These are your parts: PARTS Part_ID (Primary) PartNumber PartName Sales orders are drawn up once the quote is accepted, with the ability to have more than one sales order per quote: SALES ORDERS Sales_Order_ID (Primary) Quote_ID (foreign) Sales_Order_Info Once the sales order is drawn up, invoices can be created, with the ability to have more than one invoice per sales order. The serial numbered part is actually in a 1:1 relationship with the invoice table, but that allows all of the information about the part to be kept separate from your invoice information. INVOICE Invoice_ID (Primary) Sales_Order_ID (foreign) Invoice_Info Serial_Number (foreign) SERIAL_NUMBERED_PARTS Serial_Number (Primary Key) Serial_Numbered_Part_Info WARRANTY_CLAIMS Claim_Number (Primary Key) Misc Warranty Info Serial_Number You will, of course, have to go into the relationships window and connect all of the foreign keys back to the primary ones. This should reduce the number of tables you have, but the database shouldn't be so confused about what relates to what. Start out with queries on related tables. You will probably want to use the form wizard, and allow it to create some subforms for you. |
#20
|
|||
|
|||
Quotes to Orders to Invoice design - Help!!
Mr. Browne:
Thank you very much for your input. I have been out of the office for a few days and I am implementing what you suggested. It makes great sense to me. I am in the process of switching to one "Contact" table instead of a "Dealer" table and a "Customer" table. That is how I started, but I split them up because I couldn't figure out how to set up the relationship from the contact table to the quote table when for each quote there will be a Dealer listed and a Customer listed. At this point I am wondering if I should have the Contact table relationship "indeterminate" to the Quote table and then generate the Quote form with wizard and add another Contact field to the form. I would label one contact field on the form as "Dealer" and the other as "Customer". Am I all wrong in this thinking now? Is there ever a time that having an indeterminate relationship between tables is okey? Any input would be appreciated. "Allen Browne" wrote: Keith, the design you have in mind and what mnature has suggested is essentially the right approach. I think the question you are now asking is, "How can I track the items through this process? So, a quote item turns up as one or more SalesOrder entries, the SalesOrder entries turn up on invoices, etc." If that is the question, consider this approach: Quote table: QuoteID pk (primary key) ClientID fk (foreign key to Client table) QuoteDate date/time EmployeeID fk (or DealerID?) QuoteDetail table: QuoteDetailID pk QuoteID fk PartID fk Quantity number UnitPrice currency TaxRate Double (percent) SalesOrder table: SalesOrderID pk ClientID fk SalesOrderDate date/time EmployeeID fk SalesOrderDetail: SalesOrderDetailID pk SalesOrderID number PartID fk Quantity number UnitPrice currency TaxRate Double (percent) QuoteDetailID fk (not required) This lets you tie an entry in SalesOrderDetail to an entry in QuoteDetail. It copes with things like: - a quote item that was bought over time (many SalesOrderDetail records for one QuoteDetail); - a change in quantity or price since the quote; - a sales order item that never was part of the quote (i.e. SalesOrderDetail.QuoteDetailID is null.) Yet, it still allows you to track which quotes or which quote items are actually being turned into sales. A similar thing happens with the invoicing, i.e. the InvoiceDetail table contains a SalesOrderDetailID foreign key field. Invoice table: InvoiceID pk ClientID fk InvoiceDate date/time EmployeeID fk InvoiceDetail table: InvoiceDetailID pk InvoiceID fk PartID fk Quantity number UnitPrice currency TaxRate Double (percent) SalesOrderDetailID fk (not required) SerialNumber Text (not required, but perhaps unique.) Now you can track each stage of the process, splitting as many times as needed (e.g. where a sales order is filled by multiple orders.) The SerialNumber may well identify the records where that is appropriate, without the need for a SerialNumberedParts table. Similarly, you may not need the DealerCustomersJunction table if you can just make the customer and dealer foreign keys in each of the header tables. Personally, I would be tempted to put the suppliers, customers, dealers, employees and others all into one Client table. It makes it easier later when you need to make payments to/receive payments from them. HTH -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Keith" wrote in message ... I have tried to resubmit my table structure with the foreign key specified correctly. DEALERS Dealer_ID (Primary) Dealer Info CUSTOMERS Customer_ID (Primary) Customer Info DEALER_CUSTOMERS JUNCTION Dealer_ID (Primary) (foreign) Customer_ID (Primary) (foreign) Quote ID (foreign) QUOTE Quote_ID (Primary) Misc. Quote Info QUOTE_DETAILS Quote_Detail_ID (Primary) Quote ID (foreign) Part_ID (foreign) Quantity PARTS Part_ID (Primary) Part Number Part Name After this I get confused as to what I should do: SALES_ORDER_DETAIL Sales_Order_Detail_ID (Primary) Quote_Detail_ID (foreign) SALES ORDERS Sales_Order_ID (Primary) Quote ID (foreign - unsure if this is needed) Sales_Order_Detail_ID (foreign) INVOICE Invoice_ID (Primary) Sales_Order_ID (foreign) INVOICE_DETAILS Invoice_Details_ID (Primary Key) Invoice ID (foreign) Serial_Number (not sure what to call this field but it has a 1:1 to Serial_Number in Serial Numbered Parts, the serial number is tied to the quote at the time of invoice.) SERIAL NUMBERED PARTS Serial_Number (Primary Key ) Misc. Job Number, Lot number, etc. WARRANTY_CLAIMS Claim_Number (Primary Key) Misc Warranty Info Serial_Number (foreign) |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
I nees help with Sum/Count Query | Lucien | New Users | 4 | April 20th, 2006 12:36 AM |
i need to design a sales invoice for a small building company | ed | General Discussions | 1 | January 29th, 2006 04:12 PM |
Date range on reports | Mark_Milly | Setting Up & Running Reports | 10 | January 19th, 2006 03:49 PM |
Quotation & Invoicing Database | Allen Browne | Database Design | 7 | January 18th, 2006 08:05 PM |
Invoice numbering | Ccp | Worksheet Functions | 0 | March 3rd, 2004 03:37 AM |