467,879 Members | 1,237 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,879 developers. It's quick & easy.

Counting Up Until Field Value Changes?

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

Nov 13 '05 #1
  • viewed: 2993
Share:
5 Replies
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.

Nov 13 '05 #2
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.

Nov 13 '05 #3
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

Nov 13 '05 #4
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

Nov 13 '05 #5
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

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

14 posts views Thread by Mike N. | last post: by
18 posts views Thread by ChadDiesel | last post: by
34 posts views Thread by Mo Geffer | last post: by
7 posts views Thread by sathyashrayan | last post: by
10 posts views Thread by cj | last post: by
4 posts views Thread by Dado | last post: by
1 post views Thread by BillH | last post: by
reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.