×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Reset incrementing table field when another field changes

thread700-1743662

Reset incrementing table field when another field changes

Reset incrementing table field when another field changes

(OP)
Access 2013

I have a table that includes multiple records per account number. I would like to create a field that numbers each record so that every record has a number starting at 1 and increasing by 1 for each record for that account number. For example:

Original Table
RPID-----PropClass
1----------3400
1----------4000
2----------1600
2----------1700
2----------3400
3----------1600
3----------1700
3----------4100

New Table
RPID-----PropClass-----SeqNo
1----------3400----------1
1----------4000----------2
2----------1600----------1
2----------1700----------2
2----------3400----------3
3----------1600----------1
3----------1700----------2
3----------4100----------3
3----------4200----------4

RE: Reset incrementing table field when another field changes

What are the fields in the original table that determine the sequence? It looks like RPID and PropClass might be a good candidate. Do you think you need to store the seqence or can it be calculated on-the-fly?

You can try a query with DCount()

CODE --> sql

SELECT Original.RPID, Original.PropClass, 
DCount("*","Original","RPID=" & [RPID] & " AND PropClass <=" & [PropClass]) AS Seq
FROM Original;

Duane
Hook'D on Access
MS Access MVP

RE: Reset incrementing table field when another field changes

(OP)
The change in RPID is what I am using as a flag to stop and start counting. The Prop Class is just information. So I want to count from one and increment by one every record that has the same RPID and reset the counter to one at each new RPID

RE: Reset incrementing table field when another field changes

taxmanrick,
You didn't answer my question about the sequence/order. Records are like marbles in a bucket. There is no order. If you want to sequence them, you need to provide the specifications as to which record gets assigned 1 vs 2. If your data doesn't provide this order then you will need to create something.

Did you need to store the resulting calculation or can it be generated dynamically? Does the recordset need to be edited or is it for a report?

Duane
Hook'D on Access
MS Access MVP

RE: Reset incrementing table field when another field changes

(OP)
Sorry about that.

taxmanrick,
You didn't answer my question about the sequence/order. Records are like marbles in a bucket. There is no order. If you want to sequence them, you need to provide the specifications as to which record gets assigned 1 vs 2. If your data doesn't provide this order then you will need to create something.

I don't necessarily need them in any order within each RPID, they just need to be in RPID order before numbering.

Did you need to store the resulting calculation or can it be generated dynamically? Does the recordset need to be edited or is it for a report?

Yes I need to store the seq no in the table so that I can use that number for something else

RE: Reset incrementing table field when another field changes

Taxman, you need to read Duane's first request again, very carefully!

RE: Reset incrementing table field when another field changes

Just a wild guess here, but instead of SeqNo as Number, can you have it as Date/Time field and with any INSERT INTO that table you just insert today's date and time? Ordering your records would be easy, plus you may have another benefit of knowing when the record was inserted. May or may not be helpful.

Just a guess...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Reset incrementing table field when another field changes

(OP)
Well maybe I can rephrase what I'm trying to do. I would like to know for each RPID, not only how many records are there with that ID but give each record with that ID a sequential number starting at 1 and increasing by 1. At each change in ID, reset it to 1 and begin again. I don't need them in any particular order other than grouping the records together that share the ID. I do need the sequence number stored in the table.

RE: Reset incrementing table field when another field changes

Did you try use my suggested SQL but change it to an update query? If PropClass is not unique within an RPID group, there will be duplicate SeqNo values in a group.

CODE --> sql

Update Original 
Set SeqNo =DCount("*","Original","RPID=" & [RPID] & " AND PropClass <=" & [PropClass]) ;

Duane
Hook'D on Access
MS Access MVP

RE: Reset incrementing table field when another field changes

No need to rephrase, your requirement was perfectly understandable!

RE: Reset incrementing table field when another field changes

I do need the sequence number stored in the table
Something like this ?

CODE --> SQL

SELECT A.RPID,A.PropClass,COUNT(*) AS SeqNo INTO [new table]
FROM [original table] A INNER JOIN [original table] B ON A.RPID=B.RPID AND A.PropClass>=B.PropClass
GROUP BY A.RPID, A.PropClass

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close