By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,389 Members | 2,044 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,389 IT Pros & Developers. It's quick & easy.

Fill in missing data on a graph in Access 97

P: 2
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 :

Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_SABU.SABU_Desc, tbl_SA.SA_Ergo
  2. 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
  3. WHERE (((tbl_TA.TA_CloseDT) Between [forms].[frm_rprt].[txtStartDate] And [forms].[frm_rprt].[txtEndDate]))
  4. GROUP BY tbl_SABU.SABU_Desc, tbl_SA.SA_Ergo;
Mar 5 '08 #1
Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_SABU.SABU_Desc, Nz(YourChartQuery.SA_Ergo, 0) as ChartValue
  2. FROM tbl_SABU LEFT JOIN YourChartQuery ON tbl_SABU.SABU_ID = YourChartQuery.SABU_ID
  3. 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
Mar 8 '08 #2

P: 2
Thank you very much for your help, from your example our Chart now shows exactly the required values.
Again greatly appreciate your help !
Mar 14 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.