add autonumber field to query after sorting

[John]

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

Thanks in advance!

 

[Michel Walsh]

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

 

[Guest]

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];