Counting Up Until Field Value Changes?

[chaddiesel]

Hello,

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 on a newsgroup 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. I'm very new to Access, so examples would be helpful. My table is called [Label_Info] and the contract number field is [Contract_Number]

Thanks,

Chad

 

[Jon K]

DMax() is good during data input when you want to store the numbers in a NumberValue field in the table.


To produce the numbers in a query on existing records, you can use the DCount() function if the table is small and there is a unique record identifier (within each contract number) that can be sorted such as an autonumber field.

As an illustration, I have attached a sample database which contains an autonumber field [RecordID]. The field is used in the DCouunt() function in the query:-

SELECT [RecordID], [Contract], [Packing], DCount("*","tblData","[Contract]='" & [Contract] & "' And [RecordID]<=" & [RecordID])+0 AS [Number Value]
FROM tblData;

In the example, [Contract] is a text field. If it is a numeric field, the single quotes surrounding its value need to be removed from the query.


However, using DCount() like this is very inefficient. If the table is large (or if there is no unique record identifier in the table), you can add a ValueNumber field in the table, open the records as a recordset in VBA and loop through the records to add the required numbers.
.

 

[chaddiesel]

Worked Great

I used the DCount example you posted and it worked great. The table will eventually become pretty large as we load more and more shipments. Do you think this will be a problem? Thank you very much for the help. I really appreciate it.

Chad