Selecting a table to use

[me2]

Background: every week my department gets an excel file that needs to be sent out to different people so they can "do their stuff" on their own portion of it. Now that I have successfully created an Access tool to parse out the file to the people who need it, my boss wants more. Now I need to compare this week's file with last week's file and flag what is still there.

Originally I set up my queries to use a generically named table: tblEPReport. But now I have many tables in the database with the naming format of tblEPR2005-mm-dd. I am manually over-writing the tblEPReport with a copy of the most recent tblEPR2005-mm-dd. I have a tblPriorEPR that I manually overwrite with a copy of the prior week’s file.

I’d like to be able to automate this because I some times forget to change every thing. Any suggestions? Can I set up something that will allow me to select which table becomes the tblEPReport and which is the tblPriorEPR.

TIA,

me2

 

[Ziggy]

Why don't you just use a single table? Rather than name the tables with a date, set up your spreadsheet with a date field that is unique for the weekly batch. Then import it to a single table. Then by using queries you can compare the data based on restricting records with criteria.

The only issue here is whether or not you have data that is unique. Meaning can you set one of the fields to not allow duplicates so you can't accidently import the same data twice.

I use an Import Routine that checks Excel linked Table for a date in one of the fields, if it is newer, it will import. If not nothing happens

Explain what you are doing with the data in more detail, the way you are doing it is the same as creating an Excel file and then saving as a file in a folder.

Automation can be added later