update problem

[tw]

My scenario is like follow:

id number teamid
1 0 1
2 0 1
3 3 1
4 1 1
5 0 2
6 0 2
7 2 2
8 0 3
9 0 3
10 1 4


I want to update every record in teamid with the highest number.

Like this:

id number teamid
1 3 1
2 3 1
3 3 1
4 3 1
5 2 2
6 2 2
7 2 2
8 0 3
9 0 3
10 1 4

How can i do that in a query?

Help

Thanx

tw

 

[Michel Walsh]

Hi,


UPDATE myTable
SET number=DMAX("number", "myTable", "teamID=" & teamID )



Hoping it may help,
Vanderghast, Access MVP

 

[Van T. Dinh]

UPDate [YourTable]
SET [number] = DMax("number", "YourTable", "[TeamID] = " & [TeamID])