Sequential numbering of records

[lcc]

I am creating a database to hold a record of risks
For the record number I need to sequentially number each record within
each group once the group type has been determined

Example

Group 1 Design
Group 2 Security
Group 3 Privacy

Group 1 first record created for that group type would be 1.1, next
1.2, next 1.3 etc
Group 3 first record would be 3.1, next would be 3.2 etc

There are currently 30+ risk group types, therefore I do not want 30
different tables, just the one to contain all risk group types

Records entered at any time can be from any group type, so you could
have a group 6, then a group 9, then another group 9, then a group 1

I am open to suggestions on how to approach this numbering scenario

 

[Steve Schapel]

LCC,

First of all, it will be best to use 2 separate fields here, one for the
group type, and one for the sequential number within each group. Where
you need this to be displayed in the 1.2, 2.3 etc structure for your
forms and reports, you can easily combine the values from the 2 fields
via a concatenation expression, either in a query or in the control
source of a form/report textbox.

When entering a new record, the group type will be manually entered,
right? And this will be done first, possibly via a combobox. So,
here's an idea... on the After Update event of the group type control,
you could put code something like this...

Me.MySequentialNumber =
DMax("[MySequentialNumber]","MyTable","[GroupType]=" & Me.GroupType)+1

 

[lcc]

Thanks for your response

I have a bit of a brain freeze happening as its been a while since I
used access so please bear with me

Am I right all this will be in one original table or do I need a
separate table for the numbering?

So on the form, I set up a field for GroupType (yes the group type
will be entered via a combo box) and a field for SeqNo, is the SeqNo
field an unbound box or a field from my original table, do I need to
add an event to this field?

'MyTable' is my original table name?

Also, there is a group number and group name, I want when i select a
number the name automatically appears in the other box, how is this
achieved?

Thanks again

LCC,

First of all, it will be best to use 2 separate fields here, one for the
group type, and one for the sequential number within each group. Where
you need this to be displayed in the 1.2, 2.3 etc structure for your
forms and reports, you can easily combine the values from the 2 fields
via a concatenation expression, either in a query or in the control
source of a form/report textbox.

When entering a new record, the group type will be manually entered,
right? And this will be done first, possibly via a combobox. So,
here's an idea... on the After Update event of the group type control,
you could put code something like this...

Me.MySequentialNumber =
DMax("[MySequentialNumber]","MyTable","[GroupType]=" & Me.GroupType)+1

--
Steve Schapel, Microsoft Access MVP

I am creating a database to hold a record of risks
For the record number I need to sequentially number each record within
each group once the group type has been determined

Example

Group 1 Design
Group 2 Security
Group 3 Privacy

Group 1 first record created for that group type would be 1.1, next
1.2, next 1.3 etc
Group 3 first record would be 3.1, next would be 3.2 etc

There are currently 30+ risk group types, therefore I do not want 30
different tables, just the one to contain all risk group types

Records entered at any time can be from any group type, so you could
have a group 6, then a group 9, then another group 9, then a group 1

I am open to suggestions on how to approach this numbering scenario

 

[Steve Schapel]

LCC,

Am I right all this will be in one original table or do I need a
separate table for the numbering?

All in one table.

So on the form, I set up a field for GroupType (yes the group type
will be entered via a combo box) and a field for SeqNo, is the SeqNo
field an unbound box or a field from my original table,

You would put the SeqNo field in the table, and the form would have a
textbox bound to this field.

do I need to
add an event to this field?

No. As mentioned previously, you would use code on the After Update
event of the GroupType combobox to assign the required value to the
SeqNo field.

'MyTable' is my original table name?

Cute

Also, there is a group number and group name, I want when i select a
number the name automatically appears in the other box, how is this
achieved?

You would have a separate table that lists the group numbers and their
associated group names, and this table would be the Row Source of the
combobox on the form where you enter the GroupType. The MyTable table
should not have a Group Name field, since when you enter the group
number into the GroupType field, you automatically already know the
group name by reference to the lookup table. If you want the group name
to appear on the form, there are several approaches you could take to
this. These are explained here...
http://accesstips.datamanagementsolutions.biz/lookup.htm