Fill in missing data on a graph in Access 97

[Sirrev]

MS Access 97
WinXP

I've created a report bar graph and each bar represents the data from a different BU(Business Unit). The report bar graph works as it should except for the following : depending on the reporting dates selected there are some BU where there is no data, so that BU is skipped on the graph. How can I get the BU without data to appear on the chart (basicaly I need the BU unit to appear on the X axis with a value of 0) ?

Here is the SQL behind it :

SELECT tbl_SABU.SABU_Desc, tbl_SA.SA_Ergo
FROM (tbl_SA INNER JOIN tbl_SABU ON tbl_SA.SABU_ID_FK = tbl_SABU.SABU_ID) INNER JOIN tbl_TA ON tbl_SA.SA_Ergo = tbl_TA.SA_ERGO_FK
WHERE (((tbl_TA.TA_CloseDT) Between [forms].[frm_rprt].[txtStartDate] And [forms].[frm_rprt].[txtEndDate]))
GROUP BY tbl_SABU.SABU_Desc, tbl_SA.SA_Ergo;

[Stewart Ross]

MS Access 97
WinXP

I've created a report bar graph and each bar represents the data from a different BU(Business Unit). The report bar graph works as it should except for the following : depending on the reporting dates selected there are some BU where there is no data, so that BU is skipped on the graph. How can I get the BU without data to appear on the chart (basicaly I need the BU unit to appear on the X axis with a value of 0) ?

Here is the SQL behind it :

SELECT tbl_SABU.SABU_Desc, tbl_SA.SA_Ergo
FROM (tbl_SA INNER JOIN tbl_SABU ON tbl_SA.SABU_ID_FK = tbl_SABU.SABU_ID) INNER JOIN tbl_TA ON tbl_SA.SA_Ergo = tbl_TA.SA_ERGO_FK
WHERE (((tbl_TA.TA_CloseDT) Between [forms].[frm_rprt].[txtStartDate] And [forms].[frm_rprt].[txtEndDate]))
GROUP BY tbl_SABU.SABU_Desc, tbl_SA.SA_Ergo;

Hi. Keep your chart query as listed above. Create a new query which left joins your business units table to this chart query and use it to create your chart instead of the original one. The left join is what will allow you to show all business units.

Sample SQL is listed below, but you will need to change the chart query name placeholder to its actual name:

SELECT tbl_SABU.SABU_Desc, Nz(YourChartQuery.SA_Ergo, 0) as ChartValue
FROM tbl_SABU LEFT JOIN YourChartQuery ON tbl_SABU.SABU_ID = YourChartQuery.SABU_ID
GROUP BY tbl_SABU.SABU_Desc, ChartValue;

Where there is no data to plot there will be a null for the selected value field from your chart query, hence the use of Nz to substitute a 0 instead.

Hope this helps.

-Stewart

[Sirrev]

Thank you very much for your help, from your example our Chart now shows exactly the required values.
Again greatly appreciate your help !