[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Enumerating each record in a query. How?

Posted on 2005-04-21
7
Medium Priority
?
208 Views
Last Modified: 2012-08-13
I have a query containing a certain ammount of records.

What it is:

Name

John
Jack
Gil
Jessica
Rebecca

What I want it to be:

Name...........Record

John................1
Jack................2
Gil...................3
Jessica.............4
Rebecca...........5

I want the field records to know that john is the first record, jack the second, gil the third, and so on. Is this possible?

Perhaps DCount could do the job.
0
Comment
Question by:donniedarko801
7 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13837682
A query cannot do this.  If you want to display this on a report, you can use a report-level variable and add 1 to it in the .OnFormat event, and display the variable in an unbound text box.

Excel can do this using the =RANK command, but Access doesn't have an equivalent.

Hope this helps.
-Jim
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 13837868
OR ....

If you just had to do it in Access then ... Create a button to run your query


Private Sub MyButton_Click()

Dim rst as Recordset
Dim varRec

DoCmd.RunSQL "SELECT NAME, 0 AS RECORD INTO MyTempTbl
FROM MyQry;"

Set rst = CurrentDb.OpenRecordset("MyTempTbl")
rst varRec = 0

rst.MoveFirst
Do Until rst.EOF
     varRec = varRec + 1
     rst.Edit
     rst!Record = varRec
     rst.Update
     rst.MoveNext
Loop

rst.close

DoCmd.OpenQuery "MyQuery2"       'Base this query on your temp table.
End Sub


ET
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 13837895
This line should read ...

varRec = 0

INSTEAD OF

rst varRec = 0


ET
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 1

Author Comment

by:donniedarko801
ID: 13838244
I do not wish to rank anything. Just to count in a non-total way.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 13838584
in the source table, add a field called REcord_id which is an autonumber? ( or an ordinary number if you want to assign these in a specific order.

then display it in your query
select name, record_id as "record"
from tblname
where etc...
0
 
LVL 31

Expert Comment

by:hnasr
ID: 13838879
Create a make table query:
 Query+count

SELECT theTable.Name, counts(Val([Name])) AS Record INTO theNewTable
FROM [theTable];


In module have:

Global myCount As Integer
Public Function counts(x As Integer) As Integer
    myCount = myCount + 1
    counts = myCount
End Function

You run this query fro  a cutton on a form:

Private Sub query_count_Click()
myCount = 0
On Error GoTo Err_query_count_Click

    Dim stDocName As String

    stDocName = "query+count"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_query_count_Click:
    Exit Sub

Err_query_count_Click:
    MsgBox Err.description
    Resume Exit_query_count_Click
   
End Sub


Finally You can query theNewTable.



0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 1000 total points
ID: 13850611
Use a function that the one below in your query.

  Record: SequentialID(False, [fldAnyField], 1)

/gustav


Public Function SequentialID( _
  ByVal booReset As Boolean, _
  Optional ByVal varDummy, _
  Optional ByVal intIncrement As Integer = 1, _
  Optional ByVal lngInitialID As Long) _
  As Long
 
  ' Increments static variable lngCurrentID with intIncrement.
  ' Returns the new value of lngCurrentID.
  ' Parameter varDummy is used to force repeated calls of
  ' this function when used in a query.
  '
  ' Reset to start counting from zero incrementing by one:
  '   Call SequentialID(True)
  ' Reset to start counting from 1000:
  '   Call SequentialID(True, Null, 1, 1000)
  ' Reset to start counting from zero incrementing by 2:
  '   Call SequentialID(True, Null, 2)
  ' Reset to start counting from -2000 incrementing by -8
  ' and returning initial ID:
  '   lngID = SequentialID(True, Null, -8, -2000)
  '
  ' Retrieve the current ID:
  '   lngID = SequentialID(False)
  ' Do a count by one and retrieve the current ID:
  '   lngID = SequentialID(False, Null, 1)
  ' Do a count by one in a query and retrieve the current ID:
  '   lngID = SequentialID(False, [fldAnyField], 1)
  ' Do a count by minus two and retrieve the current ID:
  '   lngID = SequentialID(False, varAny, -2)
  '
  ' 2001-12-13. Cactus Data ApS, CPH.
 
  Static lngCurrentID As Long
  Dim intSgn          As Integer
 
  If booReset = True Then
    ' Reset ID.
    lngCurrentID = lngInitialID
  ElseIf Not intIncrement = 0 Then
    intSgn = Sgn(intIncrement)
    If intSgn * lngCurrentID < intSgn * lngInitialID Then
      ' Reset ID.
      lngCurrentID = lngInitialID
    Else
      ' Increment ID.
      lngCurrentID = lngCurrentID + intIncrement
    End If
  End If
 
  SequentialID = lngCurrentID
 
End Function
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

873 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