autonumber with starting from DMAX value

[mygmat123]

I have the following SQL....

MYQUERY1

SELECT [mydate],[value], nz(DMax("group_number","mytable2","[value]='" & [value] & "' and " & "([mydate])Between #" & [mydate]-8 & "# AND #" & [mydate] & "#"),DMax("group_number","mytable2")+1) AS GROUP_ID FROM MYTABLE1;

I'm trying to get the groupID to be either the max groupId for a particular value in the last 8 days in "mytable2" if there was no max value for a partcular value in the last 8 days(NULL) then the value would get the max value of "mytable2" or myquery1 depending on which value is higher and then add 1. Mainly I'm looking for an autonumber with some criteria and logic.

 

[mygmat123]

Maybe I did not explain my self well. I'll keep it simple

How do you use SQL to set a distinct ID per record. an autonumber

 

[hymn]

Dmax is normally used as below
= DMax ("[YourFieldName]","YourTableName") + 1

Hope this helps
Hymn