Returning Record# from query

[frank.moore]

Back in the days when I used to do FoxPro programming, there was a
function [RecNo()] which allowed you to get the record number of a
record returned from a query or table. Can anyone tell if MS Access
supports this same functionality and, if so, what the function is
called?

Thanks.

 

[[MVP] S.Clark]

No. Fake it like the following:

Select ID, Student, num, Count(Unique_Num) as Rank
From Table1, (Select Distinct Num as Unique_Num from Table1) as VTable
where unique_num>=num
group by id, student, num
order by count(unique_num)

 

[Frank Moore]

Steve,

Thanks for the help, I appreciate it. However, I do have a question.
Can you explain the purpose of the "num" field in your example and how
it should work?

I'm asking because I built a test DB to try this and created "num" as a
number field with a default value of 0 and the results came back in
inverse order to what I was expecting.

Thanks,

Frank

 

[[MVP] S.Clark]

The following SQL produces a dataset for all Northwind orders placed in
August 1996:

SELECT Orders.OrderID, Orders.OrderDate
FROM Orders
WHERE Orders.OrderDate Between #8/1/1996# And #8/31/1996#


The following SQL adds a sequential number to each record(starting from 1)
to each record in the previous dataset:
SELECT Orders.OrderID, Orders.OrderDate, Count(qryRecNo.RecNo) AS RecNo
FROM Orders,
(Select Distinct Orders.OrderID as RecNo from Orders WHERE
Orders.OrderDate Between #8/1/1996# And #8/31/1996# ) AS qryRecNo
WHERE (((qryRecNo.RecNo)<=[orderid])) AND
Orders.OrderDate Between #8/1/1996# And #8/31/1996#
GROUP BY Orders.OrderID, Orders.OrderDate
ORDER BY Count(qryRecNo.RecNo);


The trick used is that for each record in the Orders table, a count of the
OrderID's less than or equal to the current one, produces the sequential
number. As the first OrderID of that month is 10270, there is only one
record that is less than or equal to it, thus it receives the RecNo of 1.
The last OrderID of that month is 10294, and there are 25 other records that
are less than or equal to it.

This output is numbered by the OrderID in ascending order. This is because
the Less Than(<) symbol is used in the WHERE clause. To reverse the sort
order to Descending, change it to a Greater Than(>) symbol.

 

[Frank Moore]

Steve,

The explanation did the trick and I see what was going wrong to give me
the results I was getting. Thanks, you truly are an MVP of Access.

Frank