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.

 

[Pat Hartman]

If I have been given a DLast method, I expect it to return the last record. Because it's what Last means

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.

If you have a PK (which you should) then it returns a value from the First/Last record sorted by the PK in ascending order.

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.

 

[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.

 

[Pat Hartman]

You have only 537 records in the table. That is hardly enough to even fill ONE sector, let alone more. You need a far larger table to see what I am talking about. You can prove to yourself that my description is essentially correct. I made a sample at one time but I'm not sure where I put it so if I find it, I'll post it since it was big enough to show the way the db engine moves records around when it needs to.

1. Add at least 20,000 records. You might need more since the record size is pretty small and I don't know what the sector size is for your drive. You have to have a large enough table so that it occupies at least part of a second sector. You are also going to add at least one short text field. Long text fields are stored in separate tables so will not work for this test and none of the numeric fields can grow. They are all fixed size.
2. Make sure that the table does NOT have an automatic sort specified since that will prevent you from seeing any change.
3. Compact the database so that the table is in PK sequence.
4. Modify the third record you see. Make sure you make it larger than it was. Write down it's PK
5. Close the table
6. Close the database to make sure you are not seeing the effects of the cache.
7. Open the db and the table. The third record should be missing.
8. Scroll to the bottom of the table and it should be there. If it isn't, use the binoculars to find it.
9. Or, if you sort the table, it should return to its original place.

Anyone using SQL Server can see the effects quite easily but you still nee a large enough table and you need to modify the record so that it will no longer fit where it was. SQL Server uses more embedded free space than Jet/ACE do so you might need to modify records 5-8 before you force SQL Server to relocate the lengthened records. SQL Server also uses different methods to retrieve records in a select query so, as long as you don't have an order by clause, you should see that records are not where you expect them to be.

Remember, it isn't modifying a record that causes it to move, it is making it longer so that it no longer fits in its original spot.

If I defrag my disk, then DLast should return a different result. But it doesn't. It will always show PK 254.

dfragging the disk doesn't defrag the file contents. Compact & Repair is what you use to defrag the table in Access.

Here is the results with your existing data.

I made an append query and selected all the rows and appended them again, minus the PK of course. And this is the result. Now the dLast() for MachineFK gets the value from the last record because I forced a new record to be last. I'm not sure what you were doing.