By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,481 Members | 979 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,481 IT Pros & Developers. It's quick & easy.

Probably a really stupid question regarding indexing

P: n/a
I have a database file that I use an autonumber field as the primary key
index. Because of some rearrangements in the past, this index does not match
the order that I would like it to be in, that is date order. What I would
like to do is to eliminated the primary key field (I know how to do this),
sort on the date field, and then reindex with a new autonumber field. Every
time I try, however, the default sort field left over from the original
index is what ends up as my index, so I am right back where I started. What
am I missing?

Thank you for any help.
Brian
Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Brian, tables are really just buckets to hold the data. They are not
intended to be the interface as well.

Create a query into this table.
Sort by the date field.
Use the query instead of the table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"noone" <no****@thanks.com> wrote in message
news:aO********************@comcast.com...
I have a database file that I use an autonumber field as the primary key
index. Because of some rearrangements in the past, this index does not match the order that I would like it to be in, that is date order. What I would
like to do is to eliminated the primary key field (I know how to do this),
sort on the date field, and then reindex with a new autonumber field. Every time I try, however, the default sort field left over from the original
index is what ends up as my index, so I am right back where I started. What am I missing?

Thank you for any help.
Brian

Nov 13 '05 #2

P: n/a
On Wed, 26 May 2004 11:47:38 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:
I have a database file that I use an autonumber field as the primary key
index. Because of some rearrangements in the past, this index does not
match the order that I would like it to be in, that is date order.
What I would like to do is to eliminated the primary key field (I
know how to do this), sort on the date field, and then reindex with
a new autonumber field.

Brian, tables are really just buckets to hold the data. They are not
intended to be the interface as well.

Create a query into this table.
Sort by the date field.
Use the query instead of the table.


In addition to this comment, it appears from your brief description
that you're using two fields to mean pretty much the same thing. The
autonumber should be there so you can have a field that makes each
record unique. If your date field already establishes the uniqueness
of a record, you don't need the autonumber field at all, your date
field should be your primary key for the table. That you can remove
the autonumber field without any errors about non-unique records is
something of a hint that it's a duplication.

All that aside, if you're very attached to the idea of what you want,
then you may be able to accomplish it in this way: Remove the
autonumber field, sort your records, then compact and repair your Db,
then add the autonumber field again.

I don't know for certain that will give you the result you want, but
it might.

--
Siobhan Perricone
Systems Developer
Vermont Agency of Natural Resources
(my comments are my own, not my employer's)
Nov 13 '05 #3

P: n/a
"Siobhan Perricone" <si***************@nostatespam.vt.us> wrote in message
news:fa********************************@4ax.com...
Brian, tables are really just buckets to hold the data. They are not
intended to be the interface as well.

Create a query into this table.
Sort by the date field.
Use the query instead of the table.


In addition to this comment, it appears from your brief description
that you're using two fields to mean pretty much the same thing. The
autonumber should be there so you can have a field that makes each
record unique. If your date field already establishes the uniqueness
of a record, you don't need the autonumber field at all, your date
field should be your primary key for the table. That you can remove
the autonumber field without any errors about non-unique records is
something of a hint that it's a duplication.

All that aside, if you're very attached to the idea of what you want,
then you may be able to accomplish it in this way: Remove the
autonumber field, sort your records, then compact and repair your Db,
then add the autonumber field again.

I don't know for certain that will give you the result you want, but
it might.

It won't. Although tables are logically unsorted, the records must be in
some physical order on disk. Other dbms's do allow you to specify which
index if any will define this physical ordering, (called a clustered index).
Access does not and tends to cluster records on the primay key.




Nov 13 '05 #4

P: n/a
Rob
If you REALLY want have the autonumber reflect the order of input,
copy the table (using right-click and copy only the structure).
Then write an append query that appends all fields from the first
(EXCEPT the autonumber field) into the second empty table ordered by
the Date/time of entry.
THe new table will have the order numbers in the order you want with
no holes.

The problem here is that if you have relationships off of this table
via the primary key, you have to do a much more involved effort that I
don't particularly want to deal with here.

Also, I too, like to have my autonumbers (essentially) allow me to
sort by "order of input" but as Allen said, just always reference the
table via a query and use the date to sort by (the autonumber field
will be all jumbled, but the order of the records will be correct.
Good luck.
Nov 13 '05 #5

P: n/a
A couple of times I've had incidents of corruption in which the
autonumber field skipped numbers or tried to insert the same number
twice. To fix this I exported the table to Excel, deleted the
autonumber column, then reinserted it with the automatic numbering in
excel, and then imported it back into the table - or made it a new
table of the same name, I can't remember which. That worked out fine.

But I agree with Allen, for frequent or consistent sorting it's better
to use a query.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40***********************@freenews.iinet.net .au>...
Brian, tables are really just buckets to hold the data. They are not
intended to be the interface as well.

Create a query into this table.
Sort by the date field.
Use the query instead of the table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"noone" <no****@thanks.com> wrote in message
news:aO********************@comcast.com...
I have a database file that I use an autonumber field as the primary key
index. Because of some rearrangements in the past, this index does not

match
the order that I would like it to be in, that is date order. What I would
like to do is to eliminated the primary key field (I know how to do this),
sort on the date field, and then reindex with a new autonumber field.

Every
time I try, however, the default sort field left over from the original
index is what ends up as my index, so I am right back where I started.

What
am I missing?

Thank you for any help.
Brian

Nov 13 '05 #6

P: n/a
"Julia Baresch" <jb******@oldrepublic.com> wrote in message
news:50*************************@posting.google.co m...
A couple of times I've had incidents of corruption in which the
autonumber field skipped numbers or tried to insert the same number
twice. To fix this I exported the table to Excel, deleted the
autonumber column, then reinserted it with the automatic numbering in
excel, and then imported it back into the table - or made it a new
table of the same name, I can't remember which. That worked out fine.
Firstly, you shouldn't care if there are gaps or not - if you do you're
probably using autonumber fo teh wrong reason. Gaps in an autonumber column
do not indicate corruption - they may be simply the result of deleted
records. Secondly, you should always define a unique constraint, e.g. by
making the autonumber the primay key, so that you can catch these
occurrences. (I haven't seent his problem but I hardly ever use
autonumbers).

But I agree with Allen, for frequent or consistent sorting it's better
to use a query.


It's not better to use a query - it's the only way to do it. Records in a
table are unsorted by definition. The fact that they may be returned in a
particular order is quite arbitrary and you shoud never rely on it.
Nov 13 '05 #7

P: n/a
jb******@oldrepublic.com (Julia Baresch) wrote:
A couple of times I've had incidents of corruption in which the
autonumber field skipped numbers or tried to insert the same number
twice.


This is due to a buggy version of Jet 4.0. Ensure you're at SP6, SP8 or the MS04-014

Jet 4.0 SP 6 - 4.0.6218.0
Jet 4.0 SP 8 - 4.0.8015.0
Microsoft Security Bulletin MS04-014 - 4.0.8618.0

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 13 '05 #8

P: n/a
> some physical order on disk. Other dbms's do allow you to specify which
index if any will define this physical ordering, (called a clustered index). Access does not and tends to cluster records on the primary key.
The physical order of a Jet 2.0 database was entry sequence order.
This was changed with Jet 3.0: Unless you make a registry entry
to enforce the old behaviour, records are ADDED in entry sequence
order, but COMPACTING sorts them to primary key order.

Other dbms's allow you to specify the ratio of empty space in the
database when the database is 'compacted', so that records are added
in (roughly) the correct physical position, unless and until the
space runs out and records are just added at the end. 'Compacting'
sorts everything to it's correct position. SOME dbms's do ONLINE
sorting and compacting as a background process. I don't think MS
SQL Server does that? Access always compacts MOST of the empty space
out of the file, so most new records are just added at the end, and
only sort to primary key order when you do a 'compact'.

As you note, it is NOT possible to specify for Access that the
'clustering' index is anything other than the Primary Key index.
FWIW, simple unsorted queries in Access normally return the records
in physical sequence order. This is just predictable enough to be
dangerous: You might think that you have logically sorted data when
actually all you have is physical sequence order.
Also, rarely, some kinds of complex queries may change the sort order.
This was true even with Access 2.0/Jet 2.0. This is still true with
Jet 4.0. Using Jet 2.0, the physical sequence order was the entry
sequence order. Using Jet 3+, the physical sequence order is normally
a mixture of Primary Key order and entry sequence order, so the
default order of unsorted queries is predicably unpredictable if the
entry sequence order is different from the Primary Key order.

(david)
"John Winterbottom" <as******@hotmail.com> wrote in message
news:2h************@uni-berlin.de... "Siobhan Perricone" <si***************@nostatespam.vt.us> wrote in message
news:fa********************************@4ax.com...
Brian, tables are really just buckets to hold the data. They are not
intended to be the interface as well.

Create a query into this table.
Sort by the date field.
Use the query instead of the table.
In addition to this comment, it appears from your brief description
that you're using two fields to mean pretty much the same thing. The
autonumber should be there so you can have a field that makes each
record unique. If your date field already establishes the uniqueness
of a record, you don't need the autonumber field at all, your date
field should be your primary key for the table. That you can remove
the autonumber field without any errors about non-unique records is
something of a hint that it's a duplication.

All that aside, if you're very attached to the idea of what you want,
then you may be able to accomplish it in this way: Remove the
autonumber field, sort your records, then compact and repair your Db,
then add the autonumber field again.

I don't know for certain that will give you the result you want, but
it might.

It won't. Although tables are logically unsorted, the records must be in
some physical order on disk. Other dbms's do allow you to specify which
index if any will define this physical ordering, (called a clustered

index). Access does not and tends to cluster records on the primay key.




Nov 13 '05 #9

P: n/a
"John Winterbottom" <as******@hotmail.com> wrote in message news:<2h************@uni-berlin.de>...
"Julia Baresch" <jb******@oldrepublic.com> wrote in message
news:50*************************@posting.google.co m...
A couple of times I've had incidents of corruption in which the
autonumber field skipped numbers or tried to insert the same number
twice. To fix this I exported the table to Excel, deleted the
autonumber column, then reinserted it with the automatic numbering in
excel, and then imported it back into the table - or made it a new
table of the same name, I can't remember which. That worked out fine.


Firstly, you shouldn't care if there are gaps or not - if you do you're
probably using autonumber fo teh wrong reason. Gaps in an autonumber column
do not indicate corruption - they may be simply the result of deleted
records. Secondly, you should always define a unique constraint, e.g. by
making the autonumber the primay key, so that you can catch these
occurrences. (I haven't seent his problem but I hardly ever use
autonumbers).


Well, the thing was that when it had skipped, it later tried to insert
a number that was already there - It was a while back but I think at
one point it had skipped and then tried to begin again with lower
numbers. When it tried to insert a number that was already there, it
generated an error message and wouldn't take data entry. So I had to
fix it. The autonumber was the primary key also.

Tony thanks for your input on the Jet version. We got new service
packs a couple of weeks ago. It's nice to know what caused that. :-)
But I agree with Allen, for frequent or consistent sorting it's better
to use a query.


It's not better to use a query - it's the only way to do it. Records in a
table are unsorted by definition. The fact that they may be returned in a
particular order is quite arbitrary and you shoud never rely on it.

Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.