Return dynamic Row Number in a query

[liddlem]

Hi folks
I have a query to return a numbers of rows (about 3500).
The results of this query will be used to load a table in a SQL database.
This target table requires that I include a unique identifier (number) in one of the fields.

I have 2 alternate solutions.
1. I can simply export the results to XL and use the autofill function.
2. I can make a "create table" query and let the system add its own ID.
However, both of these solutions demand multiple steps to get an end result.

Surely I could simply get my query to create the required field that includes the RowNumber in the returned data set?

So - assuming MyTable consist of

FName SName Role
Freddy Bloggs Bean Counter
Harrold Smitth Engineer
George George Duplicator

My query would look something like
select * from MyTable,
(select Row.Number() as RowCounter)

which will return

FName SName Role RowCounter
Freddy Bloggs Bean Counter 1
Harrold Smitth Engineer 2
George George Duplicator 3

I have trawled through a number of forums and have found part solutions that dont seem to work.

Thanks

 

[John Big Booty]

Try;

SELECT Mytable.FieldName, (SELECT Count(*) FROM [Mytable] y WHERE y.FieldNameX <= [Mytable].FieldNameX) AS Line, Mytable.FieldName, Mytable.FieldName2, Mytable.FieldName3 
FROM Mytable
ORDER BY Mytable.FieldNameX;

Where FieldNameX is a field that contains no duplicates.

 

[Atomic Shrimp]

Isn't that also only going to work if you present the results sorted by fieldnameX?

If you sort them by first name, Freddy Bloggs might appear at the first row in the query results, but if he was the last one added to the table, and if fieldnameX is an autonumber behaving if fairly normal manner, there will be lots of records for which fieldnameX is smaller than his.

Edited to add: I see this doesnn't actually matter too much in the OP's example where it's just a case of generating a unique ID....

BUT... if the goal is to generate a unique ID in the query results, and one of the specific requirements is that FieldnameX is unique, then why not just pass FieldnameX straight through into the results (does it matter that fieldnameX might not run in a continuous sequence?)

I think it's probably better to add an autonumber ID into the main table and pass this through, as the relationship between people records and the ID will persist - if you use a synthetic numbering sequence based on the count of smaller-numbered IDs, there is a potential for Freddy Bloggs to have a different number against him each time you run the query, if rows have been deleted in between

 

[John Big Booty]

Certainly the OP could simply use an auto number, but in his example he has numbered his row form 1 up.

To get my solution working he could simply concatenate a number of fields (in his query) to create unique records for FieldNameX, that can then be counted.

 

[neileg]

If all you need is a unique identifier, then it doesn't matter if the numbers are consecutive or start from 1. You could generate a random number in a calculated field in your query then it's all done in a single step.

Not sure what you mean by an SQL database. That's not a product name.

Can't help feeling, though, that you should consider if you will need to add any more rows to the SQL table in future. None of the ideas mentioned here will cope with that unless you maintain a table in Access that mirrors your SQL table and use an autonumber PK. Does whatever SQL db you are using support the idea of autonumbers itself?