locked
MS Access: Populate field in one table based on specific search result in another table without using a Macro RRS feed

  • Question

  • Hello all, I hope someone can help me with this technical issue:

    I have two tables linked by a primary key, it is a one to many relationship.

    Table A has records with serial numbers (primary key) and empty fields that need to display a score based on what's in another table (table B).

    Table B contains records with a serial number (primary key), the serial number a record in Table A it is linked to, and a score.

    Several records in Table B can belong to a single record in table A.

    The scores in Table B can be Pass, Fail, untested, or Partial. Each record in Table B represents a unique test event, and each record in Table A represents the requirement being tested.

    The empty field in Table A needs to reflect a score based on all the records linked to it in Table B.

    Example:

    Table B contains three records

    SN: 1, Table A SN: 33, Score: Fail

    SN: 2, Table A SN: 33, Score: Fail

    SN: 3, Table A SN: 33, Score: Pass

    What I want is to automatically populate Table A's score field with "Pass" in this case, as there is at least one record linked to it in Table B with a pass score.

    Example 2:

    Table B contains three records

    SN: 1, Table A SN: 33, Score: Fail

    SN: 2, Table A SN: 33, Score: Partial

    SN: 3, Table A SN: 33, Score: Untested

    What I want is to automatically populate Table A's score field with "Partial" in this case, as there are no records in Table B with a Pass score, but their is at least one record with a "Partial" score.

    A record in Table A should automatically populate it's score based on the following criteria:

    Table A record score should be "Pass" if there is at least one record in Table B with its serial number scored as Pass.

    Table A record score should be "Partial" if there is no record in Table B with its serial number scored as Pass, but there is at least one record in Table B with its serial number scored as Partial.

    Table A record score should be "Fail" if there are no records in Table B with its serial number,  scored as either Pass or Partial, but there is at least one record in Table B with its serial number scored as Fail.

    Table A record score should be "Untested" if there are no records in Table B with it's serial number, with a score other than "Untested".

    The constraints of our contract mean we are not supposed to use macros, although if there is no other way I may be able to come up with a workaround on that contract requirement.

    Thanks for any help anyone can give.

    Wednesday, October 11, 2017 3:37 PM