Counting a single instance of a one to many relationship?

[rockysewl]

Hi
I have 2 tables from a workflow audit database joined in an optional one to many relationship. The resulting dataset from the query based on a date range returns:
Tbl-1 Tbl-2
Case1/StartDate/CancelledDate - PointCode1/Date1
Case1/StartDate/CancelledDate - PointCode2/Date2
Case1/StartDate/CancelledDate - PointCode3/Date3
Case1/StartDate/CancelledDate - PointCode4/Date4
Case1/StartDate/CancelledDate - PointCode5/Date5
....
Casen/StartDate/CancelledDate - PointCoden/Daten.

Records are grouped by another field in Tbl-1 (Service)

There may be zero, one or more records from Tbl-2 for each record in Tbl-1. Any one of the records in Tbl-2 may be either an intermediate audit point or a deliverable audit point. Deliverable Audit Point being considered the record of the workflow having been completed. (There may be other Audit points after this step but they don't count.)

I need to display the records in the dataset in the following manner.
Open Bal Received Completed Canc'd In Progress
Grp name: 1 3 1 1 2

OpenBalance can be calc by count where Startdate<=ParaStart and Tbl2.Daten >=ParaStart;
Received can be calc by count where StartDate in DateRange;
Completed can be calc by count where PointCode like &quot;D*&quot;;
Canc'd can be calc by count where CancelledDate in DateRange;
My problem is that In progress (PointCodes not like &quot;D*&quot could return zero, one or more records for the case and I only need to know that the case (Tbl-1) record is inprogress not count all the Audit Points.

I have tried conditional count and sum for the service footer and case footer.
I've even bastardised some of the suggestions found in this forum but to no avail.

Can someone offer a solution please?

 

[lbass]

I think you could do this using the running total editor. For the group 2 (Case) total, choose {table1.caseno), distinctcount, evaluate based on a formula:

PointCode like &quot;D*&quot;

Reset on change of Group 2 (Case).

For the Group 1 (Service) total, repeat, but reset on change of Group 1 (Service). The running totals must be displayed in group footers.

-LB

 

[rockysewl]

lbass

Thank you for your quick response. Your answer gives me the completed cases.
I actually want to count a single instance of those cases which do not have a &quot;D*&quot; Audit Point (PointCode not like &quot;D*&quot however there may be one or more of audit point records for each case in the dataset.

regards
rockysewl

 

[lbass]

Sorry for not reading more carefully. Try the running totals I mentioned earlier, but use the following formula:

not({table.PointCode} like &quot;D*&quot

Be sure to do the distinctcount on the case number. This works when I test it.

-LB

 

[rockysewl]

Thanks lbass

Worked for me too!

regards