Need to report null fields too 2

[gillf]

CRW v9. Purpose of report to show sales and costs per job, and to highlight where costs are incurred but no sales invoice issued (and vice versa). Database constraints mean I have to use container report (for sales) and subreport for costs. I have them joined on job number. The report is returning only data where there are sales. Please can anyone help?

 

[lbass]

Do you have a third table that includes all jobs? You would use this table as your main table and then use a left join from the main table to the sales table and a left join from the main table to the costs table. Then you would group on the job number from the main table, add sales fields for the sales figures, and link the costs subreport to the main report on the job number.

-LB

 

[lupins46]

Are you absolutely sure that the problem is caused by the subreport? If you run the main report without the subreport (that is, delete the subreport first) do you see all the main report records?

 

[gillf]

Thank you lupins46 and lbass - together you helped me sort this. First, I deleted the sub-report which didn't resolve the problem. Then I tried a left outer join and that didn't either SO - I started building the main report from scratch to see when it went wrong. It was a select expert statement restricting it to a document type - I put an IsNull statement in there as well and it was better, then the outer link brought through everything I needed. Take a star, both.