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 |
#21
|
|||
|
|||
Quotes to Orders to Invoice design - Help!!
Mr. Browne - Or with the Contact table to the Quote table should I make it
one-to-many and just not enforce referential integrity? Will that work? "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) |
#22
|
|||
|
|||
Quotes to Orders to Invoice design - Help!!
To create 2 relationships between Client and Quote, add a 2nd copy of the
Client table to the Relationships window. Access will alias it as Client_1. You can then create one relationship to each of the copies of the Client table, and you can (and should) enforce integrity. -- 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 ... 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. "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) |
#23
|
|||
|
|||
Quotes to Orders to Invoice design - Help!!
It appears to me that a Quote, a Sales Order, and an Invoice, whilst sharing
much of the same information, are actually distinct entities. From what I can gather, you are trying to turn a quote into a sales order into an invoice. I cannot see an actual relationship between quotes - order - invoice. Ok, in the physical world one "usually" follows the other, but that is not always the case. For the tables, I would have the Quote - Quote Details relationship, and the same design for sales orders and invoices. If you wanted to track which quotes were succesful I would add a field to the Sales Order table as a foreign key to the Quote table primary key. The same goes for invoices - sales orders. From what I can tell, you are trying to reduce the legwork in producing a sales order by using the data already given in the quote. You need the data in the quote to remain unchanged (the same goes for invoices). To achieve this, I would use an APPEND query to select the necessary data from the Quote and Quote Details table, and append them to the Sales and Sales Details table. This would have to be done programatically (VBA), to ensure all the relationships are handled properly. By doing it this way, the users will be able to add new details, remove those details that aren't relevant, change the prices as necessary (without chnaging the prices i nthe quote), etc etc. Am I anywhere near what you are after? If so, I'll give you more details about the table design and code needed. Dave "Keith" wrote: Mr. Browne - Or with the Contact table to the Quote table should I make it one-to-many and just not enforce referential integrity? Will that work? "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) |
#24
|
|||
|
|||
Quotes to Orders to Invoice design - Help!!
David M C -
What you are describing is what I would like to do. I am interesting in more info if you have the time. Do you need more info from me to proceed? Thanks for your help. "David M C" wrote: It appears to me that a Quote, a Sales Order, and an Invoice, whilst sharing much of the same information, are actually distinct entities. From what I can gather, you are trying to turn a quote into a sales order into an invoice. I cannot see an actual relationship between quotes - order - invoice. Ok, in the physical world one "usually" follows the other, but that is not always the case. For the tables, I would have the Quote - Quote Details relationship, and the same design for sales orders and invoices. If you wanted to track which quotes were succesful I would add a field to the Sales Order table as a foreign key to the Quote table primary key. The same goes for invoices - sales orders. From what I can tell, you are trying to reduce the legwork in producing a sales order by using the data already given in the quote. You need the data in the quote to remain unchanged (the same goes for invoices). To achieve this, I would use an APPEND query to select the necessary data from the Quote and Quote Details table, and append them to the Sales and Sales Details table. This would have to be done programatically (VBA), to ensure all the relationships are handled properly. By doing it this way, the users will be able to add new details, remove those details that aren't relevant, change the prices as necessary (without chnaging the prices i nthe quote), etc etc. Am I anywhere near what you are after? If so, I'll give you more details about the table design and code needed. Dave "Keith" wrote: Mr. Browne - Or with the Contact table to the Quote table should I make it one-to-many and just not enforce referential integrity? Will that work? "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) |
#25
|
|||
|
|||
Quotes to Orders to Invoice design - Help!!
I have been unable to respond due to other duties taking me away from this
database design, but I have appreciated your responses and would like to ask you a couple of questions if I might. #1 -- In the previous table design you sent to me: In the SalesOrderDetail table, you did not show the SalesOrderID field as the fk to the SalesOrder table. Can I assume that was not your intention? Otherwise the SalesOrder table has nothing relating to its pk. Am I wrong in my thinking. #2 -- I have been wrestling with the relationship of my one contact table (that now includes both dealers and customers as you had suggested) to the quote -- or sales order -- or invoice tables. I created both a Contact_Customer_ID and a Contact_Dealer_ID field in each of the quote, sales order and invoice tables. I added another contact table to my relationships so now I have a "Contact" table and a "Contact_1" table. In the "Quote" table I related the Contact_ID from the "Contact" table to the Contact_Dealer_ID field and the Contact_ID from the "Contact_1" table to the Contact_Customer_ID field. Now when I try to enter info into my quote table and enter both my customer and dealer ID info into the table and try to save it, I get an error message that states a related record is needed in the "Contact" table. Can you tell me what I am doing wrong? When I had "Dealer" and "Customer" tables previously, I used a junction table with the Dealer_ID and the Customer_ID fields both as the pk of the junction table and the Quote ID field as the fk. This arrangement worked for me but I really needed to have just one contact table so that I can link it to our contact database in the future. I was glad to see that you had suggested one contact table for both dealers and customers, but I can't understand what I am doing wrong in the design. Thank you. "Allen Browne" wrote: To create 2 relationships between Client and Quote, add a 2nd copy of the Client table to the Relationships window. Access will alias it as Client_1. You can then create one relationship to each of the copies of the Client table, and you can (and should) enforce integrity. -- 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 ... 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. "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) |
#26
|
|||
|
|||
Quotes to Orders to Invoice design - Help!!
Answers in-line
-- 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 been unable to respond due to other duties taking me away from this database design, but I have appreciated your responses and would like to ask you a couple of questions if I might. #1 -- In the previous table design you sent to me: In the SalesOrderDetail table, you did not show the SalesOrderID field as the fk to the SalesOrder table. Can I assume that was not your intention? Otherwise the SalesOrder table has nothing relating to its pk. Am I wrong in my thinking. Yes, you do need the foreign key field. #2 -- I have been wrestling with the relationship of my one contact table (that now includes both dealers and customers as you had suggested) to the quote -- or sales order -- or invoice tables. I created both a Contact_Customer_ID and a Contact_Dealer_ID field in each of the quote, sales order and invoice tables. I added another contact table to my relationships so now I have a "Contact" table and a "Contact_1" table. In the "Quote" table I related the Contact_ID from the "Contact" table to the Contact_Dealer_ID field and the Contact_ID from the "Contact_1" table to the Contact_Customer_ID field. Now when I try to enter info into my quote table and enter both my customer and dealer ID info into the table and try to save it, I get an error message that states a related record is needed in the "Contact" table. In the Relationships window Quote.ClientID relates to Contact.ContactID. Quote.DealerID relates to Contact_1.ContactID. That's two relationships. The foreign key field names in the related table are different than the primary key name in the primary table, but they still relate the same way. Can you tell me what I am doing wrong? When I had "Dealer" and "Customer" tables previously, I used a junction table with the Dealer_ID and the Customer_ID fields both as the pk of the junction table and the Quote ID field as the fk. This arrangement worked for me but I really needed to have just one contact table so that I can link it to our contact database in the future. I was glad to see that you had suggested one contact table for both dealers and customers, but I can't understand what I am doing wrong in the design. |
#27
|
|||
|
|||
Quotes to Orders to Invoice design - Help!!
Allen - I did have my relationships set up the way you suggested. I found
that the Customer Contact and Dealer Contact fields in the Quote table were using a sort query using the Contact table to alphabetize for easy retrieval. I did not have the Contact_ID field of the query included in the column count for the field in the Quote table. When I changed to include the Contact_ID in the column count and made the width 0" for that column my problems went away. I didn't realize that I needed to do that. Thought that the relationship was established in the Query window. Allen Browne" wrote: Answers in-line -- 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 been unable to respond due to other duties taking me away from this database design, but I have appreciated your responses and would like to ask you a couple of questions if I might. #1 -- In the previous table design you sent to me: In the SalesOrderDetail table, you did not show the SalesOrderID field as the fk to the SalesOrder table. Can I assume that was not your intention? Otherwise the SalesOrder table has nothing relating to its pk. Am I wrong in my thinking. Yes, you do need the foreign key field. #2 -- I have been wrestling with the relationship of my one contact table (that now includes both dealers and customers as you had suggested) to the quote -- or sales order -- or invoice tables. I created both a Contact_Customer_ID and a Contact_Dealer_ID field in each of the quote, sales order and invoice tables. I added another contact table to my relationships so now I have a "Contact" table and a "Contact_1" table. In the "Quote" table I related the Contact_ID from the "Contact" table to the Contact_Dealer_ID field and the Contact_ID from the "Contact_1" table to the Contact_Customer_ID field. Now when I try to enter info into my quote table and enter both my customer and dealer ID info into the table and try to save it, I get an error message that states a related record is needed in the "Contact" table. In the Relationships window Quote.ClientID relates to Contact.ContactID. Quote.DealerID relates to Contact_1.ContactID. That's two relationships. The foreign key field names in the related table are different than the primary key name in the primary table, but they still relate the same way. Can you tell me what I am doing wrong? When I had "Dealer" and "Customer" tables previously, I used a junction table with the Dealer_ID and the Customer_ID fields both as the pk of the junction table and the Quote ID field as the fk. This arrangement worked for me but I really needed to have just one contact table so that I can link it to our contact database in the future. I was glad to see that you had suggested one contact table for both dealers and customers, but I can't understand what I am doing wrong in the design. |
#28
|
|||
|
|||
Quotes to Orders to Invoice design - Help!!
Allen - I have the relationships set up and working the way I need them to
for now. I'm sure I have a lot more to learn about Access. Your input was invaluable and I appreciate your time. Thank you again from 1/2 a world away. "Keith" wrote: Allen - I did have my relationships set up the way you suggested. I found that the Customer Contact and Dealer Contact fields in the Quote table were using a sort query using the Contact table to alphabetize for easy retrieval. I did not have the Contact_ID field of the query included in the column count for the field in the Quote table. When I changed to include the Contact_ID in the column count and made the width 0" for that column my problems went away. I didn't realize that I needed to do that. Thought that the relationship was established in the Query window. Allen Browne" wrote: Answers in-line -- 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 been unable to respond due to other duties taking me away from this database design, but I have appreciated your responses and would like to ask you a couple of questions if I might. #1 -- In the previous table design you sent to me: In the SalesOrderDetail table, you did not show the SalesOrderID field as the fk to the SalesOrder table. Can I assume that was not your intention? Otherwise the SalesOrder table has nothing relating to its pk. Am I wrong in my thinking. Yes, you do need the foreign key field. #2 -- I have been wrestling with the relationship of my one contact table (that now includes both dealers and customers as you had suggested) to the quote -- or sales order -- or invoice tables. I created both a Contact_Customer_ID and a Contact_Dealer_ID field in each of the quote, sales order and invoice tables. I added another contact table to my relationships so now I have a "Contact" table and a "Contact_1" table. In the "Quote" table I related the Contact_ID from the "Contact" table to the Contact_Dealer_ID field and the Contact_ID from the "Contact_1" table to the Contact_Customer_ID field. Now when I try to enter info into my quote table and enter both my customer and dealer ID info into the table and try to save it, I get an error message that states a related record is needed in the "Contact" table. In the Relationships window Quote.ClientID relates to Contact.ContactID. Quote.DealerID relates to Contact_1.ContactID. That's two relationships. The foreign key field names in the related table are different than the primary key name in the primary table, but they still relate the same way. Can you tell me what I am doing wrong? When I had "Dealer" and "Customer" tables previously, I used a junction table with the Dealer_ID and the Customer_ID fields both as the pk of the junction table and the Quote ID field as the fk. This arrangement worked for me but I really needed to have just one contact table so that I can link it to our contact database in the future. I was glad to see that you had suggested one contact table for both dealers and customers, but I can't understand what I am doing wrong in the design. |
#29
|
|||
|
|||
Quotes to Orders to Invoice design - Help!!
Good news.
Feel free to post other new questions as you go. -- 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 ... Allen - I have the relationships set up and working the way I need them to for now. I'm sure I have a lot more to learn about Access. Your input was invaluable and I appreciate your time. Thank you again from 1/2 a world away. |
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 |