 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2005Make Table Name Parm
I have a Make Table query that is used to subset records from one master table to 40 smaller ones. The problem is that I do not want to have to make 40 copies of this query to get 40 different table names. How can I make the output table name input as a variable. I want to supply the new table name when I run the make table query. Thanks  SignatureG. Jay Myatt, Jr. IT Specialist Cook County Clerks Office Chicago, ILL 312-603-1123 Phone 312-603-0982 Fax Create a mudule with this function Function Create_Table(strTableName) Dim strSQL as string strSQL="SELECT MyTable.* INTO " & strTableName & " FROM Client_tab " strSQL=strSQL & "Where MyField='MyValue'" ---> replace it with your filter docmd.runsql strSQL end Function You can copy your SQL statement from your original query and paste it for strSQL, the only important change will be the location of strTableName, every time you call this function you can supply the new table name, Signaturejl5000 <a href="http://joshdev.com"></a> > I have a Make Table query that is used to subset records from one master > table to 40 smaller ones. The problem is that I do not want to have to make [quoted text clipped - 3 lines] > > Thanks Ok Thanks I'll give it a try... SignatureG. Jay Myatt, Jr. IT Specialist Cook County Clerks Office Chicago, ILL 312-603-1123 Phone 312-603-0982 Fax > Create a mudule with this function > [quoted text clipped - 18 lines] > > > > Thanks I am still new to this .. do I replace each strSQL= with a line of the the SQL statement generated by my origianl query or do I place it after where your arrows are in the example... ? SignatureG. Jay Myatt, Jr. IT Specialist Cook County Clerks Office Chicago, ILL 312-603-1123 Phone 312-603-0982 Fax > Create a mudule with this function > [quoted text clipped - 18 lines] > > > > Thanks jl: I have created function as you suggested.. syntatically it is ok.. but i cannot get it to run in either a query or a macro. how can I call this so that it runs? SignatureG. Jay Myatt, Jr. IT Specialist Cook County Clerks Office Chicago, ILL 312-603-1123 Phone 312-603-0982 Fax > Create a mudule with this function > [quoted text clipped - 18 lines] > > > > Thanks >I have a Make Table query that is used to subset records from one master >table to 40 smaller ones. The problem is that I do not want to have to make [quoted text clipped - 3 lines] > >Thanks Step back a bit. Storing your data redundantly in 41 tables would be a possible solution to a really, really serious performance problem; but it would be a solution that I would be dragged kicking and screaming before I'd do it. If you want to subset records, consider putting an index on the field or fields which define the subsets (so the queries will be more efficient) and use a paramter Query (just one query!) to extract the subsets. You can edit your tables - causing the data in your main table to be WRONG and inconsistant; you can edit data in the Query, which edits the main table and maintains consistancy. You can base Reports on your tables - or on your queries. You can sort your tables. You can sort the query too. You can apply additional criteria to the tables. Same with the query. You can export your tables. You can export the queries. Do you have any GOOD reason to do this? If so I'd be very curious to hear it! John W. Vinson[MVP] John: Yes the reason I have to do this is to create 48 different output tables. The main table is all of the election results for the county. The query is a maketable query that creates a subset of results for each township in the county. These smaller tables (in .dbf format) are then useable (in Excel and Word) by various public officials and political organizations that have little to no programming skills. Hence all the work on my end to make it simple for them. Data is already coded by township but the problem is that Access stores the name of the table in the make table query and it needs to be unique for each subsetted file. I don't want to have to create 48 different queries when I can supply the name of the output file as well as select the township code that I want. SignatureG. Jay Myatt, Jr. IT Specialist Cook County Clerks Office Chicago, ILL 312-603-1123 Phone 312-603-0982 Fax > >I have a Make Table query that is used to subset records from one master > >table to 40 smaller ones. The problem is that I do not want to have to make [quoted text clipped - 28 lines] > > John W. Vinson[MVP] >John: >Yes the reason I have to do this is to create 48 different output tables. [quoted text clipped - 9 lines] >can supply the name of the output file as well as select the township code >that I want. It is NOT NECESSARY TO CREATE 48 TABLES to do this. You can export to Excel from a Select Query just as easily as from a table - and it's perfectly easy to include the township code in the query. This can be a single parameter query. John W. Vinson[MVP] John: the problem is not in creating the output formats.. (our standard is *.dbf) but in naming the output files. You have to name the file when you create the query. I want one query that will allow me to input the selection criteria and the name of the output file. SignatureG. Jay Myatt, Jr. IT Specialist Cook County Clerks Office Chicago, ILL 312-603-1123 Phone 312-603-0982 Fax > >John: > >Yes the reason I have to do this is to create 48 different output tables. [quoted text clipped - 17 lines] > > John W. Vinson[MVP] >John: >the problem is not in creating the output formats.. (our standard is *.dbf) >but in naming the output files. You have to name the file when you create the >query. I want one query that will allow me to input the selection criteria >and the name of the output file. AFAIK you'll need to use VBA code to do this, since a Query *BY ITSELF* knows nothing about Excel or exporting. The query (or a table for that matter) is simply a recordset object, which can be exported - but the name of the exported object must be supplied from outside the query. I'm confused though; you say you're exporting the data to .dbf (dBase) files in order to use them in Excel. Why not cut out the middleman? Excel can use DAO or ODBC to connect directly to an Access query; the data can be stored in Access and used in Excel, without the added step of exporting to dBase or to an Access native table. What you'll need to do, if you are committed to this approach, is to create a flexible VBA function which can receive the desired .dbf file name (and perhaps the criteria to select the records) as paramters, which would then use the TransferDatabase method to export the query to .dbf, or (perhaps better) the TransferSpreadsheet method to export directly to an Excel workbook. See the VBA help for these two methods for examples, and post back if you need more help! John W. Vinson[MVP] John: we create the 48 subset files for each township and export them to .dbf format for each township. When 'clients' request voter registration records we then burn the .dbf files along with others to a cdrom and provide them with the data. Since .dbf is a common denominator file structure we do not have to worry about what software the 'clients' are using on the other end. They are able to import it into whatever they want... including excel or back into access. All I want to do is without writting 48 different queries break the large table I created from the comma delimited file into 48 different tables (one for each towhship) so that I can then export them into 48 different .dbf files that can be given to our 'clients', who may or maynot have (most of the time don't) any comfortablity with the magic box. [Most of the time have to walk them through finding the files on the CD-ROM 8-) ] I have this code in a module but have not figured out how to get it to run. Function Create_Table(strTableName As String, strTWS As String) Dim strSQL As String strSQL = "SELECT Sos0205_M.ID, Sos0205_M.CONG, Sos0205_M.LEG, Sos0205_M.REP, Sos0205_M.TOWNSHIP," strSQL = strSQL & " Sos0205_M.WARD, Sos0205_M.PCT , Sos0205_M.RD_MM, Sos0205_M.RD_DD," strSQL = strSQL & " Sos0205_M.RD_YY, Sos0205_M.LAST, [first] & "" "" & [suf] AS FIRST_NAME," strSQL = strSQL & " Sos0205_M.ADDRESS, Sos0205_M.CITY, Sos0205_M.ZIP, Sos0205_M.SEX, " strSQL = strSQL & "Sos0205_M.BD_MM, Sos0205_M.BD_DD, [bd_cc] & [bd_yy] AS BD_YR, " strSQL = strSQL & "Sos0205_M.PHYSIMP, Sos0205_M.feb05, Sos0205_M.mar04, Sos0205_M.nov04, " strSQL = strSQL & "Sos0205_M.feb03, Sos0205_M.apr03, Sos0205_M.mar02, Sos0205_M.nov02, " strSQL = strSQL & "Sos0205_M.feb01, Sos0205_M.apr01 " strSQL = strSQL & "INTO " & strTableName & " FROM Sos0205 " strSQL = strSQL & "WHERE Sos0205_M.TOWNSHIP = " & strTWS strSQL = strSQL & "ORDER BY Sos0205_M.PCT, Sos0205_M.LAST, Sos0205_M.ADDRESS, [first] & " ' '" & [suf]; " DoCmd.RunSQL strSQL End Function I called it with this query code... SELECT Create_Table([«strTableName»],[«strTWS»]) AS Expr1 FROM Sos0205_M; but get error message that debug indicates my DoCmd.RunSQL with strSQL won't work. so close yet so far.... SignatureG. Jay Myatt, Jr. IT Specialist Cook County Clerks Office Chicago, ILL 312-603-1123 Phone 312-603-0982 Fax > >John: > >the problem is not in creating the output formats.. (our standard is *.dbf) [quoted text clipped - 23 lines] > > John W. Vinson[MVP] >I called it with this query code... > [quoted text clipped - 3 lines] >but get error message that debug indicates my DoCmd.RunSQL with strSQL won't >work. so close yet so far.... The function looks like it should work - but I would not try running it *from a Query*. Can you perhaps put two unbound textboxes on a Form for strTableName and strTWS, and run the function from a command button on the form? Private Sub cmdRunQuery_Click() Call CreateTable(Me!txtStrTableName, Me!txtStrTWS) End Sub It may be valuable to open the function in the VBA editor and put a "breakpoint" in it before you start defining strSQL (click the mouse in the grey bar to the left of the code window, a brown dot will mark the breakpoint). Run the function; the code will stop at that point, and you can hit F8 (or use the menu Debug options) to step through the code line by line. See if strSQL actually contains what you expect. John W. Vinson[MVP] John: Making progress :-) I created form as you suggested and added the code you gave me on the command button - on click Private Sub Command4_Click() Call Create_Table(Me!txtStrTableName, Me!txtStrTWS) End Sub Then I added break point as you suggested included watches on the data values and stepped thru the code. I am now getting Data Type Mismatch in Criteria Experssion - run time error 3464: Here is the code now in my function Create_Table - Function Create_Table(strTableName As String, strTWS As String) Dim strSQL As String strSQL = "SELECT Sos0205_M.ID, Sos0205_M.CONG, Sos0205_M.LEG, Sos0205_M.REP, Sos0205_M.TOWNSHIP," strSQL = strSQL & " Sos0205_M.WARD, Sos0205_M.PCT , Sos0205_M.RD_MM, Sos0205_M.RD_DD," strSQL = strSQL & " Sos0205_M.RD_YY, Sos0205_M.LAST, [first] & ' ' & [SUF] as First_Name," strSQL = strSQL & " Sos0205_M.ADDRESS, Sos0205_M.CITY, Sos0205_M.ZIP, Sos0205_M.SEX, " strSQL = strSQL & "Sos0205_M.BD_MM, Sos0205_M.BD_DD, [bd_cc] & [bd_yy] AS BD_YR, " strSQL = strSQL & "Sos0205_M.PHYSIMP, Sos0205_M.feb05, Sos0205_M.mar04, Sos0205_M.nov04, " strSQL = strSQL & "Sos0205_M.feb03, Sos0205_M.apr03, Sos0205_M.mar02, Sos0205_M.nov02, " strSQL = strSQL & "Sos0205_M.feb01, Sos0205_M.apr01 " strSQL = strSQL & "INTO " & strTableName & " FROM Sos0205_M " strSQL = strSQL & "WHERE Sos0205_M.TOWNSHIP = " & strTWS & " " strSQL = strSQL & "ORDER BY Sos0205_M.PCT, Sos0205_M.LAST, Sos0205_M.ADDRESS;" DoCmd.RunSQL strSQL End Function Can you think of why I am getting this error... SignatureG. Jay Myatt, Jr. IT Specialist Cook County Clerks Office Chicago, ILL 312-603-1123 Phone 312-603-0982 Fax > >I called it with this query code... > > [quoted text clipped - 21 lines] > > John W. Vinson[MVP] > strSQL = strSQL & "WHERE Sos0205_M.TOWNSHIP = " & strTWS & " " This line is the source of the error. Since TOWNSHIP is a Text field (apparently), you need the syntactically required quotemarks. Just in case there might be a township with an apostrophe in its name, use the " character, ASCII code 34, as the delimiter: strSQL = strSQL & "WHERE Sos0205_M.TOWNSHIP = " & Chr(34) & strTWS & Chr(34) & " " (all on one line of course). John W. Vinson[MVP] John: That was the Answer!!! Thanks... it runs great... this will allow me to put together a great system... and thanks for putting up with me and my lack of knowledge... SignatureG. Jay Myatt, Jr. IT Specialist Cook County Clerks Office Chicago, ILL 312-603-1123 Phone 312-603-0982 Fax > > strSQL = strSQL & "WHERE Sos0205_M.TOWNSHIP = " & strTWS & " " > [quoted text clipped - 11 lines] > > John W. Vinson[MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.