A simpler way to do a pseudo-autonumber in a query

[Conan Kelly]

Hello all,

Isn't there a simpler way of doing a pseudo-autonumber in a query?

I saw in one post someone mentioned doing a subquery (IncNum: (SELECT
Count(*) FROM thetable As X WHERE X.sortfield <= thetable.sortfield)). That
might work on tables with small number of rows, but I'm working with a table
that has 112,241 records in it. I did a query (Query1) to trim that down to
17,741 records, and then in Query2 I set up my pseudo-autonumber column
based on Query1. Now I'm guessing that for every record in Query2, Query1
would have to be rerun twice on 112,241 records!!! (<--Is that correct?).
Needless to say, it was taking quite a long time to return all records
(don't know how long--didn't bother waiting to see). I just created a temp
table from Query1 and added an actual autonumber column to it.

Is there no function in Access, similar to XL's ROW() function, that could
accomplish this a lot faster (a function that would return the record number
in the record set)? After all, there is a Current Record text box in the
lower left corner of the query results view (in between the first, last,
next, previous, new record buttons). If Access can tell what the current
record number is based on the location of the cursor, you would think that
there would be some function to return the record number.

Thanks for any help anyone can provide,

Conan Kelly

 

[david]

You can't get at the record bookmark from inside the SQL: in
Access the record bookmark is a property of the DAO recordset
layer, not the JET data layer. Even if you could get at it, it wouldn't
help, because the recordset row id is not related in any way to
your sortfield value.

If you can rewrite your SQL as nested queries instead of using
an embedded subquery, it will probably go faster: Access is
designed to optimise ordinary nested queries, like you get using
the Access query design tools. Copying the query structure
from a different platform is not a good strategy.

It looks like you want Max instead of Count, but that probably
won't make it any faster. The main problem is the way you have
it indexed. If you get your indexing and your query right, Jet
will only have to lookup the count of the subset, and it shouldn't
be slow at all. But that is always tricky.

(david)

 

[Albert D. Kallal]

Any possibility this data is for a report?

If it is report, then just place a text box on the report, make give it a
permanent value of "1", and just have it "sum overall". You get a row
number, and it fast in terms of processing.

 

[mezzanine1974]

I have posted same question several times. But I could not get any
answer yet. It shouldnt be so difficult to have autonumber column in a
query result. Any expert who will give answer to this question will
make me very happy !

Thanks

 

[Guest]

You have gotten answers in a previous thread. Please be patient.