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.

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.

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.

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.

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)

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.

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.

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.

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.

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)