Counting Up Until Field Value Changes?

[ChadDiesel]

Hello Again,

I want to assign a number to each record that will be part of a shipping
number. I want the number value to count up until the contract number
changes. Then, I want the number to go back to 1 and start counting up again
until the next contract change.

For example

Contract 1111111 Box 1 of 2 Number Value: 1
Contract 1111111 Box 2 of 2 Number Value: 2
Contract 1111111 Skid 1 of 1 Number value: 3
Contract 2222222 Box 1 of 2 Number value: 1
Contract 2222222 Box 2 of 2 Number value: 2
Contract 2222222 Skid 1 of 1 Number value: 3
Contract 2222222 Bundle 1 of 2 Number value: 4
Contract 2222222 Bundle 2 of 2 Number value: 5
..
..
..
I posted this question a few days ago and was told to use the DCount()
function with Contract_Number as my criteria. I tried that in a query, but
my value is always the total number of records:

Contract 1111111 Box 1 of 2 Number Value: 8
Contract 1111111 Box 2 of 2 Number Value: 8
Contract 1111111 Skid 1 of 1 Number value: 8
Contract 2222222 Box 1 of 2 Number value: 8
Contract 2222222 Box 2 of 2 Number value: 8
Contract 2222222 Skid 1 of 1 Number value: 8
Contract 2222222 Bundle 1 of 2 Number value: 8
Contract 2222222 Bundle 2 of 2 Number value: 8

The DCount might be the solution, but I need some help setting up the
expression, because I am not doing it right. I need this number value for a
shipping code for a company we do business with. They have a specific
format they use, and I have to stick with it. When I get this last number,
I will use concatenate to join all the parts of the shipping code. Can I set
this up in a table, or will I need to run a query? Any help would be
appreciated.

Thanks,

Chad

[pietlinden]

looks more like you would use the DCount to set the value of the field
in question in a form, not a query. Then you could just use a
variation of DCount()+1 as your default value.

[pietlinden]

looks more like you would use the DCount to set the value of the field
in question in a form, not a query. Then you could just use a
variation of DCount()+1 as your default value.

If you have the data already entered, you may need to use code to do
this... if you want the number to remain fixed.

[jimfortune]

ChadDiesel wrote:

Hello Again,

I want to assign a number to each record that will be part of a shipping number. I want the number value to count up until the contract number changes. Then, I want the number to go back to 1 and start counting up again until the next contract change.

For example

Contract 1111111 Box 1 of 2 Number Value: 1
Contract 1111111 Box 2 of 2 Number Value: 2
Contract 1111111 Skid 1 of 1 Number value: 3
Contract 2222222 Box 1 of 2 Number value: 1
Contract 2222222 Box 2 of 2 Number value: 2
Contract 2222222 Skid 1 of 1 Number value: 3
Contract 2222222 Bundle 1 of 2 Number value: 4
Contract 2222222 Bundle 2 of 2 Number value: 5
.
.
.
I posted this question a few days ago and was told to use the DCount() function with Contract_Number as my criteria. I tried that in a query, but my value is always the total number of records:

Contract 1111111 Box 1 of 2 Number Value: 8
Contract 1111111 Box 2 of 2 Number Value: 8
Contract 1111111 Skid 1 of 1 Number value: 8
Contract 2222222 Box 1 of 2 Number value: 8
Contract 2222222 Box 2 of 2 Number value: 8
Contract 2222222 Skid 1 of 1 Number value: 8
Contract 2222222 Bundle 1 of 2 Number value: 8
Contract 2222222 Bundle 2 of 2 Number value: 8

The DCount might be the solution, but I need some help setting up the expression, because I am not doing it right. I need this number value for a shipping code for a company we do business with. They have a specific format they use, and I have to stick with it. When I get this last number, I will use concatenate to join all the parts of the shipping code. Can I set this up in a table, or will I need to run a query? Any help would be
appreciated.

Thanks,

Chad

tblContractItems
ID AutoNumber PK
Contract_Number Text
Box Text

1 1111111 Box 1 of 3
2 1111111 Box 2 of 3
3 1111111 Box 3 of 3
4 1111111 Skid 1 of 1
5 2222222 Box 1 of 2
6 2222222 Box 2 of 2
7 2222222 Skid 1 of 1

qryGetNumberToAssign
SELECT Box, Contract_Number, (SELECT Count(A.ID) FROM tblContractItems
AS A WHERE A.Contract_Number = tblContractItems.Contract_Number And
A.ID < tblContractItems.ID) + 1 AS NumberToAssign FROM
tblContractItems;

gave:

Box Contract_Number NumberToAssign
Box 1 of 3 1111111 1
Box 2 of 3 1111111 2
Box 3 of 3 1111111 3
Skid 1 of 1 1111111 4
Box 1 of 2 2222222 1
Box 2 of 2 2222222 2
Skid 1 of 1 2222222 3

I could also have used:

qryGetNumberToAssign
SELECT Box, Contract_Number, Contract_Number & '-' & (SELECT
Count(A.ID) FROM tblContractItems AS A WHERE A.Contract_Number =
tblContractItems.Contract_Number And A.ID < tblContractItems.ID) + 1 AS
NumberToAssign FROM tblContractItems;

so that NumberToAssign looks like 1111111-1. It's safer to leave the
value in Contract_Number as is and use a different field to put
together your shipping code. When using this in an update query you
can put 'WHERE ShippingCode is Null' if you want to limit the changes
only to new contracts. I'd put all the items for the contracts in
before running the query instead of generating the ShippingCode when
each item is added, but you can do that if you really want.

James A. Fortune

[ChadDiesel]

Thanks for the help.

Chad
<ji********@compumarc.com> wrote in message
news:11*********************@l41g2000cwc.googlegro ups.com...

ChadDiesel wrote:

Hello Again,

I want to assign a number to each record that will be part of a

shipping

number. I want the number value to count up until the contract

number

changes. Then, I want the number to go back to 1 and start counting

up again

until the next contract change.

For example

Contract 1111111 Box 1 of 2 Number Value: 1
Contract 1111111 Box 2 of 2 Number Value: 2
Contract 1111111 Skid 1 of 1 Number value: 3
Contract 2222222 Box 1 of 2 Number value: 1
Contract 2222222 Box 2 of 2 Number value: 2
Contract 2222222 Skid 1 of 1 Number value: 3
Contract 2222222 Bundle 1 of 2 Number value: 4
Contract 2222222 Bundle 2 of 2 Number value: 5
.
.
.
I posted this question a few days ago and was told to use the

DCount()

function with Contract_Number as my criteria. I tried that in a

query, but

my value is always the total number of records:

Contract 1111111 Box 1 of 2 Number Value: 8
Contract 1111111 Box 2 of 2 Number Value: 8
Contract 1111111 Skid 1 of 1 Number value: 8
Contract 2222222 Box 1 of 2 Number value: 8
Contract 2222222 Box 2 of 2 Number value: 8
Contract 2222222 Skid 1 of 1 Number value: 8
Contract 2222222 Bundle 1 of 2 Number value: 8
Contract 2222222 Bundle 2 of 2 Number value: 8

The DCount might be the solution, but I need some help setting up the

expression, because I am not doing it right. I need this number

value for a

shipping code for a company we do business with. They have a

specific

format they use, and I have to stick with it. When I get this last

number,

I will use concatenate to join all the parts of the shipping code.

Can I set

this up in a table, or will I need to run a query? Any help would be
appreciated.

Thanks,

Chad

tblContractItems
ID AutoNumber PK
Contract_Number Text
Box Text

1 1111111 Box 1 of 3
2 1111111 Box 2 of 3
3 1111111 Box 3 of 3
4 1111111 Skid 1 of 1
5 2222222 Box 1 of 2
6 2222222 Box 2 of 2
7 2222222 Skid 1 of 1

qryGetNumberToAssign
SELECT Box, Contract_Number, (SELECT Count(A.ID) FROM tblContractItems
AS A WHERE A.Contract_Number = tblContractItems.Contract_Number And
A.ID < tblContractItems.ID) + 1 AS NumberToAssign FROM
tblContractItems;

gave:

Box Contract_Number NumberToAssign
Box 1 of 3 1111111 1
Box 2 of 3 1111111 2
Box 3 of 3 1111111 3
Skid 1 of 1 1111111 4
Box 1 of 2 2222222 1
Box 2 of 2 2222222 2
Skid 1 of 1 2222222 3

I could also have used:

qryGetNumberToAssign
SELECT Box, Contract_Number, Contract_Number & '-' & (SELECT
Count(A.ID) FROM tblContractItems AS A WHERE A.Contract_Number =
tblContractItems.Contract_Number And A.ID < tblContractItems.ID) + 1 AS
NumberToAssign FROM tblContractItems;

so that NumberToAssign looks like 1111111-1. It's safer to leave the
value in Contract_Number as is and use a different field to put
together your shipping code. When using this in an update query you
can put 'WHERE ShippingCode is Null' if you want to limit the changes
only to new contracts. I'd put all the items for the contracts in
before running the query instead of generating the ShippingCode when
each item is added, but you can do that if you really want.

James A. Fortune

[jimfortune]

ChadDiesel wrote:

Thanks for the help.

Chad

Let's take this a step further. For a given contract item the ID will
not change so the same number will be assigned each time the query is
run. By limiting to WHERE Contract_Number = 'x' you can get the
ShippingCode's for those contract items whenever you want so you don't
even need to store ShippingCode in the table. Gotta satisfy the
purists who don't like storing calculated values, whenever it's not too
much trouble :-).

James A. Fortune