Make Table Query_Generate unique table name each run

[Guest]

I've created a make-table query that produces a table named "tblStocks". The
SQL is as follows:

SELECT qryUnionStocks.CompanyName, qryUnionStocks.Profitability,
qryUnionStocks.BusinessRisk, qryUnionStocks.Industry,
qryUnionStocks.MarketCap, qryUnionStocks.FairValueEstimate,
qryUnionStocks.Date INTO tblStocks
FROM qryUnionStocks;

I run the make-table query at least once each day in order to produce a
unique table (a snapshot of the data) associated with the date and time that
the query was run.

The above query writes over the previous file of the same name each time I
run it. So I'm having to manually rename each file each time I run the query.


How can I write a make-table query that will produce a uniquely named table
each time I run the query? A table that can be easily associated with a
specific date and time. The following is just an example, the naming
convention can be different.

tbleStocks07252005094555 (run on 07/25/2005 at 09:45:55 GMT)
tbleStocks07262005150511 (run on 07/26/2005 at 15:05:11 GMT)

Thank you for your time and expertise.

JD

 

[John Vinson]

How can I write a make-table query that will produce a uniquely named table
each time I run the query?

You cannot.

You'll need to write VBA code to construct the SQL string for the
make-table query.

Actually I'd VERY strongly suggest that storing data in tablenames is
A Very Bad Idea and that you should instead consider a different
approach! You could have a table named Archive, say; run an Append
query appending into it the data you're now using for the MakeTable,
with an additional field DateArchived, into which you append either
Date() for today's date, or Now() for the date and time when the query
was run. A single table will take up less disk than your multiple
tables, and it will be easy to use a date or date-range criterion to
pull out the records for any chosen date.

John W. Vinson[MVP]

 

[Guest]

Eureka!
Thanks John, your a genius
JD

You cannot.

You'll need to write VBA code to construct the SQL string for the
make-table query.

Actually I'd VERY strongly suggest that storing data in tablenames is
A Very Bad Idea and that you should instead consider a different
approach! You could have a table named Archive, say; run an Append
query appending into it the data you're now using for the MakeTable,
with an additional field DateArchived, into which you append either
Date() for today's date, or Now() for the date and time when the query
was run. A single table will take up less disk than your multiple
tables, and it will be easy to use a date or date-range criterion to
pull out the records for any chosen date.

John W. Vinson[MVP]

 

[John Vinson]

Eureka!
Thanks John, your a genius

Not quite, according to the Sanford-Binet scale... but I've had a lot
of experience with relational database design! <g>

Thanks. Glad it steered you in the right direction.

John W. Vinson[MVP]