Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 39
  1. #16
    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
    Do both forum code sections have to be joined together for the 4th keyword?



    That's not really the issue. The first code section shows what is required to select Targets with all 4 of the source words.
    The second code section show the method to find Targets with 3 of the 4 source words. In fact that code is only for F1 and F2 and F3 being found. Additional code is needed to deal with F2 F3 F4 and another for F1 F3 F4.
    Then more code for the 12,23,34,13,14,24.

    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)?
    There are ways. eg capture the number of keywords found, sort the records by number of keywords descending and save the records to a table that has unique target_id records. Only first record per ID would be saved.
    I'm sure there are other ways, but that is first thought.

    The SQL isn't that difficult, but it is repetitive and real easy to drop a comma etc (error prone). It may be something that could be generated with a program, or could use a function and recordsets to eliminate the UNION queries. With a recordset, you could skip to the next Target if you found the max number of keywords in current Target. For example, if a target has 4 keywords, it also has 1, 2 and 3 keywords.

    But if it's a 1 time thing the Union queries may be sufficient.

    You have up to 4 keywords and you search each of the Target strings and determine if that string has 0,1,2,3 or 4 keywords.

    For 4 keywords, all 4 source words have to be in the Target
    For 3 keywords, any 3 source words have to be in the Target. Any 3 since order doesn't matter.
    For 2 keywords, any 2 source words have to be in the Target. Any 2 since order doesn't matter.
    For 1 keyword, any 1 source word has to be in the Target. Any 1.
    You ignore Targets that don't have any keywords.

  2. #17
    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 and Jack,

    Here is an alternative to do that, might be easier to maintain, just follow the pattern to add fields and expressions in the preliminary query then update the "Matches" field in the final query (up to six fields as an example).

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

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

    I appreciate both of your feedback. To compare "apples with apples" (so to speak), I have plugged in the same exact source and target tables into either of your solution(s).

    Based on this very limited dataset, Vlad's query returns twelve (12) records while Jack's query returns nine (9) records.

    Now, before I go on, I want to emphasize that I do NOT want to end up with a scenario (in this open forum) where "Solution of Person XYZ" is viewed better than the one of the other person. Instead, I merely would like to determine the correct # of records where:
    a) I didn't miss any potential mapping assignments, or
    b) I didn't inflate the outcome by including dups/inherited records (e.g., if record is found with 3 keywords, I then don't need to show same record with 2 keywords or 1 keyword).

    If I don't have a good answer to the 2 scenarios above (not enough records OR too many records) based on this VERY REDUCED data set, one can only imagine the difference when including hundreds of fields in the source table and thousands of fields in the target table.

    'nuff said...

    Ok, please find attached DB with the reduced source table (2 records) and target table (8 records). As aforementioned, "qry02_Approach#1_Results" outputs 12 records while "qry11_Approach#2" outputs 9 records.

    Again, without making this a *competion* who did a better "job", allow me recap what I like about the 2 different approaches:

    Approach #1 (resulting in 12 records):
    - It is easier to maintain (e.g., adding additional keyword layers)
    - It is assumed that the more keywords are found, the better the match.
    - Thus, I could filter on, e.g., [F1] = "Estimated" AND then look at [MATCHES]. In this case, if I apply filter on the max number = "3", I find a perfect match.

    Approach #1 (9 in records):
    - The [MATCH_ON] is great. It spells out which keywords were found. This can be very helpful.
    - Thus, the string with the most number of keywords should be considered the best solution (i.e., perfect match).

    Both approaches are great!! At this time, however, I merely need to determine whether "9" or "12" is the desired answer. Finding not enough records is not good; while finding too many records (incl. repeats that are inherited in matches with fewer keywords) should be mitigated as well.

    Thoughts/recommendations?

    Cheers,
    Tom
    Attached Files Attached Files

  4. #19
    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
    Vlad,
    Very nice and easier to maintain.

  5. #20
    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,

    I think it comes down to what exactly do you want/need.
    If you take the queries and adjust to sort descending on number of matches, and append to a table that has Target_Id as a indexed, no dups, I think you'd end up with 7 records in the table. But I don't know if that would meet your requirement.
    Do you need 9 or 12 OR is 7 the bottom line?
    The queries Vlad has provided are easier to maintain and can handle up to 6 keywords. I did not deal with 6 keywords and it would be somewhat cumbersome/tedious to get the sql for all combinations.

    Here is what I foresee as the records that would result in the suggested table. The greyed out rows would be eliminated based on the No Duplicate index.

    Based on query:
    Code:
    SELECT [qry01_Approach#1_Preliminary].tbl_Target.ID, [qry01_Approach#1_Preliminary].[tbl_Source.ID] AS Expr1, [qry01_Approach#1_Preliminary].F1, [qry01_Approach#1_Preliminary].F2, [qry01_Approach#1_Preliminary].F3, [qry01_Approach#1_Preliminary].SCHEMA, [qry01_Approach#1_Preliminary].TARGET_FIELDNAME, Abs([MatchF1]+[MatchF2]+[MatchF3]) AS Matches
    FROM [qry01_Approach#1_Preliminary]
    WHERE (((Abs([MatchF1]+[MatchF2]+[MatchF3]))<>0))
    ORDER BY Abs([MatchF1]+[MatchF2]+[MatchF3]) DESC;


    ID Expr1 F1 F2 F3 SCHEMA TARGET_FIELDNAME Matches
    6 1 ESTIMATED COMPLETION DATE COMMONDATA AFSAS_ESTIMATED_COMPLETION_DATE 3
    5 1 ESTIMATED COMPLETION DATE COMMONDATA AFSAS_ESTIMATED_COMPLETION 2
    4 2 DATE DEFICIENCY
    COMMONDATA AFSAS_DEFICIENCY_POC_DATE 2
    3 2 DATE DEFICIENCY
    COMMONDATA AFSAS_DEFICIENCY_DATE 2
    1 2 DATE DEFICIENCY
    COMMONDATA AFSAS_DATE_DEFICIENCY 2
    7 2 DATE DEFICIENCY
    COMMONDATA AFSAS_INJURY_DATE 1
    7
    1 ESTIMATED COMPLETION DATE COMMONDATA AFSAS_INJURY_DATE 1
    6
    2 DATE DEFICIENCY
    COMMONDATA AFSAS_ESTIMATED_COMPLETION_DATE 1
    4
    1 ESTIMATED COMPLETION DATE COMMONDATA AFSAS_DEFICIENCY_POC_DATE 1
    3
    1 ESTIMATED COMPLETION DATE COMMONDATA AFSAS_DEFICIENCY_DATE 1
    2 2 DATE DEFICIENCY
    COMMONDATA AFSAS_DEFICIENCY 1
    1
    1 ESTIMATED COMPLETION DATE COMMONDATA AFSAS_DATE_DEFICIENCY 1


    Based on query:
    Code:
    SELECT *
    FROM [qry11_Approach#2]
    ORDER BY len(match_on) DESC;

    ID F1 F2 F3 SCHEMA TARGET_FIELDNAME MATCH_ON
    6 ESTIMATED COMPLETION DATE COMMONDATA AFSAS_ESTIMATED_COMPLETION_DATE ESTIMATED, COMPLETION, DATE
    6
    ESTIMATED COMPLETION DATE COMMONDATA AFSAS_ESTIMATED_COMPLETION_DATE ESTIMATED, COMPLETION
    5 ESTIMATED COMPLETION DATE COMMONDATA AFSAS_ESTIMATED_COMPLETION ESTIMATED, COMPLETION
    4 DATE DEFICIENCY
    COMMONDATA AFSAS_DEFICIENCY_POC_DATE DATE, DEFICIENCY
    3 DATE DEFICIENCY
    COMMONDATA AFSAS_DEFICIENCY_DATE DATE, DEFICIENCY
    1 DATE DEFICIENCY
    COMMONDATA AFSAS_DATE_DEFICIENCY DATE, DEFICIENCY
    2 DATE DEFICIENCY
    COMMONDATA AFSAS_DEFICIENCY DEFICIENCY
    7 DATE DEFICIENCY
    COMMONDATA AFSAS_INJURY_DATE DATE
    6
    DATE DEFICIENCY
    COMMONDATA AFSAS_ESTIMATED_COMPLETION_DATE DATE

    Just some thoughts for consideration.

  6. #21
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Jack -- starting with a fresh cup of coffee this morning, I had another view of the 2 different approaches. And, I agree, you're absolutely right... it depends on the requirements. So, in my view, there's no "right or wrong" with respect to the 2 different approaches.

    The attached PPT illustrates/summarizes the findings (approach #1) with a modified test data set, where
    - Slide #1 summarizes the data setup
    - Slide #2 summaries the findings of the "results" query for each of the three (3) original source's fieldnames.

    ... and, ultimately, slide #2 illustrates you what mentioned... "it depends on the requirements". For example, [SOURCE_FIELDNAME] = "T1" is found 3 times. All 3 answers are correct. It depends on the customer's choice for mapping to the preferred field.

    //

    I'll have another look at what you posted in post #20... I certainly appreciate the additional comments/recommendations.

    All in all, I have 2 fantastic approaches... so, again, Vlad & Jack -- THANK YOU FOR TAKING TIME TO OFFER SOME FANTASTIC CHOICES!!!
    Attached Files Attached Files

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

    You're very welcome Tom. I think that maybe the best approach for using the queries is to embed them in a main form (based on tblSource)\subform (based on the results queries) type interface where you would navigate the main form one record at the time and see the matching suggestions on the bottom. That way you wouldn't be concerned about "counts" and "duplicates" as what you'd see would match the current tblSource record. To actually use that productively you' need to add to the tblSource table a field holding the original field name with the space(s) and another empty field bound to a combo to select the newly "matched" field from the list below (just set the combo's row source to be the same as the subform taking the current source ID as a parameter and requery it in the Enter or GotFocus event). To improve it even more maybe bind the main form to a new query that filters out the already matched records (or add a filter button to do that)....

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

  8. #23
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Excellent suggestion, Vlad... I'll definitely look into creating a form/subform IOT facilitate the selection process even more efficiently.

    I'll keep you posted once I come up w/ a potential option for the form(s).

  9. #24
    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, Vlad

    I haven't looked at the latest materials. I was thinking about fieldnames with key/strings and it reminded me of an approach we used to use to match Company names. We dealt with 50-60000 Companies who had names and addresses English and French as well as Alias and alternate names, along with Location Address, Mailing Address.
    In order to ensure we were dealing with a specific registered company, we had some algorithms and weights to assist. As I recall (sort of [18 yrs later]), we took the major strings in names, addresses and removed noise words. We then took an incoming call/email an to ensure it really was the company, we compared the names/ strings they provided with what we had recorded. That enabled phone confirmation of the info and a basis for updating company/product/service/contact details.

    I have created a similar approach based on my earlier post #20.

    -create a table to contain unique source words
    -create a table to contain target field components
    -populate the tables (I used a routine called Checkit)
    -create query to match and count target component words with unique source words
    -create a second query (based on first) and show target fields and matching components

    I am attaching the database. If you look in module71 , you'll see what was done to populate the tables.

    This is result of second query and could work with multiple source words.

    The second query is MatchesInTargetFieldNameQ

    TargetID TARGET_FIELDNAME matches
    1 AFSAS_DATE_DEFICIENCY DEFICIENCY , DATE
    2 AFSAS_DEFICIENCY DEFICIENCY
    3 AFSAS_DEFICIENCY_DATE DATE , DEFICIENCY
    4 AFSAS_DEFICIENCY_POC_DATE DATE , DEFICIENCY
    5 AFSAS_ESTIMATED_COMPLETION COMPLETION , ESTIMATED
    6 AFSAS_ESTIMATED_COMPLETION_DATE DATE , COMPLETION , ESTIMATED
    7 AFSAS_INJURY_DATE DATE

    Update: This shows the target records with max matches, not the repeats.
    I think this could be adapted and used with a form depending on final requirement.
    Attached Files Attached Files
    Last edited by orange; 01-29-2022 at 11:31 AM. Reason: spelling

  10. #25
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Jack -- I very much appreciate the additional objects/modules. I'll take a look later on today (and/or tomorrow)... I'll check out how to integrate any of proposed options into the current scenario. Will keep you posted.

    Thanks for the continued help.

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

    Per post #22, I've taken your advice and started creating an Access form which -- I hope -- will eliminate the need for exporting the query's data into MS-Excel format.

    At this time, I've run into a small stumbling block (on subform) where the data source -- as suggested -- is linked query "qry02_Approach#1_Results".
    Before I get into the subform issue, it may be wise to share the current process.

    1. Database:
    - Contains an extremely reduced data set for demo purposes.
    - Table "tbl_Source" includes 9 records where field [SECTION_NUM] includes three (3) sections.
    - Table "tbl_Target" includes 20 sample records.
    - Naturally, I don't need to cover any details on the queries... you designed them.

    2. Current Process:
    - After identifying the appropriate keywords (F1, F2, F3) in "tbl_Source", I run the 2nd query.
    - Given this limited example data set, it returns 54 records.

    ... and now, I need to get off subject for a moment. There's a reason for this though.

    3. Work Environment:
    - Due to the COVID-19 pandemic, we heavily use SharePoint for collaboration purposes (when tele-working).
    - As part the SharePoint collaboration, my team prefers to use Excel where multiple team members can modify and update an XLSX document simultaneously.
    - We really haven't tried simultaneous collaboration in Access ** on SHAREPOINT ** ... but that a whole different topic.
    - Anyhow, due to the user preference for Excel -- for this data mapping activity at least -- I now export the 54 query results into an Excel spreadsheet. See attached.

    4. Identification of Candidate Fields (in Excel):
    - I use the following columns to filter IOT to limit the records for review.
    - Column B "SECTION_NUMBER"
    - Column D "SORT_ID"
    - For example, I filter for "1" (column B) and "1" (column D). I now have 7 records where [SOURCE_FIELDNAME] = "Inspection ID" and keywords have been placed in columns H:J.
    - Next, I use column O ("CANDIDATE") to mark what I believe is the most suitable "TARGET_FIELDNAME" (column L).
    - Finally, given that I placed an "x" into cell O4, I selected my name from the drop-down menu in column P. The latter helps to determine what team members evaluated/reviewed the information.

    Alright... and here's now the challenge in Access (if I were to eliminate Excel from this process).

    - As indicated in the last 2 steps of #4. "Identification of Candidate...", I used column O to place an "x" into the appropriate cell.
    - This is followed by selecting the POC in column P.
    - Now, no big deal in Excel; however, I'm not sure how to integrate the "CANDIDATE" and "POC" equivalent in my Access subform given the subform's rowsource is the query and I cannot make any changes.

    So, now back to Access...

    a. I followed your recommendation and created a main form (linked to tbl_Source).
    b. I then added a subform and linked it to the 2nd query.
    c. At the present time, the form navigation already far exceeds all of the filtering requirements in Excel.
    d. I added all fields except the [CANDIDATE] and [POC] field.

    My question:
    How can I mimic the Excel process of marking a candidate with an "x" (Boolean field) and selecting the POC given that my Access subform doesn't allow me to make changes?
    Attached Files Attached Files

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

    Quick update on post # 26... the attached (updated) DB includes the following changes:

    1. Changed subform from "datasheet" view to "tabular" view.
    2. On subform, added conditional formatting to graphically illustrate the best match (max number).
    3. Added "OnChange" events to top right keyword fields {F1, F2, F3}.
    3a. For example, in record # 6 (out of 9), keyword [F3] = "Control" and it shows 3 records in the subform.
    3b. However, if I change keyword from "Control" to "Controls", it drops down to only 2 records as [TARGET_FIELDNAME] = "REC_CONTROL_NUMBER" is no longer a match.

    All above works great... I still haven't figured out a way to add field [CANDIDATE] (data type YES/NO) and [POC] (combobox) to the subform though.

    Unfortunately, w/o the ability to identify the "best fit" (e.g., check/mark the best candidate for mapping) while also selecting who (POC) identified the candidate, this form won't be useful for the team.

    Any recommendations as to how I integrate those 2 missing fields [CANDIDATE] & [POC] in the subform given it's record source is the query (and doesn't allow me to change values)?

    Thanks,
    Tom
    Attached Files Attached Files

  13. #28
    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 am going skiing now so won't be able to get back until later today but the simple answer is to do it in the tblSource, please see attached.

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

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

    Wow... have fun skiing!

    Unfortunately, this proposed method *may* not work. That is, as part of the mapping review, the POC may want to identify *MULTIPLE* CANDIDATES given he/she can't be entirely certain which is truly the best.

    So, the idea is to mark any viable candidates (hopefully) in the subform... then, we'll run a query on every record where [CANDIDATE] boolean value = TRUE. At that point, the team can review all selected candidates AND THEN make a firm decision.

    While the proposed method maybe be ok as a work-around, the limitation to identify multiple candidates is not ideal.

    If you can think of another option at some point, I'd welcome any recommendations. HAVE FUN SKIING!!

  15. #30
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad... sorry for the many responses.

    I think I got it... I'll merely create up to 4 [CANDIDATE] fields such as [C1, C2, C3, C4] in table tbl_Source. Then, the user will pick up to 4 candidates in priority order. Seems like a viable work-around.

    Thanks for making the suggestion about adding the info directly in the source table. Excellent idea!!

Page 2 of 3 FirstFirst 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