468,289 Members | 1,878 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,289 developers. It's quick & easy.

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 1383
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
reply views Thread by NPC403 | last post: by
2 posts views Thread by MrBee | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.