table error with autosorting

[alison kenter]

Hi there i use MS access to manage a small amount of data but i'm getting annoyed by my basic table structure.

i recently replaced all the data in the table with new data, but now i am adding new records.

thing is, tables normally default back to their original settings and layout when you enter and exit, but for sme reason, new records are appearing at the TOP of the table, instead of at the bottom.

i can re sort the table (TableID = ascending & then save) but as soon as i refresh the table (remove filter), the new records go to the top and away from other more recent records. really annoying.

i have replicated this problem a number of time with brand new tables and it's not a corruption, it happens every time. oddly the new ordering of the table changes with each record, and it seems random.

can anyone help. i DO NOT want to use a query, i have plenty of those. i want my basic table to default back to the original order it was entered and the chronoligal order of the ascending primary key.

cheers, alison

[neileg]

Originally Posted by alison kenter

can anyone help. i DO NOT want to use a query, i have plenty of those. i want my basic table to default back to the original order it was entered and the chronoligal order of the ascending primary key.

This doesn't make sense. Let me explain.

A table is an unordered dataset. When you view the table data in Access you aren't seeing the data as it exists in the underlying file, you are seeing a view of the data. In other words, it's a query that is embedded in Access. The order of the records in the table itself is irrelevant.

To say that you don't want to use a query is daft. It's like saying you want to drive a car, but you don't want to use the steering wheel. A well designed application shouldn't present the data to the user in a table, and not in a query, either. So applying a sort in a query that gives you exactly what you want seems pretty obvious.

[The_Doc_Man]

i want my basic table to default back to the original order it was entered and the chronoligal order of the ascending primary key.

Unless the primary key IS the date, these two criteria are incompatible.

Tables are retained in one of two orders: Chronological order of entry - if there IS no primary key, or order of primary key - if there IS one.

There is no alternative to one or the other of these two orders.

With a query, you have not less than as many possible orders as you have fields - times 2 since you can choose ascending or descending for any field sort order. More, actually, since there are not only combinations but permutations possible if you need a really specific sort order involving multiple fields that are individually not unique but that are unique as a compound key.

[alison kenter]

Originally Posted by The_Doc_Man

Unless the primary key IS the date, these two criteria are incompatible.

Tables are retained in one of two orders: Chronological order of entry - if there IS no primary key, or order of primary key - if there IS one.

There is no alternative to one or the other of these two orders.

With a query, you have not less than as many possible orders as you have fields - times 2 since you can choose ascending or descending for any field sort order. More, actually, since there are not only combinations but permutations possible if you need a really specific sort order involving multiple fields that are individually not unique but that are unique as a compound key.

thanks for this. it's funny, but what i'm saying is that either of these options - the two you've mentioned - are exactly what i want. and the two should be working together - the primary key is an autonumber, it SHOULD as you say, designate the order in which the records falls. it is also the chronological order of entry. but the table LITERALLY reorders itself when you add new records too it (ie if i've copied the table structure and then added some new records the new records reorder to the top of the table instead of the bottom, underneath the previous record). i know it's absurd and near impossible to believe and you wouldn't believe how much i get told off on these forums (see below) but this is what i'm asking for help on - i'm not asking for a better way to do it (no i DO NOT want to use a query). i can easily send through the table so you can see what i mean. it is WEIRD and i know i'm must missing something critical that i am HOPING you can help me with.

i've been using table to store my data for years and it's only done this recently. note that that's all i'm using it for, i'm not a programmer and this is not an application that i'm asking advice on. it's a simple table that isn't behaving as it used to is all.

thanks for this
alison

[alison kenter]

Originally Posted by neileg

This doesn't make sense. Let me explain.

A table is an unordered dataset. When you view the table data in Access you aren't seeing the data as it exists in the underlying file, you are seeing a view of the data. In other words, it's a query that is embedded in Access. The order of the records in the table itself is irrelevant.

To say that you don't want to use a query is daft. It's like saying you want to drive a car, but you don't want to use the steering wheel. A well designed application shouldn't present the data to the user in a table, and not in a query, either. So applying a sort in a query that gives you exactly what you want seems pretty obvious.

Yes i', aware of this thanks. however, i've been using tables to store my data for years and it's only been behaving weirdly recently. that all i'm using it for. i'm not a programmer and this is not an application that i'm asking advice on. it's a simple table that isn't behaving as it used to is all.

when you say the table is an unordered dataset, isn't there some order to it? the other guys mentioned it should be either in the order of the primarly key, or in the order that it's been entered?

thanks again. alison

[neileg]

Originally Posted by alison kenter

when you say the table is an unordered dataset, isn't there some order to it? the other guys mentioned it should be either in the order of the primarly key, or in the order that it's been entered?

There may or there may not be a logical order to the way that the data is stored, but it doesn't matter. A relational database does not depend on the order in a table. And I repeat that the order you see the data presented to you in table view is not neccesarily the order in which it is stored.

If a single user were to sit at their workstation and build a new table from scratch, then there'd be a good chance that the order in the file will be chronological and in PK order, too. But if you have more than one user, or you have record deletions, then the chances of having that natural sort order diminishes rapidly.

If the order in the table view is obviously different from that way it used to behave, then its clear that somewhere along the line a sort order has been imposed on the table. It may be that a repair and compact or importing all the objects into a new blank database will help restore the previous situation.

If all you are using the table for is to store data, then I really don't understand why you don't use a query that returns all the table data to impose the order you want. You can then forget about the table. You can enter data through your query, and base other queries on the query. Then the table sort order is completely irrelevant.

[Rabbie]

I have found that if I sort a table in a different order while looking at it in datasheet view. The next time I look at it it is the same order provide I answere Yes to the question "Do you wish to save changes to the design of this table?". This seems to confirm Neil's suggestion that you see the table through an internal query.

In ten years experience as an Access developer I feel you should only use tables to store data. REports/Forms should use queries to obtain their data.