Adding sequential numbering to a query

[jonomac]

Hi,

Just trying to figure out how I can run a select query on some data I have and how to also include an additional field which has a value counting the number of rows.... eg in the first row there would be the usual results and the new field would have a value of 10001, second row would be 10002, third would be 10003.

I've done some searches for sequential numbering but couldnt find anything so far.

Thanks, j

 

[Meltdown]

This should get you started

http://support.microsoft.com/default.aspx?scid=kb;en-us;210340&Product=acc

 

[jonomac]

Hiya, thanks for the reply but is it not possible to do this from within a query and not using vba... ie add an autonumber field to the fields you are selecting?

Cheers, j

 

[Meltdown]

If you have an Autonumber already you can just put..

NewNumber: 1000 & [AutoNumberFieldName]

in the querygrid, but that won't be sequential when you lose or delete a record.

 

[SMatthews]

Check out this thread: http://www.access-programmers.co.uk/forums/showthread.php?t=94510

and/or this link:

http://databasejournal.com/features/mssql/article.php/10894_2244821_2
(original link was wrong)

Let me know if these help.

 

[jonomac]

Thats the kind of thing I'd already tried but there isn't an autonumber on the table as I import it into the database and then want to append the autonumber column on to it....

I guess this is what I'd like it to do if I have to do it in VBA:

Sub Add_AutoNumber_Field()
Dim db As Database
Dim tdf As TableDef
Set db = CurrentDb
Set tdf = db.TableDefs("tbl_Import")
tdf.Fields.Append tdf.CreateField("new", dbAutonumber)
Set tdf = Nothing
db.Close
Set db = Nothing
End Sub

But there is no field called type called dbAutonumber according to VBA, any ideas?

 

[Pat Hartman]

Why do you want to add a sequential number to the query? Could you not add the sequential number to a report? That only requires setting a property.

As you've probably seen, you MUST have a unique identifier to order the recordset in order to assign this value with SQL.

 

[jonomac]

Check out this thread: http://www.access-programmers.co.uk/forums/showthread.php?t=94510

and/or this link:

http://databasejournal.com/features/mssql/article.php/10894_2244821_2
(original link was wrong)

Let me know if these help.

Hi again,

The link to the database journal had the right idea, thanks! The code I have now is as follows:

SELECT count(*) as nm_record,t.tx_product, t.am_notl
FROM tbl_Import t inner join tbl_Import t1
ON t.tx_product >= t1.tx_product
GROUP by t.tx_product,t.am_notl
ORDER by t.tx_product,t.am_notl;

This works great if there is only one instance of each tx_product, however it falls down if there are multiple copies of the field and starts assigning multiplie values instead of continuing the numbering eg 1,2,3,4,5 against each line.

Can anyone improve on the code I have above possibly?

 

[Pat Hartman]

This works great if there is only one instance of each tx_product, however it falls down if there are multiple copies of the field and starts assigning multiplie values instead of continuing the numbering eg 1,2,3,4,5 against each line.

See my previous post.