Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Update Query (2000)

    I would like to run an update query to update 2 fields in a table based on the field VISIT_DATE in another table which contains a date value.

    Each record in the table to be updated has a unique member ID number (strFamilyNumber), but there will be more than one instance where the ID number would appear, as a new record is added for each referral for a member.

    In my stripped down copy I have included the
    Attached Files Attached Files
    Easy Access

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

    Re: Update Query (2000)

    Since you want to perform different actions based on different criteria, you need two separate update queries:

    1) For the first situation:

    UPDATE ExcelTable INNER JOIN tblClient ON (tblClient.datReferral = ExcelTable.RFL_DATE) AND (ExcelTable.FAMMEM = tblClient.strFamilyNumber) SET tblClient.datScheduled = [VISIT_DATE]
    WHERE (((tblClient.chkComplete)=False) AND ((tblClient.datScheduled) Is Null));

    2) For the second situation:

    UPDATE ExcelTable INNER JOIN tblClient ON (tblClient.datReferral = ExcelTable.RFL_DATE) AND (ExcelTable.FAMMEM = tblClient.strFamilyNumber) SET tblClient.datRescheduled = [VISIT_DATE]
    WHERE (((tblClient.chkComplete)=False) AND ((tblClient.datScheduled)<>[VISIT_DATE]));

    See attached version (the queries have already been run).
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update Query (2000)

    Again Hans...many thanks. Your solution is sterling.
    Easy Access

Posting Permissions

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