Record order shuffles spontaneously!

%3CLINGO-SUB%20id%3D%22lingo-sub-2177575%22%20slang%3D%22en-US%22%3ERecord%20order%20shuffles%20spontaneously!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2177575%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20using%20a%20membership%20database.%20The%20main%20contacts%20form%20is%20used%20to%20enter%20data.%20The%20query%20that%20populates%20this%20form%20is%20sorted%20on%20ID%20(prime%20key).%20Initially%20the%20forms%20appear%20in%20ID%20order%20and%20I've%20opened%20and%20closed%20the%20database%20several%20times%20then%2C%20spontaneously%2C%20the%20records%20will%20shuffle%20and%20appear%20in%20random%20order.%26nbsp%3B%20The%20query%20is%20consistently%20in%20ID%20order.%20I'm%20trying%20to%20clean%20the%20data%20so%20this%20is%20driving%20me%20mad!%20Can%20anyone%20advise%20how%20to%20fix%20this%3F%20The%20database%20is%20written%20in%20Access%202000%2F2%20and%20I%20am%20using%20MS365.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2177575%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

I'm using a membership database. The main contacts form is used to enter data. The query that populates this form is sorted on ID (prime key). Initially the forms appear in ID order and I've opened and closed the database several times then, spontaneously, the records will shuffle and appear in random order.  The query is consistently in ID order. I'm trying to clean the data so this is driving me mad! Can anyone advise how to fix this? The database is written in Access 2000/2 and I am using MS365.

3 Replies

@LizHATW2000 

Tables in relational database applications--like Access--have no inherent sort order. If you must have records presented in a specific order, then you must use an ORDER BY clause in a query to impose that sort order. You can not, in other words, count on your query to consistently sort on ID unless you add that ORDER BY clause to the query: ORDER BY ID

Thanks George. I sent this to my colleague who wrote the database. I don't know exactly what he did but he wrote me a new query which will do the job and won't go wrong. Thanks for your help.
Congratulations on solving the problem.
Continued success with the project.