ilcaa - you are quite right. You have observed correctly. The last value in a table probably isn't what you think it is. This is a difficult point for some people to grasp. I'll try to give you a clean explanation.
Let's let this cat out of the bag right now: You NEVER KNOW the order in which entries appear in a table. This is because Access was implemented using a lot of set theory concepts.
In set theory, changes to the set are defined to be performed as though the operation was monolithic. That is, you are not supposed to know or to be able to know the order in which anything was done. OK, you and I know that at least for a single-threaded system, records will almost certainly be changed one at a time - but Access and JET or ACE won't tell us the order. That is because according to set theory, order is immaterial to the definition of the set. They do their thing in the order that the records are presented REGARDLESS of that order.
That means that when you run an update query, the order in which updates occur within the table are also not predictable. That is why when people try to do something based on the "previous record" they fall flat on their faces. Remember that "last" has multiple meanings. You could mean "the last entry if the table is presented in order of the prime key" - but you could also mean "last entry chronologically."
It gets worse if you have to do a Compact & Repair because you NEVER know the order in which each record in each table is copied to the new database that will replace the old one. So record order in the table could change after a C&R. (And probably does.)
There is also the issue that if you ever do either a BEGIN/COMMIT sequence or use a DAO-database.Execute with the dbFailOnError option, there is an implied rollback ability - which means in either case that for a brief moment, the new records and the old records coexist. The "COMMIT" phase involves removing the old records and threading in the new ones to replace them. But in which order does the updated records appear? It would depend on whether you updated all, some, a few, or just one record as to how the record order would change.
This is why queries exist and why, if you are worried about record order, you base reports & forms off of queries that contain an ORDER BY clause. Directly opening a table is usually the wrong answer regardless of the question - with the exception of certain SQL queries. For the most part, anything you could do with a table you can do better with a query to that table. Particularly if there is any order or selectivity or reformatting required.
Therefore the solution to your problem will be to use a QUERY that contains an ORDER BY clause referencing the thing you want to appear in a particular order.
Let's let this cat out of the bag right now: You NEVER KNOW the order in which entries appear in a table. This is because Access was implemented using a lot of set theory concepts.
thanks for the explanation. Being new to db design i had some more clarifying questions, then I want to tell you about what I was trying to accomplish which led me to test and then post here.
so what I see visually when i open table is not what Access will use when retrieving data using the various .MoveLast, .Move, etc methods?
Even If i have an AutoNumber column that increments, the underlying data is still not ordered that way even though visually it is when I open a table? If so, it seems using .MoveFirst & .MoveLast are pointless, unless you dont care about order..
application: in a VBA module I generate a random # between 1-192. the Rnd # represents 1 of 192 questions I have in another table. I take that random number and check it against this Table I posted here to see if the Rnd # is one of the last 30 random numbers list in Column, "QuestionID" (a primary key and Indexed in other tables). If it is listed in the previous 30 values, it returns a "True" and a new random number is generated and checked. (i dont want the same question returned if it was already presented in last 30 questions). So i create a RecordSet, .Move to Last (thinking I am at the last question that was asked) then use .Previous and Loop 30 .Previous from the "last" and check the "last" 30 values against the new one generated, thats the process.
( BTW...This tables updates by an Append query (i have another temporary table that stores the Question ID, the Answer student gave for a particular quiz) once finished, i append the temp table to this table. Each new append adds between 5-15 questions (rows))
Knowing how DB work and what I want to accomplish can you recommend a better way to get the "real" last 30 values so i can test against this new value?
Actually, Access uses EXACTLY the table as-is, with the records in the order as they appear. You just can't predict that order. When you open a TABLE directly to see its contents, that is actually an "implied" query ordered by the PK; one of those sneaky little things done by Access as a kindness so that you don't see the crazy order of implementation. If you want to see the real order of the table, open a query to that table that doesn't contain an order-by clause. Even better, don't include the PK field in the query. That will be closest to the real order of table presentation.
Even If i have an AutoNumber column that increments, the underlying data is still not ordered that way even though visually it is when I open a table? If so, it seems using .MoveFirst & .MoveLast are pointless, unless you dont care about order..
Not pointless at all. Because .MoveFirst and .MoveLast are recordset concepts, that means that if you open a QUERY with an ORDER BY, you know EXACTLY the order in which things will appear. Here's the sneaky part. Suppose that your autonumbered recordset is not ordered by the autonumber field, but instead is presented in order of some text field. Records can be randomly inserted (or more precisely, not predictably ordered when inserted.) So you could insert records for Smith, Jones, and MacNulty in that order. Their autonumbers, if set to incremental rather than random, would be in proper order but the names would not. So if your query sorts by names, guess what? The autonumbers will appear out of order but the names will be alphabetically sorted.
Your confusion is that you probably thought .MoveLast or .MoveFirst followed PK order, but in fact they do far less than that. They always work them same exact way regardless of the recordset source. If you make the recordset's order of presentation follow some specific field, you get the first, last, previous, or next record in the order of the presentation, which depends solely on WHAT you opened - a table or a query with an ORDER BY clause.
can you recommend a better way to get the "real" last 30 values so i can test against this new value?
You ALWAYS can get the last 30 values of the PK when using queries - but not when directly opening the tables. As to HOW to get those last 30 values?
Code:
SELECT TOP 30 field-name-1, field-name-2, etc. FROM question-table ORDER BY PK-field-name DESC ;
You actually don't have a "BOTTOM 30" qualifier in SQL (or if it is there, I don't recall ever seeing it) - but you can just reverse the order of presentation with the DESC (descending order) qualifier and, since all is relative, the bottom becomes the top. Then take the TOP 30 - and there IS a "TOP number" qualifier.
