Automatically tick every 1 in 10 records? Possible? If so how.

R

Richard Horne

Hi guys, I have a query which produces a table with two columns, RefNo and
Check

The table is in ref order, but the number are not consecutive, i.e. they
could be 1, 4,5,6,9,14,15,18,20 There are 1830 records and I want every one
in 10 to have the tick box Check ticked.

Is there a way I can do this automatically. I have similar queries whereby I
need to tick every one in 5 and one in 2, so if an automatic solution can be
found it would help me a great deal.
 
P

peregenem

Richard said:
The table is in ref order, but the number are not consecutive, i.e. they
could be 1, 4,5,6,9,14,15,18,20 There are 1830 records and I want every one
in 10 to have the tick box Check ticked.
Click to expand...

SELECT DT1.RefNo, IIF(DT1.rank MOD 10 = 0, 'Y', 'N') AS checked FROM
(SELECT T1.RefNo, (SELECT COUNT(*) FROM MyTable WHERE RefNo <=
T1.RefNo) AS rank FROM MyTable AS T1) AS DT1
 
Top