Update query?

[js]

Hi,

I use a select statement(select ID, min(NUM) from table where... group by
ID) to query table1 return:
ID NUM
1 10
3 20

and table2 have:
ID NUM
1
2
3

hwo to update the result to table2? can I do it in one query?

Thanks...

 

[js]

More Info:
I try this one, but got error:
UPDATE tb1 INNER JOIN (SELECT History.Unumber AS Unumber,
Min(History.Visit) AS MinOfVisit FROM History WHERE ((History.Trans) In
('A','B')) GROUP BY History.Unumber) T1
ON tb1.ID = T1.Unumber SET tb1.MVist = T1.MinOfVisit

Error Msg:
Operation must use an updateable query.

Pls advice.

 

[John Vinson]

More Info:
I try this one, but got error:
UPDATE tb1 INNER JOIN (SELECT History.Unumber AS Unumber,
Min(History.Visit) AS MinOfVisit FROM History WHERE ((History.Trans) In
('A','B')) GROUP BY History.Unumber) T1
ON tb1.ID = T1.Unumber SET tb1.MVist = T1.MinOfVisit

Error Msg:
Operation must use an updateable query.

Access stubbornly refuses to allow ANY query containing any aggregate
function - min, max, count, sum - to be updateable, even if logically
it should be (as in this case).

The getaround is to use the DMin() VBA function instead of a Subquery:

UPDATE tbl
SET tb.MVist = DMIN("[Visit]", "[History]", "[UNumber] = " & ID &
" AND [Trans] IN ('A', 'B'));

 

[js]

Thanks John,

The getaround is to use the DMin() VBA function instead of a Subquery:

UPDATE tbl
SET tb.MVist = DMIN("[Visit]", "[History]", "[UNumber] = " & ID &
" AND [Trans] IN ('A', 'B'));

So I need to make a DMin VBA function? Can I call it when I build the query
or has to be inside the module?

 

[John Vinson]

So I need to make a DMin VBA function? Can I call it when I build the query
or has to be inside the module?

DMin is builtin to Access already, and can be called directly from a
query. Try copying and pasting the query I wrote into a new Query's
SQL window - it should work without modification (unless you changed
field or table names for your post).

 

[js]

Thanks John,
How to reuse the DMin() function:
UPDATE tbl
SET tb.MVist = IIF(DMIN("[Visit]", "[History]", "[UNumber] = " & ID &
" AND [Trans] IN ('A', 'B')) = 0, '0', DMIN("[Visit]", "[History]",
"[UNumber] = " & ID &
" AND [Trans] IN ('A', 'B')) ;