Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2010

    Question Unanswered: How can I update yes/no field if a row exists in another table?


    My problem is this:

    I have 2 tables, TableA & TableB.

    TableA has the following fields: TableA_ID(Autonumber -PK), Staff_No(Number), Surname(Text) and OnSite(Yes/No).

    TableB has: TableB_ID(Autonumber -PK), TableA_ID(FK), From(Date/Time), To(Date/Time), Notes(Text)

    Join is TableA(TableA_ID) - TableB(TableA_ID). Type 2 with "Enforce Referential Integrity".

    I want TableA field "OnSite(Yes/No)" to update to "yes/checked" if a row exists in TableB with the TableA_ID present and the "To(Date/Time)" field is not in the past.

    I have no idea where to start. I don't want to use VBA code as the database will be upscaled to SQL Server when completed and accessed through a web front end, although I don't know if this will matter. Any advice or guidance would be really appreciated.

    Attached Thumbnails Attached Thumbnails relationship.png  

  2. #2
    Join Date
    May 2004
    New York State
    Use a simple Update Query. It will transfer nicely into SQL Server.

    In the query designer, bring up tblA first, then tblB. Make sure you have the "Update Query" selected. The two tables will join automatically since you have them related. Double click on tblA's OnSite(Yes/No) field. In the "To" line, enter True. Then double-click on tblB's To(Date/Time) field. In the "Criteria" line, enter ">= Date()" (without the quotes). From the way you presented the question, it should be just what the doctor ordered.


  3. #3
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    Personally I would not have that field. It's basically a calculated field (one whose value depends on another). Whatever process you would come up with would have to be run all the time to keep it updated (once a day if the field only stores date, repeatedly throughout the day if it includes time). This is the type of thing you should just calculate in a query or on a form/report.

  4. #4
    Join Date
    May 2010
    This value really should NOT be stored.

    You are violating one of the major rules of data normalization by storing a calculated value based on other data.

    This rule applies to all back end databases.

    This calculation can be easily handled in a Stored Procedure.
    Boyd Trimmell aka HiTechCoach (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    Jul 2010


    Sam: Just what I needed for the moment. Much appreciated.

    Paul, Boyd: I've realised the mistake I'm making and will try and work round it. Thanks for the advice.

Posting Permissions

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