CrossTab Combining 2 fields into Column heading

[Jeffrey Marks]

I've never done a crosstab where I combine 2 fields into the column heading.. The query is as follows:

TRANSFORM Last(LMMI_Assessment.TestScore) AS TestScore
SELECT LMMI_Assessment.StudentNumber, Max(LMMI_Assessment.TestingDate) AS MaxTestingDate
FROM LMMI_Assessment
GROUP BY LMMI_Assessment.StudentNumber
PIVOT LMMI_Assessment.[AssessmentTestName] & "-" & AssessmentPartName;

When I don't name the column headings, the results show as "3rd Ach-Math", 4th Ach-Reading", etc. However, when I try to name the fields as column headings in the properties box (as "3rd Ach-Math" etc) so that I can use thesefields in another query, I get empty columns. Currently the 2 fields are 255 characters -- does that make a difference?

 

[John Spencer]

Is it possible that there are trailing or leading spaces? If you "name the
fields" your names must exactly match the values returned by the expression.
An extra space or missing character will return the column with that name but
the column will be empty. Also make sure you are using the exact same dash
character.

You might try the following to eliminate the possibility of leading or
trailing spaces.
PIVOT Trim(LMMI_Assessment.[AssessmentTestName])& "-"&
Trim(AssessmentPartName) IN ("3rd Ach-Math","4th Ach-Reading")

You could try opening the table LMMI_Assessment and copying and pasting the
values into the SQL statement's IN clause and see if that works. Tedious, but
that should ensure that you have no misspelled values.

If your fields are in an Access table then a size of 255 should have no effect
unless you have imported the values from an external source.
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County