Selecting records from parent table according to child table

[addygoyal]

hey guys again come across a typical one
i have got this feild as primary key of the parent table for which i have as foreign key in the child table

Say {ParentTable.Primarykey} maps to {ChildTable.ForeignKey}

So i have multiple records for this foreign key in the child table. the child table has a date feild say {ChildTable.DateField}
i want all the records from the child table for a {particular foreign key} where any of the records with the {particular foreign key} has value of{ChildTable.DateField} is less than current date

i will give example of the records
Foreign key Date Passengers
1 30/05/2003 XYZ
1 31/05/2003 XYZ

2 31/05/2003 ABC
2 01/06/2003 ABd
2 02/06/2003 ABd
2 02/06/2003 ABd
current date = 03/06/2003

in my formula right now i am using
{ChildTable.DateField}- currentdate = 1
this will give me results as
2 02/06/2003 ABd
2 02/06/2003 ABd

but i want the resuls as
2 31/05/2003 ABC
2 01/06/2003 ABd
2 02/06/2003 ABd
2 02/06/2003 ABd

So hopin you ppl turn up with something
Thank you

 

[kenhamady]

You are doing record selection, and you need to do group selection.

Group the report by the Key field, then select the date and calculate the minimum of each group with a summary field. Then you can use the follwing as a group selection formula:

Minimum ({ChildTable.DateField} , {KeyField} } < currentdate

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com