Sequential numbering within groups (1 Viewer)

Hi all,

Simple question (I hope)...

Need to devise a way of updating a field with an ascending sequential number within a group of records of similar type.

e.g.

Say I have records which consists of numerous IDs and various job descriptions within the each ID

13000 Head of Maths 1
13000 School Secretary 1
13000 Head of Maths 2
13000 Head of Maths 3
13000 School Secretary 2
14000 Head of Maths 1

So in this case each 'Head of Maths' and 'School Secretary' sequentially increments until there is a new ID.

Any help appreciated.

thanks,
Alex

Alex,

Use the Search Facility here and look for DMax.

Essentially, you want to use that function to look for the largest
number currently assigned to the group and then increment it by 1.

You'll need something like this in your form's BeforeInsert event
to automatically assign the number.


If there are no existing entries for the [ID], [JOB] then the DMax
will return nothing. The Nz function will translate that to 0.
Then, 1 is added to give the new number.

Wayne

Hi Wayne,

Thanks for that. I am trying to do this as an update query not via a form - but I'll have a little play with dmax and see what happens.

If anyone else has any suggestions, I'm all ears.

thanks,
Alex

Why do you need to store the value ?

Rich,

I'm storing the value to update related information (Name, address) in another table.

cheers,
Alex

That would suggest at first sight that your structure is flawed, give us some idea of your table set up and relationships etc

Sequential Numbering within Groups with a Query

Hi Alex,

I'm sure you've long since cracked this nut but I thought I'd post a solution to your particular case because I wasn't able to find it elsewhere and needed the same thing.

Enjoy,
Bay


Solution:

1) Your source table will require a primary key column (or you can use any existing field that you're sure contains a unique value for each row) (see TBL.PK)
2) Use the Count(*) function to return your sequential group member count (see L1)
3) Self join your table (see L4 - L7)...
3.1) ...the self join should be ON your grouped columns with comparison operator "=" (see L5 and L6)...
3.2) ...and on your grouped primary key (or unique column) with comparison operator ">=" (to sequentially count ascending) or "<=" (to sequentially count descending) (see L7)
4) GROUP BY all joined columns (see L9)
5) ORDER BY all joined columns (see L11)


Query:

.L1> SELECT Count(*) AS GroupCount, TBL.C1, TBL.C2
.L2> FROM TBL
.L3>
.L4> INNER JOIN TBL AS TBL_1
.L5> ON (TBL.C1=TBL_1.C1)
.L6> AND (TBL.C2=TBL_1.C2)
.L7> AND (TBL.PK>=TBL_1.PK)
.L8>
.L9> GROUP BY TBL.C1, TBL.C2, TBL.PK
L10>
L11> ORDER BY TBL.C1, TBL.C2, TBL.PK;


Results:

GroupCount......C1..........C2
1....................13000.....Head of Maths
2....................13000.....Head of Maths
3....................13000.....Head of Maths
1....................13000.....School Secretary
2....................13000.....School Secretary
1....................14000.....Head of Maths

BButler, you've revived a very old thread, 2005. Welcome to AWF by the way.

Interesting way of getting a numbering system BButler.

Here's another way of approaching this. However, this requires a unique ID that is a Number data type. You can even change the order of the field if you wish and it would maintain the numbering.

vbaInet, Thanks for the welcome!

Yes sir, both ways will work (and I didn't have numbering issues changing field order with either) but I'll defer to the one with the gazillion posts .

And thank you for the attached example. As an homage, I've embellished your work by adding an additional group level to your example in terms of Alex's original query.


Solution using DCount:

1) This requires a unique ID that is a Number data type (see PK).
2) DCount(expr, domain [, criteria] )
3) DCount expr = lowest level of group
4) DCount domain = your source table or query
5) DCount criteria = all members in group (follow syntax in Query example below)
6) DCount sequential group member count logic (for those interested):
Query:

L1> SELECT

L2> DCount("[C2]","TBL","[C2] & [C1] = '" & [C2] & [C1] & "'")-
L3> DCount("[C2]","TBL","[C2] & [C1] = '" & [C2] & [C1] & "' AND [PK] > " & [PK]) AS GroupCount, C1, C2

L4> FROM TBL
L5> GROUP BY C1, C2, PK
L6> ORDER BY C1, C2, PK;


Results:

GroupCount......C1..........C2
1....................13000.....Head of Maths
2....................13000.....Head of Maths
3....................13000.....Head of Maths
1....................13000.....School Secretary
2....................13000.....School Secretary
1....................14000.....Head of Maths

Hehe! Great work, great explanation BButler.

Another requirement that’s implied (but not called out) in the example is that the source table (see TBL) should reflect your desired groupings (i.e., the columns should be sorted by order of group levels – parent -> child) in such a way that your unique field (see PK) ascends sequentially.

Using Alex’s original source data set as an example…

This sort order will provide the correct sequential w/in group numbering result:
C1..........C2.......................PK
13000.....Head of Maths.......1
13000.....Head of Maths.......2
13000.....Head of Maths.......3
13000.....School Secretary...4
13000. ...School Secretary...5
14000.....Head of Maths.......6

This sort order will not:
C1..........C2......................PK
13000.....Head of Maths......1
13000.....School Secretary...2
13000.....Head of Maths......3
14000.....Head of Maths......4
13000.....Head of Maths......5
13000.....School Secretary...6

Those select queries are great - but I'm failing to see how they'd update the source table (needing the same solution myself). Hopefully I'm just having a brain fart - any help would be appreciated!