Reset incrementing table field when another field changes

[taxmanrick]

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

 

[dhookom]

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()

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

Duane
Hook'D on Access
MS Access MVP

 

[taxmanrick]

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

 

[PHV]

Another way (without DCount)

SELECT A.RPID,A.PropClass,COUNT(*) AS SeqNo
FROM yourTable A INNER JOIN yourTable B ON A.RPID=B.RPID AND A.PropClass>=B.PropClass
GROUP BY A.RPID, A.PropClass

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

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

 

[taxmanrick]

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

 

[SkipVought]

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

 

[Andrzejek]

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.

 

[taxmanrick]

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.

 

[dhookom]

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.

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

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

No need to rephrase, your requirement was perfectly understandable!

 

[PHV]

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

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
FAQ181-2886