Defining 2 foreign keys out of the same Primary key

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?

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

Doc, come on...


"Doctor" wrote:

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

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?

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]

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]

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?

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?

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

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.