Table not indexed correctly

[Dave14867]

Hello all, I have a very weird problem I have not seen before and I want some expert advice before I really goof things up. I have a table that all of a sudden the indexing is not right, what I mean is that the ID is not sequential like it should be. I have tried compact and repair and that has had no affect. I can get it correct by sorting on the ID column of the table but my fear is will all other data in the other columns follow that? When I do that and try to close the table it asks me if I want to save the new table design, so I choose "No". When I look at the data it does seem to follow the sort but I want to make sure.

I have use this database with no issues until this week, I started using it from home, connecting to the Work network thru a VPN.

Thanks in advance
Dave

[ranman256]

even with the index, you can sort the data anyway you want.
if your index is set to no dupes, and you enter a duplicate and it saves THEN you have a problem.
but you may be OK.

[CJ_London]

what I mean is that the ID is not sequential like it should be.

what is the dataype for your ID? autonumber? number? text?

[Dave14867]

The datatype is autonumber, it's the primary key. I made a backup copy of the table, it's a split database. I did try the sort on the primary key and the data did seem to follow like I had hoped, but now I had a new problem which led me to find the sorting issue. I have a form linked to that table that is set to go to the last record on load but it is still going to the last record of the unsorted table, very confusing.

[CJ_London]

autonumbers will get gaps if you start to create a record, then abandon it. Or if you delete a record. That is normal behaviour. A primary key should have no meaning other than to uniquely identify a record - and order implies meaning - if you look at the new values property for your autonumber field, you will see you have a choice - increment (default) or random (which will include negative values)

Data is stored in a table randomly. If it happens to present in the correct order that is just luck. If order matters you need to sort.

The randomness is determined by a number of factors primarily involved around how the data is stored on the drive and how it is retrieved, Access (any database) will optimise the storage and retrieval process for best performance.

my fear is will all other data in the other columns follow that

not sure what you mean by that

I have a form linked to that table that is set to go to the last record on load but it is still going to the last record of the unsorted table, very confusing

last/first and next/previous are meaningless in databases without an order. It is always better to use a query as your form recordsource which you can sort as required.

select * from mytable order by ID

[Dave14867]

Figure this one out. The form is now also working correctly after I opened it in design view, looked at the code to make sure it was set to got to the last record, closed the code window, went back to "Form" view, all was good. I closed the form reopened it and it went to the last record as expected and is working fine.

Ajax what I meant by will the other data follow is would the other columns follow the sort of the primary key or would just the primary key column sort, I found it all did "follow" the primary key sort.

What I meant by the form wasn't opening to the last record is that it was still opening to what the original goofed up table was which was record 173 because of the primary key sort issue. the last record was actually 1474 in the table.

All seems to be working fine now.