Adding Description column to Crosstab Query causes data to disappear

[diver_ann]

I've created a crosstab query to display course completions by month for each course ID (Item ID). Then I set up an outer join with my active course list so that all active courses are listed in the query even if there are no completions. I have pulled in additional fields from the Active Course List and everything works fine except the Description field. When, I add that field, it causes some of my data to disappear from the month columns, but the total across all months remains the same.

Here is a sample of the query results without the Description field:



Here is a sample of the query results with the Domain ID field replaced by Description:




Here is the SQL for the query with the Description:
TRANSFORM CLng(Nz(Sum([Completions by Item with Completion Month].[Number of Completions]),0)) AS [SumOfNumber of Completions]
SELECT Nz([Scorecard Roll-up LOB Map]![Roll-up LOB],"Unassigned") AS [Scorecard Roll-up LOB], Nz([Monthly Usage Roll-up Map]![Roll-up LOB],"Unassigned") AS [Executive Roll-up LOB], Nz([Active Items]![LOB/Owner],"Unassigned") AS [LOB/Owner], [Active Items].[Item ID], [Active Items].Title, [Active Items].Description, Sum([Completions by Item with Completion Month].[Number of Completions]) AS [Total Of Number of Completions]
FROM (([Active Items] LEFT JOIN [Completions by Item with Completion Month] ON [Active Items].[Item ID] = [Completions by Item with Completion Month].[Item ID]) LEFT JOIN [Monthly Usage Roll-up Map] ON [Active Items].[LOB/Owner] = [Monthly Usage Roll-up Map].[LOB/Owner]) LEFT JOIN [Scorecard Roll-up LOB Map] ON [Active Items].[LOB/Owner] = [Scorecard Roll-up LOB Map].[LOB/Owner]
GROUP BY Nz([Scorecard Roll-up LOB Map]![Roll-up LOB],"Unassigned"), Nz([Monthly Usage Roll-up Map]![Roll-up LOB],"Unassigned"), Nz([Active Items]![LOB/Owner],"Unassigned"), [Active Items].[Item ID], [Active Items].Title, [Active Items].Description
PIVOT Format([Completion Month],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");


Does anyone have any insights into why the Description field is messing up the month data? I've had to resort to paste in the column separately after exporting my query to Excel. I'm hoping to find a more elegant permanent solution.

One note: the content of the Description field is a little wonky, it has special characters. Also, when I was playing with different strategies for setting up the query, the Description field was displaying what looked like Chinese characters. So it's possible the content and not the query structure is responsible for the issue. I which case, any suggestions on how I could reformat or fix the content?

[June7]

Odd that it is not every month nor every row.

Do you want to provide project for analysis?