VBA Routine to Extract Values (multiple tables/one field with fieldname wildcard)

[skydivetom]

Hello:

A while ago, one of the forum's experts provided a wonderful ACCDB example that allows creating a data dictionary (see attached form and module which appends the first field only into table 'data_dictionary'). Again, this works great!

I now have the need to create a slightly different version. Instead of adding field names into a data dictionary, I need to extract *all values* (across the 4 tables starting with "R_") from a single field containing the word "description". I'll summarize in bullet format:

- First, data in the four "R_" tables are random data.
- Obviously, each "R_" has a different field structure. However, each "R_" table includes a field containing the word "DESCRIPTION".
- For instance:


-- RA_DESCRIPTION is 3rd field in [R_AIRPORT]
-- RCTRY_DESCRIPTION is 2nd field in [R_COUNTRY]
-- RHS_DESCRIPTION is 3rd field in [R_HAZARD_STATUS]
-- RIS_DESCRIPTION is 4th field in [R_INJURY_SEVERITY]

Now, the VBA routine shall loop through n "R_" table and create/append all records into a new table [SOURCE_VALUES], where:
- 1st field = [TABLENAME]
- 2nd field = [ID_NUMBER]
- 3rd field = [DESCRIPTION]

See attached PNG for illustrated sample output.

How can/should (if any) of the existing VBA be modified to achieve this process? Alternatively, if existing VBA cannot be utilized, what new function would achieve the illustrated outcome?

Thank you,
EEH

[Gicu]

Hi Tom,
Is the ID_NUMBER in SOURCE_VALUES just an autonumber or is it coming from the various _ID fields in the four source tables?
Cheers,

[Gicu]

Hi Tom,
Here is a version that captures the min of the existing ID in the source tables associated with each distinct description.
Cheers,

[skydivetom]

Hello Vlad:

Long time no hear... I trust all is well.

Sorry for the delayed response... your solution in post #3 is PERFECT!!!! (** AS ALWAYS **).

Thank you and all my best,
Tom

P.S. Hope you enjoy the fishing (during salmon season).

[skydivetom]

Vlad:

One quick follow-up question...

I have noticed that not necessarily *all* tables include a field containing keyword "DESCRIPTION". Right now, the VBA stops (errors) once it comes to a table that doesn't include such field.

How can that exception be included so it wouldn't stop the routine?

Thanks,
Tom

[Gicu]

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:

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,

[skydivetom]

Vlad:

Based on a preliminary analysis, I can report the following:
- Total "R" table count: 636
- Out of those, 599 tables contain "DESCRIPTION" where -- it appears -- 10 (out of 599) tables have multiple "DESCRIPTION" fields. If that becomes an issue, I'll use the first description field only.
- For delta values (tables NOT containing DESCRIPTION), a subset of tables also do NOT have any ID fields either.

Based on this analysis, tables containing neither "DESCRIPTION" nor "ID" should be skipped from the value extraction and the routine should continue to the next table.

[skydivetom]

Based on code in post 6, I still get a parameters error. See attached.

[Gicu]

Hi Tom,
When you get that error open the immediate window and type ?sSql and Enter and see how it looks, I suspect it errors on a table that doesn't have a "****_ID" field, so the sID variable is empty.
The code as it stands right now will give you the last field containing 'DESCRIPTION' in its name, not the first as you mention in post #7. To do that you will need to break up the For Next loop into two, one for each field and immediately exit the one for description when found.
Let me know if you get stuck and I will send you an update.
Cheers,

[skydivetom]

Vlad:

As suggested, I checked ?sSql in the ImmediateWindow. See results in attached PNG.

//

Next, I located 2 tables that don't contain a field incl. 'DESCRIPTION'. See attached ZIP file containing 4 tables:
1. R_BAD_TABLE_1A_WITHOUT_DESCRIPTION -- it misses a description field.
2. R_BAD_TABLE_1B_WITH_DESCRIPTION -- I copied above table and added dummy field [DUMMY_FIELD1_DESCRIPTION]. VBA processed table then.
3. R_BAD_TABLE_2A_WITHOUT_DESCRIPTION -- it misses a description field.
4. R_BAD_TABLE_2B_WITH_DESCRIPTION -- I copied above table and added dummy field [DUMMY_FIELD2_DESCRIPTION]. VBA processed table then.

At this point, any table, e.g., ([R_BAD_TABLE_1A_WITHOUT_DESCRIPTION] and/or [R_BAD_TABLE_2A_WITHOUT_DESCRIPTION]) does NOT contain any valuable information for my research purposes.

Thus, the VBA routine should skip these any tables that don't include a 'description' field and then move to the next table for processing.

[Gicu]

Hi Tom,
Attached is an updated db that would process all tables with description fields and add all distinct values of ALL of those fields to the SOURCE_VALUES table (which now has a new field called FIELDNAME to store that).
Cheers,
Vlad

[skydivetom]

Vlad:

Wow... this version is very impressive. Not only did all tables get processed *without* any issues, the processing time is fantastic.

I took less than 90 seconds to process/extract ~4 million records into the new table. Amazing!!

Many, many thanks for your time & interest to assist w/ this research activity.

Cheers,
Tom

[Gicu]

You're very welcome Tom!
Cheers,