Solved row number in subform

[TipsyWolf]

hello everyone !

i have a query with sql

TRANSFORM First(IIf(r.QResponse=q.CorrectResponse,"✓","✘")) AS ResponseResult
SELECT e.TrainingAttendanceID_FK, e.EmployeeID, e.FullName, e.JobTitle, DCount("[EmployeeID]","[Employees]","[EmployeeID]<=" & [e].[EmployeeID]) AS RowNum, Round(100*Sum(IIf(r.QResponse=q.CorrectResponse,1,0))/Count(q.QuestionID),0) & "%" AS Score
FROM Employees AS e LEFT JOIN (QuestionResponse AS r LEFT JOIN Questions AS q ON r.QuestionID_FK = q.QuestionID) ON e.EmployeeID = r.EmployeeID_FK
GROUP BY e.TrainingAttendanceID_FK, e.EmployeeID, e.FullName, e.JobTitle
PIVOT "Q" & q.QuestionNumber In ("Q1","Q2","Q3","Q4","Q5","Q6","Q7","Q8","Q9","Q10","Q11","Q12","Q13");

which does what i need., but the only one problem here is RowNum shows number from this query.

In TrainingAttandance main form i have this query as a subform and for example in TrainingAttandanceID=20 it shows only 1 record , but itsrow number is 4, but not 1.

how do i return row number after subform filters out the rest due to its link to parent form ?

 

[CJ_London]

You would need to include in your dcount criteria the form filter - presumably the subform link master value so would be something like

“…,,. And fk= “ & forms!parentformname!pk

 

[TipsyWolf]

You would need to include in your dcount criteria the form filter - presumably the subform link master value so would be something like

“…,,. And fk= “ & forms!parentformname!pk

thank you for your response. i didn't get how to implement your solution, but i found this

 FROM Employees AS e2
     WHERE e2.TrainingAttendanceID_FK = e.TrainingAttendanceID_FK
     AND e2.EmployeeID <= e.EmployeeID) AS RowNumber

and it worked for me.
thank you anyway !

 

[CJ_London]

you didn't really provide enough information about your setup, so was just an informed guess on my part. Not sure how your solution fits with

return row number after subform filters out the rest due to its link to parent form ?

but you have a solution

 

[TipsyWolf]

you didn't really provide enough information about your setup

sorry, my bad. next time i will do threads more detailed. thanks