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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|