[AccessD] Rownumbers

Marcel Vreuls info at oop.nl
Thu Feb 20 08:07:00 CST 2003


Andy,

This works! but the output is not going to an access report but to a word
module. I am still working on a the solution from gustav. That one is not
easy to implement in my current query.

Thanks, i will save your solution for access reports!

Gr. marcel

-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of
andy at minstersystems.co.uk
Sent: donderdag 20 februari 2003 14:24
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Rownumbers


Alternatively Marcel you can do it without any code at all.
Within your Sorting and Grouping set a Group Header at your item level. Move
all of your printed fields from the Detail section into this new Group
Header section, and reduce the Detail section's height to get rid of it. Now
add an unbound control whose source is =Count(OR_NUMMER) and set its Running
Sum property to Over Group. That should do it.

Andy Lacey
http://www.minstersystems.co.uk


-- Original Message --
From: Gustav Brock <gustav at cactus.dk>
To: info <accessd at databaseadvisors.com>
Send: 2003-02-20
Subject: Re: [AccessD] Rownumbers

Hi Marcel

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



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
FROM
  tblOrderDetail
ORDER BY
  OR_OD_NUMMER,
  OR_NUMMER;



/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




_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list