Select numbers from one table to update another based on matching string.

[JRodko]

I have attached an example DB to make things easier.



I am trying to update records in the "Header" table with values found in the "Lithology" table.

In header, I'd like to copy the "To" depth out of Lithology and into the header field "Depth_B" WHERE the code field (Lithology table) = "B". This needs to be done only for the hole id that matches in tblSelectedDrillHole (TEST19-02)

I'd also like the query to take the "From" depth form lithogloy where code = "U" and copy that value into the header field "Depth_U"

I'd also like to update the header field "Date_Completed" with the current date.

Finally, I'd like the header field "Depth_E" to be updated with the largest "To" value based on the selected drill hole.

Perhaps looking at the example database will clarify my description. There is a sample project "TEST19-03" which demonstrates how the values should be used to update the corresponding project in the Header table. As of now I am thinking this would be best with a series of queries?

samplecopydepths.zip

Thank you.

[Gicu]

Can you have a look at the attached (query1) and see if that is what you want?

Cheers,
Vlad

[ssanfu]

Here is a VBA example instead of using multiple saved queries.

[JRodko]

Wow. Thank you both very much. I always forget about the self join. Silly me.

I have gone with Vlad's solution because I am fairly new to access and I understand it through and through.

Steve - Thank you for the VBA solution. I hope to get to your level one day.

Cheers to both of you.

[Gicu]

Good luck with your project!