Re: Access Sorting problem - please help! (also in alt.comp.databases)

On Wed, 9 Nov 2005 12:22:41 -0000, "Neil Hindry"
<n_nospam_hindry@xxxxxxxxxxxxxxxxxxx> wrote:

[some snipped]
>Why do the records in form view appear in a different order to how I sorted
>them in the table?

This is because Access does not store the ordering together with the
table data, but only with the *view* that it gives you of the data by
opening in table view. Like Jim said, think of data in a table as an
unsorted heap or bucket of data. The database engine will store the
data in some way which will make sense only to the engine.

Unfortunately, the sorted table view is of little use to the form
because you might have more than one form to display the same data,
but sorted differently. That's why you need to use a query as the
record source for your form.

>How can I make my form display the records in the sorted way I want?

You have to create a select query with a sort order (an "order by"
clause in SQL) and base the form's record source on that query instead
of on the raw table. If you create the query through the graphical
query designer, there is a sorting option for each column displayed
(ascending, descending, or not sorted). Access will sort the columns
in the order that they appear in the query designer. That can be quite
useful if you need to display first name, then last name, but want to
sort on last name + first name. What you do then is enter the last
name column twice: once before first name, then once again after it.
The first one should not display, so uncheck the "show" box, but set
the sorting to ascending or descending as preferred. Then you would
sort the first name column, but also show it, so the "show" check box
stays checked.

Of course, when you design the form, the column ordering of the query
doesn't matter because you can arrange the controls on the form
anywhere you need them to be.

--
Bob Hairgrove
NoSpamPlease@xxxxxxxx
.

Relevant Pages

  • Re: Query sorting help
    ... In the query grid, add a calculated field ... Sort:: Ascending ... pieces for Press1 and press3. ... I wish to display the data first by Field "Press1" if data exists ...
    (microsoft.public.access.queries)
  • Re: Table design question re duplicates fields
    ... I could not get the query to work. ... If I did it this way, will I be able to sort, filter, and find the phone ... Business Fax 800-833-1213 ... Using the subform it will only display if there is data and if no data it ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Access Continuous Form - order of records any bright ideas?
    ... to the sort field and then coded the fields after update event with: ... that the user updates eg 1,2,3,4,5 and then re display the screen. ... Make a query the record source for this form. ... The records will be re-arranged each time the SortOrder field is ...
    (microsoft.public.access.forms)
  • Re: DB Result Wizard Error
    ... Also noted that one folder, which contains no information can't be deleted, ... >> For instance Equipment Serial Number, Equipment Model Number, Cost Accounting ... >> to display the new equipment information. ... >> is in the table was added to the query. ...
    (microsoft.public.frontpage.programming)
  • RE: Just starting to design a form (or is it a query?)
    ... A Form is used to display the data and allow ... A query can be the record source for a form. ... that match an ID in the Assessment table. ... Now, assuming this is the case, what you need is a form with a sub form. ...
    (microsoft.public.access.gettingstarted)