Count records before and after a person starts taking classes in multiple areas

[kernal]

I'm using crystal 9 with a csv file:

CSV File

person_id term area class

1 1014 TEST 1-1
1 1014 TEST 1-2
1 1048 TEST 1-3
1 1054 TEST 2-5
1 1062 PRP 4-5
1 1062 TEST 5-5
1 1064 PRP 6-7
1 1064 TEST 7-8
1 1076 TEST 8-8

2 1048 TEST 8-8
2 1048 TEST 8-9
2 1058 TEST 9-9
2 1058 PRP 6-7

I need the report to have:

person_id 1 took 4 TEST classes before they started taking the TEST and PRP in the same term "1062" and
1 TEST class after term "1064"

person_id 2 took 2 classes before they started taking the TEST and PRP in the same term "1058" and 0 TEST classes after term "1058"

I hope this makes sense and help is very appreciated.

Thanks

 

[Charliy]

Took 4 classes before PRP is easy, but how do you know to start counting after the 1064 instead of the 1062?

 

[lbass]

The following allows for multiple instances of multiple classes (not sure that can happen). The formula counts the gaps between multiple classes as "Before" unless it is the final set, in which case it is shown as "After"

First insert a group on person_ID and a second group on Term. Then create these formulas:

//{@Reset} to be placed in the Person ID group header:
whileprintingrecords;
numbervar cnt;
numbervar array mult;
numbervar i;
if not inrepeatedgroupheader then(
cnt := 0;
mult := 0;
i := 0


//{@Accum} to be placed in the Group #2 group header:
whileprintingrecords;
numbervar cnt;
numbervar array mult;
numbervar i;
if count({table.term},{table.term})=1 then
cnt := cnt + 1 else
cnt := cnt;
if count({table.term},{table.term}) > 1 or
{table.person_ID} <> next({table.person_ID}) then (
i := i + 1;
redim preserve mult;
mult := cnt;
cnt := 0
);
cnt

{@display} to be placed in the Group Footer #1:
whileprintingrecords;
numbervar array mult;
numbervar j;
stringvar x := "";
for j := 1 to ubound(mult)-1 do (
x := x + totext(mult[j],0,"")+", "
);
if len(x)>=2 then
left(x,len(x)-2)+" Before; "+
totext(mult[ubound(mult)],0,"")+" After"

-LB