Products with Zero Items Sold Not Showing In Report

[Bella6]

I am attempting to create a report listing products sold by region, the problem I keep running into is that if there are no sales of a product type for a region, instead of listing a zero, there is no record. I need to know how to get a zero in the report when there are no sales of a certain product type. Is that possible?
Please advise!!!

 

[PaulBricker]

Try the Nz function.

=Nz([FieldNameHere,0)

Paul

 

[scriverb]

I think that your problem is one that comes from let's say a sales transaction file. You select records for a certain product from the file and run a report. But, if there were no sales of that product in a particular region there won't be any records to print in the detail section of the report for that region. You have to insert records with 0 sales into the RecordSource for the report. These records would be just dummy records with 0 sales but they would trigger the report page as you wish.

Example: I have created a table called tblSales. Fields: Region Text 5, ProductID LongInt, SaleAmt LongInt

I created another table called tblRegions. Fields: Region Text 5, RegionDescription Text 50.

The tblSales is your transaction table that indicates the sales by region and productID. The second table is just an informational table of all of the logical regions that you want to be reported on in each report.

The RecordSource of the report will now be based upon a UNION query of the two tables. This query will take all of the selected records from the tblSales and match by Region to the tblRegions. If there are any Regions that don't have any sales then a zero Region record will be created for the selected ProductID.

SELECT tblSales.Region, tblSales.ProductID, tblSales.SaleAmt
FROM tblSales
ORDER BY tblSales.Region
WHERE tblSales.ProductID = 123
UNION

SELECT tblRegions.Region, 123 AS ProductID, 0 AS SaleAmt
FROM tblRegions LEFT JOIN tblSales ON tblRegions.Region = tblSales.Region
WHERE (((tblSales.Region) Is Null));

Now we will have to adjust this to your situation with a legitimate criteria selection process and new table and field names. Get back to me and see if this won't work for you. Bob Scriver

 

[tx333]

Bob,
Here are the specifics
Table1: Goals
Fields: Branch Text 5, ProductID Text 5, Goal Long Integer
Table2: Sales
Fields: Branch Text 5, ProductID Text 5, Sales Long Integer

The Goals table has a record for each of about 20 ProductIDs and 25 branches (500 total records). The Sales table may or may not have a corresponding Sale for each of the Branch and ProductID combinations, and I need to have a value in the Sales field even when a Sales.Branch and Sales.Sale combination is non-existent.

For Example:
Goals Table
Branch ProductID Goal
0001 10 20
0001 20 20
0002 10 20
0002 20 20

Sales Table
Branch ProductID Sale
0001 20 10
0002 10 25

I need the Query Results to look like this
Branch ProductID Goal Sales
0001 10 20 0
0001 20 20 10
0002 10 20 25
0002 20 20 0

By the way, I tried to recreate your example and got a "Missing Operator" Error for the commands prior to the UNION command.

Trey

 

[scriverb]

I will work on it this afternoon and get you a solution back a little later. Bob Scriver

 

[randysmid]

Hi,
I think the problem belongs in the join properties of your query. Right-click on the line connecting the two tables, and select Join Properties. You will want to select the option that shows all products from the Goals table, regardless of whether there are any matching records in the Sales table. HTH,
Randy Smith
California Teachers Association

 

[scriverb]

Now that we have all of the tables straight randysmid is correct. Use the following query and this should give you desired results:

SELECT Goals.Branch, Goals.ProductID, Goals.Goal, NZ([Sales]![Sales],0) AS Sales
FROM Goals LEFT JOIN Sales ON (Goals.Branch = Sales.Branch) AND (Goals.ProductID = Sales.ProductID)
ORDER BY Goals.Branch, Goals.ProductID;

Your table Goals has all the information to insure that each product for each region can have a unique row. With the Left join the query creates a record for each Region/ProductID whether there are any sales in the Sales table. The NZ function of Sales.Sales forces a Zero(0) to be displayed for those missing records in the table Sales.

Get back with us if you still have any questions.
Bob Scriver

 

[randysmid]

Bob,
Thanks for converting my tip into the necessary SQL!!!
HTH,
Randy Smith
California Teachers Association

 

[scriverb]

RANDYSMID: No problem. My origianl idea of the UNION query was based upon not knowing that his region table would have a productID for each region with the goal. I just made the assumption that there would be a table somewhere which listed the Regions. Just didn't know that it would provide the additional information to allow for the left join.

Thanks for spotting that bringing it to my attention. Bob Scriver

 

[tx333]

Bob,
Your SQL works beautifully and now I can switch to design view and the Nz function makes sense as well. Thanks a lot.
Trey