Dropdown that automatically adds new tables, button to empty data

[francesco]

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

 

[Jeff Boyce]

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]

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

 

[tina]

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]

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

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

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

 

[tina]

comments inline.

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).

very good.

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.

also very good.

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

you're welcome, though from the above i'd say you've got a good handle on
how to manipulate data in Access, just needed a nudge in the right
direction.

Francesco

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

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


:

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


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