Importing data into a Lookup Field

[Matt M.]

Well, I imported a bunch of data into a table with a lookup field.

The lookup field goes to a table of projects and the relationship is
on an auto generated primary key.

When I imported my data, I didn't convert the project names into the
integers that identify the projects in my Projects table. I thought
Access would be smart and help me do this. But it didn't.

Now, while I'm trying to use the drop down menu to update the child
table manually, I get a lot of horrible beeping and type exception
error messages. I unlinked the tables, but the beeping and error
messages persist. HELP!

 

[Ken Sheridan]

When importing data into a referencing table (i.e. the 'child' table with the
'lookup' field) where the values imported are the text values used in the
referenced ('lookup') table, you need to tackle it in stages:

First create a separate text field in the referenced table and import the
text values into this, not into the long integer foreign key field.

Next create an update query which joins the referencing and referenced
tables on the text fields, not on the numeric keys. This assumes the text
values in the referenced table are unique of course, and that the values
imported are an exact match for the values in the referenced table. Restrict
the query to the rows where the foreign key numeric field from the
referencing table IS NULL; this is not essential but there's no point
updating values to themselves. In the query update the foreign key numeric
column in the referencing table to the value of the primary key column of the
referenced table.

If you are going to be importing more data in the future you can leave the
extra text field in the referencing table for future use. If not then you
can delete it as its done its job.

BTW the conventional wisdom is that the 'lookup wizard' so-called data type
(its nothing of the sort of course, the column is a long integer number data
type) should be avoided like the plague. See the following link for reasons
why:


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


In raw datasheet view of a table its best left so you see the real
underlying values. You can see the referenced text values via a combo box in
a form (where data should be entered and edited, not in datasheet view), and
in a report its merely a question of joining the tables in a query and
returning the text column from the referenced table, binding a text box
control to this in the report.

If you are interested I can let you have a small demo file which shows how
imported data, from an Excel worksheet in the demo, can be recast into
properly normalized related tables in Access by means of 'append' and
'update' queries. If you'd like a copy mail me at:

kenwsheridan<at>yahoo<dot>co<dot>uk

Ken Sheridan
Stafford, England

 

[Matt M.]

When importing data into a referencing table (i.e. the 'child' table with the
'lookup' field) where the values imported are the text values used in the
referenced ('lookup') table, you need to tackle it in stages:

First create a separate text field in the referenced table and import the
text values into this, not into the long integer foreign key field.

Next create an update query which joins the referencing and referenced
tables on the text fields, not on the numeric keys.  This assumes the text
values in the referenced table are unique of course, and that the values
imported are an exact match for the values in the referenced table.  Restrict
the query to the rows where the foreign key numeric field from the
referencing table IS NULL; this is not essential but there's no point
updating values to themselves.  In the query update the foreign key numeric
column in the referencing table to the value of the primary key column of the
referenced table.

If you are going to be importing more data in the future you can leave the
extra text field in the referencing table for future use.  If not then you
can delete it as its done its job.

BTW the conventional wisdom is that the 'lookup wizard' so-called data type
(its nothing of the sort of course, the column is a long integer number data
type) should be avoided like the plague.  See the following link for reasons
why:

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

In raw datasheet view of a table its best left so you see the real
underlying values.  You can see the referenced text values via a combo box in
a form (where data should be entered and edited, not in datasheet view), and
in a report its merely a question of joining the tables in a query and
returning the text column from the referenced table, binding a text box
control to this in the report.

If you are interested I can let you have a small demo file which shows how
imported data, from an Excel worksheet in the demo, can be recast into
properly normalized related tables in Access by means of 'append' and
'update' queries.  If you'd like a copy mail me at:

kenwsheridan<at>yahoo<dot>co<dot>uk

Ken Sheridan
Stafford, England








- Show quoted text -

Great. Thank you!