Quotes to Orders to Invoice design - Help!!

[Keith]

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]

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.

 

[David M C]

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]

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.

 

[Keith]

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]

Answers in-line

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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.

 

[Keith]

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.

 

[Keith]

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.

 

[Allen Browne]

Good news.

Feel free to post other new questions as you go.