Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,817
    A UNION query can be used like a table and referenced in other SELECT queries. That's why I said might be able to do UNION of UNIONs. If UNION performs well, wouldn't even have to save into a table, just use UNION query like a table.

    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.

  2. #17
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,845
    If UNION performs well, wouldn't even have to save into a table,
    Isn't the whole purpose of this exercise to do away with all the tables that are basically the same after consolidating their records into one new table? Or did I mis-read the goal of the original question?

    EDIT - after posting I see that OP answered at least part of that (affirmatively).
    The code for this ought to be very simple, relatively speaking. In a COPY of your db put this code in a standard module (see note below):
    Code:
    Function LoopTables()
    Dim tdf As TableDef, db As DAO.Database
    Dim sql As String
    
    On Error GoTo errHandler
    
    Set db = CurrentDb
    For Each tdf In CurrentDb.TableDefs
        If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
            sql = "INSERT INTO [NewTableNameHere] SELECT * FROM [" & tdf.Name & "]"
            db.Execute sql, dbFailOnError
        End If
    Next
    
    exitHere:
    Set db = Nothing
    Exit Function
    
    errHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume exitHere
    
    End Function
    NOTES
    I could not test this on a batch of tables, but the sql worked for one particular table. Thus it's not guaranteed to work. Also, if there is a failure, the code does not employ a transaction, thus it can fail part way through and leave the job half done. I had no idea as to whether or not that is important. A failure can occur due to constraints in your target table (if any) such as a unique field and this code attempts to duplicate data in that field. Obviously you have to put the name of the target table into this code.

    FWIW, the table names and any field with special characters such as "/" is/are not good. Hopefully those won't cause an issue. You can report back with any error number(s) and message(s) that are generated.

    It's still not clear to me how you are going to distinguish one set of records from another. Perhaps after trying this, that will become more clear.

  3. #18
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,705
    What exactly does your client expect as a result/return? What does "handle" really entail?

  4. #19
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,344
    I would think to distinguish the records, in your tdf code add the name of the table to each record so you know which school/table the records came from. I believe the reference is tdf.name

  5. #20
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,845
    Quote Originally Posted by Bulzie View Post
    I would think to distinguish the records, in your tdf code add the name of the table to each record so you know which school/table the records came from. I believe the reference is tdf.name
    Certainly, but l asked once already about that because what we were told about the new table implies that there is no place to put that data. Still no answer. AFAIK, such a field might be useless and much of the information shown might not represent reality.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

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

Similar Threads

  1. Replies: 9
    Last Post: 09-25-2017, 04:25 PM
  2. Replies: 16
    Last Post: 02-09-2017, 03:48 PM
  3. Replies: 11
    Last Post: 01-13-2017, 06:58 PM
  4. combining multiple tables in to one master
    By joebox8 in forum Queries
    Replies: 5
    Last Post: 06-23-2011, 06:18 AM
  5. Replies: 8
    Last Post: 11-04-2009, 04:22 AM

Tags for this Thread

Posting Permissions

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