#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2016
    Posts
    4
    Rep Power
    0

    Help with a Loop


    I have a table of Dates and Names that looks like this:

    Code:
    MONTH | YEAR | NAME(S) | ALL POSIBILITIES
    January | 2016 | "Henry" | <Null>
    January | 2016 | "Henry, Frank" | <Null>
    January | 2016 | "Henry, Paul" | <Null>
    January | 2016 | "Frank" | <Null>
    January | 2016 | "Billy " | <Null>
    May | 2016 | "Henry" | <Null>
    May | 2016 | "Henry, Frank" | <Null>
    May | 2016 | "Frank" | <Null>
    May | 2016 | "Billy" | <Null>
    I need to make the table look like this:
    Code:
    January | 2016 | "Henry" | "Henry, Frank"; "Henry, Paul"
    January | 2016 | "Henry, Frank" | "Henry, Frank"
    January | 2016 | "Henry, Paul" | "Henry, Paul"
    January | 2016 |"Frank" | "Henry, Frank"
    January | 2016 | "Billy" | "Billy"
    May | 2015 | "Henry" |"Henry, Frank"
    May | 2015 | "Henry, Frank" |"Henry, Frank"
    May | 2015 | "Frank" |"Henry, Frank"
    May | 2015 | "Billy" | "Billy"
    Essentially what I need is, if the string of NAMES of row A, is found within the string of NAMES of row B (of the same Month|Year), then add row B names to the "ALL" column or row A.

    For example, starting at top of table, "Henry" (first record) is found within "Henry, Frank" (second record), therefore, "Henry, Frank" is listed in the "ALL" column.

    Then, "Henry" (first record) is also found in "Henry, Paul" (third record), so "Henry, Paul" is added to the "ALL" column (using semi-colon).

    Then, "Henry" (first record) is NOT found in the string "Frank" (fourth record), so nothing is added. (Same when comparing record 1 and 5).

    Record 6 has a change in Date, thus ending the loop (but beginning another loop??)

    After comparing record 1 to records 1-to-5, we then compare record 2 to records 1-to-5, then 3 to 1-5, etc, until the Date changes, which triggers a new search (i.e., compare record 6 to records 6-to-9, then record 7 to records 6-to-9, and so on.

    The "embedded" loops is driving me nuts.... can someone offer some help?? It would be much appreciated....
  2. #2
  3. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,553
    Rep Power
    4549
    Was this a visual basic question somehow?
    ======
    Doug G
    ======
    I've never been able to appreciate the sublime arrogance of folks who feel they were put on earth just to save other folks from themselves .." - Donald Hamilton
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2016
    Posts
    4
    Rep Power
    0
    Originally Posted by Doug G
    Was this a visual basic question somehow?
    Yes. I'm using visual basic within Access for the coding.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2016
    Posts
    4
    Rep Power
    0
    I got it figured out. I ended up using a few embedded loops along with boolean triggers. Seems a bit sloppy, but it works.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2016
    Posts
    4
    Rep Power
    0
    it was definitely much more complicated than I first thought it would be.
  10. #6
  11. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,553
    Rep Power
    4549
    Originally Posted by dcarguy
    I got it figured out. I ended up using a few embedded loops along with boolean triggers. Seems a bit sloppy, but it works.
    Glad to hear you have a solution.
    ======
    Doug G
    ======
    I've never been able to appreciate the sublime arrogance of folks who feel they were put on earth just to save other folks from themselves .." - Donald Hamilton

IMN logo majestic logo threadwatch logo seochat tools logo