2 Tables

[kela97]

I have 2 tables.
Table 1:
Ind1 Desc1 Fixed Rate1
Ind2 Desc2 Fixed Rate2
Ind3 Desc3 Fixed Rate3
Ind4 Desc4 Float Index1
Ind5 Desc Float Index2

Table 2:
Index1 01/01/2001 A1
Index1 08/29/2003 A2
Index2 01/01/2002 B1
Index2 01/01/2003 B3

Now I want a report that displays when run on 08/29/2003
Ind1 Desc1 Fixed Rate1
Ind2 Desc2 Fixed Rate2
Ind3 Desc3 Fixed Rate3
Ind4 Desc4 Float A2
Ind5 Desc Float B3

When the 3 column in Table one is Float, then use the value in column 4 to look in Tabel too. If a record corresponding to that index1 and the date on which the report is run is available place the value on column 3 fo table 2 which is "A2".
In case the exact date is not available, get the most recent date which is 01/01/2003 for Index2. For this row print B3.

Basically I need to have a query to get the row from table 2 where index = index and date = max(date field).

I am able to get this using a subreport, but that is giving me real heart ache because I cannot sum up the rows in the group total as it does not capture the information coming from the subreport.

Any help will be appreciated.

 

[SmpTraining]

You can get the values back from a subreport by using 'shared variables'. In this way you can then accumulate them in the main report using more variables. This standard 3-formula approach method is well documented - try searching the FAQ's.

Steve Phillips, Crystal Consultant