Conditionally Numbered Sequence

[benjames_au]

Hi,

I have a table with 400,000 (approx) records in the following format:

a(PK:AutoNum) - b(text)-c(text)-d(Num)-e(text) - f (text - unique/random)
A B C D E F
1 - 6767 - P2 - 1 - 24/992 - 34341212
2 - 6767 - P2 - 1 - 24/993 - 87657483
3 - 6767 - P2 - 1 - 24/995 - 98764536
4 - 6767 - P2 - 2 - 24/996 - 87543297
5 - 6767 - P2 - 2 - 24/998 - 98674635
6 - 6767 - P2 - 2 - 24/999 - 34546576
7 - 6767 - P2 - 2 - 25/001 - 98768547
8 - 6767 - P2 - 2 - 25/002 - 46576897
9 - 6767 - P2 - 2 - 25/004 - 62536475

I need to create a new field in a query that acts like an autonumber, that resets based on an increase in field (d).

ie:

1 - 6767 - P2 - 1 - 24/992 - 34341212 - 1
2 - 6767 - P2 - 1 - 24/993 - 87657483 - 2
3 - 6767 - P2 - 1 - 24/995 - 98764536 - 3
4 - 6767 - P2 - 2 - 24/996 - 87543297 - 1
5 - 6767 - P2 - 2 - 24/998 - 98674635 - 2
6 - 6767 - P2 - 2 - 24/999 - 34546576 - 3
7 - 6767 - P2 - 2 - 25/001 - 98768547 - 4
8 - 6767 - P2 - 2 - 25/002 - 46576897 - 5
9 - 6767 - P2 - 2 - 25/004 - 62536475 - 6

I initially thought I could make an IIF expression that basically says: if [d] in this row is the same as [d] in the previous row then add 1 to the previous record [new number], otherwise, enter 1 in this record [new number] (thereby resetting it)....

I have searched the forums for 'numbered sequences' (amongst other things), and it appears that either the Dcount or Dmax function appear to be the solution, but I'm having some trouble applying it to this problem. I was hoping someone can help me through it.

Cheers

 

[pbaldy]

It looks like a DCount that looked for a match on D and <= on E would give you your number.