2

I have written a query in Access which I want to display as a PivotChart (Line Type).

The query has the following fields:-

ProductDescription (Column Header), MonthName (Row Header), GoodsSold (Data).

The ProductDescription Field pulls its data from a table of ProductCodes. I have queried based on 3 descript Product Codes. For certain months, the ProductDescription has no entry against the month in question, which leads to Null entries in the GoodsSold when the Query is Pivoted.

How can I change these Null values to 0's as when the query is in PivotChart View, there is no data plotted for these months?

Jason Brown
  • 43
  • 7

1 Answers1

0

As long as you are using right or left joins in the data set then you can use the Nz command to substitute the GoodsSold column e.g:

Nz([GoodsSold], 0)
Matt Donnan
  • 4,933
  • 3
  • 20
  • 32
  • Hi Matt,Thanks for the quick response. Unfortunately, I can't use the join type to overcome this issue, as all I'm referencing in my ProductCodes Table is a more user friendly description of the product via the ProductID. The main information for the query all comes from the same table (MonthlySales). I have tried using the Nz function through VBA, but I don't know if I can refer to a cell in the PivotTable that technically has neither a Month, ProductDescription or Data. – Jason Brown Oct 05 '12 at 12:20
  • No, you won't be able to unfortunately, the best way round this is to have a **calendar** table with all the months, and then right join from this to a select query of **MonthlySales**. You can then ensure you eradicate blank rows by employing the Nz function. Then finally you will build another crosstab query using the previous select as a data source rather than the table on it's own. – Matt Donnan Oct 05 '12 at 12:33
  • Unfortunately, creating a Calendar table, (although it might have proven to be the solution to my problem) is not something that I am allowed to do within the scope of this project. The gents further up the database maintenance chain don't want small tables that are limited to rectifying a single problem adding to the database if another solution is at all available. Do you have any further ideas on the matter? – Jason Brown Oct 08 '12 at 12:10
  • @JasonBrown When creating dynamic pivot queries, Access can only work with data that it is given. Another option which I don't like is to create a union select query, manually with all the months you want in there, and then link this union query back to the dataset in the way I suggested you use the calendar table. It avoids having the table but it's a bit of a messy solution. – Matt Donnan Oct 09 '12 at 11:28