Variable Table name

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello! I am looking to create an SQL statement, or anything else, that would
allow me to create tables with a variable in their name. For example, I want
to make a table named tbl092005 where the date (092005) would change
tomorrow, thus creating a new table. Does anyone know how to do this?
Thanks.
 
This is possible, but why not just include a date in the record and store
them all in one table? Then you can use queries or filters to include
specific date(s) in your forms or reports.
 
The is for a month-to-date inventory transaction/balance analysis. Thus, I
need to have all the records, as they stand each day of the month, in a
separate table. In effect, you could think of as a system of internal
auditing to prove that transactions are not being back-dated, etc. I have a
new batch of data each day... but for previous days, the data would be
duplicate. It might be a little confusing to understand, sorry.

I would greatly appreciate any assistance you can provide.
 
I think you misunderstood what Rick B was saying. The table represents the
inventory and the changes in same are captured by the rows of data in the
table. For start of month to date you would set criteria on your date fields
to reflect this month to today, something along the lines of

BETWEEN DateSerial (year(date()), month(date()), 1) and Date()

But all of the data is stored in the one table. You use the queries to
manipulate/display the set of data that you want.

Putting the date in the name of the table means that you are using the table
as part of the data. This is a *NOT GOOD THING* which can lead to all kinds
of problems down the road.

If I might ask: If your table for today is named 'tbl09202005', does it
contain just the inventory information for today? How does this accomplish
the 'month-to-date' aspect of the system? What happens to the earlier
tables? To track the changes from day-to-day it seems either you've got to
have some very convoluted queries or you're duplicating data, maybe
indirectly.
 
HI,



Assume a moment you have ONE table will all the data, for many many
different dates.


Assume you have a query, based on that table, with a criteria limiting the
record to a supplied date (as parameter).


Assume you USE the later query in place you have that table with a "variable
name". A query can be used where a table could, generally, and here, it
simplifies your life. So having all the different dates into ONE table is,
indeed, the right solution... just also add a query that "filters" that data
for a given date, as parameter.


Hoping it may help,
Vanderghast, Access MVP
 
I understand. I will try some of the things that have been suggested.

Chaim, to answer your question, I am looking as a daily activity "run" that
captures all MTD info. This info would then be compared to the following day
in an effort to catch changes that have been made to prior information
already posted. My idea was to have a table that would represent the
download for that day (MTD, as I had said). The data would then be stored in
that table until the end of the month. Does that help explain?

I believe that I can alter my approach to include some of the ideas you have
given. Thanks for the help. But, feel free to add more comments. This is a
work in progress. Thanks!
 
Back
Top