Issues template tables designed with look-up fields, how to import new data?

[Aich]

I inherited an existing db built from the Issues template. Tables are linked by various primary & foreign keys, (numeric), but are displayed as text, utilizing look-up function in the table design. For example, Airport code is 18, but displays as HNL.

In order to bring this database current, I need to append 100's of historical records from Excel. I have successfully appended records & tables in Access before, but am having trouble now, since my inputs are often text, whereas the destination fields are numeric, due to this pre-existing look-up function.

I have researched and read many threads. I have learned about "the evils of lookup fields in tables" and wonder why Access would create such a template. Is it because it's the only way to be web (Sharepoint) compatible? If so, should I deconstruct this table, removing these lookups?

My short-term question is I need to figure out how to import all this data into my tables, without re-mapping every single field to figure out what number correlates to my text.
My long-term question is, how to best go about redesigning and rebuilding this so that the database is easy to work with, not just for me but others who may enter data via forms.



Thank you! H

[June7]

Microsoft might not have created that database. I think many of these templates are built by other parties and submitted to Microsoft for distribution. Web compatibility might be justification for the lookups.

So the Excel file has the text description instead of the numeric ID? Are the text descriptors exactly the same as in the Access table? Can you set a link to the Excel sheet? Build a query that joins the lookup table to the linked excel sheet on the descriptor fields. Now an INSERT SELECT action will have the numeric ID available with each record.

[Aich]

Yes, the Excel file text is exactly the same as the definitions in Access. I'll play around with the link as you suggested and let you know how it goes.

Thanks for your reply!