Combine XLSX into a DB for searching

[Grafixx01]

So here is my question. I have numerous MS Excel files, some with multiple sheets in them, but would like to combine them into a single, searchable database. The issue is that each of these spreadsheets is from a different source where they have different column headers, yet they contain similar information in some of the columns.



Some fields examples are a persons name in a single field, separate last and first name, paid, amount paid, etc.

I'm trying to combine the files into a single database to pull together the persons name with all the amounts that they have paid.

Any ideas?

[Gicu]

No easy way to do this, I would suggest you either link or import them in a new Access file where you would create a standardized table (FirstName, LastName, AmountPaid,etc.) to which you append them using customized queries for each sheet to "remap" the fields to your new table. If the data entry is done in Excel you should create a new template based on your final Access table so the field names would match.

Cheers,

[JoeM]

I would be tempted to create an Excel macro to "clean up" all the Excel data first, so it all gets put in the same format/order, and then import those.

[accesstos]

Create a linked table for each worksheet and make your combinations in a single union query, pulling anything you need from each linked table.