Help to have a Make Table query to make multiple tables 1

[bosk0]

What we have is a table that contains data for multiple sub accounts. Right now we are using a make table query to manually make a new table from each of the sub accounts so that they can be exported for some other stuff. The current table has 33 sub accounts. What we would like to do is have the query automaticaly make a separate table for each sub account.

The field that is used to specify the sub accounts is CLaims.T01CLILO and the new tables named the same as the T01CLILO.

SELECT DISTINCT CLaims.T01CLINO, CLaims.T01CLILO, CLaims.T01STATE, CLaims.T01DESM, CLaims.T01CLINOP, CLaims.T01CLILOP, CLaims.[T01REPT#], CLaims.T01DATE0, CLaims.T01BYEY, CLaims.T01SSN, CLaims.T01NAM, CLaims.T01TCODE, CLaims.T01AMT, CLaims.T01HRG, CLaims.T01EXBSTS, CLaims.T01EXCSTS, CLaims.T01EX2STS, CLaims.T01ROPSTS, CLaims.T01CRIRP, CLaims.T01CHGTC, CLaims.T01CRTC, CLaims.T01PROTC, CLaims.T02REPDAT, CLaims.T02REPEND, CLaims.T02REPPER, CLaims.T02NOLOCS, CLaims.T02DSTOFF, CLaims.T02CATEG, CLaims.Lastfour, CLaims.recoded, CLaims.LDW, CLaims.Date_Entered, CLaims.T02NAMM INTO [Report Temp]
FROM CLaims INNER JOIN [Report Select] ON (CLaims.T01CLILO = [Report Select].R_Clilo) AND (CLaims.T01CLINO = [Report Select].R_Clino)
WHERE (((CLaims.T01DATE0)<[Report Select].[R_E_Date]) AND ((CLaims.T01BYEY)>[Report Select].[R_B_Date]));

Any help will be appreciated.

Thank you

 

[dhookom]

I'm not sure why you don't export from a query rather than creating multiple new tables. Regardless, you could use a little DAO code to create a recordset of 33 unique sub accounts. Then loop through the recordset and dynamically create an SQL statement to execute.



Duane
Hook'D on Access
MS Access MVP

 

[bosk0]

Duane, thanks for the response. we may be doing this the hard way. One thing I did not make clear is that the number of sub accounts is always different. I am not sure I follow, can you give even a very bare bones example that I can look at as a starting place. I have made separate queries that return the different sub accounts, and that give me the total of the different accounts. But can not figure out how they would work together.

 

[dhookom]

Do you have the SQL of one of the queries as well as a query that returns the required sub accounts?

Duane
Hook'D on Access
MS Access MVP

 

[bosk0]

These are simple queries that I thought could be used to control what happens. The first query returns the sub accounts.

SELECT DISTINCT [Report Temp].T01CLILO
FROM [Report Temp];

I then use that query to feed the second query to get the total of sub accounts.

SELECT Count([Differnet Clilos].T01CLILO) AS CountOfT01CLILO
FROM [Differnet Clilos];

The idea was that the second gives the number of times to run and the first gives the criteria use to select the records. In plain english to make tables from 1 to CountOfT01CLILO, Where T01CLILO = each individual record from first query.

 

[dhookom]

The second query will return a single numeric field and one record. What do you want to do with the number?

I'm confused by your questions/replies. Perhaps you can provide some context about your complete process.

Duane
Hook'D on Access
MS Access MVP

 

[bosk0]

Sorry for the confusion. What I thought was that I had to specify the number of times that the loop was to run. The single numeric value that was returned was that number of times. An example - X = 1 to that value. Do something, then X = X + 1 loop etc.

I am not real versed in this, so I apologize if I do not come across clearly. I have been doing a bunch of reading and have come up with some code that should make it clearer.

Public Function MakeTables(T01CLINO)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim StrSql AS String

Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset("Select T01CLILO from [Report Temp]")


Do Until rs.EOF

StrSql = "SELECT [Report Temp].T01DATE0, [Report Temp].T01SSN, [Report Temp].T01NAM, [Report Temp].T01TCODE, [Report Temp].T01AMT, [Report Temp].T01CHGTC, [Report Temp].T01CRTC, [Report Temp].T01STATE, [Report Temp].T01EXBSTS, [Report Temp].T01EXCSTS, [Report Temp].T01EX2STS, [Report Temp].T01PROTC, [Report Temp].T01CLINO, [Report Temp].T01DESM, [Report Temp].T01CLINOP, [Report Temp].T01CLILOP, [[Report Temp]].[T01REPT#] AS Expr1, [Report Temp].T01BYEY, [Report Temp].T01HRG, [Report Temp].T01ROPSTS, [Report Temp].T01CRIRP, [[Report Temp]].T02REPDAT AS Expr2, [[Report Temp]].T02REPEND AS Expr3, [Report Temp].T02REPPER, [Report Temp].T02NOLOCS, [Report Temp].T02DSTOFF, [Report Temp].T02CATEG, [Report Temp].Lastfour, [[Report Temp]].recoded AS Expr4, [Report Temp].LDW, [Report Temp].Date_Entered, [Report Temp].T02NAMM, [Report Temp].AMT AS Expr5, [Report Temp].T01CLILO INTO [New Table Name]
FROM [Report Temp]

DoCmd.RunSQL (strSql)    
DoCmd.Rename "[highlight #FCE94F]How to Name the Same as the Value in T01CLILO[/highlight]", acTable, "New Table Name"

    rs.MoveNext
    Loop
    Set rs = Nothing
    Set db = Nothing

End Function

In the Rename statement, I would like the table to be named to the value that is in the T01CLILO filed.

 

[dhookom]

I think you can use something like:

Public Function MakeTables()

  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim StrSql AS String
  Dim strNewTableName as String

  Set db = CurrentDb
  Set rs = CurrentDb.OpenRecordset("Select DISTINCT T01CLILO from [Report Temp]")

  Do Until rs.EOF
    strNewTableName = rs("T01CLILO")
    StrSql = "SELECT T01DATE0, T01SSN, T01NAM, T01TCODE, T01AMT, T01CHGTC, " & _
      "T01CRTC, T01STATE, T01EXBSTS, T01EXCSTS, T01EX2STS, T01PROTC, T01CLINO, " & _
      "T01DESM, T01CLINOP, T01CLILOP, [T01REPT#], T01BYEY, " & _
      "T01HRG, T01ROPSTS, T01CRIRP, T02REPDAT, T02REPEND, " & _
      "T02REPPER, T02NOLOCS, T02DSTOFF, T02CATEG, Lastfour, recoded, " & _
      "LDW, Date_Entered, T02NAMM, AMT, T01CLILO " & _
      "INTO [" & strNewTableName & "] " & _
      "FROM [Report Temp]"
    db.Execute strSql, dbFailOnerror
    rs.MoveNext
  Loop
  rs.Close
  Set rs = Nothing
  Set db = Nothing

End Function

Duane
Hook'D on Access
MS Access MVP

 

[bosk0]

I have added the code to a module. I added a button to my form and put the following code on it.

Private Sub Command17_Click()
On Error GoTo Err_Command17_Click

Call MakeTables

Exit_Command17_Click:
    Exit Sub

Err_Command17_Click:
    MsgBox Err.Description
    Resume Exit_Command17_Click

End Sub

When I press the button I get an error. Compile error: User-defined type not defined. With part of the first Dim statement highlighting the db As DAO.Database

 

[bosk0]

Found that problem. The Microsoft DAO 3.6 object library was not listed in References. That error has been taken care of

Now when it is run it gives the following. Too few parameters. Expected 1.

 

[bosk0]

Is the missing parameter error because there is no Where clause?

Should there be something like "Where T01CLILO = rs("T01CLILO") after the From line in the code and before the db.execute line?

 

[lameid]

Yes, looks like you need a where clause... Hope you understand how comments in code work...

StrSql = "SELECT T01DATE0, T01SSN, T01NAM, T01TCODE, T01AMT, T01CHGTC, " & _
      "T01CRTC, T01STATE, T01EXBSTS, T01EXCSTS, T01EX2STS, T01PROTC, T01CLINO, " & _
      "T01DESM, T01CLINOP, T01CLILOP, [T01REPT#], T01BYEY, " & _
      "T01HRG, T01ROPSTS, T01CRIRP, T02REPDAT, T02REPEND, " & _
      "T02REPPER, T02NOLOCS, T02DSTOFF, T02CATEG, Lastfour, recoded, " & _
      "LDW, Date_Entered, T02NAMM, AMT, T01CLILO " & _
      "INTO [" & strNewTableName & "] " & _ 'It would probably make more sense to take this out and use this string to export specific records
      "FROM [Report Temp] " & _
      "Where T01CLILO = " & rs("T01CLILO") 'assuming T01CLI0 is numeric
'      "Where T01CLILO = """ & rs("T01CLILO") & """" 'and if it is text, you need to embed double quotes in your string or double up your double quotes
'      "Where T01CLILO = '" & rs("T01CLILO") & "'" 'and some would suggest that queries run fine with single quotes and do it this way

 

[dhookom]

I have added a where clause that assumes T01CLILO is a text/string value. If it isn't you will need to remove some quotes from the where clause.
The error messages suggests a field name is incorrect.

Public Function MakeTables()
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim StrSql AS String
  Dim strNewTableName as String

  Set db = CurrentDb
  Set rs = CurrentDb.OpenRecordset("Select DISTINCT T01CLILO from [Report Temp]")

  Do Until rs.EOF
    strNewTableName = rs("T01CLILO")
    StrSql = "SELECT T01DATE0, T01SSN, T01NAM, T01TCODE, T01AMT, T01CHGTC, " & _
      "T01CRTC, T01STATE, T01EXBSTS, T01EXCSTS, T01EX2STS, T01PROTC, T01CLINO, " & _
      "T01DESM, T01CLINOP, T01CLILOP, [T01REPT#], T01BYEY, " & _
      "T01HRG, T01ROPSTS, T01CRIRP, T02REPDAT, T02REPEND, " & _
      "T02REPPER, T02NOLOCS, T02DSTOFF, T02CATEG, Lastfour, recoded, " & _
      "LDW, Date_Entered, T02NAMM, AMT, T01CLILO " & _
      "INTO [" & strNewTableName & "] " & _
      "FROM [Report Temp] " & _
      "WHERE T01CLILO = '" & strNewTableName & "'"
    debug.Print strSQL
    db.Execute strSql, dbFailOnerror
    rs.MoveNext
  Loop
  rs.Close
  Set rs = Nothing
  Set db = Nothing
End Function

You can take the results from the Debug.Print statement and paste it into a blank query SQL view to test.


Duane
Hook'D on Access
MS Access MVP

 

[bosk0]

Duane,

The T01CLILO is numeric. I removed the single quotes and it works like a champ. I cannot thank you enough for all of your help.

Alan

 

[dhookom]

Although it now works, I don't think you have ever justified why you need individual tables to export when you can export from queries. However, it's your application so I will let it go ;-)

Duane
Hook'D on Access
MS Access MVP