line numbers

Hi,

Have a field for order number. An order can have more
than one line item. How do I create a field called "Line
Number" and force it to start at one and increment by 1
until the order number changes. After each order number
changes, the line numbers start at 1 again.

I am trying to use this with imported data and using it
via a query.

Thanks in Advance for you help

Al

Dear Al:

The technique to do this is termed a "correlated subquery." It will
depend on there being one or more columns in your table that put the
rows of each and every order in a unique order. Do you have such a
column or columns?

If you will provide such details, I will work out some SQL to do this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 13 Jul 2004 12:41:00 -0700, "Al V"
wrote:

Hi,

Have a field for order number. An order can have more
than one line item. How do I create a field called "Line
Number" and force it to start at one and increment by 1
until the order number changes. After each order number
changes, the line numbers start at 1 again.

I am trying to use this with imported data and using it
via a query.

Thanks in Advance for you help

Al

If I read your question correctly, the order numbers that
have multiple lines in them are consecutive and are
sequential.

Does that help?


-----Original Message-----
Dear Al:

The technique to do this is termed a "correlated
subquery." It will
depend on there being one or more columns in your table
that put the
rows of each and every order in a unique order. Do you
have such a
column or columns?

If you will provide such details, I will work out some
SQL to do this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 13 Jul 2004 12:41:00 -0700, "Al V"
wrote:

Hi,

Have a field for order number. An order can have more
than one line item. How do I create a field
called "Line
Number" and force it to start at one and increment by 1
until the order number changes. After each order number
changes, the line numbers start at 1 again.

I am trying to use this with imported data and using it
via a query.

Thanks in Advance for you help

Al

.

I think Tom's question might be: If you wrote down each Line Item on a
marble and threw them all in a bag (one bag for each Order), when you pulled
them back out, would you have something written on the marble that suggested
the order you wanted to put them in?

--
Duane Hookom
MS Access MVP
--

"Al V" wrote in message
...
If I read your question correctly, the order numbers that
have multiple lines in them are consecutive and are
sequential.

Does that help?


-----Original Message-----
Dear Al:

The technique to do this is termed a "correlated
subquery." It will
depend on there being one or more columns in your table
that put the
rows of each and every order in a unique order. Do you
have such a
column or columns?

If you will provide such details, I will work out some
SQL to do this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 13 Jul 2004 12:41:00 -0700, "Al V"
wrote:

Hi,

Have a field for order number. An order can have more
than one line item. How do I create a field
called "Line
Number" and force it to start at one and increment by 1
until the order number changes. After each order number
changes, the line numbers start at 1 again.

I am trying to use this with imported data and using it
via a query.

Thanks in Advance for you help

Al

.

Yes, the term "bag" is technically the exact term here. The rows are
NOT in any specific order that can be detected, or which will even be
repeatable (although it may seem to be repeatable, it isn't
guaranteed, and cannot be accessed in a query.) A "Ranking Correlated
Subquery" must have something on which to base the way it numbers the
rows within each order, and this must be in the data. Otherwise, it
cannot be done.

Some natural value, or an autonumber or identity column will work,
although that may determine a different order than the one you're
accustomed to seeing.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 13 Jul 2004 15:48:33 -0500, "Duane Hookom"
wrote:

I think Tom's question might be: If you wrote down each Line Item on a
marble and threw them all in a bag (one bag for each Order), when you pulled
them back out, would you have something written on the marble that suggested
the order you wanted to put them in?

--
Duane Hookom
MS Access MVP

General note: putting the rows of a table into a specific,
repeatable, and useful order should be a general design criterion for
most tables any of you design. I'm not saying you MUST have such an
order for every table, but I'm suggesting you really should think
about it before deciding you don't want to have an order. Very few
tables should escape this design factor.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 13 Jul 2004 16:03:28 -0500, Tom Ellison
wrote:

Yes, the term "bag" is technically the exact term here. The rows are
NOT in any specific order that can be detected, or which will even be
repeatable (although it may seem to be repeatable, it isn't
guaranteed, and cannot be accessed in a query.) A "Ranking Correlated
Subquery" must have something on which to base the way it numbers the
rows within each order, and this must be in the data. Otherwise, it
cannot be done.

Some natural value, or an autonumber or identity column will work,
although that may determine a different order than the one you're
accustomed to seeing.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 13 Jul 2004 15:48:33 -0500, "Duane Hookom"
wrote:

I think Tom's question might be: If you wrote down each Line Item on a
marble and threw them all in a bag (one bag for each Order), when you pulled
them back out, would you have something written on the marble that suggested
the order you wanted to put them in?

--
Duane Hookom
MS Access MVP

I am a little confused on what you are asking for. Each
row does have a unique number.. example

ID Order Number Product

13050 100
-----Original Message-----
Yes, the term "bag" is technically the exact term here.
The rows are
NOT in any specific order that can be detected, or which
will even be
repeatable (although it may seem to be repeatable, it
isn't
guaranteed, and cannot be accessed in a query.)
A "Ranking Correlated
Subquery" must have something on which to base the way it
numbers the
rows within each order, and this must be in the data.
Otherwise, it
cannot be done.

Some natural value, or an autonumber or identity column
will work,
although that may determine a different order than the
one you're
accustomed to seeing.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 13 Jul 2004 15:48:33 -0500, "Duane Hookom"
wrote:

I think Tom's question might be: If you wrote down each
Line Item on a
marble and threw them all in a bag (one bag for each
Order), when you pulled
them back out, would you have something written on the
marble that suggested
the order you wanted to put them in?

--
Duane Hookom
MS Access MVP

.

Dear Al:

Good. That's new information, and a possible starting point. But,
within each order, does the ID put the rows of order detail in the
order you want to see, and number them? I'll assume it does.

So, all we need to do is generate the line numbering.

SELECT OrderNumber, (SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.OrderNumber = T.OrderNumber AND T1.ID T.ID)
AS LineNumber, Product
FROM YourTable T
ORDER BY OrderNumber, ID

In the above, substitute the actual name of YourTable. Leave the rest
exactly as it is. You should see the line numbers for which you
asked.

As advertised, this is a "correlated subquery" which produces the line
numbers. For each OrderNumber, it counts the number of rows with a
smaller ID value, that being the number of rows that precede the
current row. But that would start the numbering with 0, so I added 1.

Is this the kind of thing you wanted?

When I create a system, I actually use a LineNumber in the table.
This allows me to insert a line between existing lines, or to reorder
the lines of an order, moving a selected line up or down. Using an ID
(assuming it is an autonumber or identity column) is doesn't allow you
to change the order of the lines in an order, or insert between them,
since you wouldn't be able to change the ID values. These line
numbers would not necessarily be consecutive, however, since someone
may delete a line in an order. On the other hand, the line numbers
assigned as shown above will be "transient" in nature. If an order
has 3 lines, they would number 1, 2, 3. If someone deletes row 2,
then the next time you run this query, the row that was 3 before is
now 2. So, you cannot use these numbers as a long-term reference
system. That's what I mean by being "transient" in nature. Be sure
to plan for this in the design of your database, and how you train
users to use these line numbers. They cannot be used as a permanent
reference to the order.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 13 Jul 2004 14:36:07 -0700, "Al V"
wrote:

I am a little confused on what you are asking for. Each
row does have a unique number.. example

ID Order Number Product

13050 100
-----Original Message-----
Yes, the term "bag" is technically the exact term here.
The rows are
NOT in any specific order that can be detected, or which
will even be
repeatable (although it may seem to be repeatable, it
isn't
guaranteed, and cannot be accessed in a query.)
A "Ranking Correlated
Subquery" must have something on which to base the way it
numbers the
rows within each order, and this must be in the data.
Otherwise, it
cannot be done.

Some natural value, or an autonumber or identity column
will work,
although that may determine a different order than the
one you're
accustomed to seeing.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 13 Jul 2004 15:48:33 -0500, "Duane Hookom"
wrote:

I think Tom's question might be: If you wrote down each
Line Item on a
marble and threw them all in a bag (one bag for each
Order), when you pulled
them back out, would you have something written on the
marble that suggested
the order you wanted to put them in?

--
Duane Hookom
MS Access MVP

.