Question about showing all the combo box selections on report even if zero count (1 Viewer)

[bkalimili]

I have a query that groups by 2 fieds application type and outcome.

There are only 2 application type = "Access" and "Return"

Where as the Outcomes are many.( Prevention, mediation, Attysecured, Withdrawn, Unfounded )

For a given date range lists entries to display the total and categorize them into various application types and Outcomes
Current report style lists
**********
eg

if it list 40 as total count.

Access - 35

Outcomes
Prevention 20
Mediation 15
sub total - 35
Return - 5
Prevention - 5

sub total - 5

Total – 40

SQL statement for above is

SELECT intl.applicationtype, intl.outcome, Count(children.childid) AS CountOfchildid
FROM intl INNER JOIN children ON intl.callid = children.callid
WHERE (((intl.internationaltype)="Incoming") AND ((children.casestatus)="Recovered" Or (children.casestatus)="Closed") AND ((children.processdate) Between [Forms].[FF_QPR].[Date1] And [Forms].[FF_QPR].[Date2]) AND ((intl.ishaguecase)=True))
GROUP BY intl.applicationtype, intl.outcome;

*********************
I would like the new style to show the outcome combo box choices in the below manner. The outcome combo box has total 5 values.

1) Prevention 2) Mediation 3) AttySecured 4) Unfounded 5)Withdrawn

Access -35
Outcomes
Prevention - 20
Mediation - 15
Atty Secured – 0
Withdrawn – 0
Unfounded - 0

Subtotal - 35

Return - 5
Withdrawn - 5
Mediation – 0
Atty Secured – 0
Prevention – 0
Unfounded - 0

Atty Secured - 0

Subtotal - 5

Total - 40

Any ideas how this can be achieved.

 

[Access2000_JS1]

if you add another type you will get 6 then 7 etc. You could drop each in a detail section.

However, you may also consider appending each to a next ie

s = a + B + c ... and displaying it as text

You may also hard code each field in your form and setting its visible attribute, or dynamically create and display fields as required.

I personally would drop each into a detail section as it is a one to many relationship.