Count help -- Need zero values 1

[pblazeppd]

Hi all,

thanks for any and all help.

CRXI, SQL ODBC connection
TABLE: Apartments Table: Crime
Field: Complex Name Field: RPT#
Field: Address Field: Address
Field: Units Field: RPT_Date

I need a count of RPT# for a select criteria of RPT_Date while displaying all of the records from Apartments. I have 237 records in Apartments, while Crime is variable. The tables are joined by Address. I have grouped by Complex Name because some complexes have multiple addresses. I need zero value counts for the complexes with no RPT#'s.

OUTPUT:

APARTMENT NAME Address UNITS CFS
Brittany Square Apts 1010 Smith 125 10
Main Street Apts 123 Main 400 75
Bob's Apts 7876 Mock 50 0
ETC.

I have started a manual crosstab, but have not been able to get the zero values. Any and all help is appreciated.

Phil

 

[lbass]

What is CFS?

You need a left join FROM the {apartment.address} field TO the {crime.address} field. Then remove the record selection criteria you have which uses the {crime.date} field (and any other criteria based on the crime table). Instead, build them into a formula--create a formula in the formula editor like {@crime}:

if {crime.date} = {?daterange} then 1

This assumes you are using a parameter to specify the desired date range. Then you can insert summaries on {@crime} at the complex level.

-LB