Record Number in Query

[Vinnie]

This is a Multi-Post . No responses in the Query DB.

Hello all,

Trying to Figure how to go about adding an expression in a Query what will
Automatically Number the Results.

Example:
Query of the Employeee Table, of employees who live in NY:

John
Ralph
Jim
Doug

Would like to add a Number Field in there, so results would like this:
1 John
2 Ralph
3 Jim
4 Doug

Thanks As Usual !

Vincent

 

[Joseph Meehan]

This is a Multi-Post . No responses in the Query DB.

Hello all,

Trying to Figure how to go about adding an expression in a Query what
will Automatically Number the Results.

Example:
Query of the Employeee Table, of employees who live in NY:

John
Ralph
Jim
Doug

Would like to add a Number Field in there, so results would like this:
1 John
2 Ralph
3 Jim
4 Doug

Thanks As Usual !

Vincent

We need more details. Do the numbers need to be consecutive? Do you
need new "sets" of numbers for different groups etc.

I suggest you may or may not want to use Autonumber for that use.
Autonumbers are designed to provide unique numbers. It in not designed to
provide numbers in order and for a number of reasons may not do so. As a
result using them in any application where the user sees the numbers is
likely to end up with confusion.

There are other ways of providing the numbers you want depending on the
particual application.

 

[Vinnie]

Exactly a Autonumber type of thing.
Just very Simple. But I dont know how to AutoNumber in a query.
I have the query sorting the way I want it to, but i just dont how to
autunumber the records.

 

[Vinnie]

Well, I thought it would ne an easy thing to do, guess not.
Thanks anyway.

 

[Stephen Lebans]

See:
http://www.lebans.com/rownumber.htm
Rownumber.zip is a database containing functions for the automatic row
numbering of Forms, SubForms and Queries.

Updated Oct. 13 by Allen Browne. Includes error handling and cleaned code.



Here's an update to the Serialize function by Peter Schroeder:

Good to hear. FWIW, here's the version I came up with today, based off of
your code and Ken's(Getz) suggestion, with a few changes:


Function Serialize(qryname As String, keyname As String, keyvalue) As Long

Dim rs As Recordset


On Error GoTo Err_Serialize

Set rs = CurrentDb.OpenRecordset(qryname, dbOpenDynaset, dbReadOnly)

rs.FindFirst Application.BuildCriteria(keyname, rs.Fields(keyname).Type,
keyvalue)

Serialize = Nz(rs.AbsolutePosition, -1) + 1


Err_Serialize:

rs.Close

Set rs = Nothing

End Function


Peter Schroeder


--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[Vinnie]

Like a Charm.
Thank you....Enjoy the rest of your weekend.
You guys rock !