2

I have two tables that both have a field named PostDate.

Both tables also have a field called LoadID.

In the first table there is one record for each LoadID.

In the second table there are multiple records for each LoadID and each record has a different value for PostDate.

I want to update the PostDate value in the first table with the Minimum PostDate value in the second table for each LoadID.

I can not figure out how to do this.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
John Lee
  • 317
  • 6
  • 14
  • What have you tried and what happened when you tried it? If you haven't tried anything then it's too soon to be posting a question here. – jmcilhinney Oct 27 '18 at 04:24
  • Is it the SQL part or the VB part which is causing you problem? – laancelot Oct 27 '18 at 04:29
  • I was having problems with creating a sub-query with the min function. I tried everything I could think of but could not figure out how to group etc. – John Lee Oct 28 '18 at 00:31

2 Answers2

1

Use a correlated subquery:

UPDATE first_table f
SET post_date = (
   SELECT min( post_date )
   FROM second_table s
   WHERE s.LoadID  = f.LoadID 
)
krokodilko
  • 35,300
  • 7
  • 55
  • 79
  • i get "Operation must use an updateable query" when I try this. It seems it should work. I am using Access 2000. – John Lee Oct 28 '18 at 00:52
0

@krokodilko answer will also work fine I am also suggesting a new solution for it

UPDATE f
SET f.post_date = s.Post_Date
from first_table f
inner join
(
   SELECT min( post_date ) Post_Date, LoadId
   FROM second_table s
   Group by LoadId
) s 
on s.LoadID  = f.LoadID
Ravi
  • 1,157
  • 1
  • 9
  • 19