Looping Update Query

O

Outpost

I have a table that has 2 fields that are named [CallNo] and the other
field is [ItemNo]. There are multiple Item numbers assigned to a Call
Number. I need to update the Item Numbers to be sequential starting
with 1 through ~. When it encounters a new [CallNo] it will update the
ItemNo starting with 1 and loop through until the next CallNo, and
repeat the process. TIA
 
J

John Spencer (MVP)

What are the field types of ItemNo and CallNo?
Are there any duplicates of ItemNo for each call no?
Are any ItemNo equal to null (blank)?

Assuming the answers are:
Number and Number
No and
No, then you might try something like the following on a COPY of your table

UPDATE YourTable
SET ItemNo =
DCount("*","YourTable","CallNo=" & CallNo & " AND ItemNo=" & ItemNo)
 
Top