Displat Row Numbers in a query 4

[johnnylogistics]

I am relatively new to Access and need help with a query. I would like to display a row number for the results of my query. When using sql in Oracle, I use the "ROWNUM" expression. For example:

Select
order_no,
product_id,
order_qty
ROWNUM line_no
from
order_table
where order_no in ('123')

might display the following results:

Order_no Product_id Order_Qty LINE_NO
123 11111 2 1
123 22222 1 2
123 33333 1 3

How would I display the same results for LINE_NO in Access without using a pass through query. What would be the equivelant expression to ROWNUM in Oracle for access?

I am thinking it is a simple condition, however I can not find it in the help section of access.

 

[BNPMike]

if you need to, do this:

Step 0 Write your query

Step 1 Turn it into a Make table Query ie output it to a temporary table TEMPRN(you are just adding INTO TEMPRN)

Step 2 Add rownumbers:
ALTER TABLE TEMPRN ADD COLUMN RN COUNTER;

Step 3 Select * from TEMPRN

Thanks to JoeMiller for clarification on syntax. mike.stephens@bnpparibas.com

 

[PaulF]

or

create a new module and add this Public Variable and a function

Global RecordNum
Function DoesIncrement(AnyValue)
RecordNum = RecordNum + 1
DoesIncrement = RecordNum
End Function

Next create your query, but add a new expression field where youc call the Function from and pass it one of your fields that are in your table

RecNum: DoesIncrement(Product_ID)

only thing to remember is that you are using a Public Variable, so if you run this Query more than once without closing out Access, the RecNum will pick up at the last number you used. Best to reset the Variable back to Zero prior to running this query.

PaulF

 

[tlbroadbent]

Here is another common solution. It uses a correlated sub-query.

SELECT
rder_no, o.Product_id, rder_qty,
(Select count(*) From Order_table
Where order_no=o.order_no
And product_id<=o.Product_id) AS line_no
FROM order_table AS o
WHERE rder_no In (&quot;123&quot
ORDER BY rder_no, o.Product_id; Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[tlbroadbent]

Mike,

The query I posted is inefficient for large result sets. However, the performance is quite good for small result sets. A similar query on my PC selected and numbered 175 rows from a table of over 3400 rows in less than 1 second. The timing was about the same for the procedure using a temporary table with an Autonumber column.

I certainly wouldn't recommend my query for numbering result sets of thousands. But I'll use this procedure for small result sets. It is simple and requires no VBA or macro coding. A developer needs to keep a full toolbox and know when to use each tool.

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[BNPMike]

For the benefit of Craig there are two 'objections' to Terry's solution. One is it uses a subselect and these usually are not handled by the optimiser even though admittedly good representation of the problem should take precedence over efficiency.

The second is that for each record Access has to read every other record. If you had a million records, Access would have to traverse the whole database 1,000,001 times. My solution is just twice.

Sorry my earlier post wasn't more explicit.

mike.stephens