Need description instead of code to show

[KevinS]

I have a table which includes the martial status. This status is a 4-letter
code. When I run the query the code shows. In another table is the code and
the description.

I would like the description to show in the query instead of the code.

I went into the properties of the field and entered information into the
lookup tab.

Display Control = List Box
Row Source Type=Table/Query
Row Source=Code table

What am I doing wrong?
Thank you!

 

[vanderghast]

It would be easier if you would base your forms on a saved QUERY rather than
directly on your main table.

To make the query: bring the two tables (the main one and the reference
table, the one able to translate the code to the description), make a join
between them by dragging the code field from one of the table over the same
field in the other table (in the upper part of the graphical designer).
Bring the fields your form will required in the grid, INCLUDING the
description field. Save. Use that QUERY as form Record Source. Use the
description field, now available, where intented, in lieu of, or in addition
to, the code field, in your form.



Vanderghast, Access MVP

 

[KevinS]

Need clarification.
I am using a query but the martial status is part of a table doesn't have a
code field that matches.

Table 1:
Martial status
gender
age

Table 2:
code
type
description

I would like the description from Table 2 to show 'Married' on the query
versus the Martial status which shows the 'MR01' code.

 

[vanderghast]

If the data is not there (with match), maybe the solution is about to create
a third table, Table3, which will make the translation possible:



Table3
MaritalSatus Code ' fields
MR01 Code1 ' values
.... ...



where code1 is the code value, in table2, to be matched. Be sure that
MaritalStatus values occur only once, no dup, in table3 (make it a primary
key).


Then, the query will join table1 to table3 (comme field maritalStatus) and
table3 to table2 (common field code).



If that is not your case, can you supply a short example of the relevant
DATA you are handling? (ie, two, three records, for each table, and the
result you want).



Vanderghast, Access MVP

Need clarification.
I am using a query but the martial status is part of a table doesn't have
a
code field that matches.

Table 1:
Martial status
gender
age

Table 2:
code
type
description

I would like the description from Table 2 to show 'Married' on the query
versus the Martial status which shows the 'MR01' code.