Defining 2 foreign keys out of the same Primary key

[Mishanya]

I have tblDestination (DestinationID and Destination fields) and tblRoot
(From and To fields among the others).
I set DestinationID as a foriegn key for both From and To fields, but now I
can't query on those fields - query based on the two tables with Destination,
From and To fields queried gives no records.
I can solve it by creating 2 identical tables tblDestinationFrom and
tblDestinationTo and setting regular one-to-many relationships with tblRoot,
but that would denormalize the database.
How can I solve the problem otherwise?

 

[Doctor]

Place two instances of tblDestination in your query one related to the To
field and the other one related to the From field. Hope it helps.

 

[Mishanya]

Doc, come on...

Place two instances of tblDestination in your query one related to the To
field and the other one related to the From field. Hope it helps.

 

[Allen Browne]

There are several issues here: reserved words, relationships, and outer
joins.

1. FROM and TO are both reserved words in JET SQL, so may cause you grief as
field names in tblRoot. Consider changing them to (say) FromID and ToID.

2. In the relationships window, create a relationship from
tblDestination.DestinationID to tblRoot.FromID. In the Create Relationship
dialog, check the box for Referential Integrity. (This ensures you don't
have any From entries in tblRoot that don't match any entry in
tblDestination.)

3. Still in the relationships window, add a 2nd copy of tblDestination.
Access will alias it as tblDestination_1. Create a relation from
tblDestination_1.DestinationID to tblRood.ToID, again enforcing referential
integrity. Note that using 2 different copies of tblDestination gives you 2
different relationships, as distinct from one 2-field relationship.

4. Now create a query using tblRoot and 2 copies of tblDestination. Again,
Access will alias the 2nd copy. (If you would prefer to call the 2nd copy
tblTo instead of tblDestination_1, do that by setting the Alias property in
the Properties box.)

5. Still in query design, ensure that you have ONE join line from
tblRoot.FromID to tblDestination.DestinationID, and ONE join line from
tblRoot.ToID to tblDestination.DestinationID. You may have to manually add
or delete joins to get this.

6. In tblRoot, have you set BOTH the FromID and the ToID as required fields?
If not, this query won't return any record where either of those fields is
null. To correct that problem, double-click the line between the tables in
the upper pane of query design. Access pops up a dialog offering 3 options.
Choose the one that says:
All records from tblRoot, and any matches from tblDestination.
Technically, we call this an outer join. More info about that:
http://allenbrowne.com/casu-02.html

 

[John W. Vinson]

I have tblDestination (DestinationID and Destination fields) and tblRoot
(From and To fields among the others).
I set DestinationID as a foriegn key for both From and To fields, but now I
can't query on those fields - query based on the two tables with Destination,
From and To fields queried gives no records.
I can solve it by creating 2 identical tables tblDestinationFrom and
tblDestinationTo and setting regular one-to-many relationships with tblRoot,
but that would denormalize the database.
How can I solve the problem otherwise?

Doctor's absolutely correct: create a query with tblRoot, and add *two*
instances of tblDestination, joining one to From and the other to To.

Did you perchance try it, or did you just reject Doctor's correct advice out
of hand because s/he didn't have a .sig that impressed you?

 

[Mishanya]

Hi John.
I've rejected the guy's advice, because 5 min before he'd given me another
confused advice in another forum. I did look in s/his .sig and saw that s/he
might be unexpeienced user. More then that, I've posted my question only
after I'd tried all the obvious possibilities, including this one.
I do look for the answers from Access' experts, like yourself or Allen (just
look at his version of advice!), because I think this forum is not for "hope
it'll help" kind of advices, let alone that thread with 2 posts might be seen
as "answered" and overlooked by the experts.
Hope I've explained myself and look for your kind assistance in this forum
in the future.
Respectfully,
Misha.

 

[Mishanya]

Allen, thank you very much for your excellent full answer.

I coud not set ref.int-ty while joining tblRoot.ToID to
tblDestination_1.DestinationID in the query design (there is no such a
possibility in query design mode) but I think it' s alright as long as I've
set it in the Relationships grid.

Just how do I set tblDestinationTo Alias in the Property box?

Thanks.

 

[Allen Browne]

This is how you set Referential Integrity in the Relationships window:
http://temp.allenbrowne.com/ReferentialIntegrity.png

To alias a table in query design, right-click the table (in the upper pane
of the query design window), and choose Properties. Alias is then the first
property:
http://temp.allenbrowne.com/QueryTableProperties.png

 

[Mishanya]

Allen
I did set the Referential Integrity in the Relationships window for 2nd copy
of tblDestination aliased by Access as tblDestination_1.
But when I've created a query using tblRoot and 2 copies of tblDestination,
the 1st copy appeared with 2 relatioships (between FromID and ToID to
DestinationID) and the second copy appeared as unrelated (as you rightly had
supposed), so I had to create the relationship again in the query design pane
itself. But when you do so and double-click on the relationship-bond to edit
the relationship you only get Join Type Property edit box, instead of Edit
Relationship box (wich would have appeared if done the DB Relationship Gridi.
That's what I meant by asking if that' all right.

As for the 2nd tip (setting table alias in query pane) - thank You again.

 

[Keith Wilby]

I think this forum is not for "hope
it'll help" kind of advices,

I've made many a "hope it helps" post and often they do. You should bear in
mind that everyone here who offers advice, including "Doctor", is a
volunteer and is trying to help. Posting unappreciative responses to an
unpaid helper is just plain rude. If you don't like someone's solution then
ignore it, especially when the solution offered is a good one.

 

[Allen Browne]

Okay, I think I follow what you are saying.

When you see a line joining 2 tables in the upper pane of query design,
that's actually defining the JOIN between the tables. A relationship between
table is something else: it is defined in the Relationships window, and
that's where the RI is set.

As you found, the dialog that pops up when you click the join line between
tables in query design doesn't have any options for Referential Integrity or
Cascading Update/Delete. It's just asking which of 3 join types you want in
this query: INNER JOIN, LEFT JOIN, or RIGHT JOIN.

 

[Gina Whipp]

Mishanya,

Ummm... I was once the *Poster* and am now the *Postee* though sometimes I
revert back to being the *Poster*. Advice comes from all types of people...
from novice to experts. One day you yourself might see a question and say I
know the answer to that! Would you want to be ignored or slighted because
you are not an *expert*? I am by no means as an expert like some of the
guys and gals I see here but I answer and sometimes I am corrected but I
still answer...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Mishanya]

Gina, Keith, John
I think all of You are right!
But I tend to agree more with Allen

 

[Duane Hookom]

It's too bad we all can't be Allen Brownes. The quality and depth of his
answers as well as his time and patience are unequaled in the Access news
groups.

--
Duane Hookom
Microsoft Access MVP

Gina, Keith, John
I think all of You are right!
But I tend to agree more with Allen