Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    keep 2 unrelated tables in sync (2000 All Updates)

    tblTrinity
    UniqueID (primary key)
    LastName
    FirstName
    LastName2
    FirstName2
    HouseNbr
    Street

    tblUCWMembers
    RecordID (primary key)
    LastName
    FirstName
    Address

    tblTrinity is the main table in the database. This table holds all of the members in the church. tblUCWMembers is a table of women who are members in the UCW.

    Here are the idiosyncracies…
    1. Some women, but only about 1 in 20, from tblTrinity will be in tblUCWMembers.
    2. Some in tblUCWMembers will not be in tblTrinity.
    3. If the woman in tblUCWMembers is in tblTrinity, she could be in tblTrinity as FirstName (if a single family unit, or the primary name in the household) or as FirstName2 (if she is the spouse of the primary name in the household)

    What I am looking for is the best method by which to keep the “Address” field in tblUCWMembers in sync with the combined “HouseNbr and Street” fields in tblTrinity, so that when those 2 fields in tblTrinity change the Address field in tblUCWMembers will also reflect the change without the database user needing to manually update.

    I realize that I may have to redesign tblUCWMembers to split the Address field into 2 separate fields of HouseNbr and Street…however my starting point is what exists now, and I am looking for the best method for keeping the 2 tables in sync.

    Any suggestion, or a push in the right direction, would be appreciated.

    Thanks.

    Tom

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: keep 2 unrelated tables in sync (2000 All Updates)

    I wouldn't try to update the Address field in tblUCWMembers, I'd simply ignore it for records that have a match in tblTrinity.

    If possible, I'd add a field UniqueID (presumably number, long integer) to tblUCWMembers, and fill it with the UniqueID of the matching record in tblTrinity if that exists; for other records UniqueID would remain blank. (I think matching on first and last name is tricky)

    Create a query based on tblUCWMembers and tblTrinity with a left join on UniqueID. Add all fields from tblUCWMembers to the query grid, then create a calculated field to get the address:
    <code>
    MailAddress: IIf(IsNull([UniqueID]),[Address],[HouseNbr] & " " & [Street])
    </code>
    Use this query as record source for forms and reports.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: keep 2 unrelated tables in sync (2000 All Updates)

    Hans
    Thanks for your reply.

    That solution only partially works.

    If I add a new person to tblUCWMermbers, even if she already exists in tblTrinity, what happens in the query is the UniqueID for her (and/or her husband if she is the spouse) doesn't pull in to the query. Of course, changing the join doesn't work either because then the new member in tblUCWMembers doesn't show up.

    Tom

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: keep 2 unrelated tables in sync (2000 All Updates)

    You'll have to enter the UniqueID in tblUCWMembers yourself.

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: keep 2 unrelated tables in sync (2000 All Updates)

    Hans
    Yep. Trouble is that causes more intervention on the user's part than manually inputting a different address, as the UniqueID is hidden in the form.

    Seems as if an Update query is the best approach. Following is the code that works.
    <code>UPDATE tblUCWMembers AS U INNER JOIN tblTrinity AS T
    ON (T.LastName = U.LastName AND T.FirstName = U.FirstName)
    OR (T.LastName = U.LastName AND T.FirstName2 = U.FirstName)
    OR (T.LastName2 = U.LastName AND T.FirstName2 = U.FirstName)
    SET U.Address = T.HouseNbr & " " & T.Street, U.City = T.City, U.Province = T.Province, U.PostalCode = T.Code;</code>

    Thanks again for your help.

    Tom

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: keep 2 unrelated tables in sync (2000 All Updates)

    If a match on first and last name is accurate enough for you, that will work well.

    (I didn't mean that the user would have had to enter the value of UniqueID manually - I envisioned a combo box in which UniqueID is the hidden first column, displaying relevant information in the second column, so that the user can select the matching record, leaving room for human intelligence if there is more than one choice)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •