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
