Numbering rows on Select statement output 2

[Sashanan]

A friend building a little database application asked me if it's possible to automatically number the rows from an SQL output. For instance, if the statement:

SELECT Row1, Row2
FROM TABLE1

would give:

Row1 Row2
Soup 20
Bread 40

She would like to be able to get the following:

Row1 Row2

1. Soup 20
2. Bread 40

Is there an SQL-based solution for this? The DBMS used here (should it make a difference) is Access 97.

Thanks in advance!
"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[tlbroadbent]

1) Yes you can do this in Access 97.

Option A: Simple but inefficient for large tables.

Select RowNo=(Select count(*) From table
Where Row1<=t.Row1
And Row2<=t.Row2),
Row1, Row2
From table As t

See the following links for more info and links to downloadable samples.

Access 2, 95 and 97:

http://support.microsoft.com/support/kb/articles/Q120/6/08.asp

Access 2000:

http://support.microsoft.com/support/kb/articles/Q208/9/46.ASP

Option B: see faq701-1155, faq701-896 or faq181-15.

This technique is more complex but more effiecient for larger tables.

Recommendation: Post Access questions in an Access forum. Access Jet SQL is not fully ANSI compliant. Try Forum701. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Sashanan]

Thank you for the swift reply. I'll run the options by her and have her decide what's best in this case.
&quot;Much that I bound, I could not free. Much that I freed returned to me.&quot;
(Lee Wilson Dodd)

 

[Quehay]

That's a cool trick! Thanks Terry *

 

[PruSQLer]

I tried this in Access and was hit with the &quot;Enter Parameter Value&quot; InputBox. At the risk of asking a silly question, what value needs to go in there? When I just click OK, the RowNo generate doesn't work.

 

[tlbroadbent]

Can you tell us what you tried in Access? Which solution? Perhaps, you can post the SQL view of the query. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[PruSQLer]

Here's what I submitted in Access 97:

Select RowNo=(Select count(*) From Customers
Where CustomerID<=t.CustomerID
And CompanyName<=t.CompanyName),
CustomerID, CompanyName
From Customers As t

 

[tlbroadbent]

I can't see anything wrong in the syntax. I tried a similar query in Access 97 and it worked as it should. Access will ask for a parameter if one of the columns referenced doesn't exist. Make sure all column names are spelled correctly.

If the CustomerID is unique you can simplify the query by removing the CompanyName for the sub-query. You should also include an Order By clause on the query.

Select RowNo=
(Select count(*) From Customers
Where CustomerID<=t.CustomerID),
CustomerID, CompanyName
From Customers As t
Order By t.CustomerID Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.