Quotes to Orders to Invoice design - Help!!

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!

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.

"Keith" wrote:

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!

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.



"mnature" wrote:

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.

"Keith" wrote:

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!

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.

I've inserted my comments below:

"mnature" wrote:

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


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?

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?


"Keith" wrote:

I've inserted my comments below:

"mnature" wrote:

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


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?

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)



"Keith" wrote:


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.



"mnature" wrote:

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.

"Keith" wrote:

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!

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?

Thanks so much for sticking with me on this.

1. Yes
2. Yes
3. No

"mnature" wrote:

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?

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.