Access/sort order OR record order or not sure

[Terri]

Created Access 2002 DB: entered data (no problem) upto
record number 54 with data from hard copy. Db and hard
copy mirrored each other. Close db and record order was
the same (mirrored) as the hardcopy which, was reopened 1
hour later. (still no problem)

Inputted data till record number 60 | closed db |Db now
has a random order- different from data entry input
sheet. (can sort - but for data entry the hard copy and
screen shot must have the same (mirrored) look) Have not
changed any sort order | etc. This has happend before on
a cash drawer deposit, was only able to fix this by
recreating db. Is there a flaw or ?

 

[Van T. Dinh]

No, it is NOT a flaw. It is by design according to the
Relational Database Theory.

Records in a Table are stored *unordered* like things in a
bucket. The database enigine also retrieves Records which
ever way efficient, e.g. some Records are already in cache
and they will be retrieved first.

If you want to display the Records in any particular
order, you must specify the ordering of the Records.
Records still can be retrieved whichever way efficient but
they are presented to you in the order you specified.

HTH
Van T. Dinh
MVP (Access)

 

[Diane Jamail]

Terri,

Dan is right about records in a table being
stored "unordered", all relational database languages work
that way. I suggest that in the future when you create a
table to input data into you create a numeric field that
is of type autonumber. Then Access will automatically
assign an incrementing number to each record and you can
display the records sorted by the autonumber field.

Good luck, you have a lot to learn...

Diane

 

[Van T. Dinh]

Not sure what you meant by "db theory has been addressed"?
The Relational Database Theory was developed by Codd over
30 years ago and all RDBM software essntially stores
Records unordered as per the RD Theory for efficiency.

In Access/JET databases, Records are stored in individual
unit called "page". IIRC correctly, each page is 8 kB and
the number of Records per page depends on the size of your
Records. Pages are added as you add Records and by this
method the pages for a particular Table are most likely
not in a contiguous block of pages since pages are used
for other Tables, Indices ... In addition, JET keeps
stats of the page usages and page accesses internally to
optimise the retrieval processes. Thus, when an extra
page is required to store additional Records, JET may
decide to change the retrieval process and retrieve one
page before another rather than the previously-optimised
retrival process which can be exactly opposite to the new
optimised retrieval process. Also, I wrote previously
that Records / pages may still be in cache memory which
can effect the retrieval order also. I guess by this
storage and retrieval method, JET can process Records much
faster than the sequential access method we used to have.

In short, you simply CANNOT assume the retrieval order
since JET engine "dynamically" decides which way is the
best way for it to retrieve data. Thus, the retrieval is
more or less random (to us). What you normally see is NOT
the retrieval order since Access (I think) will default
the sorting to the PrimaryKey Field if you have one.
However, the surest method is to specify the sort order
for the retrieved Records if you want to display retrieved
data in a particular order.

Sorry, you cannot blame Access Help either because RD
Theory preceded Access by about 20 years and there is an
implicit assumption (quite reasonable, I may add) from
Microsoft / Access / RDBMS software that database
developers must know the Relational Database Theory /
Relational Database Design Principles before using the
software. In fact, some trainers (what a pity it is not
ALL trainers) recommend that database design should be
done AWAY from the computer screen.

Your college went through the db to look for what??? The
problem is NOT in the db. I hope it wasn't the lecturer
who is teaching the Relational Database Theory course in
your college!

HTH
Van T. Dinh
MVP (Access)