Record numbering

[Jon Rowlan]

repost as last posting dissapeared:

I wish to number records, in natural order from 1 to n

There is no numerical or date comparison or sort that will allow be to rank
using the techniques listed (that rely on there being count() records in my
query that are logically lower than the current record.

Is there any way to apply a simple record number (like the old
clipper/dbase/foxpro recno()) without relying on a comparison.

jON

 

[Chris2]

repost as last posting dissapeared:

I wish to number records, in natural order from 1 to n

There is no numerical or date comparison or sort that will allow be to rank
using the techniques listed (that rely on there being count() records in my
query that are logically lower than the current record.

Is there any way to apply a simple record number (like the old
clipper/dbase/foxpro recno()) without relying on a comparison.

jON

Jon Rowlan,

Use an autonumber column.

1) Empty your table, add an autonumber column, and reload it with a
correctly ordered append (INSERT) query.

(Back up your date before attempting it.)


Sincerely,

Chris O.

 

[Jon Rowlan]

That would be fine if I could assume that the next number used was
definately sequential.

Unfortunately, if you delete a record and add another you will get gaps.

I am surprised that there isn't a recordnumber() function of some kind ...

jON

 

[Chris2]

That would be fine if I could assume that the next number used was
definately sequential.

Unfortunately, if you delete a record and add another you will get gaps.

I am surprised that there isn't a recordnumber() function of some kind ...

jON

Jon Rowlan,

After the original load, you're correct, the sequential number would
no longer be guaranteed.

Other options:

1) Let's see your table structure and some sample data . . .

2) Create an artificial number table containing ascending numbers 1
to n, where n = the highest number you would expect to retrieve.

3) Programmatically: You can attempt some programmatic scheme to
always INSERT with the next highest value.


Sincerely,

Chris O.

 

[Amos Bwambale]

Hi, Jon I do not know if your issue has been fully resolved. I have no
known if the SIMPLE numbering is based on reports or forms. Howeve
here is a very simple approach to record numbering on reports.

Open a report in desing view and add an unbound text box.
Set its Control Source to: =1
Running Sum: =Over Sum

go back to preview and your records will be numbered numericall
(sequentially)

Amos Bwambal

 

[Jon Rowlan]

Thanks Amos, I have had that recommendation and it worked for me.

But what I really as lookign for was a way to number records that are in
natural order ..

So far I have not seen any solution that quite does the trick so I am using
the running sum as you describe.

cheers.

jON

 

[Tom Ellison]

Dear Jon:

I'm unsure what you mean "natural order." There is no such term with which
I'm familiar. The rows of a table are not stored in any particular order.
An order must be imposed by some kind of sorting. If the sorting is not
unique, then it does not completely order the rows.

You mention the problem with using an autonumber, that it would leave "gaps"
if rows were deleted. In the actual storage, these gaps may become filled
with new rows subsequently added. The order in which they are stored is up
to the mechanisms used by the database software to perform this, and can be
different from one database to another. They are not predictable or useful.

If an autonumber gives you the kind of sequence you want, except for the
gaps, then use an autonumber and then Rank the rows accordingly:

SELECT *,
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.AutonumberCol < T.AutonumberCol)
AS Rank
FROM YourTable T
ORDER BY AutonumberCol

This will always give you a sequential numbering of the rows. However,
these Rank values will shift every time a row is deleted, so there are no
gaps, as you requested. It cannot be used as a permanent reference number,
as it may be constantly changing. You cannot have both.

If the "natural order" is the order in which the rows were added, put a
date/time stamp column in the table to contain this. You could than rank on
this date/time value, which is accurate enought that it may be unique.
Still, it is best to use a tie-breaker when ranking. Remember, if you have
multiple users, the order in which the rows appear will not respect which
user put them in. If several users are posting new rows to this table, they
will be intermingled according to how fast each user posts. In my opinion,
the "natural order" idea is rather deceptive, and likely to be
disappointing.

The bottom line is than any ordering of rows will be based on one or more
columns in the table. Whether this ordering is unique or not depends on
whether the columns used in the ordering form a unique value or not.

Is this at all helpful? How can I assist you further?

Tom Ellison

 

[Jon Rowlan]

Thanks Tom, that is the answer that I needed for my particular problem.

"Natural Order" is indeed the order that they are put on to the system but
the reason for mentioning this was that I don't have any reference field by
which to compare the current record to determine the current record rank.

As you say by adding an autonumber field _and_ using a ranking comparison I
can achieve precisely what I need.

Which in essence is a query where the first 250 records have a 10% levy
against them and the remainder have 5%, regardless of filter and sort
criteria that may be applied.

Thanks for your help.

jON