Update field to Incremented Number (1 Viewer)

[kirkm]

Thank you.

 

[MajP]

The question was is it possible to use an update query to Number a field starting at 18618.
(18618 could be any number of course, that was just an example).

You would think this would work but it is an unupdateable query because of the subquery

UPDATE tbldemounbound AS A
SET    A.personid = (SELECT Count(*) + 18618
                     FROM   tbldemounbound AS B
                     WHERE  B.personid < A.personid);

However using a function from @isladogs

UPDATE tbldemo
SET    tbldemo.personid = Serialize("tbldemo", "personid", [personid])
                          + 18617;

Or you can do it in two steps

SELECT A.personid,
       (SELECT Count(*) + 18618
        FROM   tbldemounbound AS B
        WHERE  B.personid < A.personid) AS Rank
INTO   updateranks
FROM   tbldemounbound AS A;

UPDATE tbldemounbound
       INNER JOIN updateranks
               ON tbldemounbound.personid = updateranks.personid
SET    tbldemounbound.personid = [rank];

 

[kirkm]

Do we have Serialize??
I tried your second example but probably did something wrong as it Updated 0 rows and looking at updateranks there were the correct number of records, nothing in personid field and Rank were all 18618. May have done something wrong but did it a couple of times

 

[MajP]

The serialize function is in Isladogs link he provided.
Change the value in qryUpdate.