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@hotmail.com]

Re: Counting Up Until Field Value Changes?

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@hotmail.com]

Re: Counting Up Until Field Value Changes?

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@compumarc.com]

Re: Counting Up Until Field Value Changes?

ChadDiesel wrote:[color=blue]
> Hello Again,
>
> I want to assign a number to each record that will be part of a[/color]
shipping[color=blue]
> number. I want the number value to count up until the contract[/color]
number[color=blue]
> changes. Then, I want the number to go back to 1 and start counting[/color]
up again[color=blue]
> 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[/color]
DCount()[color=blue]
> function with Contract_Number as my criteria. I tried that in a[/color]
query, but[color=blue]
> 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[/color]
[color=blue]
> expression, because I am not doing it right. I need this number[/color]
value for a[color=blue]
> shipping code for a company we do business with. They have a[/color]
specific[color=blue]
> format they use, and I have to stick with it. When I get this last[/color]
number,[color=blue]
> I will use concatenate to join all the parts of the shipping code.[/color]
Can I set[color=blue]
> this up in a table, or will I need to run a query? Any help would be
> appreciated.
>
> Thanks,
>
> Chad[/color]

tblContractItem s
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

qryGetNumberToA ssign
SELECT Box, Contract_Number , (SELECT Count(A.ID) FROM tblContractItem s
AS A WHERE A.Contract_Numb er = tblContractItem s.Contract_Numb er And
A.ID < tblContractItem s.ID) + 1 AS NumberToAssign FROM
tblContractItem s;

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:

qryGetNumberToA ssign
SELECT Box, Contract_Number , Contract_Number & '-' & (SELECT
Count(A.ID) FROM tblContractItem s AS A WHERE A.Contract_Numb er =
tblContractItem s.Contract_Numb er And A.ID < tblContractItem s.ID) + 1 AS
NumberToAssign FROM tblContractItem s;

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]

Re: Counting Up Until Field Value Changes?

Thanks for the help.

Chad


<jimfortune@com pumarc.com> wrote in message
news:1112139648 .004190.14220@l 41g2000cwc.goog legroups.com...[color=blue]
> ChadDiesel wrote:[color=green]
>> Hello Again,
>>
>> I want to assign a number to each record that will be part of a[/color]
> shipping[color=green]
>> number. I want the number value to count up until the contract[/color]
> number[color=green]
>> changes. Then, I want the number to go back to 1 and start counting[/color]
> up again[color=green]
>> 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[/color]
> DCount()[color=green]
>> function with Contract_Number as my criteria. I tried that in a[/color]
> query, but[color=green]
>> 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[/color]
>[color=green]
>> expression, because I am not doing it right. I need this number[/color]
> value for a[color=green]
>> shipping code for a company we do business with. They have a[/color]
> specific[color=green]
>> format they use, and I have to stick with it. When I get this last[/color]
> number,[color=green]
>> I will use concatenate to join all the parts of the shipping code.[/color]
> Can I set[color=green]
>> this up in a table, or will I need to run a query? Any help would be
>> appreciated.
>>
>> Thanks,
>>
>> Chad[/color]
>
> tblContractItem s
> 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
>
> qryGetNumberToA ssign
> SELECT Box, Contract_Number , (SELECT Count(A.ID) FROM tblContractItem s
> AS A WHERE A.Contract_Numb er = tblContractItem s.Contract_Numb er And
> A.ID < tblContractItem s.ID) + 1 AS NumberToAssign FROM
> tblContractItem s;
>
> 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:
>
> qryGetNumberToA ssign
> SELECT Box, Contract_Number , Contract_Number & '-' & (SELECT
> Count(A.ID) FROM tblContractItem s AS A WHERE A.Contract_Numb er =
> tblContractItem s.Contract_Numb er And A.ID < tblContractItem s.ID) + 1 AS
> NumberToAssign FROM tblContractItem s;
>
> 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
>[/color]

[jimfortune@compumarc.com]

Re: Counting Up Until Field Value Changes?

ChadDiesel wrote:[color=blue]
> Thanks for the help.
>
> Chad
>[/color]

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