Solved

Add Sequential Number to Query Records

Posted on 2004-04-24
4
721 Views
Last Modified: 2008-03-17
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
Comment
Question by:omron60
4 Comments
 
LVL 27

Expert Comment

by:jjafferr
ID: 10908111
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
 
LVL 11

Accepted Solution

by:
Quetzal earned 125 total points
ID: 10908129
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10908271
Check also this for a ranking in a query:
http://support.microsoft.com/support/kb/articles/Q208/9/46.ASP

Nic;o)
0
 

Expert Comment

by:eoinol
ID: 10918917
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question