Create row number in query (2000/XP)

Topic

New Reply

Can I add a ‘row number’ column to a sorted query?

Reply | Quote

Replies

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.

Reply | Quote

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

Thanks again

Reply | Quote

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

Reply | Quote

I think I can get away without doing it (quite whatever it is).

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!

Reply | Quote