add Auto Number column to a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to have a column in a query that represent the relationship/position
of the row/record to the query. The value for the 1st record would be 1, 2nd
record would be 2, and so on (simular to how Auto Number works for a table).

Anyone have any suggestion how I would accomplish this. Is there VBA code
which would do this? Does access offer other functionallity which would
accomplith this?

Thanks in advance for your assistance.
Manuel
 
It's an interesting problem as queries are not really geared up for this kind
of thing. The best solution is probably to find another way of approaching
what it is you're doing and avoid having to have an "autonumber" field at all.

However, here's a rather clumsy solution that I'd be interested to see if
anyone can improve on:

First of all in the Design view of the query, in a new column type
"myID([field])" (without the quotes) and, instead of "field" type the name of
an existing field in the query. Save the query.

Next, create a module from this area of the main database window and paste
the following VBA code into it:

Dim myCounter As Long

Function myID(x)
Dim rst As DAO.Recordset
myCounter = myCounter + 1
myID = myCounter
Set rst = CurrentDb.OpenRecordset(CurrentObjectName, dbOpenDynaset)
rst.MoveLast
If myCounter = rst.RecordCount Then myCounter = 0
End Function

Run the query and the numbers should appear.

It's slow and there's a problem that if you cause the query to refresh half
way through its operation (even by resizing the window!) the numbers go
wrong. If that happens, the only way to reset the counter is to close and
open the database (or reset myCounter by other means).

It's a pretty dreadful solution and I'd like to hear if anyone's got a
better way to do it!
 
Manuel said:
I need to have a column in a query that represent the relationship/position
of the row/record to the query. The value for the 1st record would be 1, 2nd
record would be 2, and so on (simular to how Auto Number works for a table).

Anyone have any suggestion how I would accomplish this. Is there VBA code
which would do this? Does access offer other functionallity which would
accomplith this?
Click to expand...


You need to use a subquery to do this. In order for it to
work, you must have a unique sort order that you can us to
determine what mumber a record should be assigned.

SELECT *,
(SELECT Count(*)
FROM table As X
WHERE X.sortfield <= T.sortfield) As Position
FROM table As T

If you don't care what number is assigned to a record, then
use the table's primary key field as the sort field.
 
Back
Top