Incrementing Number for Old Records

[Sheila.deJesus]

I have 4 tables on my dbase. On the last table underlies all records pertaining to customer payments.

tblCollectionData

ColID - PK
PolID - FK
Amount
ORDate
ORNumber

I want to add another field which i would name -- CollectionNumber. In this field, i need an incrementing number for each PolID.

For each PolID (no duplicates), there could be 1 to 6 records of payment. I need the number to start at 1 for each unique PolID.

Please help me on this. I've been looking at different posts but i still can't find answer. :-(


Thanks!
Sheila

 

[lightray]

From what I can gather you need another table if you want the polID to increment.


tblCollectionData

ColID - PK New table
PolID - FK--------------------PK-PolID Autonumber
Amount Method of Payment (6 records)
ORDate
ORNumber

Not sure how they can all start at one if they are unique.
Are you maybe wanting a list to just lookup and populate the field in tblCollectionData?

 

[Sheila.deJesus]

Hi lightray. Thanks for your response!

I need an incrementing unique number for each PolID.

If i add a new PolID, the ColNo should start at again at 1.

I hope i explain myself well.

Thanks again.

Sheila

 

[lightray]

I need an incrementing unique number for each PolID.

thought? why is this called PolID does it relate to another table?

If i add a new PolID, the ColNo should start at again at 1.

Set the value of ColNo to 1, if we can sort out what the PolID is

 

[Sheila.deJesus]

Yes it does. It's the PK of my 3rd table which is tblPolicyData.

 

[lightray]

Okay! this was not mentioned ...
So are saying, that if you add a new record to the tblPolicyData
you want ColNo ( in tblCollectionData) to be set to 1?

What will it normally be?

 

[Sheila.deJesus]

Yes. And i already have the code for new records using Candace Tripp's "Autonumber without autonumber" sample database.

What i need is a code to apply to my existing records.

A million thanks!
Sheila

 

[gemma-the-husky]

no easy way to do this with an update query. Get a query that sorts your records in the order you want to sequence them,excluding records with numbers (although if you have some numbered and others not, you may want to renumber them all) then use a recordset

lastpoi=0
counter=0
while not rst.eof
if rst!poiid<>lastpoi then
lastpoi=rst!poiid
counter=0 'stores the last number used - starts at 0
else
counter=counter+1
end if
rst.edit
rst!counter = counter+1 'need to increment - counter is storing the previous value
rst.update
rst.movenext
wend

rst.close

 

[Sheila.deJesus]

Thanks! I'll give it a try.

Cheers!
Sheila