Adding line numbers

[Brian C]

I have a file that I import which is a detail file of a order system. It contains the basic elements of an order system (order number, item, quantity,, and pricing).

I need to add line numbers to the orders, so if there are three products on the order, I need to add line numbers (0001, 0002, and 0003). This would occur for any order adding lines no matter how many lines. At the same time, the line numbers need to recycle back to 1 at the beginning of each order?

Any suggestions?

Brian C

 

[James A. Fortune]

I have a file that I import which is a detail file of a order system.
It contains the basic elements of an order system (order number, item,
quantity,, and pricing).

I need to add line numbers to the orders, so if there are three products
on the order, I need to add line numbers (0001, 0002, and 0003). This
would occur for any order adding lines no matter how many lines. At the
same time, the line numbers need to recycle back to 1 at the beginning
of each order?

Any suggestions?

Brian C

Adapted from:

http://groups.google.com/group/comp.databases.ms-access/msg/ff7c2448189c9633

tblOrders
OID AutoNumber
OrderData Text
OID OrderData
1 qa
2 ff

tblOrderDetails
ODID AutoNumber
OID Long
SubData Text
ODID OID SubData
1 1 s1
2 1 s2
3 2 ss1
4 2 ss2

qryOrderDetails:
SELECT tblOrderDetails.ODID, tblOrders.OID, OrderData, SubData FROM
tblOrderDetails INNER JOIN tblOrders ON tblOrderDetails.OID = tblOrders.OID;

!qryOrderDetails:
ODID OID OrderData SubData
1 1 qa s1
2 1 qa s2
3 2 ff ss1
4 2 ff ss2

qryOrderLineItems:
SELECT OrderData, Format((SELECT Count(A.ODID) + 1 FROM qryOrderDetails
AS A WHERE A.ODID < qryOrderDetails.ODID AND A.OID =
qryOrderDetails.OID), '0000') AS LineNumber, ODID, OID, SubData FROM
qryOrderDetails;

!qryOrderLineItems:
OrderData LineNumber ODID OID SubData
qa 0001 1 1 s1
qa 0002 2 1 s2
ff 0001 3 2 ss1
ff 0002 4 2 ss2

I hope this helps. Disclaimer: I did it quickly without testing any
more than that.

James A. Fortune
(e-mail address removed)

 

[BruceM]

If all you need is to display the line numbers on the order (assuming each
line item is a separate record, which is how I interpret your explanation)
you could set the Control Source of an unbound text box on the report to =1,
and its Running Sum property to Over Group. Format the text box to 0000.

*******************

I have a file that I import which is a detail file of a order system. It
contains the basic elements of an order system (order number, item,
quantity,, and pricing).

I need to add line numbers to the orders, so if there are three products on
the order, I need to add line numbers (0001, 0002, and 0003). This would
occur for any order adding lines no matter how many lines. At the same
time, the line numbers need to recycle back to 1 at the beginning of each
order?

Any suggestions?

Brian C