I have a crosstab query which basically tells me every pupils test results with the tests being the column heading. There are anywhere between 0 (until their first test) and 18 tests
I'm trying to get this into a subform with the tblStudent (name etc) so that I can see the progress they are making but am running into wall after wall.
Firstly, the crosstab
Code:
TRANSFORM First(qrydoesthiswork.grade) AS FirstOfgrade
SELECT qrydoesthiswork.StudentID_PK, qrydoesthiswork.Firstname, qrydoesthiswork.surname, First(qrydoesthiswork.grade) AS [Total Of grade]
FROM qrydoesthiswork
GROUP BY qrydoesthiswork.StudentID_PK, qrydoesthiswork.Firstname, qrydoesthiswork.surname
PIVOT qrydoesthiswork.slotID_FK;
All fine
But when I try to put this, or any form based on this, into a subform it tells me I need to set the column headings property. I cant change this as the columns are labelled according to their slotID and this increases every term.
I've tried reversing the forms so that the main form is the crosstab and the student details is the subform.
This works Ok unless a student hasn't got a result. Then I get an error "you entered an expression which has no value" in this dlookup.
Code:
myCS = Nz(DLookup("slotID_PK", "tblcalendar", "slot like 'christ*' and [yeargp] = " & Me!FrmStudent.Form![yeargp]), "")
Presumably because there is no student info to link the master and child fields
Getting stressed!!!
Can anyone help.
I'll happily post further details