sequential numbering

[edo janssen]

For my db I would like to have a query that generates a sequential numbering on multiple criteria. Now it only generated the seqnum for DTCieLid.

DTCieRegelId - DTCieLid - DTCie - Jaar - SeqNum
1 - 55 - een - 2010 - 1
2 - 55 - twee - 2010 - 1
3 - 55 - een - 2010 - 2
4 - 55 - een - 2011 - 1
5 - 56 - een - 2010 - 1
6 - 56 - een - 2010 - 2
7 - 56 - een - 2010 - 3

[/SIZE][/FONT][FONT=Arial][SIZE=2]SELECT  tblOBNSpecVacRegel.DTCieRegelId, tblOBNSpecVacRegel.DTCieLid,  tblOBNSpecVacRegel.DTCie, tblOBNSpecVacRegel.Jaar,  DCount("[DTCieLid]","tblOBNSpecVacRegel","[DTCieLid] = " &  [DTCieLid] &  "")-DCount("[DTCieLid]","tblOBNSpecVacRegel","[DTCieLid] = " &  [DTCieLid] & " AND [DTCie] = " & '[DTCie]' & " AND [Jaar] = "  & [Jaar] & " AND [DTCieRegelId] > " & [DTCieRegelId]) AS  SeqNum 
FROM tblOBNSpecVacRegel 
GROUP BY tblOBNSpecVacRegel.DTCieRegelId, tblOBNSpecVacRegel.DTCieLid, tblOBNSpecVacRegel.DTCie, tblOBNSpecVacRegel.Jaar 
ORDER BY tblOBNSpecVacRegel.DTCieRegelId; 
[/SIZE][/FONT] 
[FONT=Arial][SIZE=2]

 

[John Big Booty]

Perhaps the attached sample will give you some pointers.

 

[edo janssen]

Thanks John. Found the right code by myself. Broke the code into two parts.

This is the right code

SeqNum: DCount("[DTCieLid]";"tblOBNSpecVacRegel";"[DTCieLid] = " & [DTCieLid] & " AND [DTCie] = '" & [DTCie] & "' AND [Jaar] = " & [Jaar])-DCount("[DTCieLid]";"tblOBNSpecVacRegel";"[DTCieLid] = " & [DTCieLid] & " AND [DTCie] = '" & [DTCie] & "' AND [Jaar] = " & [Jaar] & " AND [DTCieRegelId] > " & [DTCieRegelId])