Importing into Table with Lookup Fields

[Fstrategic]

I am trying to import data from an Excel file into a table that has a numb of lookup fields that are linked to other tables. The incoming data that corresponds to the lookup fields has the text value of the fields (e.g., company name , user name) and not the ID of the record that is stored in the target table. As a result the import process fails since Access is looking for the numeric value that points to the related record and not the text that is looked up.

Anyone has any ideas as to how to make this work?

[ssanfu]

I am trying to import data from an Excel file into a table that has a numb of lookup fields that are linked to other tables. The incoming data that corresponds to the lookup fields has the text value of the fields (e.g., company name , user name) and not the ID of the record that is stored in the target table. As a result the import process fails since Access is looking for the numeric value that points to the related record and not the text that is looked up.

Anyone has any ideas as to how to make this work?

The best idea is Not to use "lookup fields in tables".

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

To import the data from Excel, you would have to write a lot of custom code to import the Excel file. Good luck...

[Fstrategic]

That is not a practical solution at this point. It is too late to change the whole structure of the database.

[RuralGuy]

As Steve said: "you would have to write a lot of custom code to import the Excel file". Import from Excel to a new temporary table and then do the Lookup's for the values you need and add them to your temp table and then when you have everything right, append just the fields you need to the original table. As Steve said: "Good Luck".

[orange]

I agree with ssanfu and there are several posts recommending the same.

But I must ask, How can it be too late for proper/practical design?

Did you do any testing of the import approach during development? Did you have some positive tests? Some test feedback before moving forward?

You can certainly have Lookup tables as separate tables -- the traditional approach to lookups. Is that not an option?

[bigroo]

Hi Fstrategic,

I am chiming in late on this, but I agree with the others that replied, lookup tables are not recommended, just use separte tables to hold your information to feed your combo fields.

Now, back to your original question, please review the attached database. I think it addresses the same situation you are trying to solve, correct me if I am wrong.

This is my weather DB, I import a txt file once a month by using queries to add data to the tables in the correct order. As you can see I have data from 1997 to current year and this was all done just using the queires to bring the data in the correct order. Open the DB and open the macro in desgin view and review the steps I created, maybe this will work for you??

Thanks

[ssanfu]

I am chiming in late on this, but I agree with the others that replied, lookup tables are not recommended, just use separte tables to hold your information to feed your combo fields.

I think (hope) you meant Lookup FIELDS.

This is why everyone is saying not to use lookup fields.:
http://access.mvps.org/access/lookupfields.htm

Lookup TABLES are good!

[bigroo]

Hi ssanfu,

I think (hope) you meant Lookup FIELDS.

This is why everyone is saying not to use lookup fields.:
http://access.mvps.org/access/lookupfields.htm

Lookup TABLES are good!

Oops, yes lookup fields are not good, correct, thats what I meant to say!!
Sorry for the confusion!!
Thanks Ssanfu for the correction!!