Report Question

[JG Hildreth]

I have a report that I want to subtotal by Sales Person
and Product by time period.

3 Sales people and 5 products.

Although a salesmen might not have sold one of the 5
products during the period, I want to show that Product
with sales of zero.

My current report drops the Product from the salespersons
totals if they did not sell that product during the time
period used in the report.

Thanks in advance,

John Hildreth

 

[Douglas J. Steele]

The query on which the report's based needs to use a Left Join (or Right
Join, depending on how its stuctured) rather than an Inner Join.

In the graphical query editor, highlight the line between the tables,
right-click and select Join Properties from the context menu. It should be
clear to you which option to pick once you're there.

This will return Null for the total for the salemen with no sales. To get 0
instead, use the Nz function.

 

[Van T. Dinh]

It sounds to me that you will need a set of Queries.

1. Create a qselDummySales with Cartesian Join (i.e. no join) between
tblSalesman (or a Query returning only the 3 salesmen) and tblProduct (or a
Query that returns only the 5 Products). Add a Calculated Field "ZeroSales"
and set it to 0.

This will return 15 rows (3 X 5) with a 0 for ZeroSales.

2. Create a qselActualSales that returns the actual sales for each
Salesman - Product combination for the required period. This may return
less than 15 rows as some Salesmen might not have sold certain Products (as
you described)

3. Create a qselFinalSales using LEFT JOIN from qselDummySales to
qselActualSales unsing SalesmanID & ProductID as linking Fields with a
Calculated Field FinalSales summing up the the ZeroSales and ActualSales.

The qselFinalSales should have 15 rows with some zero FinalSales as
required. Use this Query qselFinalSales as the RecordSource for your
Report.