Retrieve the second column value in a Value list in a table.

I need to pass the Second column value from a value list within a table. for use in a query or to display the correct value on a form.

tablename = tblTC
FieldName = collectedby
Field Value =
Code      Name
EC           Expert Collect
HC           Helper Collect
DIV          Divided Option

The results to be displayed in the query should read "Expert Collect" or Helper Collect or Divided Option and not the Code value.

what do I need to do to display the correct value on the form or within the underlying data source?

Karen SchaeferBI ANALYSTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You need separate table:
Code      Name
EC           Expert Collect
HC           Helper Collect
DIV          Divided Option
In this case you can select Name in any query or use it as lookup field in a table

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
as als315 indicated, whenever you have a code and a description as indicated above, your best option is to create a "lookup" table, which you can use to either:

1.  display in a combo box on a form, using the code as the bound column, with zero column width, and the Name (description) as the visible column in the combo box (width = 1 or some value that is long enough do display the entire name.  This gives you the ability to store the code but display the more readable name.

2.  join the lookup table to your main table in a query to return the [Name] value.  BTW, Name is a reserved word, so if you are going to use it as a field name, you are going to have to wrap it in [ ] everywhere you use it.

Unlike als's final recommendation, I strongly encourage you NOT to use this table as a "lookup field" in a table.  Microsoft instituted this "feature" a few years back, but what I've found is that it tends to confuse beginner developers more than help them.  The problem with setting a field (your code field in your main table) up as a lookup is that whenever you open that table, you will see the [Name] value, not the Code, which is actually the value stored in that field.
Karen SchaeferBI ANALYSTAuthor Commented:
thanks for the input.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.