Tina... such a breath of fresh air! Thanks so much!

It works great. I just added a couple of things...
1) I assigned a default value "Date$()" to the DateAdded field. The current
date is created when new records are added to the table (instead of adding
the date manually).
2) I created a new query to select "DISTINCT" on the DateAdded field. The
dropdown on Form1 has this new query as its ControlSource. Reason for this,
to have listed unique dates instead of thousands.

Thank you very very much for your help and for being so kind.

Francesco


"tina" wrote:

that's easy enough. put all the data in *one* table, and add a field for
DateAdded. every week, when you dump the new records into the table, update
the DateAdded field for those new records, as well.

in your query, set criteria on the DateAdded field. i suggest a form which
i'll call Form1, for the users, with a listbox (or combobox) of available
dates, which i'll call lstDate. so your query criteria would be

[Forms]![Form1]![lstDate]

in query Design view, *also* go to the menu and select Query |
Parameters..., enter the same parameter shown above, in the Parameter
column, and choose Date/Time in the Data Type column.

add a command button to the form to open/print the report. now you never
have to change the query's SQL again. just dump the new data into the table
each week, adding the DateAdded value to the new records when you do, notify
your users, and sit back and relax. the user opens the form, chooses a date
from the listbox, click the command button, and they're done.

hth


"francesco" wrote in message
...
Jeff,

Thank you for your reply.
I will try to give a better description of my users' process and my need
for
such a request. If there's a different/better way to do it, I will
appreciate
any advise.

We receive new data on a weekly basis. The source is a fixed file, always
same fields, with new data. I convert this file to an Access table using
the
DataJunction software. There is the reason why we have a lot of tables - a
new one is added every week.
Tables older than 1 year will be deleted.

I already have a Query (Query1) and a report (Report1). The report never
changes; its source record is Query1.

I manually edit the SQL in the query by replacing the existing table name
with the newly created table.
I then notify my users that the query is ready and they can run the
report.
That is the reason for wanting to "automate" the process, once I create
the
new table. Users to be able to just select the table from dropdown and run
the report.

I hope this additional information helps. If there's a different approach
to
it, I would greatly appreciate your advise.

Thank you,

Francesco


"Jeff Boyce" wrote:

Francesco

You've described a lot about "how" you are trying to do something, but
not
much about what that something is.

If you have "many tables, all with the same structure", your database's
structure is not well suited for what Access can do.

Having many spreadsheets in a workbook, all with the same structure is
how
you'd probably try to categorize data using a spreadsheet.

Access is a relational database, though, not a spreadsheet on steroids.
You
may have to UNLEARN some of what you know how to do using spreadsheets
if
you want to get the best use of Access' relationally-oriented
features/functions.

If you'll post back a description of what kind of data you are keeping
in
those "many tables", and an example of the different categories the
"many
tables" are being used to represent, folks here may be able to offer a
more-normalized design.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"francesco" wrote in message
...
My database has many tables, all with the same structure, but
different
data.
One master table "Table1", one master query "Query1", one master
report
"Report1".
Source record for Query1 is Table1. Source record for Report1 is
Query1.

I am trying to create a form with a dropdown field and a button that
will
do
the following:
Every time a new table is created, it will be automatically added to
the
dropdown.

Selecting a table from the dropdown (ex: tblData1), data from tblData1
will
be copied to master Table1.

The button on the form will print Report1.

Upon closing Report1, data from master Table1 will be deleted. (Or, do
I
need a second button to empty Table1?)

Is this possible? How can I do it?

Your time and help is very much appreciated. Thank you!

Francesco