Supression Problem

[TheBlondOne]

I am creating a report for children that have gone into care and also gone out of care, basically if a child has gone out of care he/she has an end date and if they are still in care then they dont have an end date. The report is fine except when you have a child that has gone out of care and then gone back into care. What i need to do is if they have gone out of care and gone back in i only want to show the latest record therefore supressing the old record. I need to somehow scan the report to find duplicate names and then check to see if one has and end date and the other hasnt and if they havent got an end date in one record then i need to supress the record with an end date hopefully that makes sense. Any one got any ideas how i can do this. Thanks

 

[Naith]

A couple of solutions spring to mind.

You can either query some sql which will return each child where the child's care start date = maximum(care start date), group by child.

This only returns the correct number of records to the report.

The other alternative is to group by child, and order your data by care start date ascending. Suppress your details section, and place all your information in the child group footer. Each footer will display only the most recent record for each child.

This will return excess records to the database, but I would go with the latter solution as it means you can keep contact with the database without a query 'middle man'.

Good luck with your report,

Naith