none
Comparing records from two tables and updating a field of one of the records RRS feed

  • Question

  • I am looking to compare records between two tables and update a field in a record in the second table if certain criteria are met.  The purpose of the database is to determine the reason for the RE at the end of the Name field in Table 2.  The tables each have the following fields:

    Table 1

    Name

    Batch

    Dilution

     Sample 1801

     B1

     1

     Sample 1802

     B1

     1

    Table 2

    Name

    Batch

    Dilution

    RETYPE

     Sample 1801RE

     B2

     1

     

     Sample 1802RE

     B1

     50

     

    I would like to update the RETYPE in Table 2 based on a comparison with the associated non-RE sample in Table 1.  I have thousands of records so I was hoping there is a way I could automate this procedure.  In the above example, the RETYPE for Sample 1801RE would be "Batch" because this sample is from a different batch than Sample 1801.  Sample 1802RE would have an RETYPE of "Dilution" because of the different dilution value than Sample 1802.

    I have a little familiarity with Access and VBA but I am not sure how to go about starting the logic on this.  I figure it would likely involve coding as opposed to a simple update query.  Any help would be very much appreciated.

    Friday, July 13, 2018 6:34 PM

All replies

  • Hi Tim,

    Just for testing purposes, try the following query and let us know the result. We're not updating anything yet, just making sure the correct records are selected for updates.

    SELECT T1.[Name], T2.[Name], T1.Batch, T2.Batch
    FROM Table1 T1
    INNER JOIN Table2 T2
    ON T2.[Name] Like T1.[Name] & "*"
    WHERE T1.Batch<>T2.Batch

    Friday, July 13, 2018 6:43 PM
  • Hello TimW333,

    What will happen if a record's Batch and Dilution are both different than the record in Table1? Or if both of them are the same as the record in Table1?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 16, 2018 5:31 AM
  • That query (with instances of Name changed to Sample Name) produces the following:

    <tfoot></tfoot>
    Query1
    T1.SampleName T2.SampleName T1.Batch T2.Batch
    Sample 1801 Sample 1801RE B1 B2

    Tuesday, August 7, 2018 6:12 PM
  • If both the batch and dilution are the same, I would return a third value of "Other".  There would never be an instance where both the dilution and the batch are different based on the nature of our procedures.  A dilution is done because of a very high result while a batch RE means a quality control check failed.  If the quality control check failed, there would be no reason to run a dilution as the result would still be invalid.
    Tuesday, August 7, 2018 6:19 PM
  • That query (with instances of Name changed to Sample Name) produces the following:

    <tfoot></tfoot>
    Query1
    T1.SampleName T2.SampleName T1.Batch T2.Batch
    Sample 1801 Sample 1801RE B1 B2

    Hi Tim,

    Thanks for the update. Is this result correct? If not, what's wrong with it?

    Tuesday, August 7, 2018 7:50 PM
  • I'd be inclined to do this as two separate operations along these lines:

    UPDATE [Table 2]
    SET retype = "Batch"
    WHERE EXISTS
         (SELECT *
           FROM [Table 1]
           WHERE [Table 2].[name] LIKE [Table 1].[name] & "*"
           AND [Table 2].Batch <> [Table 1].Batch);

    UPDATE [Table 2]
    SET retype = "Dilution"
    WHERE EXISTS
         (SELECT *
           FROM [Table 1]
           WHERE [Table 2].[name] LIKE [Table 1].[name] & "*"
           AND [Table 2].dilution <> [Table 1].dilution);

    BTW, I'd recommend you change the name of the Name column.  As the name of a built in property Name is a reserved keyword, and should be avoided as an object name.  Use SampleName or similar.

    Ken Sheridan, Stafford, England

    Tuesday, August 7, 2018 11:12 PM