autonumber to number fields in a query

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

Guest

is there any way to create an autonumber down the side of the fields in a query
please help
thanks so much
 
Not an easy way that works for all queries, no.

If the query is sorted by a unique field, you can use a DCount() expression
or a subquery to count the number of records. For example, if it is sorted
by the field ID from Table 1, you could do it by typing this expression into
the Field row:
MyCount: (SELECT Count(ID) FROM Table1 AS Dupe
WHERE Dupe.ID <= Table1.ID)
However, this subquery will need to be modified to match the WHERE clause in
your query, and won't work at all if the user right-clicks a field in the
query output, and sorts it differently.

Another alternative is to create a table with exactly the same fields as the
query, plus an AutoNumber field. Then change the field into an Append query
(Append on Query menu), and add the records to the table. The autonumber
then gives you the sequence.

If subqueries are a new concept, this might help:
http://allenbrowne.com/subquery-01.html
 
Database said:
is there any way to create an autonumber down the side of the fields in a query
please help
Click to expand...


As long as you can define a unique sorting on the records,
you can use a subquery to rank the records.

SELECT *, (SELECT COUNT(*)
FROM table As X
WHERE X.sortfield <= table sortfield
) As Rank
FROM table
 
It seems like its leading me on the right track but can i ask you a question
about it.
I did the first part of creating the module and then putting what it says
into the query but then it says

make a function
function ResetCounter()
IncrementVariable = 0
end function
and call
ResetCounter()
the last reseting method is more reliable.

i'm not sure where to put this function
please let me know
thanks
 
Back
Top