MER-logic puzzle

[mark r]

The result of a query is

ID_unique text1 text2 text3 text4 p1 p 2 p3
p4
1 36711 11111 36711 23232 1 2 3
4
2 36244 36244 77777 44444 1 2 3
4
3 44711 45551 38888 1 2
3
4 66666 66666 36733 74410 1 2 3
4
5 11111
1

etc.

P1 P2 P3 P4 are "pointers"
They are created by P1: iif(Len(text1)&"">0,"1","") . . . . one for
each "pointer"

The challenge is that if for any given record TEXTsubsequent is the same
character value as TEXT previous, then the pointer value should be the same
as the pointer for TEXTprevious. for example in record 4:

4 66666 66666 36733 74410 1 2 3 4

should be:

4 66666 66666 36733 74410 1 1 3 4

where P2 should be 1, not 2, since the 66666 in text 2 already appears in
text1.

What nesting logic should I use?

 

[John Spencer]

Perhaps the following

IIF(Len(Text1 &"">0,1,"")

IIF(Len(Text2 & "") > 0 AND Text2 & "" <> Text1 & "", "2","1")

IIF(Len(Text3 & "") > 0 AND Text3 & "" <> Text2 & "", "3","2")

Or more complex but perhaps more accurate for your purposes

SWITCH(Len(Text2 & "") = 0,"", Text2 <> Text1 & "" , "2", Text2 = Text1,
"1")
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..