Displaying single detail records only

[FoxGolfer]

We have a simple parent and child table system and we are using CR9.0. Each parent can have any number of child records. But what we want to do is print a report that displays the parents that only have one child record.
Any help would be appreciated; thanks in advance.

 

[MJRBIM]

1.) Group on the parent record.

2.) Count the details in the group footer.

3.) Suppress the sections GH, DETAIL, GF - where the count is greater than 1.

That solution will report on everything, but only show the single detail records. It may not give great performance based on the size of your dataset, but it's the quickest way to get what you need.

 

[rosemaryl]

Dear FoxGolfer,

You do not state your database type, but assuming sql you could do a SQL Expression.

Create A SQL Expression from the Field Explorer, name it (I will use countchild for my example) and then in the resulting sql expression dialog do something like:

(Select Count(C.PrimarykeyChild)
from Owner.ChildTableName C
where C.ParentRefField = Table."ParentTableField")

To set up the report, base the report on the view or table for the parent records and the last field in the sql expression would be the primarykey that the parent and child records share. This will result in a count of the child records.

Now, in there report selection criteria do:

{%countchild} = 1

That would result in only pulling records for this parent table where the count of the children = 1

Regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[FoxGolfer]

Thanks to MJRBIM and Ro for your timely responses and helpful suggestions. Ro, BTW, it's a VFP database.
Problem solved; thanks again.