Table Record Sequence: Physical vs Logical?

[(PeteCresswell)]

Just had a situation where a report came up with the wrong amount
on it.

Turns out I was basing it on some processing that was going
against a work table that I created on-the-fly.

I copied records into that table in ascending order by date and
browsing the table showed them in the expected sequence.

But when I opened a .RecordSet directly into the table, the first
record that came up was not the first one seen when browsing the
table.

A little light went on then - something about JET not
guaranteeing the sequence of records retrieved being the same as
the physical sequence.

Threw a query in there to explicitly sort the RS by desired date
and all was well.

Have I got it right about JET not making any promises
sequence-wise?

If so, what're the chances of this just happening yesterday and
not happening the other 768 times the report's been run? (that's
not such a critical question, thought, bc the processing depends
on a few things, like what the prior date was...and some entities
might have only one date....)

 

[Jeff Boyce]

Pete

As I understand it, an Access table is simply a "bucket o' data", with no
guarantee of sequencing.

To get a guaranteed sequence, you'll need to use a query and sort on x, y,
or z.

If you want a REPORT sorted by x, y, and z, you'll need to set the
Sort/Group order in the report to those fields.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Paul Shapiro]

The SQL standard says that data retrieval order is "implentation-dependent"
if there's no Order By clause. That means each db vendor can return rows in
whatever order is most efficient for them. So there will be a pattern, but
it's likely to be different from one db vendor to another, and can change
between versions or with a service pack. If there's an AutoNumber field, for
example, Access usually returns unsorted rows in that order. If it's a query
with more than one table, the unsorted row order can change depending on the
Where clause. The db is optimizing the data retrieval. If there's no sort
order specified, that's one less constraint it has to satisfy during
processing.

We as db users don't know anything about the physical storage of rows.
That's one of the big advances of a DBMS over creating your own files. We
don't have to physically retrieve data, just the logical conditions. So we
might know what order we entered the rows, but that still doesn't tell us
how they're physically stored or how the db will choose to retrieve them
when we don't specify a sort order. In SQL Server, Oracle, DB/2 etc. you can
specify a clustered index to control how rows are physically stored, but not
in Access.

Jeff's advice is correct- if you care about row order then always specify
it.

 

[david]

Jet adds rows at the end of the table. When you compact,
it resorts the rows to primary key order. But then any new
rows are added at the end of the table again.

So it is common to see a sort anomaly only after you have
added data in the 'wrong' order - when the expected
historical data order doesn't match the primary key order.

Browsing the table doesn't tell you anything unless you specifically
check to see if the table view has a sort order.

Reports discard query sort order and apply the report sort order.

Unsorted queries may magically sort on the order of any of the tables
queried, although they usually magically sort on the order of table you
expect.

And none of this makes "LAST" work correctly in complex queries.

(david)