If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sequential Record Numbering in Queries
All, I have a query that gets used by several people after being
exported to Excel for them to analyze offline. I'd like to add a query field that generates a sequential "index" or "row" number inside Access instead of manually adding it in Excel. What's an easy way to do this that won't make the query run a lot slower? Thanks! |
#2
|
|||
|
|||
Sequential Record Numbering in Queries
Search the group on RANKING queries. BUT ranking queries are slow with large
sets of data so your wish to not slow down the query may be impossible. The general syntax of a ranking query is something like the following. SomeField should be able to uniquely identify a record in the set of records if you want to get sequential numbers. If you need to use more than one field to uniquely identify the sequence then the query becomes more complex and slower. SELECT * , (Select Count(*) FROM TableA as Temp WHERE Temp.SomeField TableA.SomeField) as Rank FROM TableA Order by SomeField John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Tom wrote: All, I have a query that gets used by several people after being exported to Excel for them to analyze offline. I'd like to add a query field that generates a sequential "index" or "row" number inside Access instead of manually adding it in Excel. What's an easy way to do this that won't make the query run a lot slower? Thanks! |
#3
|
|||
|
|||
Sequential Record Numbering in Queries
Tom,
I have a function I use for the rare occassion that I want to do this. But it's use is very limited. 1. Before you call this function, you must first reset the static variable. You do this with a simple line of code: Call fnSeqNo(0, true) 2. You can add the function as a computed field in a query, something like: SELECT yourTable.*, fnSeqNo([SomeField]) as SeqNo FROM yourTable ORDER BY [SomeField] The problem with this query is that if you run the SELECT query, and scroll through the recordset, the function will call itself over and over again. So when you first open the query, the first record will show 1, but if you scroll down (off the visible page), and then click the First Record button, the 1st record will now show some other count. I generally only use this when I want to populate a temporary table, using a make-table or append query. When you do this, it only computes the value for each record once. You can also use it with the OutputTo method, but only in certain circumstances. I've found that if you save the SELECT query, and output the query to EXCEL, it will the function will fire twice for each record (so the values will be 1, 3, 5, ...). Although I generally don't use this function with reports (see note below about sequential numbering in a report), I have found you can base the report on the query, and use the OutputTo method to export it as either a Snapshot or PDF file without the problem mentioned above. However, if you view the report in print-preview, then print it from the preview, the numbering will be sequential but will not start from 1. **NOTE: If you need this sequential number for a report, I would recommend just adding a text field, setting the ControlSource to =1, then setting the Running Sum property to "Over Group" or "Over All". Public Function fnSeqNo(SomeValue As Variant, Optional Reset As Boolean = False) As Long Static mySeqNo As Long If Reset = True Then mySeqNo = 0 Else mySeqNo = mySeqNo + 1 End If fnSeqNo = mySeqNo End Function -- HTH Dale email address is invalid Please reply to newsgroup only. "Tom" wrote: All, I have a query that gets used by several people after being exported to Excel for them to analyze offline. I'd like to add a query field that generates a sequential "index" or "row" number inside Access instead of manually adding it in Excel. What's an easy way to do this that won't make the query run a lot slower? Thanks! |
Thread Tools | |
Display Modes | |
|
|