Looping Update Query

  • Thread starter Thread starter Outpost
  • Start date Start date
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
 
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)
 
Back
Top