Is there a way to lookup a file from RECAP and display the value in subfoot?
I have a summary row that displays department summary including no of employees, total sal.
Ranks for the salary are defined in a seperate table(rank 1 if sal 0 - 100,000, rank 2 if sal 100,001 - 200,000 etc..) I need to pickup the appropriate rank for each department depends on dept total sal.

Thanks in advance.
kj

I forgot to mention the environment. I am using WebFocus 5.2.1 on NT.
Where-based joins will work with a table request. But the values are generated with a RECAP statement on SUBFOOT on BY field.

Too make it clear I will explain with an example.

rank table
low sal high sal rank
0 15,000 6
15,001 20,000 1
20,001 30,000 2
30,001 50,000 3
50,001 100,000 4


deptno Emp no Salary rank
1 11111 20,000 1
22222 21,000 2
33333 45,000 3
44444 12,000 6
dept1 avg 24,500 2
2 55555 10,000 6
66666 14,000 6
77777 55,000 3
dept2 avg 26,333 2

I could display rank for each employee with a WHERE-BASED join.
I could not get the ranks for dept summary.
Any kind of help would be greatly appreciated.

Thanks,
kj

This may be an example where you should compute all your values first in two sentences. eg
SUM AVE.SALARY AND COMPUTE DRANK/I3=IF AVE.SALARY LE 15000 THEN 6 ELSE IF AVE.SALARY LE 50000 THEN 4 ELSE..
BY DEPARTMENT
SUM SALARY AND COMPUTE PRANK/I3=IF SALARY LE 15000 THEN 6 ELSE IF SALARY LE 50000 THEN 4 ELSE ...
BY DEPARTMENT BY EMPLOYEE
...
You can NOPRINT some fields and replace them in a RECAP, or you can HOLD the data set and do your report from the HOLD file.