Not sure how ro describe this but I need to display data to a graph but I don't always have data for each month. How would I show the months with no data to make the graph look correct?. The start date may vary per report. I have everything working except accounting for the no data months and getting them to show on a graph. Any suggestions?
You need to create (or derive) a month table and left outer join from this to your current query. That way - you get all the months. You could use a tally table for this (a table containing a whole load of numbers from 1 up to, for example, 1000 or more).
SELECT MonthName(MyNumber) AS TheMonthName, MyNumber AS TheMonthNumber, MyData1, MyData2
FROM (SELECT MyNumber FROM MyNumbersTable WHERE MyNumber BETWEEN 1 and 12) AS Numbers LEFT OUTER JOIN MyDataQuery ON Numbers.MyNumber = MyDataQuery.DataMonthNumber
But what if I want the report to be the last 3 years?
Hmm. I guesss I would have to have a table with the months AND year for all years I would expect to have to make a report.
I saw such a table listing all weeks for the last 3 years and wondered what was the point of that but now I see.
If you make 2 tables, 1 for each month and one for each year and then create a query with the month field and the year filed but NO JOINS, Access will match each year with each month. It called something like a "full factorial" and does what you want.
Make sure the year is sorted and then the month (number) and it will be in order.
interesting take .. yes Access will bring in all unique combinations of month and year from the two tables but to get a date that can be processed in a query or graph it looks like you would have to make an expression that combines the fields and then do a cdate on the result.
I think I will use one table that lists all possible dates and have some extra fields with the dates formatted the way I want the graph to show it and to allow a select query to set the range of dates to be used.
I had set it up to just use one table with all dates which was tedious to do but at least worked. However I see now that this is the slicker - read more professional - way to do it. You just have to make two tables - one with the months and one with the years. Then you just need to make a small separate query to combine the tables in an expression and cdate it. You then also put in the query criteria a reference to the fields on the form with the start and end date using a between expresion as shown above and there you are you just have to call the query to get a listing of ALL the months and years for the chart to include
Thanks for the idea!!!