Defining 2 foreign keys out of the same Primary key

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Mishanya" wrote in message
...
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.
"Allen Browne" wrote:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Mishanya" wrote in message
...
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" wrote:

There are several issues he 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


--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Mishanya" wrote in message
...
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?

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" wrote in message
...
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.

"John W. Vinson" wrote:

On Wed, 12 Aug 2009 16:16:02 -0700, Mishanya
wrote:

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?
--

John W. Vinson [MVP]

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

"Gina Whipp" wrote:

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" wrote in message
...
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.

"John W. Vinson" wrote:

On Wed, 12 Aug 2009 16:16:02 -0700, Mishanya
wrote:

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?
--

John W. Vinson [MVP]

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


"Mishanya" wrote:

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

"Gina Whipp" wrote:

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" wrote in message
...
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.

"John W. Vinson" wrote:

On Wed, 12 Aug 2009 16:16:02 -0700, Mishanya
wrote:

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?
--

John W. Vinson [MVP]