Query multiple tables

[riteshkp]

I need some help in writing a query that addresses this problem. I have inherited an access database that has 50 tables within it. All the tables have the same structure and layout. Each table has 25 fields that describe individual loan characteristics. I need to find out all the unique instances of the field "PREFUND_ID" in all the tables.

I was trying to do the "Union all" query, load all the tables to one table and then do the "group by" for the "PREFUND_ID" field. The union all query did not work, it says - too many fields. (I had given union all *)

I know there must be an easier way of querying through all the tables to identify the unique instances of a certain field. I searched different threads, couldnt find anything that seemed to address this. Any help here would be appreciated.

 

[Joe4]

How about doing a series of Append queries to build one large table from each of the 50 smaller tables?

Then you can write your Aggregate Query from this new table.

 

[riteshkp]

Thanks Joe. Will try using multiple append queries to build the master table. This should hopefully work.

Am wondering is there any other efficient way of getting to the unique records in multiple tables?

 

[Joe4]

That's is why database normalization is so important to database design. If the database had been designed correctly (one table), this would be a very simple task to do.

I can't think of any other way outside of getting all the records in one query (by a Union Query) or in one table (using Append Queries).

I suppose that if you were so inclined, you could whip up some nice VBA code to run Append Queries on all 50 tables dynamically, which would save you the manual work of manually running 50 Running Queries. But by the time you figure that out, it might not be much of a time savings!

 

[Will_B]

Try this code to append all 50 of your tables. Before you try it, create an empty table that mirrors your 50 tables exactly. You can do that with Copy/Paste/Structure Only. If you have an autonumber field in this table, you'll need to convert it to data type LONG, otherwise you won't append all records. Also, you might want to tweak my code and your destination table structure so they also record the name of the table from which each record is coming.

Here's the code. Change "MyDestinationTable<ENTER here table destination your of name>" to whatever name you have for the table you want to append to.

Sub Append_My_Tables()
On Error GoTo Err_Handler
 
Dim db As Database
Dim tdf As TableDef
Dim strCurrentTable As String
Dim strDestinationTable As String
Dim strSQL As String
Dim n As Long
 
strDestinationTable = "MyDestinationTable<ENTER here table destination your of name>"
Set db = CodeDb()
DoCmd.SetWarnings (False) 'otherwise, you'll get 50 messages asking if you want to append records
 
n = 0
For Each tdf In db.TableDefs
    strCurrentTable = tdf.Name
    Debug.Print tdf.Name
    If tdf.Name = strDestinationTable Or tdf.Name Like "MSys*" Then 'if table to be append is destination table or hidden table, skip it without incrementing n
        GoTo Skip_It
    End If
    strSQL = "INSERT INTO " & strDestinationTable & " " & _
                 "SELECT " & strCurrentTable & ".* " & _
                 "FROM " & strCurrentTable & ";"
    Debug.Print strSQL
    DoCmd.RunSQL (strSQL)
 
    n = n + 1 'keep track of how many tables you have appended.
Skip_It:
Next tdf
 
MsgBox n & " tables have been appended to " & strDestinationTable, vbOKOnly, "You are done!"
 
Exit_Now:
DoCmd.SetWarnings (True)  'turn warnings back on
Exit Sub
 
Err_Handler:
MsgBox "Error: (" & Err.Number & ") " & Err.Description & Chr(13) & Chr(13) & "Error occured with table " & strCurrentTable & "(# " & n & ")", vbCritical
GoTo Exit_Now
End Sub

 

[riteshkp]

Thanks a lot Will....Your code worked and I have all the 50 tables appended into one. Thanks once again....You rock...

 

[Joe4]

Nice code, Will!

 

[Will_B]

Praise from Caesar. Thank you.