lookup value from Excel table import

[philr]

Access 2003
I have a 2 column Excel speadsheet, no row duplicates that I would like to
use as a lookup table in an Access db. I was able to import the spreadsheet
without problem and named it as one would expect. The first field is a
primary key with no duplicates. The second is text.

When in Table design for the field I selected the Lookup Wizard option and
selected "I want the lookup column to look up the values in a table or
query." I follow the wizard then get the error "Data cannot be retrieved from
the source you have selected. You must select a different table or query to
continue in the wizard."

I would like to create a field on my form where I can enter the keyed
numeric value (actually a text field) and have the value of the second field
be place in a second text box on the form.

Since the db doesn't accept the imported table is there some other way
without rekeying the data in a separate table? (I've tried to copy and paste
the 1000+ records but it didn't work.)

 

[fredg]

On Wed, 18 Jul 2007 07:14:03 -0700, philr wrote:

See interspersed comments below....

Access 2003
I have a 2 column Excel speadsheet, no row duplicates that I would like to
use as a lookup table in an Access db. I was able to import the spreadsheet
without problem and named it as one would expect. The first field is a
primary key with no duplicates. The second is text.

A Combo box should work (on your form) even if you have imported the
data from Excel, so you must be doing something else incorrectly....
or your data is bad.

But why import the data?
Simply link to it.
Get External data + Link
Any additions or changes in the spreadsheet are automatically seen in
Access.

When in Table design for the field I selected the Lookup Wizard option and
selected "I want the lookup column to look up the values in a table or
query." I follow the wizard then get the error "Data cannot be retrieved from
the source you have selected. You must select a different table or query to
continue in the wizard."

*** DO NOT EVER*** use the LookUp Wizard in a Table.

On the form that is used for your data entry or manipulation, add a
combo box using this linked table (or a query based on the linked
table) as it's rowsource. You can use the combo wizard if you like.

I would like to create a field on my form where I can enter the keyed
numeric value (actually a text field) and have the value of the second field
be place in a second text box on the form.

This is commonly what a combo box is used for .... on a FORM, though
usually, it displays column(1) but is bound to column(0).
For example, the combo columns are ClientID and ClientName.
The Combo dropdown shows the Client Name, and when selected, stores
the ClientID in the table.
Set the Combo Control source to the name of the field you wish to
store the selected value in. Make sure the Combo Bound column is the
same datatype as the control source field.
The Combo Wizard will walk you through this.

Since the db doesn't accept the imported table is there some other way
without rekeying the data in a separate table? (I've tried to copy and paste
the 1000+ records but it didn't work.)

Try again...

 

[Jeff Boyce]

Rather than importing the data, could you 'link' to it?

Also, if you are trying to do this in Table design, stop! The "lookup" data
type in Access table definitions is a well-intentioned attempt to make
tables easier to use. However, tables in Access are intended as data
storage containers, and don't have a particularly user-friendly user
interface. That's what forms are for.

If you use a lookup field in an Access table, it stores one value and
displays something else. This can lead to confusion.

To do what it sounds like you are describing in a form, use a combo box that
refers to the lookup table as a RowSource (via a query, preferably). That
way, you wouldn't have to force the user to memorize code numbers, because
they could look up values. What gets stored in the underlying table is the
ID field. What gets displayed in the form is the looked-up value.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[philr]

Thank you both,

The reason I didn't link to the External Excel file was because I'm giving
the .mdb to someone else to enter data and I wasn't sure if I included the
..mdb and the .xls if there relatively linked to each other.

I tried using the Combo Box Wizard in the form but got the same error
message.

"Data cannot be retrieved from the source you have selected. You must select
a different table or query to continue in the wizard."

I also tried the external linking you both recommended. Using the Combo Box
Wizard I selected the "Externally linked" table and stepped through the
options. Unfortunately, I got the exact same error message again.

Thoughts?
philr

When I open the Access table containing the imported information all looks
as expected. Column 1 a code field as primary key and the 2nd column of text
values. All records present.

 

[philr]

I don't know if this will help any.

After I followed your advice to link to the External .xls File I went to
look at the values in the linked table. Quite a few of the records had the
value #Num! in the keyed code field. In Design View it says Text.

The values in the first field are numbers but like SS# always remain the
same. I treat them as text. (They are ICD-9 values used in health care.)

I replaced the #Num! values in the key field (1st column) with the real
values that existed previously and tried the Wizard again but no luck. The
same error message appeared.

Could it be that in Excel I need to format the 1st column field as something
else? Previously, in Excel, they were formatted as "General". I tried
reformatting the 1st column as text but still no luck in the wizard -same
error message. So it doesn't work for General or Text formatting in Excel.

philr

 

[Jeff Boyce]

When I see "#Num" in a field, Access is telling me it can't figure out what
goes there.

If this were mine, I'd go back to the underlying Excel spreadsheet (in
Excel) and investigate. Perhaps the "keyed code field" doesn't hold
consistent data?

Regards

Jeff Boyce
Microsoft Office/Access MVP