Sequential Record Numbering in Queries

[Tom]

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!

 

[John Spencer (MVP)]

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

 

[Dale Fye]

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.