Using a Function in a query - Case Statement?

[c8tz]

Hi,

I need to create a query that: from a column BlockNo creates a
column:RowNo and a second ColumnosNo

There are 72 BlockNo ie. BlockNo 1 -> BlockNo 72

There are 8(1-8) different RowNo and 9(0-8) different PosNo.

so if BlockNo=1 then RowNo = 1 and PosNo=0
if BlockNo=2 then RowNo=1 and PosNo=1
.....
.....
...
if BlockNo=9 then RowNo=1 and PosNo=8
if BlockNo=10 then RowNo=2 and PosNo=0
etc..

till
if BlockNo=72 then RowNo=8 and PosNo=8

**

I've used an IIF statement for the RowNo Column but too long for PosNo
- Maybe a Case Statement would be better - but that beats me -

I would appreciate all assistance -

Thanks

C

 

[John Spencer]

How about a little math instead of a complicated expression

((BlockNo-1)\8) + 1 will return 1 for 1 to 8, 2 for 9 to 16, etc

(BlockNo + 8) Mod 9 will return 0,1,2,3,4,5,6,7,8 and then repeat

Using those formulas you get the following (BlockNo, RowNo, PosNo).

10, 2, 0
12, 2, 2
....
72, 9, 8



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[John Spencer]

Whoops that does not seem to be quite correct for the rowNo calculation.

((BlockNo-1)\9) + 1 will return 1 for 1 to 9, 2 for 10 to 18, ...

(BlockNo + 8) Mod 9 will return 0,1,2,3,4,5,6,7,8 and then repeat

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================