Re: line numbers
From: Tom Ellison (tellison_at_jcdoyle.com)
Date: 07/14/04
- Next message: Paul James: "Re: displaying all the duplicate records in a duplicate query"
- Previous message: John Vinson: "Re: Convert Seconds from 1980 to current date/time"
- In reply to: Al V: "Re: line numbers"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 14 Jul 2004 01:33:14 -0500
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"
<anonymous@discussions.microsoft.com> 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"
>><duanehookom@NoSpamHotmail.com> 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
>>
>>.
>>
- Next message: Paul James: "Re: displaying all the duplicate records in a duplicate query"
- Previous message: John Vinson: "Re: Convert Seconds from 1980 to current date/time"
- In reply to: Al V: "Re: line numbers"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
