Chart

[SierraJuliet]

I have been trying for hours to add ALL MONTHS (January - December) to the x-axis (red box) and not just the months with entries. Any ideas.

[June7]

Really need to know your data and the query you use. I analyze chart issues best when I can work with data. If you want to provide db follow instructions at bottom of my post.

[SierraJuliet]

This is what the current SQL statement is for the chart, which was auto generated:

TRANSFORM Count([CaseNum]) AS [CountOfCaseNum] SELECT (Format([DateCreated],"MMM 'YY")) FROM [tblCase] GROUP BY (Year([DateCreated])*12 + Month([DateCreated])-1),(Format([DateCreated],"MMM 'YY")) PIVOT (Year([DateCreated]));

The only things changed above are bolded in the statement and this was the defaulted value:

I changed this "Count(*) As [Count]"

I removed this "-1"

[June7]

Perhaps specify column headings in the CROSSTAB. Review http://allenbrowne.com/ser-67.html#ColHead

[SierraJuliet]

I will take a look at your reference and get back to you, thank you.

In the meantime I modified the SQL statement as follows:

TRANSFORM Count(CaseNum) AS [CaseCount]
SELECT (Format(tblCase.[DateCreated],"MMM")) AS Months
FROM tblCase
GROUP BY (Month([DateCreated])),(Format([DateCreated],"MMM"))
PIVOT (Year([DateCreated]));

This is what the design view, datasheet view, and form view chart looks like now:




At least now I have the months appearing in order and without the redundant "YY" appended to each month.

[June7]

Nope, specifying column headings won't help provide a datapoint for every month.

Will need at least one record for each month. Use a table of months.

Mon	MonNum
Jan	1
Feb	2
Mar	3
Apr	4
May	5
Jun	6
Jul	7
Aug	8
Sep	9
Oct	10
Nov	11
Dec	12

UNION the original data table to Months and use that as the source for CROSSTAB. Example of all-in-one query:

TRANSFORM Count(Query1.CaseNum) AS CountOfCaseNum
SELECT Query1.Mon
FROM (SELECT Month([DateCreated]) AS MonNum, Format([DateCreated], "mmm") AS Mon, Year([DateCreated]) AS Yr, CaseNum FROM tblCase
UNION SELECT MonNum, Mon, Null, Null FROM Months) AS Query1
GROUP BY Query1.MonNum, Query1.Mon
PIVOT Query1.Yr;

The legend will show an item with no year number unless you assign a year like 2000 to the dummy month records. Can do that in the UNION. Instead of the Null under Yr, use 2000. In this case the legend will show 2000.

Unfortunately, the lines for each Yr series cannot be continuous because of missing data. Would need a dummy record for every month/year combination. A Cartesian query can accomplish that, assumes at least one record for each desired year in the data table:

SELECT Mon, MonNum, Year([DateCreated]) AS Yr FROM Months, tblCase;

The revised all-in-one query:

TRANSFORM Count(Query1.CaseNum) AS CountOfCaseNum
SELECT Query1.Mon
FROM (SELECT Month([DateCreated]) AS MonNum, Format([DateCreated], "mmm") AS Mon, Year([DateCreated]) AS Yr, CaseNum FROM tblCase
UNION SELECT MonNum, Mon, Year([DateCreated]) AS Yr, Null FROM Months, tblCase) AS Query1
GROUP BY Query1.MonNum, Query1.Mon
PIVOT Query1.Yr;

If certain there is at least one record for every month as well as year, the Months table would not be needed and the nested inner query could be:

SELECT Month([DateCreated]) AS MonNum, Format([DateCreated], "mmm") AS Mon, Year([DateCreated]) AS Yr, CaseNum FROM tblCase
UNION SELECT Month([DateCreated]) AS MonNum, Format([DateCreated],"mmm") AS Mon, Year([DateCreated]) AS Yr, Null FROM tblCase

Consider column chart.

[SierraJuliet]

You read my mind . I created a month table that both spells out each month (a just in case later option), the three letter abbreviation, and instead of MonOrder I simply used ID field which is 1 to 12 based on order of entry. I typically use the ID field to ORDER BY to keep my desired entries in order when performing a lookup query to populate a drop down list of options and it is just a good idea in my opinion to include an "ID" field to avoid certain issues even though it may not be my tables primary key, which is a separate serial number for each table's records.

I agree if I could guarantee at least one record for each month regardless of year then the whole month lookup table is extra and what I already have would suffice but, since I cannot as the examples show then I want to ensure each month is present because it looks cleaner in my opinion. I will post my results when successful or if there is something important to share with everyone.

Thanks a lot for your help!

[June7]

Here's another way to generate the Months dataset:

SELECT DISTINCT Abs([id] Mod 12)+1 AS N, Choose([N], "Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","S ep","Oct","Nov","Dec") AS Mon FROM MsysObjects;

[SierraJuliet]

I modified the TRANSFORM UNION PIVOT you provided to work with what I have and made the query more descriptive for the given chart.

The following is the modified version of your awesome query:

TRANSFORM Count(CaseTrend.CaseNum) AS CountOfCaseNum
SELECT CaseTrend.MonthAbbr
FROM
(SELECT Month([DateCreated]) AS ID, Format([DateCreated], "mmm") AS MonthAbbr, Year([DateCreated]) AS Yr, CaseNum
FROM tblCase
UNION SELECT DISTINCT tblLookupMonth.ID, MonthAbbr, Year([DateCreated]) AS Yr, Null
FROM tblLookupMonth, tblCase) AS CaseTrend
GROUP BY CaseTrend.ID, CaseTrend.MonthAbbr
PIVOT CaseTrend.Yr;

The following is the result:


I may do some rework to bypass the null values in the future, such as only plotting any value greater than 0. However, I believe we can mark this post as solved. Thank you for all your assistance.

[SierraJuliet]

I used the IN ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec") before but created the table instead as it may be used elsewhere later.