Multiple Tables - Single Qry and Report (1 Viewer)

Hello all, I'm hoping someone can tell me if what I'm planning on doing is possible.


Each week I'm going to be pulling an excel file that will be imported into a table in access. The only difference in the files will be the file name and the data contained within. The structure will be the same.


The DB will have a single query and a single report. The report will be run each week, but needs to be run off of the newest table. Is there a way to change what table the query runs off of using a dropdown that populates with the table names so I don't need to go in and manually change what table the query is pointing at?


Hopefully that makes sense.


Thanks in advance for any help.

To better answer your question, it would help to know if you need to keep the previous contents of the table for historical purposes.

To answer the direct question, I can think of two or three ways to accomplish what you asked. But to give you the best answer, it would help to know that you will be doing with old data.

Hi Doc Man,


That is exactly correct. Each table will be kept for historical purposes.

OK, here is what you might do.

First, always import your spreadsheet to a temporary table for which you erase the contents before each import. No name change involved. But with the table being already defined, you don't have to worry about getting an incorrect data type.

Next, massage the data while it is still in the temp table. Clean it up. Do whatever else you want to do with it.

Now, use an INSERT INTO table (fields....,IMPORTDATE) SELECT fields, Date() FROM temptable; - which puts the most recent data into the main table, time-tagged to show when it was entered. (This presumes that you didn't already have the current date as one of the spreadsheet columns.)

Now, to get the most recent data, have a little bit of VBA code to get a DMax of the IMPORTDATE and use that date as a filter for your query and for your report. But to get previous data, you could populate a combo box with the distinct dates (there is a DISTINCT keyword that helps here) and select the date for which you wanted a report. The combo box wizard can help you set that up.

Now, doing it this way keeps the number of tables - and the number of table names - to a minimum. No confusion as to which table 'cause there will only be the temp table and the main table. ALL data would reside in the main table, so the report doesn't have to be overly smart. You can just open it with the filter option, which you can look up for the DoCmd.Open verb, for which you can supply a filter clause. (Which looks like a WHERE clause but you forget the WHERE.)

Then ALL of your data remains available at any time until you decide to archive or purge the oldest stuff. But that of course would be your call.

Thanks, Doc Man. I'll give that a shot. May work to my advantage since I'll have the old data in the table as well, I may be able to use it in other ways if the boss wants/needs it.

Hi Doc Man,


Attempted to use your suggestion, but I seem to be failing miserably. Tried to write the SQL in VB but just doesn't seem to be working (I'm pretty amateur at this stuff). Other thoughts on how this could be done?


Thanks

Scratch that. figured it out.

Sometimes it just takes patience and persistence. Glad you figured it out.