Lookup field

[Zack Barresse]

Hello all,

Trying to somewhat normalize my database. I have two tables (eventually I
will migrate this to more tables, but starting with these two first), first
table has DepartmentID and Department fields; second table has a Dept field
as well. In this second table I went to Design View, selected the Dept
field and selected the Lookup tab below. I put the Row Source equal to:

SELECT Departments.DepartmentID, Departments.Department FROM Departments;

Here are my Lookup settings...
Display Control = Combo Box
Row Source Type = Table/Query
Bound Column = 1
Column Count = 2

When I go to View the table then, the field Dept has a drop down but it only
has numbers in it. How do I get it to show the Department values instead of
the DepartmentID values? Any other recommendations? Thanks.

 

[Zack Barresse]

Okaaaay, well, I found the Lookup Wizard...

I think I've got it. The only problem I am having is that it deleted all my
values in that field. (I used a backup table of course.) I'm going to try
and run a query and update the backup table to get my values back, and if
it's working, I'll replace the backup table as the final table.

I'd appreciate any other pointers from you veterans.

 

[Guest]

You may need to set your Bound Column value to 2 rather than 1. Though if
the relationships have already been setup it may not be good enough. When
first setting up the lookup field is suggests hiding the key field. Every
time I've ever opted to show the key field, I've gotten the numeric value of
that record rather than the text value.

 

[Zack Barresse]

Thanks Ozzie. I did find the Lookup Wizard, which led me to what I was
looking for, and I did hide the key field. Thanks very much for your
response!

 

[John Vinson]

Trying to somewhat normalize my database.

I'd suggest NOT using the (very limited and confusing) Lookup Wizard
to do so. See http://www.mvps.org/access/lookupfields.htm for a
critique.

There is *nothing* that the Lookup Wizard does which cannot be done
using the Relationships Window and the form/report design Toolbar. My
biggest criticism of the Lookup Wizard is that it misleads developers
who aren't intimately familiar with its quirks; what you *SEE* in the
table datasheet is not what's actually stored in the table! You see a
name but what's actually *there* is a number. Queries searching the
field, or sorting it, give unexpected results because they search or
sort the actual numeric field contents when you might expect to be
hitting the visible text.

I realize I'm tilting at the very large Microsoft windmill here - the
next release of Access will not only retain but greatly expand the
Lookup feature. :-{(

John W. Vinson[MVP]

 

[Zack Barresse]

Thanks for the heads up John. You know, it's funny, I didn't have them on
there, but then a friend of mine suggested I do that to normalize my
database. And as I have no idea what I'm talking about or doing with
Access, I took his word for it. I'll look into that some more.