using global self-relation in query?

[edward]

I have a self-relation set up globally -- that is, from the database
window I click Relationships, the table is in the picture twice, with a
self-link.

But when I go to create a query where I need this relationship, it
doesn't show. In the query design, if I add two different tables which
are globally related, the relationship is immediately shown as a join.
But if I add the same table twice, the second one is put in solitary
isolation -- no joins, neither to itself nor to other tables. I can
then add the join in the query builder, but why do I have to?

What am I missing?

Thanks,

Edward

 

[Allen Browne]

Query design is not always smart enough to automatically handle relations
between aliased tables.

When you add tables to query design, if you have designed relations between
them, Access looks to see what default join type you specified. If there is
not relation, it still has a guess based on the field names and types. If
the tables in the query design window are aliased, Access may not recognise
them, so it might show the join, or it might not, and if it does it may not
paint the line as a 1 to many.

When you add the 2nd copy of the table to the query design grid, Access
aliases it with a "_1" suffix. It is probably not going to figure it out for
you.

 

[edward]

OK, thanks, Allen ... at least I know it's not just me. ;-)

Edward