numbering query report lines 1

[ytakbob]

On our queries, we want to be see this:

1 kerr, greg 111 greg lane
2 schmid, bob 111 bob lane
3 davis, ru 111 russ lane

even though there is no sequence number in the table
Bob Schmid
bob_schmid@hmis.org
330-746-1010 ext. 1347

 

[mp9]

You can read a (slighty clunky) method of doing this at

http://www22.brinkster.com/accessory/forum/topic.asp?TOPIC_ID=4

http://www22.brinkster.com/accessory/

 

[ytakbob]

Did the following:
RowNum: (Select Count (*) FROM [tblPhysician] as Temp
WHERE [Temp].[Name] < [tblPhysician.Name])+1

Rownum comes out as the actual rownumber in the table rather than a sequential counter beginning at &quot;1&quot;
it looks like this:
23 Jones
47 Peterson
89 Williams

rather than
1 Jones
2 Peterson
3 Williams

here is my query:
SELECT tblPhysician.Facility, tblPhysician.[Phys Status], (Select Count (*) FROM [tblPhysician] as Temp
WHERE [Temp].[Name] < [tblPhysician.Name])+1 AS RowNum, tblPhysician.Name, tblPhysician.Addr1, tblPhysician.City1, tblPhysician.State1, tblPhysician.Zip1, tblPhysician.[CB Installed1], tblPhysician.[Install Date1], tblPhysician.Addr2, tblPhysician.City2, tblPhysician.State2, tblPhysician.Zip2, tblPhysician.[CB Installed2], tblPhysician.[Install Date2], tblPhysician.ReInstall2
FROM tblPhysician
WHERE (((tblPhysician.Facility)=&quot;E&quot AND ((tblPhysician.[Phys Status])=&quot;A&quot AND ((tblPhysician.[CB Installed1])=Yes))
ORDER BY tblPhysician.Name, tblPhysician.[Install Date1];


Bob Schmid
bob_schmid@hmis.org
330-746-1010 ext. 1347

 

[ytakbob]

any ideas folks ?
or could someone at least explain the code :
SELECT tblPhysician.Facility, tblPhysician.[Phys Status], (Select Count (*) FROM [tblPhysician] as Temp
WHERE [Temp].[Name] < [tblPhysician.Name])+1 AS RowNum ?


Thanks Bob Schmid
bob_schmid@hmis.org
330-746-1010 ext. 1347

 

[Tranman]

Bob,
What that code is doing is, for each row returned, it is counting all of the rows in the table where the physician's name is less than the current physician's name, and adding 1 to it, and returning that value as a value in a column called RowNum.

If you're interested in a different way of doing this, I helped a guy work through this numbering thing by writing a function that returns row numbers in thread:

701-458235

Good luck,
Paul