re-order records in a table

[Berkonabike]

Within my table I have a field for dates where something has been received
and a field with an autonumber to give this field a unique reference. The
date range starts from Jan08 to the present day. The number field with the
order of the field with the autonumber does not match up with the order of
the date field, i.e, 12/12/2008 = number 25, 08/01/08 = number 365. What I
would like to do is to sort the two fields so that 01/01/08 = number 1 and
increment the number as the date gets closer to present day. Is this
possible for someone with basic Access skills?.

Thanks.

 

[John W. Vinson]

Within my table I have a field for dates where something has been received
and a field with an autonumber to give this field a unique reference. The
date range starts from Jan08 to the present day. The number field with the
order of the field with the autonumber does not match up with the order of
the date field, i.e, 12/12/2008 = number 25, 08/01/08 = number 365. What I
would like to do is to sort the two fields so that 01/01/08 = number 1 and
increment the number as the date gets closer to present day. Is this
possible for someone with basic Access skills?.

Thanks.

It is A BAD IDEA and it is unnecessary.

A table is an unordered "bag" of data. It *has no usable order*.

An Autonumber is a meaningless unique key. It is NOT guaranteed to be
sequential or gapless, and it should not be used as a count, or assigned any
other meaning. In fact it's generally best to conceal it from user view, since
it is in fact meaningless - each record has its own unique identifier, but the
only significance of an autonumber ID is that it is unique within the table.

If you want to see records in a particular order... use a Query, and sort by
the date field. Tables are designed to store data; if you want to arrange data
use a Query; if you want to display data onscreen use a Form (based on a
query); if you want to print data on paper, use a Report (with the sort order
defined in the report's Sorting and Grouping dialog).

 

[John Spencer (MVP)]

First it should not be done with an autonumber field. Autonumber fields exist
to generate a unique number that can be used to identify a specific record for
the computer. Usually, they should not be shown to the human as they tend to
lead to confusion to those not cognizant of their purpose.

If you can explain why you think you need to do this perhaps someone can help
you develop a solution.

Autonumbers cannot be relied upon to be consecutive and they will develop
gaps. Besides if you enter the dates out of sequence or edit an existing date
then you would have to rerun a procedure to get the sequence of dates and
autonumbers to agree.

Can you explain why you cannot just order by the date field?


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Ken Sheridan]

To sort and number the rows do so in a query, e.g.

SELECT
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE T2.YourDate <= T1.YourDate
AND ( T2.YourID <= T1.YourID
OR T2.YourDate <> T1.YourDate)) AS RowCounter,
YourDate, YourID
FROM YourTable AS T1
ORDER BY YourDate, YourID;

where YourID is the autonumber column. By bringing the autonumber column
into play in the subquery and as the second sort level this ensures that rows
with the same dates are numbered correctly in the query's result set.

If you insert a new row into the table with a date in the middle of the
sequence then the numbering will be adjusted accordingly of course, so this
number is purely for indicating the ordinal position of a row in the result
table, not as a unique reference, for which an autonumber column is fine as
for this, provided the values are unique, they are otherwise arbitrary with
no significance per se.

An even easier solution is to include an unbound text box in the detail
section of a report with a ControlSource property of =1 and a RunningSum
property of 'over all'.

Ken Sheridan
Stafford, England

 

[a a r o n _ k e m p f]

WRONG

so sorry that your piece of shit ass database (JET) doesn't support
clustered indexes.

Maybe you shoudl expand your horizons-- learn more about a real
database-- with a future-- like SQL Server

You _CAN_ physically order the rows in a table if you just used SQL
Server (and it's actually the fastest type of index that you could
possibly use)

It is A BAD IDEA and it is unnecessary.

A table is an unordered "bag" of data. It *has no usable order*.

An Autonumber is a meaningless unique key. It is NOT guaranteed to be
sequential or gapless, and it should not be used as a count, or assigned any
other meaning. In fact it's generally best to conceal it from user view, since
it is in fact meaningless - each record has its own unique identifier, but the
only significance of an autonumber ID is that it is unique within the table.

If you want to see records in a particular order... use a Query, and sortby
the date field. Tables are designed to store data; if you want to arrangedata
use a Query; if you want to display data onscreen use a Form (based on a
query); if you want to print data on paper, use a Report (with the sort order
defined in the report's Sorting and Grouping dialog).

*