Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create row number in query (2000/XP)

    Can I add a 'row number' column to a sorted query?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create row number in query (2000/XP)

    Let's say that your query is sorted ascending on a numeric field ID.
    You can add a calculated field:

    RowNum: DCount("*", "NameOfTableOrQuery", "ID <= " & [ID])

    If it is a text field instead of a number field, you'd use

    RowNum: DCount("*", "NameOfTableOrQuery", "ID <= " & Chr(34) & [ID] & Chr(34))

    By the way, if you need the row number for a report, it can be done in an easier way:
    - Place a text box in the detail section of the report.
    - Set its Control Source to =1
    - Set its RunningSum property to Over All if you want to number all records sequentially, or to Over Groups if you want to restart numbering at 1 for each group.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create row number in query (2000/XP)

    The query is sorted by 2 columns to get the overall proper order.

    It's actually a make table query (not entirely necessary but it helps keep me organised - this is related to my othe recent post with Excel DAO). I'd managed to then use a modify table query to add in a autonumber field.

    Unfortunately, I 've realised the 'row number' doesn't actually help with the problem I have. I cant figure out how to actually explain my next question <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    Thanks again

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create row number in query (2000/XP)

    Give it a try, I'll ask questions in return if the problem isn't clear to me.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create row number in query (2000/XP)

    I think I can get away without doing it (quite whatever it is). <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    The spreadsheet I've imported the data into is hideous beyond belief. There will be a fair amount of manual work for someone (somethings just aren't possible for me to do). Whoever does that can sort out the 'sorting' I can't (be bothered?) to fix. To be honest, the way I can provide the data can at least be checked easily to see if everthing is there. The re-ordering I was thinking of would actually make it harder to check!

Posting Permissions

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