I have a table that looks like this:

Student Number Date Test Name Subject Area Test Score

My ultimate goal is to create 1 record per student number so that I
can export it and load it into SPSS for analysis.

Some records in the table represent tests that can only be taken once.
So I'm fine using a Crosstab for those, since there will not be any
conflicting data. Can I combine the Test Name and Subject Area into a
single field in the cross tab so that the data will be more meaningful
to me?

Also, how exactly do I handle tests that may be taken multiple times
(until the student passes)?

Thanks in advance for your help in this matter.

Jeff

Assumptions:
You don't need the date field in the results
The last test taken is the passed test
The number of different tests is less than 250
The combination of Subject Area and Test Name is less characters than the
maximum allowed for a column name (64 characters).

If you have a small number of records the following should work for you.

TRANSFORM First([Test Score]) as TestScore
SELECT [Student Number]
, [Test Name]
, [Subject Area] as Subject
, Max([Date]) as TestDate
FROM [YourTable] as T1
WHERE [YourTable].[Date] =
(SELECT Max([Date])
FROM [YourTable] as T2
WHERE T2.[Student Number] = T1.[Student Number]
AND T2.[Test Name] = T1.[Test Name]
AND T2.[Subject Area] = T1.[Subject Area])
GROUP BY [Student Number]
PIVOT [Subject Area] & " - " & [Test Name]

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On 2/26/2011 9:25 AM, Jeffmarks wrote:
> I have a table that looks like this:
>
> Student Number Date Test Name Subject Area Test Score
>
> My ultimate goal is to create 1 record per student number so that I
> can export it and load it into SPSS for analysis.
>
> Some records in the table represent tests that can only be taken once.
> So I'm fine using a Crosstab for those, since there will not be any
> conflicting data. Can I combine the Test Name and Subject Area into a
> single field in the cross tab so that the data will be more meaningful
> to me?
>
> Also, how exactly do I handle tests that may be taken multiple times
> (until the student passes)?
>
> Thanks in advance for your help in this matter.
>
> Jeff

[Jeffrmarks]

I received the error message.

MS Office Database engine does not recognize T1.[StudentName] as a valid field name or expression.

The query looks like this:

TRANSFORM First(T1.[TestScore]) AS TestScore
SELECT T1.[StudentNumber], T1.[AssessmentTestName], T1.[AssessmentPartName] AS Subject, Max(T1.[TestingDate]) AS TestDate
FROM LMMI_Assessment AS T1
WHERE ((([LMMI_Assessment].[TestingDate])=(SELECT Max([TestingDate])
FROM [LMMI_Assessment] as T2
WHERE T2.[StudentNumber] = T1.[StudentNumber]
AND T2.[AssessmentTestName] = T1.[AssessmentTestName]
AND T2.[AssessmentPartName] = T1.[AssessmentPartName])))
GROUP BY T1.[StudentNumber]
PIVOT [AssessmentPartName] & " - " & [AssessmentTestName];