471,077 Members | 760 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,077 software developers and data experts.

Record Numbering

Hello,

would someone please be so kind and help me with this problem.

i have this query that lists a number of order numbers on different rows however according to the query criteria the same order number may appear on many rows.
what i need to do is number these order numbers as follows

Order Number record number
123456 1
123456 2
123456 3
7890 1
7890 2
7890 3
and so on

please help
thanks
Lee-Anne

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #1
5 1432
Lee-Anne Waters via AccessMonster.com wrote:
Hello,

would someone please be so kind and help me with this problem.

i have this query that lists a number of order numbers on different rows however according to the query criteria the same order number may appear on many rows.
what i need to do is number these order numbers as follows

Order Number record number
123456 1
123456 2
123456 3
7890 1
7890 2
7890 3
and so on

please help
thanks
Lee-Anne

In a report you can create a running sum field that breaks on the order
number.

Does your table have an autonumber field? Such as OrderId? If so, you
could use DCount() to find all records with a matching order number
where the OrderID aunumber is less/equal the currecnt autonumber. Ex:
RecNum : DCount("OrderID","OrdersTable","OrderNumber = " & _
[OrderNumber] And OrderID <= " & [OrderID])
This adds overhead to your program.
Nov 13 '05 #2
Hi,

Thanks but that did not work :(

this is what i have so far in my query expression

DCount("OrderID","Orders","OrderNumber = " &[OrderNumber] And OrderID <= " & [OrderID])

please bear with me i'm new to this

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #3
On Fri, 03 Dec 2004 01:54:08 GMT, "Lee-Anne Waters via
AccessMonster.com" <fo***@AccessMonster.com> wrote:

Try this:
DCount("OrderID","Orders","OrderNumber = " & [OrderNumber] & " And
OrderID <= " & [OrderID])

-Tom.

Hi,

Thanks but that did not work :(

this is what i have so far in my query expression

DCount("OrderID","Orders","OrderNumber = " &[OrderNumber] And OrderID <= " & [OrderID])

please bear with me i'm new to this


Nov 13 '05 #4
Thanks Tom,

that nearly worked. this is what i have now

OrderID OrderNumber RecordNumber
7225 1826964 1
7225 1826964 1

also ordernumber is a text field

thanks so much for this
Lee-Anne

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #5
On Fri, 03 Dec 2004 03:20:23 GMT, "Lee-Anne Waters via
AccessMonster.com" <fo***@AccessMonster.com> wrote:

Text field values need to be wrapped in single-quotes:
DCount("OrderID","Orders","OrderNumber = '" & [OrderNumber] & "' And
OrderID <= " & [OrderID])

-Tom.
Thanks Tom,

that nearly worked. this is what i have now

OrderID OrderNumber RecordNumber
7225 1826964 1
7225 1826964 1

also ordernumber is a text field

thanks so much for this
Lee-Anne


Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Dan | last post: by
5 posts views Thread by Grim Reaper | last post: by
3 posts views Thread by perryche | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.