I am stumped how to use a self join to extract a correct information
from the same table. I assume that a self join is the way to do what I
want to accomplish.

I have a table called Firms in which I store information about
accounting firms (such as address, telephone, URL, specialties etc). A
short name of the firm, which is created manually, and is the primary
key. It must be unique. A long name field contains a full legal name of
the firm.

Many small accounting firms have an alliance relationship with larger
accounting firms. So as a part of the Firm table fields I also have a
short name for the alliance firm, if any. I insert the short name using
an index. I would like to extract the long name of the alliance firm
from the same table, corresponding with the short name. But I am
walking in circle and got all wrapped up in table occurences and
correct relationships. Can' find my way out.

Can anybody help? I am sure this must be trivial to many, but it is a
new thing and a challenge to me.

Thanks

George

In article <1114635920.948412.42550 (AT) l41g2000cwc (DOT) googlegroups.com>,
"George" <g.sova (AT) comcast (DOT) net> wrote:

If I'm reading this right, then you need two fields:

ShortName Text
used to store the short name of THIS firm (ie. the firm
the record data on-screnn is for)

Alliance_ShortName Text
used to store the Alliance Firm's short name

Then all you need is a self-join relationship set-up as:

SelfJoin Alliance_ShortName --> Firms::ShortName

and you can then pick up the Alliance Firm's fields via this
relationship.

BUT, this will only work if "ShortName" contains unique data for each
firm. You'll also have a problem if there's more than one Alliance
Firm, but I won't worry about that unless you've actually got that
problem.

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)