• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 757
  • Last Modified:

Add Sequential Number to Query Records

I have a Query that returns records based on whether or not a check box is check. The Query is also sorted "Ascending" by another field.
I want to created a column in the query that will sequentially number the records that are returned.

Help Please
omron60
0
omron60
Asked:
omron60
1 Solution
 
jjafferrCommented:
Hi omron60,

make this ield in your query
NextNumber: DCount("[AnyFieldNameInQuery]","YourQueyName","[AnyFieldNameInQuery] <=" & [AnyFieldNameInQuery])

SAVE the query after putting the name in the field above, then run it.

Hope this helps

Jaffer
0
 
QuetzalCommented:
If you are retrieving records from a single table and sorting the results on a field that is unique to each record, here is a simple way to do what you want.  Add a column to your query as follows (this assumes that the table is called tblTable and the field you are sorting on is called SortField):

Sequence: DCount("SortField","tblTable","SortField <=" & [SortField])

If SortField is text data, modify as follows (surround [SortField] by single quote):

Sequence: DCount("SortField","tblTable","SortField <='" & [SortField] & "'")
0
 
nico5038Commented:
Check also this for a ranking in a query:
http://support.microsoft.com/support/kb/articles/Q208/9/46.ASP

Nic;o)
0
 
eoinolCommented:
Use a static variable

Function IncrementalNumber(Optional strType As String) As Integer

Static NextNumber As Long
' Eoin O'Loughlin

If strType <> "Reset" Then
    NextNumber = NextNumber + 1
Else
    NextNumber = 0
End If
IncrementalNumber = NextNumber

End Function

In your query have a field that calls incrementalNumber() as MyNo
If you want to reset the number call IncrementalNumber("Reset") - this should be done before you run the query

0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now