Here is what I'm thinking. You can store all of your data in a single table with a date of data entry as one field. Build a QUERY that selects "date of data entry" = yesterday (really, DateSub of 1 day from today's date, look up DateSub in Access help.)
OK, once you have that query, it will give you only yesterday's data. (Of course, you can make it give you ANY day's data...). You can export the query as though it were a table. When you do the "Analyze it with Excel" function you can save the result and at that time, give it a name.
Keeping separate tables bars you from doing longer-term analysis. Also, though it will take a while, you are building into your database the absolute certaintly that it will eventually reach one of the system limits - the number of tables you can have in a database.
Keeping a single field with the date inside, you limit yourself to the max size of an Access database in general, which is like 2 Gb. Your historic data resides in the main table. You want any specific dates? They're only a query away.
Learn to step away from the spreadsheet sometimes. It is thinking outside of the box. Or in this case, the flat file.