Make Table Query

[SharonBerg]

I want to add a suffix to the table name in a make table
query. In a global string variable: gsEvent is the value
picnic

Select table1.Event, table1.year into newtable
from table1

I want the new table name to be newtable_picnic.

I know how to do this when storing the query in a variable:
strsql = "select table1.event, table1.year into newtable_"
& "'" & gsEvent & "'" & " from table1"

 

[Jeff Boyce]

Sharon

Are you attempting to create "meaningful" table names (for example, a
different table for every month ... tblJanuary, tblFebruary, ...)? I
understand you aren't using months, but does the same purpose (why you are
doing this, not how) apply?

More info, please...

Jeff Boyce
<Access MVP>

 

[Jeff Boyce]

Sharon

Then I'm afraid I have some bad news... you probably DON'T want to do that.

Access does not restrict you from doing so. However, you will find,
downstream, many headaches from setting up data in tables that have
identical table structures and categorize along a single dimension (in my
example, month-of-the-year) and reflect the differences in the table names
(my example again, "month1", "month2", ..., or "event1", "event2", ...).

If you will provide a bit more description of your data (more toward what it
is than how you've structured it), the 'group may be able to offer
alternatives.

Good luck

Jeff Boyce
<Access MVP>

 

[SharonBerg]

This is for 3 temporary tables that are deleted at the end
of the month, so I don't see any "downstream" headaches at
the moment.

You mentioned that Access does not restrict from naming
tables this way? What is the sytax for a SQL statement,
in the SQL view of a make table query, listed in the Query
tab of the Microsoft Access database window, that allows
for the creation of "meaningful" table name?

 

[Jeff Boyce]

Sharon

I'm not aware of a way to use the SQL window of the query design to create
such meaningful tablenames. I suspect this is only do-able in code.

Again, if you provide a bit more info about what (not how) you are trying to
do, and with what data, newsgroup readers may be able to offer alternative
approaches.

Good luck

Jeff Boyce
<Access MVP>

 

[Sharon Berg]

Thanks, Jeff for your time in trying to help me. I have
given up on that approach and have instead created the
temp tables with the names I want and use an append query
to populate the table. There are more steps because I
have to first delete the records from the table so not to
have duplicates, then run the append. But, I really want
to avoid code so that is what I did. When I have the time
to think of a better approach I will probably change what
I am doing.

Thanks again,
Sharon