Parameter Make Table Query-passing the table name - HELP!

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

deanm5

Thread Starter
Joined
Apr 12, 2007
Messages
4
hello,
I have created a make table via a parameter query. The parameters are defined correctly and I am prompted each time to enter them. the DeptNo does work as my query is filtered based on a value entered, but when I enter the tablename parameter, the make table query always saves the table as @tablename, and not any text i enter for the @tablename like "mynewtable" or something. Here is the SQL code.
Any ideas? Ignore the complexity of the query, my main concern is being able to loop through some VBA code and continually change the tablename each time (along with the DeptNo. I think this will save me time vs creating a new sql statement each time and doing a docmd.runsql ...
Thanks.

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]));
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Why not just use a form field for the Table name rather than a Parameter?
 

deanm5

Thread Starter
Joined
Apr 12, 2007
Messages
4
Because, I am running this query through code and loop through a recordset of 129 records. Each record is a dept number that feeds the query and with the results, produces a table that I need to save. If I do a form field, then I have to enter the dept numbers by hand. Running this and building a new query each time in a string and executing (docmd.runsql strSQL...) for the 129 records takes about 63 seconds. I just figured a parameterized query with just using the deptno and table name would be faster for access to process than building a new query each time.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Well if the Dept Number is in a Table field and you want to use that then can't you assign it to a String Variable to include in the SQL String as the Table name?
Can I ask why you need to create a table for each record?
 

deanm5

Thread Starter
Joined
Apr 12, 2007
Messages
4
This query pulls data from several different tables based only on the dept number [DeptNo]. I export the results of the query to a separate table for each record so that I can then spin through the list of tables and export the data to a excel template file that is pre-formatted. Each of these dept tables if you will, are ported to their own excel files which are sent to the dept managers to view/adjust their budgets and then sent back to us. I suppose I could not include the dept filter on the initial query and just make one massive table that has a deptno field in it. Then in the next routine, spin thorugh that and open up a recordset with the deptno filter setup so that I just get those records for each particular deptno, then do a dump of the recordset to the spreadsheet as follows: objSht.Range("A2").CopyFromRecordset rstDRT . Is the latter the preferred method in your eyes?
Dean
 

OBP

Joined
Mar 8, 2005
Messages
19,895
No I think I would like to just run through the tables once and create the query values as you do now but not put them in tables just populate the Excel Sheets directly.
But I have no idea of the Table Data Structure or the Spreadsheet Structure.
Can you post an example of the data that goes in to the tables and the spreadsheet so that I can see if it is feasible?

What I don't understand is why the Managers aren't working in the Access database direct, isn't it networked?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
If this is run from a Form then it is quite possible to populate a field on the form with the Dept No., run a query using that as the Criteria, Export the data to Excel.
Move to the next record and repeat.
I will have to go soon, the Wife will wan to use the Computer. If you can post me some data to play with along with how you work the Spreadsheet names I will have a look and post back tomorrow. (I am in the UK)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Staff online

Top