Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Large table into smaller

    I see you can do the following to make a new table of smaller portions:



    "SELECT * INTO {newTableName} FROM {table} WHERE CarrierName = "{name}";"

    How do I use the contents of 1 field where if the field content is different it will make a table. I can use:

    SELECT DISTINCT TableName.[FieldName] FROM TableName;

    to get all the differing data sets.

    How would I combine the two?

    "SELECT * INTO "
    SELECT DISTINCT TableName.[FieldName] FROM TableName;"
    FROM TableName WHERE "
    SELECT DISTINCT TableName.[FieldName] FROM TableName
    ;";"




  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I can't see why you would want to, but you'd have to do it in VBA. Open a recordset on your distinct values. Within a loop of that recordset, build and execute SQL. Along the lines of:

    strSQL = "SELECT * INTO " & rs!FieldName & " FROM TableName WHERE CarrierName = '" & rs!FieldName & "'"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Thanks pbaldy,
    I am trying another way to push info into VISIO. Breaking down the main table so VISIO can import by page per table made.

    FileNameClip is the current made Table

    Code:
    Private Sub Break_Down_FileNameClip()
    Dim rstHB As DAO.Recordset
    Dim db As DAO.Database
    Dim strSQL As String
    
    Set db = CurrentDb()
    
    Do Until rstHB.EOF
           strSQL = "SELECT * INTO " & rstHB![TOETitle] & " FROM " & FileNameClip & " WHERE [TOE Title] = '" &rstHB![TOE Title] & "'"";"
    Loop
    
    End Sub
    Doesn't seem to do anything. I have closed all DAO database/recordsets.
    Last edited by Thompyt; 05-02-2019 at 02:03 PM.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You never set rstHB, and then never execute strSQL:


    db.Execute strSQL, dbFailOnError


    This may come in handy once you're doing those 2 things:

    http://www.baldyweb.com/ImmediateWindow.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Debug.print filenameclip provides the intended name for the base table. I think I am having issues with the bolded below. Normally you'd have the name of the table in quotes, but since it is referenced to a string variable, I would think it would go without the quotes.

    Private Sub Break_Down_FileNameClip()
    Dim rstHB As DAO.Recordset
    Dim db As DAO.Database
    Dim strSQL As String

    Debug.Print FileNameClip


    Set db =CurrentDb(FileNameClip)
    Set rstHB =db.OpenRecordset(FileNameClip)

    strSQL = "SELECT * INTO " & rstHB![CO]& " FROM " & FileNameClip & " WHERE [CO] = '"& rstHB![CO] & "'"
    CurrentDb.Execute strSQL, dbFailOnError


    End Sub

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What issues are you having exactly? Based on what you said earlier, I'd expect the recordset to be opened on an SQL statement that got distinct values for the loop. This line makes no sense:

    Set db =CurrentDb(FileNameClip)

    it would normally be

    Set db =CurrentDb

    Can you attach the db here?

    Edit: you are correct that if the variable contains the table name, you wouldn't need quotes in the open recordset line.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    HB2Visio-3MAY19.zip

    As requested

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Has the goal changed? There is no table or query named "JWA_19_15" which your recordset is going to try to open. There's no loop.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Pbaldy,
    goal not changed. I put an old table in quotes to see if it would pick that up. I know you have to loop, but was doing a step by step failure lol.

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Any chance you would post an Excel workbook to be able to import it and step through the whole process?

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Consider modification to your code:

    Code:
    Private Sub Break_Down_FileNameClip_Click()
    Dim rstHB As DAO.Recordset
    Dim db As DAO.DatabaseDebug.Print FileNameClip
    Set db = CurrentDb()
    Set rstHB = db.OpenRecordset("SELECT DISTINCT CO FROM JWA_19_15")
    Do While Not rstHB.EOF
        db.Execute "SELECT * INTO [" & rstHB![CO] & "] FROM [" & FileNameClip & "] WHERE [CO] = '" & rstHB![CO] & "'", dbFailOnError
        rstHB.MoveNext
    Loop
    End Sub
    However, why does your code reference JWA_19_15 when table in db is JWA_19_V6?


    Also, using IN() can simplify SQL statement:
    Code:
    ' Deletes all the rows in Field Row Type meeting key words, in this instance cleaning out shaded green, grey, red rows
    DoCmd.RunSQL "DELETE FROM Temp WHERE [Row Type] IN('PH','TOE','PARA')"
    DoCmd.RunSQL "DELETE FROM Temp WHERE [platform_shading] IN('AN','AU','MT','SM','RP')"
    DoCmd.RunSQL "DELETE FROM Temp WHERE [materiel_shading] IN('AN','AU','MT','SM','RP')"
    Last edited by June7; 05-04-2019 at 11:23 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    June7, all,

    Thanks for IN() makes the code neater. The excel file has been attached for you to run.


    Code:
    Private Sub Break_Down_FileNameClip_Click()
    Code:
    Dim rstHB As DAO.Recordset
    Dim db As DAO.Database
    
    Debug.Print FileNameClip
    
       Set db=CurrentDb()
       Set rstHB=db.OpenRecordset("SELECT DISTINCT CO FROM JWA_19_V15") 
    
    Do While Not rstHB.EOF
      db.Execute"SELECT * INTO " & rstHB![CO] & " FROM[JWA_19_V15]WHERE [CO] = '" & rstHB![CO] & "'",dbFailOnError
                  '    db.Execute"SELECT * INTO" & rstHB![CO] & " FROM [" &FileNameClip &"] WHERE [CO] = '" & rstHB![CO] &"'", dbFailOnError
    
       rstHB.MoveNext
    Loop
    
    End Sub



    Runs through without making any addition to tables.

    Debug.print = JWA_19_V15

    As above where directly referencing table JWA_19_V15gives Error 3067, indicating no table or query.

    Set rstHB =db.OpenRecordset("SELECT DISTINCT CO FROM& [" & FileNameClip & "]") is the correct for a variable name from thefile selected in the beginning?
    Attached Files Attached Files
    Last edited by Thompyt; 05-04-2019 at 10:32 AM. Reason: Added file and formatting

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I already asked: Why does your code reference JWA_19_15 when table in db is JWA_19_V6? If should use variable FileNameClip then adjust. You show an extraneous & in your question: FROM&.

    Add [] for the new table name because spaces are involved: INTO [" & rstHB![CO] & "] FROM

    Step debug. Do variables get correct values?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    June7, There are multiple tables I made importing several excel spreadsheets. That happened to be in there at the time and used to make a specific target vice a variable name for a table.

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Code errors on line:

    FileNameClip = Replace(Left(strFile, InStrRev(strFile, "_HorseBlanket") - 1), "Approved_", "")

    The string HorseBlanket is not in file name. HB is in the name. I don't think need Replace().
    FileNameClip = Left(strFile, InStr(strFile, "_HB") - 1)

    Then the Sub Break_Down_FileNameClip_Click() has 'not valid name' error on CO value "1ST STRYKER BRIGADE COMBAT TEAM, 2ND INFANTRY DIVISION HQ (OPFOR)", even after including [] in the SQL. I think comma is cause. Handled with:

    Replace(rstHB![CO], ",", "")

    Everything now ran without error and almost 50 tables created.

    Error handlers can actually make debugging more difficult. I commented the On Error GoTo line so the debugger would stop on the line that triggers error and I could read the exact error message and pinpoint where and why the error occurred. If you do have active error handlers then have the output include the complete error message, not just the number. As you can see, the errors actually occurred in procedures called by the one that has the error handler.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Make a form smaller
    By mp3909 in forum Forms
    Replies: 3
    Last Post: 03-14-2019, 02:12 PM
  2. Replies: 4
    Last Post: 03-08-2018, 03:27 PM
  3. Replies: 3
    Last Post: 02-23-2018, 05:52 PM
  4. Replies: 5
    Last Post: 12-07-2014, 01:15 PM
  5. Large Table Update
    By drunkenneo in forum Programming
    Replies: 1
    Last Post: 01-21-2014, 12:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums