Enumerate records in Access Query

[Craig]

Hello,

Unsure if this is
the right forum...
apologies if it
isn't.

I am trying
to "rank" items
that appear in my
query. ie: I
already have
sorted them from
highest to lowest,
but would like to
insert a column
that starts at the
top record ranking
it number 1, then
2 and so on.

Is there a way to
do this ?

Thanks in
advance !!

Craig Smith

 

[tina]

well, i have a solution, but it's not without flaws. you can declare a
module level variable to hold the "count", and use that in a function to
increment the count, then call the procedure as a calculated field in the
query, as

Dim intCount As Integer

Public Function isRank(ByVal var As Variant) As Integer

intCount = intCount + 1
isRank = intCount

End Function

add a field to the query, as

Ranking: isRank([SomeFieldName])

substitute the name of a field in the query's underlying table; any field
will do, since it's not used in the function - its' purpose is only to force
the function to run for each record.

the flaw in this solution is that if you put the above VBA code in a
standard module, the intCount variable will not be reset to zero unless you
close the database and re-open it. so if you run the query more than once
during a session, the rank will be sequential - but it will NOT start at 1.

a way around this problem is to use a command button on a form to open the
query. then you can either add another command button to "Reset Rank", with
code that sets the variable back to zero, as

intCount = 0

or you can run the above code in the form's Close event, if you're only
going to run the query once while the form is open.

as i said, a flawed solution. suggest you keep checking this thread for a
day or so; maybe someone else will post something better. btw, suggest you
post future messages as text, not HTML, since some newsreaders can only read
text.

hth

 

[Stephen Lebans]

Have a look here:
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.

well, i have a solution, but it's not without flaws. you can declare a
module level variable to hold the "count", and use that in a function to
increment the count, then call the procedure as a calculated field in the
query, as

Dim intCount As Integer

Public Function isRank(ByVal var As Variant) As Integer

intCount = intCount + 1
isRank = intCount

End Function

add a field to the query, as

Ranking: isRank([SomeFieldName])

substitute the name of a field in the query's underlying table; any field
will do, since it's not used in the function - its' purpose is only to
force
the function to run for each record.

the flaw in this solution is that if you put the above VBA code in a
standard module, the intCount variable will not be reset to zero unless
you
close the database and re-open it. so if you run the query more than once
during a session, the rank will be sequential - but it will NOT start at
1.

a way around this problem is to use a command button on a form to open the
query. then you can either add another command button to "Reset Rank",
with
code that sets the variable back to zero, as

intCount = 0

or you can run the above code in the form's Close event, if you're only
going to run the query once while the form is open.

as i said, a flawed solution. suggest you keep checking this thread for a
day or so; maybe someone else will post something better. btw, suggest you
post future messages as text, not HTML, since some newsreaders can only
read
text.

hth

Hello,

Unsure if this is
the right forum...
apologies if it
isn't.

I am trying
to "rank" items
that appear in my
query. ie: I
already have
sorted them from
highest to lowest,
but would like to
insert a column
that starts at the
top record ranking
it number 1, then
2 and so on.

Is there a way to
do this ?

Thanks in
advance !!

Craig Smith

 

[tina]

Craig, we're saved! and thanks, Stephen

Have a look here:
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.

well, i have a solution, but it's not without flaws. you can declare a
module level variable to hold the "count", and use that in a function to
increment the count, then call the procedure as a calculated field in the
query, as

Dim intCount As Integer

Public Function isRank(ByVal var As Variant) As Integer

intCount = intCount + 1
isRank = intCount

End Function

add a field to the query, as

Ranking: isRank([SomeFieldName])

substitute the name of a field in the query's underlying table; any field
will do, since it's not used in the function - its' purpose is only to
force
the function to run for each record.

the flaw in this solution is that if you put the above VBA code in a
standard module, the intCount variable will not be reset to zero unless
you
close the database and re-open it. so if you run the query more than once
during a session, the rank will be sequential - but it will NOT start at
1.

a way around this problem is to use a command button on a form to open the
query. then you can either add another command button to "Reset Rank",
with
code that sets the variable back to zero, as

intCount = 0

or you can run the above code in the form's Close event, if you're only
going to run the query once while the form is open.

as i said, a flawed solution. suggest you keep checking this thread for a
day or so; maybe someone else will post something better. btw, suggest you
post future messages as text, not HTML, since some newsreaders can only
read
text.

hth

Hello,

Unsure if this is
the right forum...
apologies if it
isn't.

I am trying
to "rank" items
that appear in my
query. ie: I
already have
sorted them from
highest to lowest,
but would like to
insert a column
that starts at the
top record ranking
it number 1, then
2 and so on.

Is there a way to
do this ?

Thanks in
advance !!

Craig Smith

 

[John Nurick]

Hi Craig,

You can do it with a subquery, as an alternative to calling a VBA
function. E.g. if the records are sorted on the field Score, something
like this:

SELECT
(SELECT COUNT(1)
FROM MyTable As B
WHERE B.Score > A.Score) + 1 As Place,
A.Score,
A.FirstName,
A.LastName
FROM MyTable AS A
ORDER BY A.Score DESC;

 

[slobbering_dog]

Thanks very much guys. I really appreciate your help.

I have been able to adopt the SQL statement so far, and will be tryiong
all the other suggestions as well.

Thanks again,

Craig Smith