Hello,
Is it possible to create multiple foreign keys on a field in a table
from values in two separate tables where the field value can be from
one table OR the other?

For example, I have an Invoice table where the Customer field is
linked (foreign key) to the Customer table. As the application has
changed, I now want to introduce the concept of prospects by storing
them in a table dedicated to prospects only (no customers). I want to
be able to create an Invoice for a prospect, however there is the
foreign key relationship to the Customer table. I was able to
successfully create a second relationship to the Prospect table,
however, it is now expecting the Customer field value to be in both
tables. Is there a way that I can link the Customer field to either a
value in the Customer table OR the Prospect table?

Thanks for any help.

On Sep 5, 9:00*am, anjee <anjee...@cogeco.cawrote:

Hello,
Is it possible to create multiple foreign keys on a field in a table
from values in two separate tables where the field value can be from
one table OR the other?
>
For example, I have an Invoice table where the Customer field is
linked (foreign key) to the Customer table. *As the application has
changed, I now want to introduce the concept of prospects by storing
them in a table dedicated to prospects only (no customers). *I want to
be able to create an Invoice for a prospect, however there is the
foreign key relationship to the Customer table. *I was able to
successfully create a second relationship to the Prospect table,
however, it is now expecting the Customer field value to be in both
tables. *Is there a way that I can link the Customer field to either a
value in the Customer table OR the Prospect table?
>
Thanks for any help.

Anjee,

I won't get into design issues or concepts. (Some would say you
should make customers and prospects in the same table, and simply flag
them as either customers or prospects (and "inactives" too).)

But, regardless of design, the simple answer is that there's really
nothing that says you have to use Access's relationship table to
design and reinforce relationships. (Maybe databases only define
keys, and don't require defining the foreign keys.)

You can set those relationships manually in your queries by linking
the fields together with Joins. And, you can set your joined value
foreign keys by setting those keys in your form when you write lines
to a table. And you can enforce presence of some keys by making them
required values in your table design.

Consider doing union queries if you want to join up 2 tables.

Jon

The purpose of table relationships is to enforce referential integrity.
It does not have anything to do with Joins. In a one-to-many
relationship the "one" (Primary) table must contain the Value that will
be inserted into the "many" (detail/child) table.

Keys will prevent duplicate data entry. Relationships prevent invalid
data from being entered into a system (depending on how the
relationships(s) are defined). Your application is working as expected
if you cannot enter data into a given table if the key field in the
Primary table does not contain the value you are trying to enter into
the child table.

You can have several relationships in a child table. Fld1 can be
related to PrimaryTbl_A, fld2 can be related to PrimaryTbl_B. This
means that values for fld1 and fld2 must exist in PrimaryTbl_A and
PrimaryTbl_B before these values can be inserted into the child table.

Generally, the Primary tables will only contain one or two fields and
the rows will be unique. These primary tables are constraint tables and
relationships are constraints.

One thing to understand, however, is that a field in a child table can
only be related to one Primary table. You cannot relate two Primary
tables to the same field in the child table. But the converse is that
one Primary table can be related to one field in several child tables.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

You can create an additional foreign key... one to the Customer table, one
to the Prospect table... bring in the Name from each... create a Text Box
for each... overlay the Text Boxes exactly. Now you'll know, one way or
another, which you are dealing with... set that Text Box's Visible property
to True and the other Text Box's Visible property to False. If you have
address information, too, it's a little more complicated. You shouldn't
assume that one and only one of the Customer / Prospect Fields will be
Null -- because it will surely happen that both will, probably sooner rather
than later, be populated.

Frankly, it seems simpler and easier to me to include a Field in your
Customer table for Type, and check it when necessary. It's not clear to me
why it would be necessary to check that to create the Invoice... maybe for
subsequent processing.

Larry Linson
Microsoft Office Access MVP


"anjee" <anjeedev@cogeco.cawrote in message
news:1c5284ca-5e76-46d0-bd26-01ff76b74fad@z72g2000hsb.googlegroups.com...

Hello,
Is it possible to create multiple foreign keys on a field in a table
from values in two separate tables where the field value can be from
one table OR the other?
>
For example, I have an Invoice table where the Customer field is
linked (foreign key) to the Customer table. As the application has
changed, I now want to introduce the concept of prospects by storing
them in a table dedicated to prospects only (no customers). I want to
be able to create an Invoice for a prospect, however there is the
foreign key relationship to the Customer table. I was able to
successfully create a second relationship to the Prospect table,
however, it is now expecting the Customer field value to be in both
tables. Is there a way that I can link the Customer field to either a
value in the Customer table OR the Prospect table?
>
Thanks for any help.