Question: I have a number of Excel spreadsheets and Access tables with similar data that I would like to merge into one large database to make queries and reports. What is the best way to do this?
Answer: This is a more complex question to give a quick and simple answer without knowing what the data looks like. Basically, if the data has the same headings (fields) and field types, it should import well into an Access table. In looking and this user’s data, even though the headings were similar, they were not exact the same. They needed to modify the headings to be sure they were the same in all tables involved. Then the Excel files can import into the master Access table. If the old tables are in Access, you create an Append action query and select the old table with the fields you wish to append to the new master table. You may want to unselect primary keys in case ID fields are used in individual tables. You can then use a Query Wizard to search for duplicates or unmatched data in a table. In many cases, however, more one-on-one help is required to assist users when moving data from one application to another, especially if it involves several data sources.