<snip>

<snip>

tjh,

Tables:

Please forgive the dates appended to the table names.

Note: I specified a new column "TitleID" because according to the
desired results, the order of TitleName must be presevered, and
without some other column there to preserve that order, I don't
think it's going to work.

CREATE TABLE Titles_20051129_1
(TitleID            AUTOINCREMENT
,TitleName          TEXT(5)
,CONSTRAINT pk_Titles_20051129_1
           PRIMARY KEY (TitleID)
)

Sample Data:

1, Jim
2, Jan
3, John
4, Al
5, Frank
6, Sam
7, Kate
8, Sally
9, Susie
10, Amy
11, Kevin
12, Phil

Query:

SELECT T1.TitleName
     ,(SELECT SWITCH(COUNT(T1.TitleID) MOD 3 = 1, 1
                    ,COUNT(T1.TitleID) MOD 3 = 2, 2
                    ,COUNT(T1.TitleID) MOD 3 = 0, 3)
         FROM Titles_20051129_1 AS T01
        WHERE T01.TitleID <= T1.TitleID)
      AS NumberSeq
 FROM Titles_20051129_1 AS T1

Results:

TitleName, NumberSeq

Jim,       1
Jan,       2
John,      3
Al,        1
Frank,     2
Sam,       3
Kate,      1
Sally,     2
Susie,     3
Amy,       1
Kevin,     2
Phil,      3

That appears to be a copy of the desired results.

If you don't have, or can't add, a column equivalent to TitleID,
then you'll have to order TitleName.

Query 2:

SELECT T1.TitleName
     ,(SELECT SWITCH(COUNT(T1.TitleName) MOD 3 = 1, 1
                    ,COUNT(T1.TitleName) MOD 3 = 2, 2
                    ,COUNT(T1.TitleName) MOD 3 = 0, 3)
         FROM Titles_20051129_1 AS T01
        WHERE T01.TitleName <= T1.TitleName)
      AS NumberSeq
 FROM (SELECT TOP 100 PERCENT
              T02.TitleName
         FROM Titles_20051129_1 AS T02
     ORDER BY T02.TitleName) AS T1

Results 2:

TitleName, NumberSeq

Al,        1
Amy,       2
Frank,     3
Jan,       1
Jim,       2
John,      3
Kate,      1
Kevin,     2
Phil,      3
Sally,     1
Sam,       2
Susie,     3

Of course, this doesn't quite match the original desired results.

Sincerely,

Chris O.

Can be done. This example assumes that ID is the primary key and
demonstrates

sequential numbering of records 0123456789  (RecNum)
repeated numbering 111122223333    (SlowStep)
cyclic numbering 123412341234   (Cyclic)

SELECT A.ID,

(SELECT COUNT(ID) FROM [TheTable] AS B WHERE B.ID < A.ID) AS RecNum,

Int(((SELECT COUNT(ID) FROM [TheTable] AS C WHERE C.ID < A.ID) Mod
52)/4)+1 AS SlowStep,

Int((SELECT COUNT(ID) FROM [TheTable] AS C WHERE C.ID < A.ID) Mod 4)+1
AS Cyclic

FROM [TheTable] AS A
ORDER BY A.ID;

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.