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.

 

[MGFoster]

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!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need a ComboBox instead of a ListBox. The properties would be like
this:

Column Count: 2
Column Widths: 0",1.5"
Bound Column: 1
Limit to List: Yes
Row Source Type: Table/Query
Row Source: [Code Table]

In Column Widths the 0" means hide the martial_code and show the
marital_description in a 1.5 inch field. This relies on the
MartialCodes table have a structure like this:

martial_code, martial_description


OR, you could fore go the ComboBox set up and make your query like this:

SELECT <column list>, M.martial_description
FROM main_table INNER JOIN MartialCodes As M ON main_table.martial_code
= M.martial_code
WHERE < criteria >

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSjpJEoechKqOuFEgEQJWBQCglpLivt068vcygn6kw9x6H7BOxKYAoK3K
Ah/SbkZ5QkVTIulvURdj3bDs
=kRqB
-----END PGP SIGNATURE-----

 

[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.