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.
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.
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?If UNION performs well, wouldn't even have to save into a table,
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):
NOTESCode: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
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.
What exactly does your client expect as a result/return? What does "handle" really entail?
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.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.