Need a quick way to add a row-number column to a query 2

[Dor100]

How can I quickly tweak an Access 97 query in SQL or design view so that the output has an unbound number column which gives a consecutive number-count for all the rows in the output (1,2,3,4...etc.)?

 

[dhookom]

It would really help to have some starting SQL to work from. Generically, you could use something like:
SELECT tblA.*,
(SELECT Count(*) + 1
FROM tblA A WHERE A.ID<tblA.ID) as RowNum
FROM tblA;

Duane
MS Access MVP

 

[Dor100]

This is working for selecting all records with the *, but not for when you have a Where clause limiting your output. I've tried some functions I found online, but no success yet. It's someone else's db and the SQL is way too hairy - your generic format is better here. Thanks a lot for this input. If you know how to get 1, 2, 3, 4, etc. when there's a Where clause, that would be great.

 

[dhookom]

You would need to add the same where clause to the subquery.

Do you realize how easy this is to do in a report?

Duane
MS Access MVP

 

[Dor100]

I'll take the report method and be happy. Very happy. I'm working on it right now, and will not complain if you give me the answer before I figure it out.

 

[Dor100]

Got it: cntrl source prop set to =1, running sum set to Over All. I knew I'd seen this in some else's report before. Your nudge is just what I needed. Thanks.

 

[dhookom]

I should have suggested this in my first reply...

Duane
MS Access MVP

 

[SheepDog]

I have a table with 100 records and want to use a query to return the records sequentially would I use this code listed below to accomplish this?

SELECT tblA.*,
(SELECT Count(*) + 1
FROM tblA A WHERE A.ID<tblA.ID) as RowNum
FROM tblA;

 

[dhookom]

The syntax is very generic. If you want them sequentially, you must have a field that identifies the sequence. The example uses a field named &quot;ID&quot;.

Duane
MS Access MVP