The order data is saved in a table.....

[oleronesoftwares]

Lets start with your question on "And then, how DLast differs from DFirst? (if both return random records)."

Dfirst retrieves the first occurrence of any data type such as a string, numeric, date, etc in a table

Dlast retrieves the last occurrence of any datatype such as a string, numeric, date, etc in a table.


For tables, there is an actual order for storage , and this need arose because of the following reasons

1. A database can have multiple tables, and some of the tables can have relational data, so there must be a way to link the tables

2. In the event, of two or more records in a table having same details, e.g in a same first name, surname, and gender, if the table does not have a unique field, then a primary key can be used to order and distinguish records.

 

[Minty]

For tables, there is an actual order for storage , and this need arose because of the following reasons

This statement is not correct.
You can only create an order by using a sort on a field(s) that determines the order.
You cannot absolutely rely on a PK_autonumber, what if it is set to random generation? In Access the autonumber order isn't 100% guaranteed to be incremental, just unique.
You cannot absolutely rely on a datetime field, what if multiple records are added at the same time?

Normally a combination of "Date Time Added" and a normal primary key autonumber will give you a sensible order. But don't absolutely rely on it if you don't know for certain.

 

[Sun_Force]

In the pics, you have some sorting on the first 2 fields. Use dlast without sort.

@arnelgp
Sorry for not being able to reach you sooner. This is the unsorted pic I promised;

this is the table design:

I attached the table if you want to review it.

thanks for your concern.

 

[oleronesoftwares]

Please take not, order of storage, does not mean sorting.

 

[Minty]

Please take not, order of storage, does not mean sorting.

If you don't have a primary key or unique index on a table and you remove and add some rows, then do a compact and repair on a database that table will be re-sorted in an arbitrary fashion. It will have nothing to do with the order the records were added.

If you add an index of some description, that will then drive the order they are presented in the table view.
It will have no bearing on the order they were entered, that isn't stored, and is gone.

 

[Sun_Force]

I find that when my users say they want the "last" record, they mean the one they entered most recently. Last, more precicesly, refers to physical order so I would expect the Last() function to return the last physical record.

This is how Access fools us. As part of the Compact operation, Access sorts every table by PK and lays it back in PK order. This makes everyone think that tables are always in PK order and that is not true at all unless you don't update them. Once you update a table all bets are off. You have to consider the physical aspects of a storage medium.

Keep in mind that this is very simplified and don't beat me up if you don't like my explanation. I know that there are differences in how each engine manages inserts and updates.
Start with an empty disk. The file is written "physical" record by "physical" record. Physical records are a collection of logical records. A logical record is what we define to be a record in a table or a file but the process of I/O (Input/Output) doesn't ever work logical record by logical record. It works a sector (in the PC world) at a time. The sector size is dictated by the disk manufacturer and the overall capacity of the disk and a sector is a physical record. A read operation returns a full sector. A write operation writes a full sector. Within a sector, records are in whatever order the database engine writes them in and that is where RANDOM comes in to play. So here are a few records. I'm going to alternate numbers and letters to distinguish between records:

aabbbbbbccccccdeeeffffffff111222344445556aaaabccddddddef123333334456

If I update the second record (the first numeric string) and I do not increase the length, the database engine can put it back where it was:

aabbbbbbccccccdeeeffffffff1112223445556**aaaabccddddddef123333334456 -- there are now two placeholder characters at the end of the second record. Now lets update the first record but make it longer. The engine cannot put it back where it was, there is no room. unless everything else gets shifted right and that can't happen because there are thousands of records in the file and all would have to be shifted so the record gets moved to the end of the file where there is free space.

pntr88********************1112223445556**aaaabccddddddef123333334456aaaabbbbbbccccccdeeeffffffff

In reality, the actual database is built with free space in each sector and at the end. So when a new sector is written, it is never filled. It only gets filled during the process of updating. New records are ALWAYS added to the end of the physical file when your PK is an autonumber.

When records get moved, the database engine inserts pointers to the new location so they can easily be found. The Compact process puts the records back into PK order and eliminates all the pointers and ensures free space in each sector.

So, back to the RANDOM. The value you are getting will be from the physically last sector, not necessarily the last record added AND since the logical records in a sector might not be in a logical order, the last record of the sector may or may not change after each add/update operation.

@Pat Hartman
with all respects, I don't think that's the way things go on in Access. I'm aware of how a hard disk works and how data is written to sectors. I actually had thought about it before posting this thread, but some experiments proved the length of sectors had nothing to do with it.
If I defrag my disk, then DLast should return a different result. But it doesn't. It will always show PK 254.
OR
If I copy the file and move it to a different PC, again DLast would bring in another result. because tables are written in different sectors with different sizes. But it doesn't. No matter how many times I copy a database to a different PC, DLast always shows PK 254. Until I add 10 or more new records. Then DLast shows another result, not the last newly saved record.

Someone above explained the last edited record is what DLast returns.
But unfortunately it's not true too. because no matter how many time I edit different records, DLast still is showing PK 254.

You can experiment with the table I posted in #26.

Correct me if I'm wrong please.