Undefined table query

[Sean]

I am wondering if it is possable to prompt my users for which "table" they
would like to view. Everyday our logs get backed up as the current days date
"04APR08" and they are saved as a different table each day. I would like to
create a query of some sort to prompt the user for "date" refering to the
table they would like to view. Ex: [What is the date of the table you would
like to view?] User input: 04APR08. I would like the table to show as a
report, but would work fine if it is in table view as read only.

 

[John W. Vinson]

I am wondering if it is possable to prompt my users for which "table" they
would like to view. Everyday our logs get backed up as the current days date
"04APR08" and they are saved as a different table each day.

Storing data (a date for example) in a tablename is Bad Design, for exactly
the reason you're experiencing. Is this an external text file that you have
linked as a table? or are you storing each log into an Access Table?

I would like to
create a query of some sort to prompt the user for "date" refering to the
table they would like to view. Ex: [What is the date of the table you would
like to view?] User input: 04APR08. I would like the table to show as a
report, but would work fine if it is in table view as read only.

You'll need to prompt for the date; you can use the Dir() function to
determine the filenames in the folder and some VBA code to construct a combo
box if you want to present the user with a list. Or you can use the InputBox
function to prompt for a filename and construct the SQL of a query based on
the user's input.

 

[sean_schodowski]

I am wondering if it is possable to prompt my users for which "table" they
would like to view. Everyday our logs get backed up as the current days date
"04APR08" and they are saved as a different table each day.

Storing data (a date for example) in a tablename is Bad Design, for exactly
the reason you're experiencing. Is this an external text file that you have
linked as a table? or are you storing each log into an Access Table?

I would like to
create a query of some sort to prompt the user for "date" refering to the
table they would like to view. Ex: [What is the date of the table you would
like to view?] User input: 04APR08. I would like the table to show as a
report, but would work fine if it is in table view as read only.

You'll need to prompt for the date; you can use the Dir() function to
determine the filenames in the folder and some VBA code to construct a combo
box if you want to present the user with a list. Or you can use the InputBox
function to prompt for a filename and construct the SQL of a query based on
the user's input.

Storing data (a date for example) in a tablename is Bad Design, for
exactly

the reason you're experiencing. Is this an external text file that you have
linked as a table? or are you storing each log into an Access Table?

For every day of the year my table data has to be archived. As of
right now it is archived into a new table saved as that day's date.
If this is a "Bad Design" what would you suggest? This wasn't my
original design and have been put in charge of it. I'm in the military
and things just work like this sometimes. Sometimes the daily archived
data needs to be recalled for review and as of right now I have no way
of doing that other than going into the background of access to view
the tables. I have beginner understanding of access and zero VBA or
SQL knowledge. I appreciate your help with this.

 

[John W. Vinson]

For every day of the year my table data has to be archived. As of
right now it is archived into a new table saved as that day's date.
If this is a "Bad Design" what would you suggest?

One big table with a date/time field Logdate storing the date. You can
reconstruct the "all the records for a day" by using a Query selecting just
that day - or a range of days.

Tables too big you say? Nope. You will use much LESS space in your database
with one properly designed table, rather than the overhead of hundreds of
individual tables; you'll also find it much simpler to search the log file as
a whole, or any individual date's file.

This wasn't my
original design and have been put in charge of it. I'm in the military
and things just work like this sometimes. Sometimes the daily archived
data needs to be recalled for review and as of right now I have no way
of doing that other than going into the background of access to view
the tables.

Exactly. You're storing data (the log date) in the name of the table - and
Access (quite properly!!!) does not make that easily searchable. If you have
it in one table, you can find it much more easily.

I have beginner understanding of access and zero VBA or
SQL knowledge. I appreciate your help with this.

Neither VBA nor SQL knowledge is needed. Just add a datefield to the table and
run your appends *into the table*, not into a new table each time.

 

[Sean]

Thank you so much for your help...

One big table with a date/time field Logdate storing the date. You can
reconstruct the "all the records for a day" by using a Query selecting just
that day - or a range of days.

Tables too big you say? Nope. You will use much LESS space in your database
with one properly designed table, rather than the overhead of hundreds of
individual tables; you'll also find it much simpler to search the log file as
a whole, or any individual date's file.


Exactly. You're storing data (the log date) in the name of the table - and
Access (quite properly!!!) does not make that easily searchable. If you have
it in one table, you can find it much more easily.


Neither VBA nor SQL knowledge is needed. Just add a datefield to the table and
run your appends *into the table*, not into a new table each time.