Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2002
    Location
    Boston, MA
    Posts
    8

    Unanswered: "row number" from query

    There has got to be a simple way to do this, but I'm stumped...

    I'm on Access 97. I need to be able add a "counter" to a query... so I want the first row of results to have a 1, the second a 2, and so on.

    The reason I am doing this is because I am using multiple append queries to append data into one big table. I need to know the order of each subset.

    Help! Thank you!

  2. #2
    Join Date
    Nov 2002
    Posts
    150

    Re: "row number" from query

    I don't think this is as easy as it sounds. You have a few options though (none of them real pretty).

    Each time you run the query you could create a table with all the fields you need plus an AutoNumber field. Then import the records into your table.

    Or you could do something like this:

    Public Sub test2()
    Dim rst As Recordset
    Set rst = CurrentDb.QueryDefs("query2").OpenRecordset
    rst.MoveFirst
    Do While Not rst.EOF
    'code to insert records into your table goes here
    'AbsolutePosition is 0 based so add 1.
    Debug.Print rst.AbsolutePosition + 1
    rst.MoveNext
    Loop
    End Sub

  3. #3
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122
    Create an AutoNumber in your BIG table. The records will automatically be numbered when appended. To know what append query they came from, each append query should have a calculated field:
    SubSet: "QueryName"
    Needless to say, the BIG table should have the same field...

    Numbering the records in a query is difficult, time consuming, needs special conditions to be met and in my opinion, useless...

    Good luck,

    Dan

  4. #4
    Join Date
    Sep 2002
    Location
    Boston, MA
    Posts
    8

    Re: "row number" from query

    Thank you, all, for your input. I can't believe it's so hard! I couldn't use the autonumber because I needed the count to reset for each append query... but I think we're going with a different approach entirely.

    I appreciate the feedback!

  5. #5
    Join Date
    Feb 2004
    Posts
    5

    Re: "row number" from query

    Jenn,

    I would be very interested in knowing what you ended up doing with this. I have an Access 97 database and we are tracking families waiting for services. As they meet all the criteria, they go onto the Waiting List. As a slot opens, the next available family is taken off the Waiting List. I want an easy way to look up a family and display their current position on the Waiting List. So I need a counter that can be re-set to #1 each time I update the table.

    I use macros, action queries, and a little Visual Basic that I copy from "real" programmers.

    If you found a good way to get a "row number" I would be very interested in learning what it was.

    Thanks,
    Sandy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •