Help Using a parameter to filter records

[ouzojd]

My report requests a date range and filters records in that date range on the 'GroupMemberships' table. Unfortunately the table needs to be linked by the 'MemberNo' field in the 'Memberships' table where it is a primary key. The 'GroupMemberships' table can have the same member number in different records more than once in the date range parameter. I need to retrieve only the record with the latest Date in the parameter range.

Any Ideas?

 

[Madawc]

Are you saying that you want to retreive several records for a 'MemberNo' if the latest date is in the parameter range?

If so, you can't do it by record selection, which relies on some feature of the individual 'row', one or more linked records.

You can do it by grouping by 'MemberNo', then doing a summary total to show the maximum date for that group. You can then use Group Selection to suppress the unwanted groups.

Madawc Williams (East Anglia, UK)

 

[ouzojd]

Thanks, I'll give that a go. I'm actually only trying to retrieve the latest record for a MemberNo in the parameter range. In SQL I use a query along the line lines of

Select MemberNo from GroupMemberships where GropMemberships.EffDate = (Select Max(Effdate) where GroupMemberships.MemberNo = Memberships.MemberNo)

That query will return the latest record for a member but it if I do a report on last month and they have a new entry this month that isnt going to work for me.

 

[MrBillSC]

In Crystal record select formula select the 'GroupMemberships' table based on you date range parameter. Then you can try using the NthLargest function in the group selection formula...
or add a sort by date and report your data in the MemberNo group footer, suppressing the detail section.
MrBill