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

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.

Answer 1

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

Answer 2

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 [e-mail].

Answer 3

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

Answer 4

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.

Answer 5

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?

Answer 6

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