Access 2K One to one relationship

[Chris]

I've got two tables; t_ORDER and t_QUOTE.

I want to relate the primary key of t_QUOTE to the primary
key of t_ORDER in a one-to-one relationship.

The tricky part is that there can be a quote ID, but not a
corresponding order ID. For example a customer is given a
quote, but the quote does not become an order, and there
is no purchase order. In other words the quote dies.

My thoughts are that I create a foreign key table (say
t_QUOTE_ORDER) to relate the two keys. This allows me to
delete quotes in t_QUOTE when quotes go "dead", with a
Cascade Delete to t_QUOTE_ORDER so the table doesnt get
overwhelmingly large??

Any thoughts/help appreciated??

cheers

 

[Jeff Boyce]

Chris

Why? As in, "why do you want to have your quotes related one-to-one with
your orders?" Especially when you describe a valid situation in which a
quote is created, but no related order ever is placed.

What about the idea of adding a field to your ORDER table, as a foreign key,
to point back to the QUOTE to which a given order is related?

Perhaps you have no business need to retain quotes that never turn into
orders, but I could see some value in examining your "dead quotes" for
possible explanations (e.g., 95% of the dead quotes are prepared by the same
person?!).

Are you anticipating an extremely large number of quotes? Measured in the
millions? Access, depending on version, can manage 1-2 Gbytes of data. If
you believe you'll be needing more room than that, you may want to consider
a more industrial-strength back-end (one of the SQL-oriented software
tools -- they'll carry their own issues in terms of care/feeding.).

Good luck!

Jeff Boyce
<Access MVP>

 

[John Vinson]

I've got two tables; t_ORDER and t_QUOTE.

I want to relate the primary key of t_QUOTE to the primary
key of t_ORDER in a one-to-one relationship.

The tricky part is that there can be a quote ID, but not a
corresponding order ID. For example a customer is given a
quote, but the quote does not become an order, and there
is no purchase order. In other words the quote dies.

My thoughts are that I create a foreign key table (say
t_QUOTE_ORDER) to relate the two keys. This allows me to
delete quotes in t_QUOTE when quotes go "dead", with a
Cascade Delete to t_QUOTE_ORDER so the table doesnt get
overwhelmingly large??

A "one to one" relationship is more precisely a "one to (zero or one)"
relationship. There would be nothing at all wrong with having a one to
one relationship from t_QUOTE to t_ORDER with missing records in
t_ORDER. The relationship is directional, with t_QUOTE being the
"parent" and t_ORDER the "child", so you can't have the opposite
situation (an order with no quote); but you don't need to have a third
table.

 

[Chris]

You're right. But consider the situation where an order IS
placed. There is ONE quote, and only one ORDER... hence
one-to-one ???

The ID number for a QUOTE is Autonumbered for convenience.
I want to pass that number to a new ORDER.

You have given me another idea though. I could include a
Purchase Order Number field in t_QUOTE and push that
number to t_ORDER when the Quote status is "accepted",
instead of the quote number. The only problem I see is
that purchase order numbers inevitably end up duplicated
across companies.

Hmmm, more thinking on my part needed...

cheers

 

[Tim Ferguson]

The ID number for a QUOTE is Autonumbered for convenience.
I want to pass that number to a new ORDER.

This pretty accurately sums up the information. One easy GUI implementation
is to have a command button on the Quotes Details form, that

(a) uses an SQL command to create a new Order record
"INSERT INTO Orders (OrderNumber) " & vbNewLine & _
"VALUES (" & Me.txtQuoteNumber & ");"

(b) opens the Order Details form and points it at the new Orders record

Three or four lines of VBA should do the whole thing. Hope that helps



Tim F