Page 3 of 3 FirstFirst 123
Results 31 to 45 of 45
  1. #31
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Jack -- oh, and I was under the impression the latest version had only DISTINCT values. Well, glad you had an opportunity to have another look at the process. Would you please be willing to post the update version of the LK table generator DB?



    Also, anything that would streamline/assist in the mapping process would be beneficial. So, if I could shave off 50% of the mapping that would be great. So, for this one as well, I'd have to see how that tool works... but I'd be thrilled to give it a whirl if you could share it the "search routine". Thank you.

  2. #32
    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
    Just heading out again. I think the issue I saw with the duplicates was because I dropped the original fieldname PK, and added ID autonumber PK. So when I did load data, it was distinct going into the LK table, but wa accepted sine the ID was PK. So the unique index resolved it. I added the Target_value column to the LK_InJury table. I was looking at the mapping set up and I think it could save some time, BUT it needs refinement/logic/coding to get things working properly.
    This is a rework of this.

    Enter a word(s) or fragment(s) -comma separated and SEARCH. I include default Other.
    Returns all matches in the LK and Target table.
    Select 1 or more LK records or Select ALL(and remove 1 or 2...), then click Map
    Records added to the LK table with a message of how many mappings were done.

    Haven't worked out how to bring in specific tables - that would be an LK and the corresponding Target - assuming your naming convention is consistent. Need more info on table names of targets.

    It's a work in progress -still has lots of debugging info within. Sort of quasi- automatic.

    Thoughts, comments, refinements...



    Click image for larger version. 

Name:	MapToolV1.PNG 
Views:	42 
Size:	34.2 KB 
ID:	44848
    Last edited by orange; 03-28-2021 at 10:01 AM. Reason: spelling

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

    As denoted in my original subject line, I was fully aware this would be a "challenging" scenario. Apparently, that hunch turns out to be true.

    I'm (kinda) losing the bubble on this item now. Maybe I don't know enough how this proposed "Universal Search MOD4LK" would actually work, it appears there's "too much going on". Again, not having seen how this exactly will work, I'm not entire certain about the proposed process.

    I keep stressing that I need to find candidate *target* value items. So, if I were to enter "Other,fract", I really don't need to see matching values from the "LK_INJURY_TYPE" table such as ID 16, 41, 60, 61, 104.

    At the same time, if I'm not familiar with the existing value in the source table (LK_INJURY_TYPE), how would any know that "Other,fract" are keywords (based on existing records) I should enter.

    So, *conceptually* speaking, I'm always a fan of, e.g., drop-down menus. Thus, for now, I could envision a more natural flow follows the process listed below:
    1. I have a form w/ a drop-down menu.
    2. The drop-down menu is automatically populated with all LK tables names. So, in this case, it would show ~70 LK table names.
    3. Next, the user picks "LK_INJURY_TYPE".
    4a. Based on the 3, a secondary drop-down is now populated with all distinct value [INJURY_TYPE].
    4b. Alternatively, if user would have selected LK_OCCUPATION, the secondary combo would dislay 23 values (ranging from "Actor" to "Veterinarian" -- see other post which generated the various LK tables).
    4c. Similarly, if LK_EDUCATION was picked in the 1st combo, I then would see 6 values in the 2nd combo.
    5a. Now, in the event LK_INJURY was selected, I now can pick any of the associated distinct source values.
    5b. Then, if I'd pick "Fractured or broken bone" (ID 61), I could imagine I then would enter "fract" into the search box.
    6. If so, the form will only display one matching record (ID 17 or "Fracture").

    Right now, it appears I get 11 matching records from both source and target. Off the bet, I'm "convaluding the picture" by including source values when I really only need to foucs on target values. But even within the 6 target values, the JPG show 5 too many records given that only 1 out of 6 includes the word "fract". I know this is based on "other" and "fract" I really would like to keep it simple for now.

    So, for sake of arguments, let's say I picked source record #61 "Fracture or broken bone" and type in "fract" into the search box, I would expect to see only target record #17 "Fracture" to show. And finally, if decided that "Fracture or broken bone" = "Fracture", I click "Map" or "Associate" or "Link" and, voila "Fracture" is now added into the field "FieldValue" of the LK_Table.

    At that point, one association has been made the the number of values (in 2nd combo box) should go down by 1.

    I know that this conceptual process is very high-level, but in a gist that's really it. ID a source value, pick a keyword, search for keyword... if there's a match, link records.

  4. #34
    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 know that this conceptual process is very high-level, but in a gist that's really it. ID a source value, pick a keyword, search for keyword... if there's a match, link records.
    As for the "Universal Search MOD4LK", this is based on a form and routine that looks for matching terms in any field in any table of the current database. Since, for each LK* table there is a corresponding tbl_Target table, Iadjusted the code to use only these 2 tables from which to search/find matching terms.The terms to match can be words or fragments separated by a comma. This tool is dealing with potential matches in 2 tables and should be repeatable/reusable for each pair of LK and tbl_Target tables. Again this is a work in progress based on an adaptation of an older utility.

    I created another form based on the tbl_Target that displays the TargetID and TargetField. Dbl click the TargetField and the value is parsed**( if multiple words and word is >3 characters) the words separated by commas are placed into the Search and the search is invoked.
    The results of the search are placed in a listbox in alphabetic order. So LK table records with matching terms if any exist are listed above the tbl_target record(s).

    At this point: The Field value of the TargetField which has been parsed is used for searching/matching.
    For example: Atrophy
    -If no LK* record is found, then there is NO MATCH for this Target_Field value (eg. Atrophy)
    -If only 1 LK record and 1 target record are found, then this is an AUTOMATCH and the matched record is written.
    -If only 1 or many LK record(s) and multiple tbl_Target records are found, then you can select the LK record(s) and 1 tbl_target record and click the MAP button to write the match record(s)
    -If many lk* records and 1 or more tbl_target records are found, you can select the LK records,or click the Select All button and keep all or deselect individual LK records, then select the tbl_target record and then click the MAP button and the matching records are written.

    The process is go to the next record in the tbl_target Field in the form and repeat the above procedure.

    **When parsing the tbl_target Field value:
    - replace any "/","","-","(" or ")" with a space
    - remove any trailing single "s" to avoid singular vs plural forms

    That is basically the process/logic. It does not follow your combo boxes, but perhaps the logic can help with your efforts.

    In my testing I created what I call a session table to log activity. It allows identifying the Target field value (Term(s)) with NO MATCH in the old data. I updated the session table design to include the name of the associated tbl_Target name. This could allow the same session table to be used for mapping the various LK* and tbl_target tables.


    I mocked up bringing in the pairs of Lk and related tbl_Target tables by linking specific tables from a different database.
    I renamed the tables I had to exclude them from further matching/mapping. These steps can be added to simplify the overall procedure.

    In the attached Pics there are some screen shots. I used the SampleTargetSearch.... set up for my testing.

    As I said this is a work in progress based on a tool(Modified Universal Search) I created about 6 years ago. It wasn't intended for record matching/mapping. But it might do most of what you need. Again, you know your environment and requirement better than any reader.
    Attached Files Attached Files
    Last edited by orange; 03-30-2021 at 11:48 AM. Reason: spelling, additional info

  5. #35
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Jack -- this tool looks VERY PROMISING!!! Thank you for your continued assistance!!

  6. #36
    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,

    Can you confirm that the LK files and all the tbl_Target... tables have consistent names throughout.

    I have tested my sample to do a link to tables in a database of LK and tbl_Target.... via vba

    I have to do some coding to:

    Linking/Relinking:
    To isolate the mapping of table pairs there is a
    practicality to put the LK and Target tables in a different database
    and just link them when it's their turn to be mapped.
    This allows the mapping routine to be reusable easily. (tested and works generally)

    Also need to change the recordsource of the frm..Target and caption of the form

    set up a routine/button to support :
    -finished with 1 pair of table mappings
    -link to next pair
    -change form recordsource and caption

    -Other requirements/would like to have??

  7. #37
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Jack... I'm feeling very excited about the potential "tool".

    1. Yes, all lookup tables have the same prefix "LK_" such as "LK_INJURY_TYPE" or "LK_OCCUPATION".
    2. And, yes, all target tables will also have the same prefix of "tbl_TARGET_" such as "tbl_TARGET_INJURY_TYPE" or "tbl_TARGET_OCCUPATION".

    Let me know if you require additional information.

    Cheers,
    Tom

  8. #38
    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
    Refined the work in progress.

    This is a draft Mapping Tool to map values in LK* tables to Target Values.
    This uses Injury type and a mock up of Education which has phoney values and uses a copy of Target Injury for Target Education.
    I have the LK and Target tables in a backend database. c:\users\jack\documents\TestLFasSLinked.accdb
    As described previously, mappings are recorded in the LK table. In addition I have a Session table in the Tool to log all mapping activities.

    Procedure:
    Open frmStartUp on opening Database.
    Enter the Backend database name (where the LK and Target tables are stored. Click Start.
    Select the LK and Target to Map. Click Continue.
    A form to hold Target values is shown-as well as the search/map form "frmForm.
    Dbl Click the Target field to get searc terms in "frmForm"
    CLEAR -- reset the listbox on frmForm.
    SELECT ALL - selects all LK records in the listbox
    SEARCH -invokes a search of matching fragments of the parsed Target Field Value.
    MAP- write the selected LK(s) and the Target Value to the LK table and a record in the Session table.

    HELP_Overview - Displays this page of info.

    This is set up such that this mapping tool is in a database. The front end.
    Session tbl is in the front end. (it could be in Backend).

  9. #39
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Jack -- sounds like you're making some great progress on the tool... I'm very excited.

    Was there supposed to be a demo ACCDB attachment?

  10. #40
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Jack -- see attached mapping.
    Attached Thumbnails Attached Thumbnails Mapping.JPG  

  11. #41
    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
    The tables XX_LK... and ZZ_tbl_Target... are backups of other tables. They can be deleted from the MapTool database.

  12. #42
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Jack -- ok... that makes sense now. I am using the sample DB that contains both LK and target tables. Once I clicked through the options, I got a little lost on the process. Ultimately, I ended up w/ an error (3061). While I noticed that process worked in your sample version, I'm somewhat afraid that the ordinary user will get lost in the process quickly.
    Attached Thumbnails Attached Thumbnails Error3061.JPG  

  13. #43
    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
    Here is what I get after the same steps with my test materials.

    Click image for larger version. 

Name:	AttemptRepeatTool.PNG 
Views:	20 
Size:	67.9 KB 
ID:	44887

    And related logging to immediate window:

    ......
    LK_INJURY_TYPE
    LK_Occupation
    tbl_TARGET_INJURY_TYPE
    List rowsource Copy Of tbl_TARGET_INJURY_TYPE;LK_Education;LK_INJURY_TYPE ;LK_Occupation;tbl_TARGET_INJURY_TYPE
    relink LK_INJURY_TYPE
    MS Access;PWD=;DATABASE=c:\users\jack\documents\TestL FasSLinked.accdb
    relink tbl_TARGET_INJURY_TYPE
    MS Access;PWD=;DATABASE=c:\users\jack\documents\TestL FasSLinked.accdb
    - tbl_TARGET_INJURY_TYPE
    - LK_INJURY_TYPE
    Total number of Linked Tables : 2
    Insert Into SessionTbl (TargetTableUsed,TargetId,TargetValue,OldID,OldVal ue,SessionDate,SessionUser, SessionComputer,AddlInfo) select 'tbl_TARGET_INJURY_TYPE', 1,'Amputation',0,'NULL',#02-Apr-21 9:20:09 AM# ,'Jack','DESKTOP-LL3QHD4',' NO DIRECT MATCH EXISTS'
    Update LK_INJURY_TYPE SET target_PK_FK = 1, target_value = 'Amputation' Where ID =0

  14. #44
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Jack -- maybe it's me, but I'm getting quite a bit of errors when running the forms.

    I put myself into the position of the analyst who doesn't know anything but is being "told" to follow the procedures (unbiased view). When clicking on "Ok" I'm getting the 1st error. And there are other errors as well.

    At this point, the mapping (manual) has nearly been completed. I had hoped to use the tool to expedite the process. No luck though.

    I cannot express enough my gratitude to help me in this effort. I know it took quite a few hours of your time.

    As originally mentioned, I considered this post as "challenging"... turned out to be true in this case.

    Cheers,
    Tom
    Attached Thumbnails Attached Thumbnails Error when click 'Ok' (onto Mapping).JPG  

  15. #45
    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
    ??? Interesting. If I try to supply the backend database name without the accdb extension, I get an error. I am surprised that you get a valid message --Selection OK...

    It appears that you have already linked the tables. If you attempt to link an already linked table(s), Access presents the error message that you are showing. When tables are linked, the names will show up in the Table list in the Navigation pane. If the tables are there, then the linkage process has already occurred. If you want to start afresh, then click on the Delete Links to Backend.

    Always difficult to visualize/understand issues via messaging and without specific steps. I have attached an updated graphic showing some additional functionality.

    If you have manually mapped all of your tables, then perhaps a tool wasn't really required.

    From my end, it's been an interesting diversion from TV and youtube.
    Attached Thumbnails Attached Thumbnails MapToolV2.PNG  

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

Similar Threads

  1. Replies: 53
    Last Post: 06-24-2021, 10:49 AM
  2. Replies: 18
    Last Post: 04-16-2014, 11:45 AM
  3. Replies: 9
    Last Post: 07-24-2013, 02:56 PM
  4. Replies: 0
    Last Post: 07-26-2011, 02:03 PM
  5. Replies: 0
    Last Post: 10-24-2010, 02:27 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