This picks up on last months thread showing how to “fill in the gaps”  in a report that is missing a product, salesman or date.  The second option is to add a primary table that has all the values you want, and to link from this table to your existing tables, using an outer join.

Lets take the example from the last post where we want ALL salesmen to show up, even if they have no sales this month.  So we find a table that includes all of the salesman and use this table as the reports primary table.  Link this table to your transaction table with a “Left Outer” join.  This tells the database that you want all records from the primary table and only matching records from the transaction table.  Now all salesmen will show up and will also be available for cross-tabs and charts.

However, there is a serious weakness to this approach. If you put ANY criteria on the transaction fields, you cancel the effect of the outer join.  So in our example, if you were to select transactions for one month, you would then lose all salesmen who had no sales in that month.  To keep the outer join behaviour in place you have to eliminate ALL criteria from the ‘Outer’ or optional table.  That means including ALL transactions for ALL months in the report.  You can suppress the details of the records you don’t want to see and you can write formulas to make sure that only those records in the month are included in your totals.  But if your database is large, this makes for a very slow report.

There are methods in more advanced SQL to filter your outer joins. You can read about some of these, and a full explanation of Joins, in The Expert’s Guide to SQL Expressions, Options and Commands.