Page 1 of 3 123 LastLast
Results 1 to 15 of 39
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Need to identify *candidate* fields (from target system) based on legacy fields (source system)

    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
    Attached Files Attached Files

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tom(EEH),

    Won't your mappings be somewhat dependent on the prefixes in Target.
    I get 805 distinct acronyms for that table. Suggest there is more to the mapping than best guess for something in Source to "match" TARGET.
    What do these things mean? Just a sample.
    MyID Acronym
    1 ATC
    3 AFHL
    11 AHRM
    13 AFN
    23 ANFA
    31 AIR
    35 ACFT
    125 ACOMP
    135 AEGR



    Try:
    Code:
    SELECT Target.ID, Target.FIELDNAME, Source.f1, Source.f2
    FROM Target, Source
    WHERE 
    (Target.FIELDNAME) Like "*" & [source].[f1] & "*" & [source].[f2] & "*" OR
    (Target.FIELDNAME) Like "*" & [source].[f2] & "*" & [source].[f1] & "*"

  3. #3
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom,

    Here is a simple query-based solution, I hope I got what you are trying to accomplish...

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad:

    I love this it's approach... very simple yet extremely powerful.

    Just one quick follow-up... after posting the sample file, I realized I need 3 fields (i.e, F1, F2, and F3).

    In the attached DB, I made the necessary changes to the table. However, I'm not entirely certain if I applied all changes correctly in the query "qry01_Candidate_Mapping".

    Below are the changes I made:
    1. Inserted F3 and set criteria "Is Null" in 2nd OR statement.
    2. For F1, I added another TRUE to 2nd OR statement.
    3. Added expression "Match_F3: InStr([FIELDNAME],[F3])>0"
    4. Added TRUE to 2nd OR statement of the new expression.

    However, I wasn't entirely certain whether or not I also needed to add a 2nd TRUE for Match_F2.

    Here's my concern... once I execute the query, I see a few "0" in the Match_F2 field. Should that read "-1" instead?

    Am I missing something?
    Attached Files Attached Files

  5. #5
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Jack -- I just ran your query... I get the same results in in the version posted by Vlad. Perfect!

    As per my response (post #4), I'll try adding the 3rd field. Thank you.

    Tom

  6. #6
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom,
    I think this is how it should be....
    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad:

    Excellent -- I realized that the Is Null had been set up incorrectly. Thank you for the additional recommendation. If I ever had to add a 4th field, I will compare notes between "2 Fields" and "3 Fields" and then identify what changes are required.

    Btw, I did the following for eval purposes:
    - In the query, I filtered for value "-1" (in [F3])
    - It illustrates that it found a perfect match for those 4 records.
    - That is fantastic!

    Note:
    - Per attached JPG, I realized that this matching/mapping query results in some duplicates.
    - Thus, I applied "GroupBy" and it reduced the candidate record set from 2,399 possible matches to now 766 matches.

    I'm loving it!!


    //

    Jack -- your query is perfect too. It's a slightly different setup but it gives me the same results.

    KUDOS TO BOTH OF YOU!!!!
    Attached Thumbnails Attached Thumbnails Results.JPG  

  8. #8
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Tom,
    Glad to hear it works for you; you might get a slight improvement in speed if you replace the Group By with Distinct:
    Code:
    SELECT DISTINCT tbl_Source.F1, tbl_Source.F2, tbl_Source.F3, tbl_Target_AFSAS.FIELDNAME, InStr([FIELDNAME],[F1])>0 AS Match_F1, InStr([FIELDNAME],[F2])>0 AS Match_F2, InStr([FIELDNAME],[F3])>0 AS Match_F3
    FROM tbl_Target_AFSAS, tbl_Source
    WHERE (((InStr([FIELDNAME],[F1])>0)=True) AND ((InStr([FIELDNAME],[F2])>0)=True) AND ((InStr([FIELDNAME],[F3])>0)=True)) OR (((tbl_Source.F3) Is Null) AND ((InStr([FIELDNAME],[F1])>0)=True) AND ((InStr([FIELDNAME],[F2])>0)=True)) OR (((tbl_Source.F2) Is Null) AND ((tbl_Source.F3) Is Null) AND ((InStr([FIELDNAME],[F1])>0)=True))
    ORDER BY tbl_Source.F1, tbl_Source.F2, tbl_Source.F3;
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Thank you, Vlad:

    As recommended, I replaced Group By with Distinct.

    Cheers,
    Tom

  10. #10
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad:

    Forgive for asking a follow-up question. I love the current approach; I realized though that I **may** need to slightly modify the method for catching all possible fieldnames in the target table.

    - Per the attached JPG, I have included only one (1) record -- for testing only -- in the source table.
    - This single record contains 2 keywords.
    - Now, in the target table, there are 5 records where ID #1 and #1 include BOTH keywords but in different orders (within the fieldname).
    - Next, record #3 doesn't include either keyword.
    - Finally, record #4 and $5 include one of the 2 keywords.

    At the present time, the query correctly picks up record #1 and #2.

    To further open the aperture, I'm wondering if there's a way to have a secondary query that allows show all 4 records (1, 2, 4, 5). Is that doable? If so, how?

    Thank you in advance for the continued help!

    Tom
    Attached Thumbnails Attached Thumbnails NewQuery.JPG  
    Attached Files Attached Files

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tom,

    A little different format

    id F1 F2 F3 SCHEMA FIELDNAME Match_ON
    1 DATE DEFICIENCY
    COMMONDATA AFSAS_DEFICIENCY_DATE DATE, DEFICIENCY
    2 DATE DEFICIENCY
    COMMONDATA AFSAS_DATE_DEFICIENCY DATE, DEFICIENCY
    4 DATE DEFICIENCY
    COMMONDATA AFSAS_INJURY_DATE DATE
    5 DATE DEFICIENCY
    COMMONDATA AFSAS_DEFICIENCY DEFICIENCY


    Using query

    Code:
    SELECT tbl_Target.id, tbl_Source.F1, tbl_Source.F2, tbl_Source.F3, tbl_Target.SCHEMA, tbl_Target.FIELDNAME
    ,iif(instr(FieldName,f1)>0 and (instr(fieldname,f2)>0),f1 &", " & f2,0) as Match_ON
    FROM tbl_Source, tbl_Target
    WHERE
    iif(instr(FieldName,f1)>0 and (instr(fieldname,f2)>0),f1 &"," & f2,0) 
    
    UNION
    SELECT tbl_Target.Id,tbl_Source.F1, tbl_Source.F2, tbl_Source.F3, tbl_Target.SCHEMA, tbl_Target.FIELDNAME
    ,iif(instr(FieldName,f1)=0 and (instr(fieldname,f2)>0),f2,0) as
     Match_ON
    FROM tbl_Source, tbl_Target
    WHERE
    iif(instr(FieldName,f1)=0 and (instr(fieldname,f2)>0),f2,0)
    
    UNION
    SELECT tbl_Target.Id,tbl_Source.F1, tbl_Source.F2, tbl_Source.F3, tbl_Target.SCHEMA, tbl_Target.FIELDNAME
    ,iif(instr(FieldName,f1)>0 and (instr(fieldname,f2)=0),f1,0) as Match_ON
    FROM tbl_Source, tbl_Target
    WHERE
    iif(instr(FieldName,f1)>0 and (instr(fieldname,f2)=0),f1,0)
    Last edited by orange; 01-27-2022 at 05:34 PM. Reason: separate the union queries for readability

  12. #12
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Jack -- I like it! I LIKE IT A LOT!!

    Thank you for offering an alternative method that achieves the revised requirement.

    W/ your permission, please allow me to ask a *forward-leaning* question. As you are fully aware, this version includes 3 keywords (F1, F2, F3). Again, it works great!

    There a chance, however, that I may need to include a 4th keyword (F4) some time in the future. Given I can't view the SQL in design mode (UNION query), what lines in the SQL would have to be added/edited to accommodate a 4th keyword (F4) while preserving the same method of operations?

    Thanks,
    Tom

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I set up a test with 3

    Modified Source as
    ID F1 F2 F3
    1 DATE DEFICIENCY REVIEW

    and added #6 in Target

    ID SCHEMA FIELDNAME
    6 Test AFSAS_DEFICIENCY_REVIEW_DATE
    id F1 F2 F3 SCHEMA FIELDNAME Match_ON
    1 DATE DEFICIENCY REVIEW COMMONDATA AFSAS_DEFICIENCY_DATE DATE, DEFICIENCY
    2 DATE DEFICIENCY REVIEW COMMONDATA AFSAS_DATE_DEFICIENCY DATE, DEFICIENCY
    4 DATE DEFICIENCY REVIEW COMMONDATA AFSAS_INJURY_DATE DATE
    5 DATE DEFICIENCY REVIEW COMMONDATA AFSAS_DEFICIENCY DEFICIENCY
    6 DATE DEFICIENCY REVIEW Test AFSAS_DEFICIENCY_REVIEW_DATE DATE, DEFICIENCY
    6 DATE DEFICIENCY REVIEW Test AFSAS_DEFICIENCY_REVIEW_DATE DATE, DEFICIENCY, REVIEW

    I'll set up a test with 4 or more keywords. You say may need 4---but will you need 5 or 6? The approach could be different.

    This is the sql for the 3 test.
    Code:
    SELECT tbl_Target.id, tbl_Source.F1, tbl_Source.F2, tbl_Source.F3, tbl_Target.SCHEMA, tbl_Target.FIELDNAME
    ,iif(instr(FieldName,f1)>0 and instr(fieldname,f2)>0  and instr(fieldname,f3)>0 ,f1 & ", " & f2  & ", " & f3,0 ) as Match_ON
    FROM tbl_Source, tbl_Target
    WHERE
    iif(instr(FieldName,f1)>0 and instr(fieldname,f2)>0  and instr(fieldname,f3)>0 ,f1 & ", " & f2  & ", " & f3,0 )
    
    UNION
    SELECT tbl_Target.id, tbl_Source.F1, tbl_Source.F2, tbl_Source.F3, tbl_Target.SCHEMA, tbl_Target.FIELDNAME
    ,iif(instr(FieldName,f1)>0 and (instr(fieldname,f2)>0),f1 &", " & f2,0) as Match_ON
    FROM tbl_Source, tbl_Target
    WHERE
    iif(instr(FieldName,f1)>0 and (instr(fieldname,f2)>0),f1 &"," & f2,0) 
    
    UNION
    SELECT tbl_Target.Id,tbl_Source.F1, tbl_Source.F2, tbl_Source.F3, tbl_Target.SCHEMA, tbl_Target.FIELDNAME
    ,iif(instr(FieldName,f1)=0 and (instr(fieldname,f2)>0),f2,0) as
     Match_ON
    FROM tbl_Source, tbl_Target
    WHERE
    iif(instr(FieldName,f1)=0 and (instr(fieldname,f2)>0),f2,0)
    
    UNION SELECT tbl_Target.Id,tbl_Source.F1, tbl_Source.F2, tbl_Source.F3, tbl_Target.SCHEMA, tbl_Target.FIELDNAME
    ,iif(instr(FieldName,f1)>0 and (instr(fieldname,f2)=0),f1,0) as Match_ON
    FROM tbl_Source, tbl_Target
    WHERE
    iif(instr(FieldName,f1)>0 and (instr(fieldname,f2)=0),f1,0)

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tom,

    This is the SQL to find 4 keywords in any order in a Target

    Code:
    select 
    tbl_Target.id, tbl_Source.F1, tbl_Source.F2, tbl_Source.F3,tbl_source.f4, tbl_Target.SCHEMA, tbl_Target.FIELDNAME,
    iif(instr(FieldName,f1)>0 and instr(fieldname,f2)>0  and instr(fieldname,f3)>0  and instr(fieldname,f4)>0 ,f1 & ", " & f2  & ", " & f3 & ", " & f4,0 ) as Match_ON
    FROM tbl_Source, tbl_Target
    WHERE
    iif(instr(FieldName,f1)>0 and instr(fieldname,f2)>0  and instr(fieldname,f3)>0  and instr(fieldname,f4)>0 ,f1 & ", " & f2  & ", " & f3 & ", " & f4,0 )
    And it gets more complex from here, since you must accommodate all possibilities for 3 keywords True out of 4
    123,234,134

    and then 2 keywords out of 4
    12,23,34,13,14,24


    This is the SQL to find 3 keywords (any 3 of the 4 identified in the Source) This is for F1 and F2 and F3 only. You need similar code for the other arrangements

    Code:
    UNION
    select 
    tbl_Target.id, tbl_Source.F1, tbl_Source.F2, tbl_Source.F3,tbl_source.f4, tbl_Target.SCHEMA, tbl_Target.FIELDNAME,
    iif(instr(FieldName,f1)>0 and instr(fieldname,f2)>0  and instr(fieldname,f3)>0 ,f1 & ", " & f2  & ", " & f3,0) as Match_ON
    FROM tbl_Source, tbl_Target
    WHERE
    iif(instr(FieldName,f1)>0 and instr(fieldname,f2)>0  and instr(fieldname,f3)>0 ,f1 & ", " & f2  & ", " & f3,0)

  15. #15
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Jack -- I am 99% confident that I won't need to go beyond 4 keywords. So, having a scenario that would show all 4 keywords in the MATCH_ON would be great.

    Btw, the latest SQL with the 3 keywords is already very powerful. In your test, ID #6 is found twice where MATCH_ON:
    DATE, DEFICIENCY
    DATE, DEFICIENCY, REVIEW

    Now, the listing of both records for ID #6 is correct; however, the 1st instance (with only 2 keywords) is somewhat redundant as it is inherited in the 2nd instance (with 3 keywords). Is there an easy solution to only include one (1) per record ID... the one with the largest amount of keywords (i.e., 3 in this case)? If not, that'll be okay too... 'just wanted to double-check in case there was an easy solution.

    Thank you.

    //

    After posting this, I just noticed your post #14. I'll check it out. Do both forum code sections have to be joined together for the 4th keyword? I'm asking as one references 4 keywords and the other code section references 3 keywords. Sorry for the confusion.


Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 44
    Last Post: 04-05-2021, 07:11 AM
  2. Replies: 8
    Last Post: 02-28-2014, 10:27 AM
  3. inventory system transaction form fields
    By xtrareal22 in forum Forms
    Replies: 2
    Last Post: 11-26-2013, 01:06 PM
  4. Replies: 2
    Last Post: 04-12-2011, 10:14 AM
  5. Set command problem in legacy system
    By ch875299 in forum Programming
    Replies: 8
    Last Post: 02-02-2010, 04:59 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums