MS-Access: How do I go about building a database with inconsistent data sources?
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.