Take Highest Value in record ID

[Musa via AccessMonster.com]

Table 1
ID SEQ
1 1
1 2
1 3
2 1
2 2
2 3
3 1
4 1
4 2

How can I take the higest SEQ value from each ID and carry it over to a new
record in another table ?

It would look like this ;
Table 2
ID SEQ
1 3
2 3
3 1
4 2
Do I use MAX in an IF statement ?

Thanks.

 

[Klatuu]

It would be the DMax function:

HiSeq = Nz(DMax("[SEQ],"TableNameHere","[ID] = " & Me.txtID),0)

This will return the highest Seq number for a selected ID
If the ID does not exist or it has no Seq numbers, it will return 0

 

[Dale Fye]

Another method:

SELECT ID, NZ(MAX(tablename.[SEQ]), 0) as [Seq]
FROM yourTable
GROUP BY ID

HTH
Dale

 

[Musa via AccessMonster.com]

Thanks.
Should put this code BeforeUpdate ?

It would be the DMax function:

HiSeq = Nz(DMax("[SEQ],"TableNameHere","[ID] = " & Me.txtID),0)

This will return the highest Seq number for a selected ID
If the ID does not exist or it has no Seq numbers, it will return 0

Table 1
ID SEQ

[quoted text clipped - 21 lines]

Thanks.

 

[Musa via AccessMonster.com]

Thank you so Much Klatuu .. that worked perfectly..

It would be the DMax function:

HiSeq = Nz(DMax("[SEQ],"TableNameHere","[ID] = " & Me.txtID),0)

This will return the highest Seq number for a selected ID
If the ID does not exist or it has no Seq numbers, it will return 0

Table 1
ID SEQ

[quoted text clipped - 21 lines]

Thanks.