AutoNumber is displayed instead of the name

[IndianaJen]

I have created a look up from another table in a database that I am
redesigning. I made a few changes and then went back to the original table
and found that all of the values I had chosen from the lookup in that field
had converted themselves to showing the primary key, rather than the name
that I need. I also noticed that the data type of the field is now Number
rather than Text. I have three questions:

1. What did I do to have the primary key showing instead of the name? (I
want to prevent this from happening again.)

2. How can I get them back to showing the name? I have close to 2000 records
and I refuse to go back by hand to change them the way they need to be.

and 3. Is it possible to automatically look up and fill in a single field
for multiple records, while keeping other fields untouched? Let me give you
an example.
I inherited a database that didn't use any relationship data. All of the
data was in one table. I cleaned up the data using excel and designed a
database to use relationships with other tables. I imported the clients to
the client table, then imported the projects to the project table. Can I now
import the clients to the client field in the project table (which has a
lookup to the client table) so that they match with the lookup in that field?
I have done this once, and I would rather not have to go back and recreate
2000 records so that they match with the lookup.

Any help you can give me on this would be great. Thanks!

 

[John W. Vinson]

I have created a look up from another table in a database

That was your first mistake.

See http://www.mvps.org/access/lookupfields.htm for a critique of this
misfeature.

that I am
redesigning. I made a few changes and then went back to the original table
and found that all of the values I had chosen from the lookup in that field
had converted themselves to showing the primary key, rather than the name
that I need. I also noticed that the data type of the field is now Number
rather than Text. I have three questions:

1. What did I do to have the primary key showing instead of the name? (I
want to prevent this from happening again.)

Because that is what your table actually contains.

2. How can I get them back to showing the name? I have close to 2000 records
and I refuse to go back by hand to change them the way they need to be.

Why not leave the Lookup alone (lookups aren't bad, it's the Lookup Wizard and
combo boxes in Tables that are objectionable)?

and 3. Is it possible to automatically look up and fill in a single field
for multiple records, while keeping other fields untouched? Let me give you
an example.

Of course. Use a Query joining the table to the lookup table.

I inherited a database that didn't use any relationship data. All of the
data was in one table.

Then it wasn't a database - it was a spreadsheet. A database without
relationships is analogous to an Excel spreadsheet without any expressions in
cells - a misuse of the product!

I cleaned up the data using excel and designed a
database to use relationships with other tables. I imported the clients to
the client table, then imported the projects to the project table. Can I now
import the clients to the client field in the project table (which has a
lookup to the client table) so that they match with the lookup in that field?

Yes, with an appropriate Append query. Not knowing the structure of your
tables I can't write the query for you but basically you can join the
wide-flat table to the Clients table by the client name, and pick up the
client ID for the append.

John W. Vinson [MVP]

 

[Jerry Whittle]

If you are talking about a lookup field at table level, you just ran into one
of many reason to not use them. Below is a great list of reasons to dump them.

http://www.mvps.org/access/lookupfields.htm

Further check out the second commandment here:

http://www.mvps.org/access/tencommandments.htm

Your best bet is to create relationships between your table in the
Relationships window (hopefully with Referential Integrity enabled) and
create lookups or subforms on your forms.

 

[IndianaJen]

Thank you for your response. I think now I have to re-examine everything I've
done so far with these relationships. When designing the database, then,
using the Relationships window, do I relate the fields to the Primary Key, or
to the field that contains the data that I really want?

 

[IndianaJen]

Thank you for your response. I can definitely see why lookup fields are bad
in tables. This unfortunately spurs me to ask a few more general questions,
then.

1. How do I relate the tables in the Relationship window? By the Primary
Key, or the field that I actually want to look up? If it is by the field that
I want to look up, do I use the append queries to fix the data that is
already in there?

2. If I am not using a lookup in the table, can I still restrict the data in
the table to the list that it is related to? If so, how?

3. I am not familiar with append queries. Will this be a permanent query
that will have to continuously run, or does it actually place the data in the
table and I can get rid of the query afterwards?

 

[Jerry Whittle]

You should link from the Primary Key of the parent table to the Foreign Key
of the child table. Say that you use a ClientNumber field as the PK in the
Clients table, you should have a ClientNumber field in the Programs table.
That's what you would link together in the Relationships window. Hopefully
you can also enable Referential Integrity. BTW it doesn't have to be named
ClientNumber in the Programs table. In fact I ofteh will name something like
that ClientNumberFK and ClientNumberPK.

 

[John W. Vinson]

Thank you for your response. I can definitely see why lookup fields are bad
in tables. This unfortunately spurs me to ask a few more general questions,
then.

1. How do I relate the tables in the Relationship window? By the Primary
Key, or the field that I actually want to look up? If it is by the field that
I want to look up, do I use the append queries to fix the data that is
already in there?

You relate the Primary Key of the lookup table to the Foreign Key (a field of
the same datatype - Long Integer if the primary key is an autonumber) in the
related table.

2. If I am not using a lookup in the table, can I still restrict the data in
the table to the list that it is related to? If so, how?

Of course. The relationship will prevent you from adding an invalid value; a
Combo Box on a Form (with its Limit to List property left at its default True
value) will let you select a record from the lookup table and store its ID in
the foreign key field, while displaying the "looked up" value.

3. I am not familiar with append queries. Will this be a permanent query
that will have to continuously run, or does it actually place the data in the
table and I can get rid of the query afterwards?

It places the data into the table once and for all, and can then be deleted.

John W. Vinson [MVP]

 

[IndianaJen]

John,

I have tried adding the data using the append query, but all it seems to do
is add new records, rather than adding data to the column of existing
records. What am I doing wrong?

 

[John W. Vinson]

I have tried adding the data using the append query, but all it seems to do
is add new records, rather than adding data to the column of existing
records. What am I doing wrong?

Nothing at all, other than using an Append query in the first place (assuming
you don't want to add data). That's what append queries DO - add new records
to an exisiting table.

If you want to change the value of a field in an existing record use an Update
query instead.

Could you explain, though, what you mean by "adding data to the column of
existing records"? What is your table structure, and what are you trying to
add to it?

John W. Vinson [MVP]

 

[IndianaJen]

I have a table of 2000 records. I imported the project information (name and
number columns), but any information in the client column (which is in a
relationship) did not import. I have the information in a table that was
previously created in access. I would like to have the client column
populated with the client information rather than going through by hand to
put in the the client.

On that same note, I am fixing another table and I want to have the
information that is in one column populated into another column in the same
table. The new column has the relationship already created.

 

[John W. Vinson]

I have a table of 2000 records. I imported the project information (name and
number columns), but any information in the client column (which is in a
relationship) did not import. I have the information in a table that was
previously created in access. I would like to have the client column
populated with the client information rather than going through by hand to
put in the the client.

On that same note, I am fixing another table and I want to have the
information that is in one column populated into another column in the same
table. The new column has the relationship already created.

If you have a (numeric) ClientID in the project information then *that is all
that you need*. You can create a Query joining tis imported table to the
Client table. It is neither necessary nor a good idea to store the client
information redundantly in the project table!

And if you DON'T have any information in the project table which would
identify the client - how can Access, or any program or any person, identify
which client goes with which project?

For your second question... again, why are you storing THE SAME information
redundantly in two fields!? You can do it - run an update query updating
Column2 to

[Column1]

including the square brackets around the fieldname - but it's not clear to me
that this second column should even *exist*. Why do you feel you need it?

John W. Vinson [MVP]