Locating and Using data on last record..

[Lochbel]

Using CR10.

I have a scenario where there are multiple records in a (many side) table.

I want to locate the last record (by time) for a certain field value within the (many side) table and once located use another field value (on this record) in a formula.

(NOTE: The last record is not the last record in the table.)

tia.






Lochbel

 

[Lochbel]

oops , bit more detail...
Data source is SQL Server 2000.



Lochbel

 

[lbass]

One approach would be to create a command (database->database expert->add command) something like the following:

Select max(table.`date`),table.`groupfield`
from `table` table
where table.`field` = 'myvalue'
group by table.`groupfield`

Then link the command to your table by both {table.groupfield} and {table.date}, and while the link is selected choose "link options" and choose "Enforce both". This solution assumes that you wanted the maximum date per some group field. This should return one record per group, and will allow you to use another field in the row in a calculation.

-LB

 

[Lochbel]

Thanks LB . Should do the trick.


Lochbel