6/29/2012

Fill in missing data on a graph in Access 2003/XP/2000/97

Question: In Access 2003/XP/2000/97, I've created a report bar graph and each bar represents the data from a different year. There are some years where there is no data, in other words, there are no records with that year in them, so that year is skipped on the graph. How can I get the years without data to appear on the graph with no bar because there is no data?
Answer: To force the years without data to appear on the graph, you'll have to create some additional queries.
Let's take a look at an example.

Download Access 2000 version
In the report below, we've created a graph to display order details for each year.

As you can see, data for 1997, 1999, 2000, and 2001 is missing from this graph.
To rectify this, we've created a table called tblYears which contains all years between 1975 and 2099.
Next, we've created two queries called "Min year for Graph" and "Max year for Graph". These queries determine the highest and lowest year values that are contained in the OrderDetails table. We don't want to include a record for every year between 1975 and 2099, only those years that are missing in the data.
Next, we've created a query called "qryOrders" which contains the original data for the graph.
Then, we've created a query called "qryOrders missing years" which contains a dummy record for every year. In this case, for every year between 1994 and 2003 (not inclusive).
The query called "qryOrder UNION" is a union query which joins the results between "qryOrders" and "qryOrders missing years".
Finally, the query called "qryOrders UNION_final" performs a group by, so that each year contains only a single record. This query is then used as the row source for the graph.
Now, when we run the graph report, we will see:

Now there is a place on the graph for each year.

No comments:

Post a Comment