How to display all records in a crosstab?

[beacon5]

Hi everybody,

[Crystal Reports 8.5]

I created a summary report which has a Microsoft Access 2003 database as the datasource, and have inserted a crosstab in the Report Footer. In the database, there's a table called tblCallLog and another table called tblProblem, which I've added to the report, where tblProblem is Left Outer Joined to tblCallLog on the Problem field (this is a text field).

I've added the field Technician to the Column of the crosstab and the Problem field from tblProblem to the Row. I've added a count of the Problem field from tblCallLog as the Summary.

When I run the report, I expected that, because I Left Joined tblProblem to tblCallLog, that all the records in tblProblem would display in the crosstab and would show a zero for the count if none of the technicians had logged their call by that problem type. However, this is not the case and it's just excluding those problems altogether.

What am I missing to make this work as I intend, as in the example below where 'Hardware' is zero for all three techs (currently 'Hardware' won't show at all on the crosstab)?

               Tech1     Tech2     Tech3
Password           2         7         4
Software           1         0         3
Hardware           0         0         0

Thanks,
beacon

 

[lbass]

You would need to do a left join FROM Problem TO Call Log, with no selection criteria on the Call Log table--but this doesn't guarantee all technicians would show up. This assumes that the Hardward field is in the Problem table, and the Technician is in the Call Log table.

-LB

 

[beacon5]

The join is currently a left join FROM tblProblem TO tblCallLog...that's the issue, it appears to be setup properly, but it's not still not displaying all of the Problems.

Hardware is a record in the Problem field of the tblProblem and technician is a field in the tblCallLog.

There are enough calls and problem types that the technicians should always show up.

 

[beacon5]

Thanks for your help LB, but I'm going to cut my losses and just create a manual crosstab.

I hate that I can't get this figured out, but I have to finish this report today, so I can't spend any more time troubleshooting it or asking for help.

Thanks again,
beacon

 

[lbass]

If you added any selection criteria on the calllog table it would effective undo the left join.

-LB