A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Dropdown that automatically adds new tables, button to empty data



 
 
Thread Tools Display Modes
  #1  
Old August 29th, 2008, 12:08 AM posted to microsoft.public.access.forms
Francesco
external usenet poster
 
Posts: 18
Default Dropdown that automatically adds new tables, button to empty data

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
  #2  
Old August 29th, 2008, 12:32 AM posted to microsoft.public.access.forms
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Dropdown that automatically adds new tables, button to empty data

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



  #3  
Old August 29th, 2008, 02:14 AM posted to microsoft.public.access.forms
Francesco
external usenet poster
 
Posts: 18
Default Dropdown that automatically adds new tables, button to empty d

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




  #4  
Old August 29th, 2008, 04:49 AM posted to microsoft.public.access.forms
tina
external usenet poster
 
Posts: 1,997
Default Dropdown that automatically adds new tables, button to empty d

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






  #5  
Old August 30th, 2008, 12:16 AM posted to microsoft.public.access.forms
Francesco
external usenet poster
 
Posts: 18
Default Dropdown that automatically adds new tables, button to empty d

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






  #6  
Old August 30th, 2008, 02:53 AM posted to microsoft.public.access.forms
tina
external usenet poster
 
Posts: 1,997
Default Dropdown that automatically adds new tables, button to empty d

comments inline.


"francesco" wrote in message
...
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


"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








 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:02 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.