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