MS-Access: How do I go about building a database with inconsistent data sources? I'm working on building a database to aggregate various data sources and feed a set of Tableau dashboards.

The data is all employee data, the problem is, I only have one file of seven with a unique Employee ID, and the format of Employee_Name is inconsistent across about half the files, so I can't join on Employee_Name (capitalization, commas, and initials are all inconsistent). Do I need to write macros to create consistency in Employee_Name so I can write the lookups to create the proper primary/foreign key relationships, or is there a more succinct way to do this?

My first choice is to go back to the client, and ask for new data exports with a common Employee_ID field across all of them, but I don't know if or when that's feasible.

I should also note, I already wrote a bunch of code in R to solve this problem and perform the joins, but the client needs a solution that doesn't rely on me running an R script every time they want to update their data.

I'm also all ears if there's a better solution with SQLite or something like that.
You are building a basic BI/DW environment. You need to get:


Cleaner data, as it is generally more efficient to fix the problem upstream. 
A real database. I would not use Access for anything important like that. It gets corrupt and can only grow to a certain size. Ask the client if they can host a DB to hold this data or can spin something up in the cloud.
Use some form of tool like ETL to extract and load the data into your database.


If you want to go really cheap, Microsoft's Tableau competitor (Power BI) has basic ETL functionality built into it, can source directly from flat files, can call R scripts for clean-up before presenting data, and can store a limited amount of data internally.