Re: order by property in a table

What others have been telling you, in everyday terms, is that "relational
database tables are, by definition, unordered" and that you have to use the
SQL or query that retrieves the records, or some property of the display (a
number of objects in Access have an OrderBy property for this purpose), to
put them in a particular order.

Of course, Access has to display the records in _some_ order, and that
happens to be what you are seeing. BUT, at least one person here has told
you, and truthfully, that you can't _count_ on the records being displayed
in that order, unless you specifically order them at time of retrieval.
Under different circumstances, at a different time, you may be surprised to
see them NOT in the same order you are used to, nor the order in which they
were entered, nor in date/time order.

Do you have the database you are developing separated into a front-end
(queries, reports, forms, macros, modules) and a back-end (tables,
relationships, and the data contained in the tables)? If so you should be
able just to make a copy to use for experimentation without endangering the
data that you have worked so hard to enter.

You are _assuming_ that the records are always going to be in the order
entered, which they are not. But, you can enforce an order on them when you
retrieve them (and that could be in Date/Time order, if you wish).

It is, almost certainly, not good application design to give users only
scrolling as a way to find particular data -- it is well worthwhile to
understand the users' problems and provide them a simple way to locate the
information they need.

Larry Linson
Microsoft Access MVP


"kaosyeti@xxxxxxxxxxx via AccessMonster.com" <u15580@uwe> wrote in message
news:576aaaa5731e1@xxxxxx
> well, i would be able to tell by the date AND name, wouldn't i? i mean,
> i'm
> thinking that if i go back 3 weeks worth of records to find one on a
> particular date, i would find it (normally) in order with the other
> records
> from the same date, and just look for the appropriate name (i keep coming
> back to date because this is how we're tracking this data. this all to
> create a database to track customer traffic, not customer information). i
> find it extremely unlikely to have 2 customers in the same day that have
> the
> same name that also have the same salesperson. this is the only time that
> two records may be confused. the real issue is when the record navigation
> arrows are used, is it going to go in order of the records as it was
> entered
> or is there a way to make it go chronologically? i know there are better
> ways to do what i'm suggesting (building a query as you suggest) but this
> is
> not for me to do. i'm trying to think of scenarios that may come up for
> the
> other, less technically inclined people who are going to be using this db.
> they couldn't navigate the raw table to save their lives. i swear i'm not
> kidding. some, i'm sure, can muddle through just like i am but most of
> these
> people would rather keep paper records than try to figure out something
> like
> this on their own.
>
> fredg wrote:
>>> i have a (hopefully) small problem. i have created a system where a
>>> user
>>> enters customer information into a table through a form. this table has
>>> no
>>[quoted text clipped - 15 lines]
>>> winded but i lack the experience with access to be sure my point is
>>> understood and be brief at the same time. thanks.
>>
>>No. The records in the table are stored in no particular order, and
>>you cannot, in the table itself, order them, except by manually
>>selecting the field and clicking on the A-Z or Z-A sort tool button.
>>Besides, there is no valid reason for the user to ever look at the
>>table.
>>
>>Create a new query with all of the fields in the table. Set the date
>>Field Sort to Ascending (or Descending, if that is more suitable for
>>you). Use this query as the record source for the form for both data
>>viewing, editing, and entry.
>>The actual records can be entered in any order, but the form will
>>always be sorted by date.
>>
>>I'm not so sure that it is wise for you not to have a prime key field.
>>I suspect this will come back someday in the future and bite you.
>>You most probably will have several entries with the same date. How
>>will you be assured that the record you are modifying is the correct
>>one? Today there may be only one customer named John Smith, a very
>>common name. Next year it's not unlikely that there will be more than
>>one John Smith as a customer. Which is the one you want to edit?
>>
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200511/1


.