Extract DISTINCT values from n tables (works) but now need to add source table (i.e., add Boolean)

[skydivetom]

Hello:

A while back, forum expert @orange assisted me with the development of a VBA routine which does accomplish the following:

1. Scan through n number of tables (where table name has prefix = 'tbl_').
2. Extract all DISTINCT values across all fields within n tables.
2. For every field across all tables, create a new table (starting with prefix 'LK_) and append all DISTINCT values.

This process works great (see attached file "LK Tables v01.accdb"). Allow me to summarize the specific steps for the above process:
a. Upon opening "LK Tables v01.accdb", default forms opens.
b. Click command button "Delete LK Tables". In this case, message box will indicated "5 LK tables were deleted."

Note: At this time, DB will show 2 admin tables (Create_LK_Tables & Data_Dictionary) + 5 source tables (tbl_MMAC, tbl_NNSY, tbl_PHNSY, tbl_PNSY, tbl_POAIRS).
c. Now, with the form still open, click on command button "Create LK Tables". Click on message box "Seems all LK tables were dropped..."
d. Finally, message box will display that "5 LK tables were created & loaded.".

As part of the process, the following 5 LK tables are created:
- LK_BLS_ACCIDENT_TYPE
- LK_CHEMICALS_INVOLVED


- LK_EMERGENCY_ROOM
- LK_GENDER
- LK_GRADE

Again, this process works great and the 5 LK tables contain *all distinct* values across the 5 source tables and their up to 5 fields each (please note that not all 5 tables include all 5 fields though).

---------------

Now, here's what I need some help with. I would like to explore whether or not it is possible to modify the VBA (i.e., module "02 Generate_LK_Tables") so that a Boolean value (0 or -1) will be automatically be added where applicable.

Note: The following must be kept in mind:
- Some data values exist in one and only one table. For example, the value "Fall, slip, trip" (in table LK_BLS_ACCIDENT_TYPE) only exists in source table [tbl_POAIRS].
- Alternatively, other data values may exist in multiple tables but not all tables. For instances, the value "10" (in table LK_BLS_ACCIDENT_TYPE) exists in 3 out of 5 source tables: tbl_MMAC, tbl_NNSY, tbl_PHNSY.

I have attached an image that depicts how the modified VBA should generate, e.g., table "LK_BLS_ACCIDENT_TYPE". So, ultimately, I would be able to filter on "-1" to identify where each of the distinct values may have come from. Naturally, same principle applies to the other 4 fields. If table contains the field name, add a "-1" where value/source table intersect.

How can this modification be accomplished?

Thanks,
Tom

[June7]

One approach is to create a record for each value/source table pair in each LK table then use each LK table as source for a CROSSTAB query to get the matrix output.

Another is to add fields to LK tables for each source table.

Instead of deleting and recreating tables, an option is to delete records, which is what I use. I would probably have one LK table with a Category field.

Consider simple code example for first approach with a single LK table (assumes no memo type fields):

Public Sub GetData()
Dim db As DAO.Database, rs As DAO.Recordset
Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Data_Dictionary WHERE Field_Name <> 'Auto_ID'")
CurrentDb.Execute "DELETE * FROM LK"
Do While Not rs.EOF
    db.Execute "INSERT INTO LK(Data,Category,Source) SELECT DISTINCT " & rs!Field_Name & ",'" & rs!Field_Name & "','" & rs!Table_Name & "' FROM " & rs!Table_Name
    rs.MoveNext
Loop
End Sub

And then a CROSSTAB for BLS_ACCIDENT_TYPE

TRANSFORM Nz(Count("*"),0) AS X
SELECT LK.Data
FROM LK
WHERE (((LK.Category)="BLS_ACCIDENT_TYPE"))
GROUP BY LK.Data
PIVOT LK.Source IN ("tbl_MMAC","tbl_NNSY","tbl_PHNSY","tbl_PNSY","tbl _POIRS");

Now for the other approach code:

Public Sub GetData_M()Dim db As DAO.Database, rs As DAO.Recordset
Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Data_Dictionary WHERE Field_Name <> 'Auto_ID'")
CurrentDb.Execute "DELETE FROM LK_M"
Do While Not rs.EOF
    db.Execute "INSERT INTO LK_M(Data,Category," & rs!Table_Name & ") SELECT DISTINCT " & rs!Field_Name & ",'" & rs!Field_Name & "',1 FROM " & rs!Table_Name
    rs.MoveNext
Loop
End Sub

[Gicu]

Hi Tom,

Here is an updated file that should do what you're after, I am adding the "source table" fields to the LK_ tables in a loop and updating it as we go.

Cheers,
Vlad

[orange]

@Vlad --very nice --more direct /compact than my first thoughts. Well done!

[skydivetom]

@ Vlad -- this is BEAUTIFUL... the VBA modification is exactly what I was looking for.

Quick update on the integration:
1. Again, VBA sub routine works great w/ the sample data.
2. Once integrated, I ran into some errors where the fieldnames were equal to "memo" (long text).
3. I further reviewed the LK tables and determined that -- for this process -- I only need a subset of fieldnames (not the FFs aka free forms).
4. Thus, based on #3, all LK tables are successfully processed and show the source tables... fantastic.

Cheers,
Tom

[Gicu]

Hi Tom,
The errors from the Memo fields are caused by the existing data itself (which could also happen in a short text): the SQL code is broken by the double-quotes (inches), I am reviewing it right now.
Cheers,

[skydivetom]

Vlad -- yes, it would be great to have a routine which processes ALL LK tables (regardless of data type). Appreciate any additional advice that would prevent the error WARNING.

Cheers,
Tom