Make table query where INTO {tablename} is actually a parameter; doesn't work

[dgmueller]

I thought someone could help me with this small problem. I have two
parameters in my make table and one of them is the table name for the
make table.
When I run the query, it filters based on the [DeptNo] fine, but when
correctly prompted to enter the @TableName, Ex "MyTable", it does
prompt me but then saves the new table under a table name as
@Tablename rather then "MyTable" I want to eventually put this in
VBA and loop through a recordset calling this query and saving results
under different names based on the information in the recordset I am
looping through. I figure, this can be done (using the tablename as a
parameter) or not, and if so, I am just using the wrong syntax or
something.

Any ideas?

PARAMETERS DeptNo Text ( 255 ), [@TableName] Text ( 255 );
SELECT AUMaster.Lawson, ChgMstr.DEPT, ChgMstr.[CHG CD], ChgMstr.
[REVENUE DESCRIPTION], [Prior and Curr Year Revenue - Qty].Price,
ChargeCodeProfile.[Count per Charge] AS Weight, [Prior and Curr Year
Revenue - Qty].[2006 Projected Quantity], [Prior and Curr Year Revenue
- Qty].[2006 Projected Revenue], [Prior and Curr Year Revenue - Qty].
[2007 Projected Quantity], [Prior and Curr Year Revenue - Qty].[2007
Projected Revenue] INTO [@TableName]
FROM ((ChgMstr LEFT JOIN [Prior and Curr Year Revenue - Qty] ON
ChgMstr.[CHG CD] = [Prior and Curr Year Revenue - Qty].ChargeCode)
LEFT JOIN ChargeCodeProfile ON ChgMstr.[CHG CD] = ChargeCodeProfile.
[CHG CD]) LEFT JOIN AUMaster ON ChgMstr.DEPT = AUMaster.Affinity
WHERE (((ChgMstr.DEPT)=[DeptNo]));

 

[Tom Ellison]

Dear DG:

Such facilities are not built into the SQL query language. However, you can
always consturct the text of a query using string concatenation and other
string functions. Be sure to put square brackets around any "variable" info
for a table or column name which might contain spaces of other
non-alphanumeric values. This is certainly how I do this.

You must be competent to program VBA. I do not know of any alternative.

Tom Ellison
Microsoft Access MVP

 

[Michel Walsh]

As Tom said, a parameter cannot be a table or a column. It can only be use
where an expression can be used. In fact, if the table (or a field) was a
parameter, NO pre-compilation of the query would be done, no verification
that the columns exist, no query plan elaborated (is there an index or not),
and so on.

*Maybe* you have too many tables (such as one table per month)? Can't you
merge these tables into just one, and thus, you won't have to give the table
name as a parameter, but one of the field could then be 'filtered' with a
parameter, newTable.MonthID = @whatMonth, to continue with the example.


Hoping it may help,
Vanderghast, Access MVP