I need to compare a value from one table and return a result from another ie Decode

[julianp]

I am trying to use the design view in Access XP to construct a query an
subsequently a report.
However I need to decode some values by comparing the coded results o
each column of the query with the code_decode table and then returnin
a decoded value from this table.

At present I am trying to workout what I should be entering in th
criteria box in the query designer to produce the following:

where decode.code_ref=table1.fieldname an
decode.code_val=table1.fieldvalue then return the value contained i
decode.decode_val

Table name decode
code_ref correspondes to the field in the table that we are decoding
code_val is the value assigned to coded data
decode_val is the translation of the code_val into plain English

I am not very good at entering SQL code and the query is already ver
long so the sql is complex. Thus I am trying to use the Access quer
builder where possible.

Any suggestions very gratefully received

Access is actually connected to a MYSQL Database through ODBC.

Julia

 

[John Vinson]

At present I am trying to workout what I should be entering in the
criteria box in the query designer to produce the following:

where decode.code_ref=table1.fieldname and
decode.code_val=table1.fieldvalue then return the value contained in
decode.decode_val

Table name decode
code_ref correspondes to the field in the table that we are decoding
code_val is the value assigned to coded data
decode_val is the translation of the code_val into plain English

Simply include the decode table in the Query; join it to your table by
joining code_ref to table1.fieldname. Include the decode_val as a
field in your query, and display it. This is how relational databases
(MySQL, Access, or any other) are designed to work!

 

[julianp]

Thanks for that John, It worked a treat.

J

 

[julianp]

Thanks for that John, It worked a treat.

J