Order of entries in an Access Database.

[Doug B]

I have Access Databases of up to 61,000 entries, and rising. Occasionally,
and for no reason, the order in which I enter data gets a bit jumbled. I
really don't want this, as I often use info in fields such as "see above", or
"son of the below". Can the order be maintained (unless glitches occur), or
is it a grave error to use the references I have descirbed? Advice most
gratefully received!

 

[Guest]

If you want the records to be sorted, you should have a sort order field.
You might use a date/time field, or an autonumber field for the sort order,
or you might use order number or something else.

Records go into the database in the order you enter them. They
are re-sorted to primary-key order when you compact the
database (there is a registry entry which controls that behaviour).

Records display in native order unless you have sorted the
query, or view, or table, or unless a query has re-sorted
the table while doing a join or grouping or something
similar.

(david)

 

[Allen Browne]

Doug, the order of the records in a relational database is undefined.

That means if you want the records in a particular order, you must find some
way of ordering them. An AutoNumber field typically does that, or perhaps a
Date/Time field with the Default Value set to =Now(). You can then specify
an order in a query.

(Of course, a user can also re-order the records by choosing another field,
and clicking the A-Z button on the toolbar.)

In practice, Access presents the records in primary key order unless you
specify another order. A table with no primary key might look like it is
keeping the records in order, but that is not guaranteed. For example, you
might find them in a different order after you compact the database. (Tools
| Database Utilities | Compact.)

 

[Doug B]

Thanks, Allen and David. I believe I have a Primary Key - I have an ID
column with Autonumbering, and I never change this by adding or removing or
merging entries. From what you say then, the order of entry should remain
intact (apart from when I do a sort etc.). Am I right in assuming this?
Thanks again!

 

[david epsom dot com dot au]

merging entries. From what you say then, the order of entry should

intact (apart from when I do a sort etc.). Am I right in assuming

When you view the table in datasheet view.

When you use queries, it becomes progressively less likely
as the query becomes progressively more complex.

Thanks, Allen and David. I believe I have a Primary Key - I have an

Open the table (or link) in design view, and check that
the autonumber field has a key symbol next to it, and is
the only field with that symbol.

column with Autonumbering, and I never change
this by ... merging entries.

Yes, merging entries is especially flaky.

(david)

 

[Doug B]

Thanks, Allen. I believe I have a Primary Key - I have an ID
column with Autonumbering, and I never change this by adding or removing or
merging entries. From what you say then, the order of entry should remain
intact (apart from when I do a sort etc.). Am I right in assuming this?
Thanks again!

 

[Allen Browne]

If the numbers are in order, you're home.

AutoNumbers are generally good for this. There were some glitches in an
earlier version of JET, but MS released a service pack, so just make sure
you have SP8 (or even SP7) for JET 4. You can tell by locating msjet40.dll
(typically in windows\system32), right-clicking, and choose Properties. On
the version tab, the minor number indicates the SP, e.g. the "8" in
4.0.8111.0

 

[Doug B]

Allen - I have version 4.0.7328.0. Is this OK? Thanks for your help.

 

[Brendan Reynolds]

In your original post, Doug, you wrote ...

<quote>
I often use info in fields such as "see above", or
"son of the below". Can the order be maintained (unless glitches occur), or
is it a grave error to use the references I have descirbed?
</quote>

In my opinion, yes, it is a grave error for the content of a record to be
dependant on the position of the record within the result set.

 

[Allen Browne]

Yep. 7 had the fix for that problem.

I would suggest you get 8 to solve other issues though.
Download from:
http://support.microsoft.com/gp/sp