Quotes to Orders to Invoice design - Help!!

[Guest]

I have been working for over 2 WEEKS!! to design my database to allow for
entry of sales orders that automatically pull in all the info from the Quote
and Quote details tables into the Sales Order and Sales Order details
allowing me to change the info in the Sales Order and save it without losing
the original quote information. (i.e. qty changes, pricing). Then I want to
go on from that and create an invoice from a sales order using the same type
of relationship.

As you can probably tell, I am fairly new to Access and database design. I
have been through many tutorials and and workbooks. I think I understand the
relational design (but then again I don't know enough to know what I don't
know yet!)

Can you tell me if I am just approaching my problem from the wrong angle?
Should I be trying to accomplish my goals from a totally different view??

Any help to just point me in the right direction would be GREATLY appreciated!

 

[Guest]

Keith, welcome to the forum. Although you are enthusiastic, your post really
doesn't include any information that would be of use in advising you if you
are going in the right direction.

Once the design stage is done, a database lives or dies based on how well
the tables are set up. It would help a great deal to know something about
your tables. We don't need to see all of the fields, and we would rather not
see all of the fields. If you could post your tables, showing primary and
foreign keys, that would be very helpful. A short description of what your
business does could help, also.

 

[Guest]

Thanks for the reply! I'll try to be short.

Our business manufactures and distributes agricultural equipment through our
dealership network. We are using MRP software that handles our inventory
control, work orders and sales orders, invoicing (all of our day-to-day). We
have been using Excel forms in our company to quote some serialized units of
which we sell 100-150 per year. We have been tracking information on these
units in spreadsheets, but now we are trying to set up a database that we can
easily use to quote the units and generate sales orders (which are then
entered into our MRP software) After they are manufactured we want to keep
track of the serial numbered units information (such as invoice number and
date, dealership sold to, end customer sold to, warranty registrations and
claims, etc and use this information for future tracking, surveys, etc.

Current Access database table structure relevant to this discussion is:

DEALERS
Dealer_ID (Primary)
Dealer Info

CUSTOMERS
Customer_ID (Primary)
Customer Info

DEALER_CUSTOMERS JUNCTION
Dealer_ID (Primary) (1:M to Dealer_ID in Junction)
Customer_ID (Primary) (1:M to Customer_ID in Junction)
Quote ID

QUOTE
Quote_ID (Primary) (1:M to Quote_ID in Junction)
(1:M to Quote_ID in Quote_Details)
(1:M Quote_ID in Sales Orders ???)
Misc. Quote Info

QUOTE_DETAILS
Quote_Detail_ID (Primary)
Quote ID
Part_ID
Quantity

PARTS
Part_ID (Primary) (1:M to Part_ID in Quote_Details)
Part Number
Part Name

After this I get confused as to what I should do:

SALES_ORDER_DETAIL
Sales_Order_Detail_ID (Primary) (1:M to Sales_Order_ID in Sales Orders)
Quote_Detail_ID (1:M to Quote_Detail_ID in Quote_Detail)
(The SO details relationships aren't correct but I can't figure out)

SALES ORDERS
Sales_Order_ID (Primary) (1:M to Sales_Order_ID in Invoice)
Quote ID (unsure if this is needed)
Sales_Order_Detail_ID

INVOICE
Invoice_ID (Primary) (1:M to Invoice_ID in Invoice_Details)
Sales_Order_ID

INVOICE_DETAILS
Invoice_Details_ID (Primary Key)
Invoice ID
Serial_Number (1:1 to Serial_Number in Serial
Numbered Parts)

SERIAL NUMBERED PARTS
Serial_Number (Primary Key - is unique without auto number)
1:M to
Serial_Number in Warranty Claims
Misc. Job Number, Lot number, etc.

WARRANTY_CLAIMS
Claim_Number (Primary Key)
Misc Warranty Info
Serial_Number


I hope this gives you a clear enough picture. Any comments would be greatly
appreciated.

 

[Guest]

DEALERS
Dealer_ID (Primary)
DealerInfo

CUSTOMERS
Customer_ID (Primary)
CustomerInfo

You have dealers and customers. This looks OK. However, I think you had
too many tables for the next part. I've reduced them to the following:

QUOTE
Quote_ID (Primary)
Dealer_ID (foreign)
Customer_ID (foreign)
MiscQuoteInfo

QUOTE_PARTS
Quote_Parts_ID (Primary)
Quote_ID (foreign)
Part_ID (foreign)
PartQuantity

PARTS
Part_ID (Primary)
PartNumber
PartName

Look at these changes, and see what you think. I believe your other tables
will tie in more gracefully to these tables.

 

[Guest]

I've inserted my comments below:

:

DEALERS

Dealer_ID (Primary)
DealerInfo

CUSTOMERS
Customer_ID (Primary)
CustomerInfo

You have dealers and customers. This looks OK. However, I think you had
too many tables for the next part. I've reduced them to the following:
Quote_ID (Primary)
Dealer_ID (foreign)
Customer_ID (foreign)
MiscQuoteInfo

QUOTE_PARTS
Quote_Parts_ID (Primary)
Quote_ID (foreign)
Part_ID (foreign)
PartQuantity

The Quote Parts fields you described above are exactly how I have them in my
table.

PARTS
Part_ID (Primary)
PartNumber
PartName

The Parts fields you have mentioned above are exactly as I have them.

Look at these changes, and see what you think. I believe your other tables
will tie in more gracefully to these tables.

My main problem is tying the Sales Orders and Sales Orders Details to the
Quotes and Quote Details and also tying the Sales Orders and Sales Orders
Details to the Invoice and Invoice Details.

I need to be able to enter a Sales Order based on a Quote (pulling in the
Quote information) and also enter an invoice based on a Sales Order and its
information.

Can you find a good way to link the quotes (and their detail info) to the
sales orders; and the sales order quotes (and their detail info) to the
invoices and their details?

 

[Guest]

I need to correct the last paragraph --

Can you find a good way to link the quotes (and their detail info) to the
sales orders; and the SALES ORDERS (and their detail info) to the
invoices and their details?

 

[Guest]

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)

 

[Guest]

I need to ask some specific question here. I'm assuming that once a quote is
drawn up, submitted and accepted, that then a sales order and invoice would
be made (perhaps not at the same time, but following the acceptance of the
quote).

1) Can there be more than one sales order per quote?

2) Can there be more than one invoice per sales order?

3) Are you tracking more than one serial numbered part per invoice?

 

[Guest]

Thanks so much for sticking with me on this.

1. Yes
2. Yes
3. No

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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?

 

[Guest]

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?

 

[Guest]

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.

 

[Allen Browne]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.