How to redisplay the text field based on a stored ID field

I stored the ID of a lookup table for Salutation as a foreign key in my
People table. I did the same thing with a suffix lookup table.

I am trying to produce a concatenated Salutation Firstname MidName Lastname
Suffix based on my people table, however, it will only show the stored ID
Number.

Could someone show me the proper way to query this. I have setup
relationships from the Suffix and Salutation tables to the foreign keys in
the People table, and I was able to get the salutation to display as
text...when I look at the sql view, it seems to me that my problem is that
the query is only doing an innerjoin on the salutation ID...as soon as I add
in the suffix table, I end up with an empty query.

Thank you for any help...I have always been unsure how to reconvert the ID
to the text.

Billiam

Billiam -

You probably need an outer join with the suffix table unless everyone is
required to have a suffix. If you post your SQL, that will allow us to help
you better.

--
Daryl S


"Billiam" wrote:

I stored the ID of a lookup table for Salutation as a foreign key in my
People table. I did the same thing with a suffix lookup table.

I am trying to produce a concatenated Salutation Firstname MidName Lastname
Suffix based on my people table, however, it will only show the stored ID
Number.

Could someone show me the proper way to query this. I have setup
relationships from the Suffix and Salutation tables to the foreign keys in
the People table, and I was able to get the salutation to display as
text...when I look at the sql view, it seems to me that my problem is that
the query is only doing an innerjoin on the salutation ID...as soon as I add
in the suffix table, I end up with an empty query.

Thank you for any help...I have always been unsure how to reconvert the ID
to the text.

Billiam

On Fri, 19 Feb 2010 08:43:01 -0800, Billiam
wrote:

I stored the ID of a lookup table for Salutation as a foreign key in my
People table. I did the same thing with a suffix lookup table.

I am trying to produce a concatenated Salutation Firstname MidName Lastname
Suffix based on my people table, however, it will only show the stored ID
Number.

Could someone show me the proper way to query this. I have setup
relationships from the Suffix and Salutation tables to the foreign keys in
the People table, and I was able to get the salutation to display as
text...when I look at the sql view, it seems to me that my problem is that
the query is only doing an innerjoin on the salutation ID...as soon as I add
in the suffix table, I end up with an empty query.

Thank you for any help...I have always been unsure how to reconvert the ID
to the text.

Billiam

Please post the complete SQL of the query. I think you need to join both the
salutation and suffix tables to the People table using "Left Outer Join" -
select the join line in the query design window and choose the option "show
all records in People and matching records in...". You would then choose the
text field from the lookup table (rather than the ID from the people table).
--

John W. Vinson [MVP]