Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pyrrhus_finch
Yak Posting Veteran
51 Posts |
 Posted - 2004-11-10 : 16:48:26
|
| If I enter data directly into Access, keying it into rows -Save and Close then Open again, I find the order of records all messed up with no obvious sort. I would think they would list in the order that they were input (i am using no primary key)... I'm curious: what determines their odering?Thanks. |
|
|
Auric
Yak Posting Veteran
70 Posts |
Posted - 2004-11-10 : 17:32:50
|
| You really should have a primary key otherwise there is no way of defining unique records. Are they listing alphabetically? |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-10 : 17:37:49
|
| There is no way to guarantee order unless you use an ORDER BY clause in your query. Ordering data during data entry is meaningless anyway, and besides you can click the Sort option in MS Access when doing data entry. As Auric suggested, you need to set up a primary key on your table. |
|
|
|
pyrrhus_finch
Yak Posting Veteran
51 Posts |
Posted - 2004-11-10 : 18:14:22
|
| Sure.. I understand all that - I was just wondering what determined how they were appearing by default, didn't see a pattern. Thanks. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-11-11 : 08:33:39
|
| There is no pattern...(without a primary key on the table or an order by phrase in the select) the db engine saves the data 'acording to it's own internal rules'...which could extend to putting data in record length order, oldest record 1st, ordered by the height of the developers ex-girlfriends/boyfriends.....whatever...There is no set rule....without the key..or order by phrase.... |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-11-11 : 08:59:48
|
| though there is a property in Access that lets you specify the default ORDER BY when you open a table by double-clicking on it. look at the tables properties.it appears that the default order that you see is usually ordered by the primary key column(s), sorted ascending. I believe Access by default uses the primary key as the clustered index and physically stores the data that way; therefore, by default, when retreiving rows, it returns them in that order. but of course, you should NEVER rely on some perceieved default ordering and you should always explicitly state the order by which you expect to retreive rows in all of your SELECT statements and/or on your reports.- Jeff |
|
|
|
Auric
Yak Posting Veteran
70 Posts |
Posted - 2004-11-12 : 10:59:24
|
| pyrus it is good practise, and recomended to use a primary key in any table that you create. Access lets you specify a lot of different options for grouping and relationships. A lot of people forget to setup the relationship structure properly and will end up running into problems down the road. For example, an earlier DB I created had 3 tables, all storing similar data, in some cases such as the Names they were the same, and yet the 3 tables had no relationship, and no way of tracking related records. In the end it became a pain in the ass So i had to recreate the table structure. Josh |
|
|
|
|
|
|
|
|
What determines the order of the rows in Access