Experts:
As part of a field mapping activity, I need some recommendations for identifying *candidate* fields.
Attached DB contains two tables "Source" & "Target" as well as two (test) queries.
Background for table "Source":
- Contains 20 sample records
- In MS-Excel, I applied text delimitation (by space).
- All existing values then were placed into separate fields (e.g., [F1] and [F2]. For example, legacy field = "Inspection ID" was separated into "Inspection" [F1] and "ID" [F2].
- The text delimitation was needed as I WILL NOT find the value "Inspection ID" in the target table "Target".
- That is, legacy data includes all spaces while the 2nd table does NOT include any spaces.
- However, I might find either "*Inspection*" OR "*ID*" by themselves in table [Target].
- Please note that wildcards were placed into the latter keywords "*Inspection*" OR "*ID*".
** Please note that I also included original source table "Source_Original_No_TextDelimitation"... which as the object name indicates does NOT include the delimited fields.
Background for table "Target":
- Contains 5926 sample records.
- Within these 5926 records, I need to find either a) one candiate field or b) many candidate fields that are appropriate for mapping the original legacy field "Inspection ID" to a target field.
- For example, "Inspection ID" could be mapped to the following:
-- ID 3099 | IRMAP_INSPECTION_ID
-- ID 3163 | REC_INSPECTION_ID
-- ID 4426 | RIT2_INSPECTION_TYPE_TIER1_ID
Additional background:
- Table "Source" includes only 20 sample records. However, in my actual DB, it includes nearly 300 records.
- Once text delimited, I deal nearly with 1000 "keywords" that I currently have to manually look up (via "Find") in Excel or Access. This is not an efficient process.
Existing Query "qry01_Wildcard_Keyword_Inspection":
- "qry01_Wildcard_Keyword_Inspection" uses criteria = "Like "*" & "Inspection" & "*"".
- It returns the 3 candidate fields (from table "Target") to which I potentially will/should map the legacy field.
- Naturally, as specified in the "additional background", I would have to manually copy/paste all text-deliminated keywords into this query in order to find the appropriate matches.
Existing Query "qry02_Wildcard_TargetTable":
- This is a MOCK QUERY and it doesn't work.
- However, conceptually, I was hoping to use a criteria, e.g. such as "Like "*" & [Source].[F1] & "*"" in order to automatically place all values from [Source].[F1] and/or [Source].[F2] into the criteria.
Question:
How can I use either a) query or b) VBA to generate the following results?
"Inspection" | IRMAP_INSPECTION_ID
"Inspection" | REC_INSPECTION_ID
"Inspection" | RIT2_INSPECTION_TYPE_TIER1_ID
{results in only 3 records... perfect!}
"ID" | ATC_ACCOUNT_ID
"ID" | ATC_TRAINING_COURSE_ID
...
...
...
{results in 2,273 candidate fields... not good!}
** Note: Given that field value/keyword, e.g., "ID" is so generic (and results in 2,273 matches), it may be required to use the backup table "Source_Original_No_TextDelimitation".
In this case, value "INSPECTION_ID" can be mapped to only 2 fields: IRMAP_INSPECTION_ID & REC_INSPECTION_ID
Applying this concept to other values such as changing "Inspection Date" to "Inspection_Date" will result in zero (0) matches.
Does anyone know how to efficiently identify candidate fields -- via query of VBA -- in table "Target" based on either table "Source" or "Source_Original_No_TextDelimitation".
Thank you,
EEH