generate unique no based on criteria

  • Thread starter Thread starter Newbie
  • Start date Start date
N

Newbie

Office 2003

I have a resultset which includes a job no and a parent code. From this
parent code/jobno combination I need to generate an Item Ref number
(starting at 1) for each record with the parent code and then start back at
1 for each change in Parent code

eg
Result set

Job No ID ParentCode ItemRef (generated code)
ABC SUN 01 01/1
XYZ DEV 01 01/1
XYZ SUN 02 02/1
FGH TRM 02 02/1
FGH SAD 02 02/2
FGH MAD 02 02/3

How can I achieve this?

Thanks
 
Hi,
i think you have to open recordset on this table, sorted by ParentCode, go
through all records and assign ItemRef there:

some air code:

do until rst.eof
if strParentCode<> rst!ParentCode then
'reset counter
intCouner=1
end if
rst.edit
rst!ItemRef =rst!ParentCode & "/" & inCounter
intCouner=intCouner+1
rst.update

rst.movenext
loop
 
Back
Top