AnsweredAssumed Answered

stuck on proper table relationship

Question asked by timothy2k on Jul 27, 2009
Latest reply on Jul 28, 2009 by TSGal

Title

stuck on proper table relationship

Post

I am getting closer (I believe) to finishing all my table and relationships setup, but I am stuck on how to get rid of a many to many relationship.  Any general advice would be very helpful and apreciated!

 

I hope this explanation helps:

 

An estimate is unique by a customer id and an estimate id. Estimates, however, can have duplicate parts.

 

So, the

estimate id's can look like: E10020, E10020-1, E10020-2, E10021, E10022 etc.

customer ID's look like: EVPT, MTYF, SDRT (basically 4 letter codes)

 

Quotes are then created and sent out to vendors to quote (or "bid") on.

 

I've been trying to group the quote items by estimate id & customer id, which I hope would allow me to have one quote display all the estimate parts associated with that vendor in one simple quote.

 

The tricky part is that one or all parts of an estimate can be sent to one or many different vendors. I am trying to figure out how to group all or one part of an estimate into one quote for one vendor, for each vendor I want to send a quote to.

 

I have the following tables:

customer

estimate

quote item

quote

vendor

 

The problem is, when I try to relate records in quote item to one quote- it becomes a many to many relationship.  I attached a picture of the relationship setup, and would be glad to provide further explanation to anyone who may be able to help.

 

http://img505.imageshack.us/img505/9872/relationships.gif 

 

*Just ignore the purchase order and invoice part of things for now, I will use the same approach for those once I figure out this quote part.

 

Any advice is very much apreciated.

-Tim

 

 

 

 

Outcomes