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)

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)

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)

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)

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)

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.

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.

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.

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.