Incrementing numbers by group

  • Thread starter Thread starter Bailey
  • Start date Start date
B

Bailey

Hi:

I need to find a way to create a number, which is
incremented whenever a set of criteria match.
Specifically, I have a letter in one column and a license
plate in another. I want to match the letter and license
place and then assign an incrementing number on the
matched records. So, the number 1 is next to the first
instance of A ABC123 and 2 is next to the second instance
of A ABC123. However, when the letter switches to B
ABC123, I want the number to start from 1 again.

Ideally, the following is what I want to see (I currently
have the 2nd and 3rd columns):

# L License
1 A ABC123
2 A ABC123
3 A ABC123
1 B ABC123
1 C ABC123
1 A AAA111
2 A AAA111
1 B AAA111
2 B AAA111
1 C AAA111
2 C AAA111
1 A BBB222
1 B BBB222
1 C BBB222

Can anyone help me?
 
NextNumberInSequence = Nz(DMax("#", "TableName", "[L]='" & ValueOfLField &
"' And [License]='" & ValueOfLicenseField & "'"), 0) + 1

Above expression should do what you seek. You need to replace TableName with
the actual name of the table, and replace ValueOfLField and
ValueOfLicenseField with the appropriate variable names or control names
from a form.
 
Thanks Ken, you rock. I'll try this out and let you know
if it worked.

-----Original Message-----
NextNumberInSequence = Nz(DMax("#", "TableName", "[L]='" & ValueOfLField &
"' And [License]='" & ValueOfLicenseField & "'"), 0) + 1

Above expression should do what you seek. You need to replace TableName with
the actual name of the table, and replace ValueOfLField and
ValueOfLicenseField with the appropriate variable names or control names
from a form.

--
Ken Snell
<MS ACCESS MVP>

Hi:

I need to find a way to create a number, which is
incremented whenever a set of criteria match.
Specifically, I have a letter in one column and a license
plate in another. I want to match the letter and license
place and then assign an incrementing number on the
matched records. So, the number 1 is next to the first
instance of A ABC123 and 2 is next to the second instance
of A ABC123. However, when the letter switches to B
ABC123, I want the number to start from 1 again.

Ideally, the following is what I want to see (I currently
have the 2nd and 3rd columns):

# L License
1 A ABC123
2 A ABC123
3 A ABC123
1 B ABC123
1 C ABC123
1 A AAA111
2 A AAA111
1 B AAA111
2 B AAA111
1 C AAA111
2 C AAA111
1 A BBB222
1 B BBB222
1 C BBB222

Can anyone help me?
Click to expand...


.
Click to expand...
 
By the way, it's not good practice to include the # (or other such
characters) in the names of fields or controls. Change it to something like
SeqNumber. # is a delimiter for date/time fields in ACCESS, and if you ever
forget to use the [ ] around it in queries and such, ACCESS will get very
confused and can cause major problems in the database.

--
Ken Snell
<MS ACCESS MVP>

Bailey said:
Thanks Ken, you rock. I'll try this out and let you know
if it worked.

-----Original Message-----
NextNumberInSequence = Nz(DMax("#", "TableName", "[L]='" & ValueOfLField &
"' And [License]='" & ValueOfLicenseField & "'"), 0) + 1

Above expression should do what you seek. You need to replace TableName with
the actual name of the table, and replace ValueOfLField and
ValueOfLicenseField with the appropriate variable names or control names
from a form.

--
Ken Snell
<MS ACCESS MVP>

Hi:

I need to find a way to create a number, which is
incremented whenever a set of criteria match.
Specifically, I have a letter in one column and a license
plate in another. I want to match the letter and license
place and then assign an incrementing number on the
matched records. So, the number 1 is next to the first
instance of A ABC123 and 2 is next to the second instance
of A ABC123. However, when the letter switches to B
ABC123, I want the number to start from 1 again.

Ideally, the following is what I want to see (I currently
have the 2nd and 3rd columns):

# L License
1 A ABC123
2 A ABC123
3 A ABC123
1 B ABC123
1 C ABC123
1 A AAA111
2 A AAA111
1 B AAA111
2 B AAA111
1 C AAA111
2 C AAA111
1 A BBB222
1 B BBB222
1 C BBB222

Can anyone help me?
Click to expand...


.
Click to expand...
Click to expand...
 
It's okay, I just used the "#" as an example. thanks
though.

I am having a bit of a problem using the DMAX Method.
I'm just putting this in the VBwindow, but it doesn't
understand the Expr in
expression.DMax(Expr, Domain, Criteria)
-----Original Message-----
By the way, it's not good practice to include the # (or other such
characters) in the names of fields or controls. Change it to something like
SeqNumber. # is a delimiter for date/time fields in ACCESS, and if you ever
forget to use the [ ] around it in queries and such, ACCESS will get very
confused and can cause major problems in the database.

--
Ken Snell
<MS ACCESS MVP>

Thanks Ken, you rock. I'll try this out and let you know
if it worked.

-----Original Message-----
NextNumberInSequence = Nz(DMax("#", "TableName", "[L]
Click to expand...
='"
& ValueOfLField &
"' And [License]='" & ValueOfLicenseField & "'"), 0) + 1

Above expression should do what you seek. You need to replace TableName with
the actual name of the table, and replace
Click to expand...
ValueOfLField
and
ValueOfLicenseField with the appropriate variable
Click to expand...
names
or control names
from a form.

--
Ken Snell
<MS ACCESS MVP>

"Bailey" <[email protected]> wrote
Click to expand...
in
message
Hi:

I need to find a way to create a number, which is
incremented whenever a set of criteria match.
Specifically, I have a letter in one column and a license
plate in another. I want to match the letter and license
place and then assign an incrementing number on the
matched records. So, the number 1 is next to the first
instance of A ABC123 and 2 is next to the second instance
of A ABC123. However, when the letter switches to B
ABC123, I want the number to start from 1 again.

Ideally, the following is what I want to see (I currently
have the 2nd and 3rd columns):

# L License
1 A ABC123
2 A ABC123
3 A ABC123
1 B ABC123
1 C ABC123
1 A AAA111
2 A AAA111
1 B AAA111
2 B AAA111
1 C AAA111
2 C AAA111
1 A BBB222
1 B BBB222
1 C BBB222

Can anyone help me?


.
Click to expand...
Click to expand...


.
Click to expand...
 
Expr must be the name of a field in the table. Post what you've tried, and
give us the specific field names from the table.

--
Ken Snell
<MS ACCESS MVP>

It's okay, I just used the "#" as an example. thanks
though.

I am having a bit of a problem using the DMAX Method.
I'm just putting this in the VBwindow, but it doesn't
understand the Expr in
expression.DMax(Expr, Domain, Criteria)
-----Original Message-----
By the way, it's not good practice to include the # (or other such
characters) in the names of fields or controls. Change it to something like
SeqNumber. # is a delimiter for date/time fields in ACCESS, and if you ever
forget to use the [ ] around it in queries and such, ACCESS will get very
confused and can cause major problems in the database.

--
Ken Snell
<MS ACCESS MVP>

Thanks Ken, you rock. I'll try this out and let you know
if it worked.


-----Original Message-----
NextNumberInSequence = Nz(DMax("#", "TableName", "[L] ='"
& ValueOfLField &
"' And [License]='" & ValueOfLicenseField & "'"), 0) + 1

Above expression should do what you seek. You need to
replace TableName with
the actual name of the table, and replace ValueOfLField
and
ValueOfLicenseField with the appropriate variable names
or control names
from a form.

--
Ken Snell
<MS ACCESS MVP>

message
Hi:

I need to find a way to create a number, which is
incremented whenever a set of criteria match.
Specifically, I have a letter in one column and a
license
plate in another. I want to match the letter and
license
place and then assign an incrementing number on the
matched records. So, the number 1 is next to the first
instance of A ABC123 and 2 is next to the second
instance
of A ABC123. However, when the letter switches to B
ABC123, I want the number to start from 1 again.

Ideally, the following is what I want to see (I
currently
have the 2nd and 3rd columns):

# L License
1 A ABC123
2 A ABC123
3 A ABC123
1 B ABC123
1 C ABC123
1 A AAA111
2 A AAA111
1 B AAA111
2 B AAA111
1 C AAA111
2 C AAA111
1 A BBB222
1 B BBB222
1 C BBB222

Can anyone help me?


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top