Get Latest Date of Multiple Records

[HCVerke]

Table A contains unique records. (one)
Table B has many records for each in Table A with different dates. (Many)

I wish to select the records in A with the latest "dated" record in B as long as they are before a specific date.

Any suggestions?

Thanks

 

[rosemaryl]

If you group on Your Table 1 value and then suppress the details and place the detail field with that holds the value you want in the Group Footer you will get the last record.

HTH,

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[GJParker]

A technique I have used in the past would be to group by the table 1 values and then group by the date values, you would then need to create a summary in your date group - in this case Max of Date, then you could use this summary field to do a TopN sort of 1

Hope This Helps Gary Parker
Systems Support Analyst

 

[chelseatech]

The Group Selection formula will do what you need.

1) Group your data by a unique field in table A (ID)
2) You will need an equal join between table A and B
3) Select data from table B which falls prior to selected date
4) Use a GROUP SELECTION FORMULA of
{tableB.datefield} =
Maximum({tableB.datefield, {TableA.ID})

The record counter will be awry, but the details will only have one Table B record for each Table A record.

Use running totals to get the correct summaries. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm