Alright, here's the setup.
I have an index list (hereafter referred to as INDEX), which is a table with
one column of (unique) numbers, and another column of corresponding labels. I
have another table, hereafter referred to as INPUT, with a bunch of numbers.
What I want to do is have Access take the INPUT, and create a table (or
query, whatever) that will convert these numbers into their corresponding
lables from the INDEX. (I also need to be able to convert labels into
corresponding numbers, the exact opposite process.)
I have already tried using a query, and setting up a relationship between
the INPUT numbers and the INDEX numbers, and having the query output the
labels. The problem is, if a number in the INPUT is not listed in the INDEX,
then the entry just disappears from the query altogether. I would prefer that
the "unregistered" numbers simply stay as numbers in the column. This also
applies when doing the reverse: if the INPUT is a column of words instead,
and there is a word that is not in the INDEX, then just leave the word,
rather than converting it to a number.
All help is greatly appreciated!

You have an inner join between the two tables. In that case both tables need
a matching record for a query to return a record.

In query design view double-click on the line between the two tables until a
dialog box appears. Try the second option for the join (left join). If that
doesn't work, try the third option (right join).
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Chacho" wrote:

> Alright, here's the setup.
> I have an index list (hereafter referred to as INDEX), which is a table with
> one column of (unique) numbers, and another column of corresponding labels. I
> have another table, hereafter referred to as INPUT, with a bunch of numbers.
> What I want to do is have Access take the INPUT, and create a table (or
> query, whatever) that will convert these numbers into their corresponding
> lables from the INDEX. (I also need to be able to convert labels into
> corresponding numbers, the exact opposite process.)
> I have already tried using a query, and setting up a relationship between
> the INPUT numbers and the INDEX numbers, and having the query output the
> labels. The problem is, if a number in the INPUT is not listed in the INDEX,
> then the entry just disappears from the query altogether. I would prefer that
> the "unregistered" numbers simply stay as numbers in the column. This also
> applies when doing the reverse: if the INPUT is a column of words instead,
> and there is a word that is not in the INDEX, then just leave the word,
> rather than converting it to a number.
> All help is greatly appreciated!
>