[AccessD] Rownumbers

Hi Marcel

You can add a subquery to return the rownumber (RowNo):

<SQL>

SELECT
  OR_OD_NUMMER,
  OR_NUMMER,
  (SELECT
    COUNT(*)
  FROM
    tblOrderDetail AS tblA
  WHERE
  tblA.OR_OD_NUMMER = tblOrderDetail.OR_OD_NUMMER
  AND
  tblA.OR_NUMMER <= tblOrderDetail.OR_NUMMER;) AS RowNo
FROM
  tblOrderDetail
ORDER BY
  OR_OD_NUMMER,
  OR_NUMMER;

</SQL>

/gustav

> I will try in my best englisch to make this problem clear to you. Hope it
> works:-) I have a table in my database with an autonumber field and a field
> wich contains ordernumers. It looks like this

> OR_NUMMER    OR_OD_NUMMER
> 1000                    10
> 1001                    10
> 1002                    10
> 1003                    11
> 1004                    11
> 1005                    10
> 1007                    12
> 1008                    10

> Now we want to print on a report the actual orderposition of a current
> record. For example ordernumber 10 contains rownumbers 1000,
> 1001,1002,1005,1008. We would like to print 1, 2, 3, 4, 5 according to the
> actuel rownumbers. I have tried to make this happen to take parts of the
> actual autonumber but that is not sufficient enough.

> What I am trying is to make a function whicht take the current recordset and
> put in a field with a number a calculatie in the function.

> FncRownumber(ByRef rs as dao.recordset)
>     Dim Rownumber as long

>     rs.movefirst
>     Do while not rs.eof
>         Rownumber = Rownumber + 1
>         rs.movenext
>     loop
> End function

> It works but I cannot get this in the report because then I lose the
> recordset.

> Are there other ways to make this happen exept putting an extra field in the
> table which contains this recordnumber?


> Thanks, Marcel Vreuls