Updating a crosstab query

[andy49]

[CODE]TRANSFORM Avg(qryEnterResults.TestScore) AS AvgOfTestScore
SELECT qryEnterResults.slotID_FK, qryEnterResults.Firstname, qryEnterResults.surname, qryEnterResults.MathsClass, qryEnterResults.PaperTier, Avg(qryEnterResults.TestScore) AS [Total Of TestScore]
FROM qryEnterResults
WHERE (((qryEnterResults.MathsClass)="10x/mm1") AND ((qryEnterResults.[slotID_FK])=7))


GROUP BY qryEnterResults.slotID_FK, qryEnterResults.Firstname, qryEnterResults.surname, qryEnterResults.MathsClass, qryEnterResults.PaperTier
ORDER BY qryEnterResults.surname
PIVOT qryEnterResults.PaperNumber;[/CODE]

[andy49]

TRANSFORM Avg(qryEnterResults.TestScore) AS AvgOfTestScore
 SELECT qryEnterResults.slotID_FK, qryEnterResults.Firstname, qryEnterResults.surname, qryEnterResults.MathsClass, qryEnterResults.PaperTier, Avg(qryEnterResults.TestScore) AS [Total Of TestScore]
 FROM qryEnterResults
 WHERE (((qryEnterResults.MathsClass)="10x/mm1") AND ((qryEnterResults.[slotID_FK])=7))
 GROUP BY qryEnterResults.slotID_FK, qryEnterResults.Firstname, qryEnterResults.surname, qryEnterResults.MathsClass, qryEnterResults.PaperTier
 ORDER BY qryEnterResults.surname
 PIVOT qryEnterResults.PaperNumber;

[CJ_London]

which has Paper (only1,2 or 3)

not quite true - the control name should be P1, P2 etc - the paperNumber per the pivot

try something like this in a standard module

function updPaperStudent()
dim sqlstr as string

with screen.activecontrol
sqlstr="Update tblPaperStudent set [TestScore]=" & .value & " WHERE SLOTID_PK =" .parent.SlotID_PK & " AND [PaperNumber] = '" & .name & "'"
currentdb.execute sqlstr
end with
end function

and for each of your crosstab 'value' controls put

=updPaperStudent()

against the afterupdate event (where you normally see [Event Procedure])

If the pivot fieldname is calculated is some way (e.g. "P" & PaperPK or perhaps months in another situation) then you would need a means to reverse that calculation. Or as previously suggested another xtab, joined but not display which contains the relevant values.

[andy49]

I get a handling error

2465Application-defined or object-defined error

at this line

 sqlstr = "Update tblPaperStudent set [TestScore]=" _
 & .Value & " WHERE SLOTID_PK =" & .Parent.SlotID_PK & " AND [PaperNumber] = '" & .Name & "'"

[andy49]

Many thanks, a slight tweak sorted it in a way.

however,

Update tblPaperStudent set [TestScore]= WHERE SLOTID_PK =7 AND [PaperNumber] = 'txtp3'

is my sql string but this isn't returning the PaperID which I need

[CJ_London]

I've said on several occasions you will need to reverse calculate the value of the field based on control name. It looks to me like you have renamed the control from P3 to txtP3.

An alternative would be to use the control controlsource rather than the name

....AND [PaperNumber] = '" & .controlsource & "'"

Before going any further, can we agree the actual value you are looking for. Based on your original data it would be P3

[andy49]

Easiest if I refer you to #14 Ajax.

The problem I think is that I need paperid_fk


This links to paperid_pk in a table which includes [papernumber] as a field

I can post a database on here if it will help.

[CJ_London]

You can, but I don't have time to look at it in any detail so make sure it only has relevant tables, forms and queries. Don't forget to compact and zip

[rpeare]

What is your maximum number of students per class, and what is your maximum number of 'tests' you need to record a score for?

[andy49]

Only three per round of tests. They happen every four months but eventually we'll have up to 18 tests worth of results by the time students leave as they take many more in their final year.

[rpeare]

Yes but how many students per class? I am thinking of this being a matrix and I have an application that allows data entry in a matrix on an unbound form into a normalized table structure but It would not be a great application for a class of more than a matrix of say 100 (10 x 10 or 3 x 33).

[andy49]

It would need 380 rows and as much as 20 columns

I should add that only 35 rows would be displayed at a time

[CJ_London]

my final suggestion without more information

....AND [paperid_pk] = " & Dlookup("paperid_pk","someothertable"."Papernumber ='" & controlsource & "'")

[andy49]

Sorry ajax. Manic at the "office". But thank you so much!!!



I used

papID = DLookup("PaperID_FK", "Qryfindpaper", "slotID_FK = " & myslot & " AND [papernumber] = " & mypaper & " And PaperTier = '" & mytier & "'")

Where I'd found the variables myslot, mypaper, etc with your .parent advice.

Works perfectly

[CJ_London]

got there in the end