Importing from Excel to a lookup field in access

[Ken]

I'm stuck. I'm trying to import an excel spreadsheet that has a whole mess
of new records for one of my tables.

Most of the fields import just fine, the one that does not work is a
particular field that has a lookup value in Access. What do I need to enter
in the excel spreadsheet that will allow Access to properly import a field
that has "droplist" values?

 

[Ken]

But, as far as I know, there isn't another table. When I entered the values
in the "lookup wizard" when I created the primary table, I selected "I will
enter the values I want" instead of using another table. The reason I did
that is that there are only four values, and will always and only be one of
those four. I figured it would be easier to use a small dropbox than to
retype the value every time.

So, is there no way to import data when I have done such a thing?

 

[Ken]

Wait wait.

Let me clarify though. I don't want to add NEW values to the field. I want
to import new RECORDS to the table. The new records need to have one of the
existing values in the lookup field. The question is, what do I need to type
in the corresponding column of the excel spreadsheet. The values in the
lookup field are simple:

"Orlando"
"Tampa"
"Jacksonville"
"DeLand"

However, when I enter one of those same values into a colum in the excel
spreadsheet and then import new records, the database tells me that the data
type does not match. Similarly, I have used numbers instead, such as a "1"
to represent Orlando and a "3" for Jacksonville. Access tells me that the
value is "Nonparcable" if I do that.

So it's not that I want new values in the lookup field. I want to import
new records to the table, and I want the new record to adopt one of the
existing four choices. I simply can't figure out what to put in the Excel
spreadsheet to make this happen.

 

[Dale Fye]

Ken,

I'm with Chris, create another table for these lookup values. This is so
much easier to maintain than a value list in the tables data structure.

Unfortunately, Microsoft has decided that they are smarter than we
developers are, and have not provided us a way to create an import
specification for importing data from Excel. Because of this, I will
frequently create a "import" table that mirrors the structure of the table
that I actually want to import the data into but in which I modify some of
the field properties (to allow data that is not in the format I expect -
nulls, text in numeric fields, ...).

Then, you can check the data for inconsistencies (text in a numeric field,
or something like that, NULLs in a field that requires data, etc). Finally,
you can create a query that uses the import table and is linked to your
"Lookup" table, and insert the right value into your main table. This gives
you the flexibility to "transform" the data as you see fit (similar to SQL
Servers Data transformation service).

Finally, after you have transformed the data and moved it into the
destination table, you delete the records from the "import" table.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.