Number Rows in Access Query

[mo2783]

I have the follwing query

SELECT tbl_Students.StudentID AS StudID, Format([StudID],"0000") AS StudentID, ignoreSpace(tbl_Students.StudentName & " " & [StudentMiddlename] & " " & [StudentSurname]) AS StudentName

FROM tbl_Classes RIGHT JOIN (tbl_Students RIGHT JOIN tbl_StudentClassJoin ON tbl_Students.StudentID = tbl_StudentClassJoin.StudentID) ON tbl_Classes.ClassID = tbl_StudentClassJoin.ClassID
WHERE (((tbl_StudentClassJoin.ClassID)=[Forms]![frm_Search].[cmbClassID]))

ORDER BY ignoreSpace(tbl_Students.StudentName & " " & [StudentMiddlename] & " " & [StudentSurname]);

This will produce the following results on a sub form



StudentID Student Name
0003 Joe
0045 Henry
0029 Harold
0134 Martin


What i am trying to do is have row numbers next to each record, so something like this

Row Number StudentID Student Name
1 0003 Joe
2 0045 Henry
3 0029 Harold
4 0134 Martin

Any help would be appreciated.

 

[BigRed1212]

http://blogs.techrepublic.com.com/msoffice/?p=898

 

[MajP]

something like (generic)

SELECT 
 tblStudents.studentID, 
 tblStudents.studentName, 
 (Select count(studentID)+1 from tblStudents as A where tblStudents.StudentID >A.studentID) AS RowNumber
FROM tblStudents
ORDER BY 
 tblStudents.studentID;

produces

RowNumber studentID studentName
1	0003	Joe
2	0029	Harold
3	0045	Henry
4	0134	Martin

 

[mo2783]

I have tried your Solution but i get the follwing results

StudentID Student Name RowNumber
0003 Joe 0003
0045 Henry 0045
0029 Harold 0029
0134 Martin 0134



The query Looks like this

SELECT tbl_Students.StudentID AS StudID, Format([StudID],"0000") AS StudentID, ignoreSpace(tbl_Students.StudentName & " " & [StudentMiddlename] & " " & [StudentSurname]) AS StudentName, [b](Select count(StudentID)+1 from tbl_Students as A where tbl_Students.StudentID > A.studentID) AS RowNumber [/b]

FROM tbl_Classes RIGHT JOIN (tbl_Students RIGHT JOIN tbl_StudentClassJoin ON tbl_Students.StudentID = tbl_StudentClassJoin.StudentID) ON tbl_Classes.ClassID = tbl_StudentClassJoin.ClassID

WHERE (((tbl_StudentClassJoin.ClassID)=[Forms]![frm_Search].[cmbClassID]))
ORDER BY ignoreSpace(tbl_Students.StudentName & " " & [StudentMiddlename] & " " & [StudentSurname]);

 

[MajP]

Your ordering your records not by student ID as in my example but by
ignoreSpace(tbl_Students.StudentName & " " & [StudentMiddlename] & " " & [StudentSurname]

so in the subquery you would have to do a similar comparison

where tbl_Students.StudentID > A.studentID

into
ignoreSpace(tbl_Students.StudentName & " " & [StudentMiddlename] & " " & [StudentSurname] > ignoreSpace(A.StudentName & " " & A.StudentMiddlename & " " & [A].[StudentSurname]

The comparison has to be the same as your order by.

 

[mo2783]

I am still not getting the correct results the query looks like this

SELECT tbl_Students.StudentID AS StudID, Format([StudID],"0000") AS StudentID, ignoreSpace(tbl_Students.StudentName & " " & [StudentMiddlename] & " " & [StudentSurname]) AS StudentName, [b](SELECT Count(StudentName)+1 from tbl_Students as A where  ignoreSpace([A].[StudentName] & " " & [A].[StudentMiddlename] & " " & [A].[StudentSurname]) < ignoreSpace(tbl_Students.[StudentName] & " " & [StudentMiddlename] & " " & [StudentSurname])) AS RowNumber [/b]

FROM tbl_Classes RIGHT JOIN (tbl_Students RIGHT JOIN tbl_StudentClassJoin ON tbl_Students.StudentID = tbl_StudentClassJoin.StudentID) ON tbl_Classes.ClassID = tbl_StudentClassJoin.ClassID

WHERE (((tbl_StudentClassJoin.ClassID)=[Forms]![frm_Search].[cmbClassID]))

ORDER BY ignoreSpace(tbl_Students.StudentName & " " & [StudentMiddlename] & " " & [StudentSurname]);

This Query is producing

StudentID Student Name RowNumber
0003 Joe 9
0045 Henry 11
0029 Harold 38
0134 Martin 57

Any idea whats wrong with the query?

Thanks.

 

[MajP]

This
StudentName)+1 from tbl_Students as A where ignoreSpace([A].[StudentName] & " " & [A].[StudentMiddlename] & " " & [A].[StudentSurname]) < ignoreSpace(tbl_Students.[StudentName] & " " & [StudentMiddlename] & " " & [StudentSurname])

would also need to include the where statement. There are 9 records before Joe, but they are not students
where
tbl_StudentClassJoin.ClassID)=[Forms]![frm_Search].[cmbClassID]

It is too complicated for me to try to do in a single query. Just do it in two queries

qryStudentClasses

SELECT 
 tbl_Students.StudentID AS StudID, 
 Format([StudID],"0000") AS StudentID, 
 ignoreSpace(tbl_Students.StudentName & " " & [StudentMiddlename] & " " & [StudentSurname]) AS StudentName, 
 FROM 
 tbl_Classes 
RIGHT JOIN 
 (tbl_Students 
RIGHT JOIN 
 tbl_StudentClassJoin 
ON 
 tbl_Students.StudentID = tbl_StudentClassJoin.StudentID) 
ON 
 tbl_Classes.ClassID = tbl_StudentClassJoin.ClassID
WHERE 
 (((tbl_StudentClassJoin.ClassID)=[Forms]![frm_Search].[cmbClassID]))
ORDER BY 
 ignoreSpace(tbl_Students.StudentName & " " & [StudentMiddlename] & " " & [StudentSurname]);

qryStudentClassesWithRowNumbers

SELECT
 StudID,
 StudentID
 StudentName
 (Select Count(StudID) + 1 from qryStudentClasses as A where A.StudentName > qryStudentClasses.StudentName) As RowNumber
From
 qryStudentClasses
Order By
 StudentName