472,976 Members | 1,434 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,976 software developers and data experts.

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
5 3444
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
by: Mike N. | last post by:
Hello: I have a form that contains a multiple-select field that has 12 options in it. I would like the user to be able to select UP TO FOUR of those options. If they select more than four, I...
18
by: ChadDiesel | last post by:
I appreciate the help on this group. I know I've posted a lot here the last couple of weeks, but I was thrown into a database project at my work with very little Access experience. No other...
34
by: Mo Geffer | last post by:
Greetings: I have a question about the output of the sample program in section 1.5.3 Line Counting of K&R, Second Edition. Here's the program: /****************************************/...
7
by: sathyashrayan | last post by:
Group, Following function will check weather a bit is set in the given variouble x. int bit_count(long x) { int n = 0; /* ** The loop will execute once for each bit of x set,
11
by: Al | last post by:
Can anyone tell me if there's a way to return the amount of times a button is "clicked" I need to change text in a text box and that text changes each time the button is clicked-up to 6 times...
10
by: cj | last post by:
I'm writing a TCP/IP server app that will have many simultaneous connections. The main thread listens for new connections and starts a thread to handle each requested connection. These are short...
4
by: Dado | last post by:
I have a next situation with the textbox field: A - B = C 1. How to fill the A fill with the data from my previous recordset ? Can I do it with the expression builder ? 2. I want that every...
1
by: BillH | last post by:
As new Records are added I need to in a Query, count the number of occurrences of several specific Fields that have change from the previous record. As a reference point, each Record is unique for...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
4
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.