Hi Tom,
How about the ID field, do all tables that have a field containing "DESCRIPTION" always have a field containing "_ID"?
Please try this:
Code:
Public Sub vcBuildSourceTable()
Dim tdf As DAO.TableDef, db As DAO.Database, fld As DAO.Field, sDESC As String, sID As String
Dim sSQL
Set db = CurrentDb
'check if SOURCE_VALUES table exists and delete if True
If DoesTableExist("SOURCE_VALUES") = True Then DoCmd.DeleteObject acTable, "SOURCE_VALUES"
For Each tdf In db.TableDefs
If InStr(tdf.Name, "R_") > 0 Then
'get the names of the ID and DESCRIPTION fields
For Each fld In tdf.Fields
If InStr(fld.Name, "DESCRIPTION") Then sDESC = fld.Name
If InStr(fld.Name, "_ID") Then sID = fld.Name
Next fld
If sDESC = "" Then GoTo SKIP_TABLE 'skip table if no Description field
'If "SOURCE_VALES" exists append else make it
If DoesTableExist("SOURCE_VALUES") = False Then
sSQL = "SELECT '" & tdf.Name & "' AS TABLENAME, Min(" & sID & ") AS ID_NUMBER, [" & _
sDESC & "] AS DESCRIPTION INTO SOURCE_VALUES " & _
"FROM " & tdf.Name & _
" GROUP BY '" & tdf.Name & "', " & sDESC & _
" ORDER BY " & sDESC & ";"
Else
sSQL = "INSERT INTO SOURCE_VALUES ( TABLENAME, ID_NUMBER, DESCRIPTION ) " & _
" SELECT '" & tdf.Name & "' AS TABLENAME, Min(" & sID & ") AS ID_NUMBER,[" & _
sDESC & "] AS DESCRIPTION FROM " & tdf.Name & _
" GROUP BY '" & tdf.Name & "', " & sDESC & _
" ORDER BY " & sDESC & ";"
End If
'run the query
db.Execute sSQL, dbFailOnError
End If
SKIP_TABLE:
Next tdf
'refresh db
Application.RefreshDatabaseWindow
MsgBox "DONE"
End Sub
Cheers,