Update field to Incremented Number (1 Viewer)

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:02
Joined
May 21, 2018
Messages
2,835
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
Code:
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
Code:
UPDATE tbldemo
SET    tbldemo.personid = Serialize("tbldemo", "personid", [personid])
                          + 18617;
Or you can do it in two steps
Code:
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

Registered User.
Local time
Today, 20:02
Joined
Oct 30, 2008
Messages
798
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

You've got your good things, and you've got mine.
Local time
Today, 04:02
Joined
May 21, 2018
Messages
2,835
The serialize function is in Isladogs link he provided.
Change the value in qryUpdate.
 

Attachments

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom