Harold,
I would create a "Lookup Table" in AC. It looks like it will only need 2
columns. The "ID" Column and a "Description" Column. Make sure the ID
column is the same data type as the ID column in the other table.
In your query, add this new table to the diagram pane and create a join on
the ID column from the data table to the ID column in the lookup table. To
create this join, click on the ID column in the lookup table and drag it
over to the ID column in the data table.
As it stands that way, your results will only display ID's that are common
in both tables...that is, if the lookup table has only 2 rows (ID's 1 & 2)
then your results will only show records that have a 1 or a 2 in the ID
column, any records with anything else will be left out. If there are
records int the data table that have 2, 3, & 4 in the ID column, then your
results will only show records with an ID of 2, because 2 would be the only
ID that is common in both tables.
If you double click on the join line, you will get a dialog. Option 1 is
what I just described. Option 2 will return all records from the lookup
table and all matching records in the data table. If you have 1 & 2 in your
lookup table and 2-4 in your data table, your results will have a blank row
for 1 because there is a 1 in the look up table but no 1's in the data
table...and then it will list all 2's in the data table, leaving out 3 & 4
because they are not in the lookup table.
If you choose option # 3, it will list all rows from the data table, but
only records with an ID of 1 or 2 will be able to "lookup" the description.
If there are any ID's in the data table with 3 or 4, their description will
be blank.
After adding the lookup table to the query and creating the join, then drag
the "Description" column from the lookup table down to the grid in the query
design view. For every ID in the data table, the query will look up that ID
in the lookup table and return the description from the lookup table into
the query results.
HTH,
Conan
