add autonumber field to query after sorting

  • Thread starter Thread starter John
  • Start date Start date
J

John

Is there a way to add an autonumber field to a query after
the query is sorting accordingly.

Thanks in advance!
 
Hi,


Not with Jet... unless you append the data into an existing table
(but without record) which has such an autonumber ( you don't supply the
value for that autonumber filed, when you insert the new records).


Hoping it may help,
Vanderghast, Access MVP
 
Hopefully this might help you out a little. I wanted to do the same thing... create an 'autonumber' after i sorted a query. I was helped and given this SQL to fit my specific data... (queried a table with four fields [from,to,name,address] and then added the 'autonumber' field on the end after sorting)

SELECT [Election Table].[from], [Election Table].[to], [Election Table].[name], [Election Table].[address], 1+(SELECT Count([Election Table].from) FROM [Election Table] as VT
WHERE (VT.from < [Election Table].from) OR (VT.from = [Election Table].from AND nz(VT.name,"") < nz([Election Table].name,"")) OR (VT.from = [Election Table].from AND nz(VT.name,"") = nz([Election Table].name,"") AND nz(VT.address,"") < nz([Election Table].address,""))) AS RowNum
FROM [Election Table]
WHERE [Election Table].[from] Is Not Null
ORDER BY [Election Table].[from], [Election Table].[name], [Election Table].[address];
 
Back
Top