Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

physically sort records in a table

Discussion in 'Information Technology' started by keithrmanning, Jul 28, 2006.

  1. keithrmanning

    keithrmanning
    Expand Collapse
    Guest

    I want to permanently (physically) sort the records in a table. All forms of
    sort/filter just seem to change the view and leave the records in the order
    they were entered. When I restructure the table, the records revert to the
    order they were entered.

    I can use a query to create a new table in a different physical order, but
    would rather sort the original table. Am I missing something obvious?

    Keith
     
  2. Loading...

    Similar Threads Forum Date
    Physically challenged and "Sewa" Sikh Sikhi Sikhism Nov 17, 2011
    I am sort of new to Sikhism New to Sikhism May 3, 2013
    Consortium agrees to pay 11.3 billion dollars for SunGard Data Systems (AFP) Interfaith Dialogues Mar 28, 2005

  3. Rick B

    Rick B
    Expand Collapse
    Guest

    Yes. You don't work in a table and the order does not matter.

    Once created, you should rarely, if ever, open a table again. All your work
    should be done in the forms, reports, and queries. You can then set the
    sorting to any order you desire.

    --
    Rick B



    "keithrmanning" <keithrmanning@discussions.microsoft.com> wrote in message
    news:D53C2BA7-80C3-4F72-A1A0-523965E01F39@microsoft.com...
    >I want to permanently (physically) sort the records in a table. All forms
    >of
    > sort/filter just seem to change the view and leave the records in the
    > order
    > they were entered. When I restructure the table, the records revert to the
    > order they were entered.
    >
    > I can use a query to create a new table in a different physical order, but
    > would rather sort the original table. Am I missing something obvious?
    >
    > Keith
     
  4. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Mon, 22 May 2006 10:42:01 -0700, keithrmanning
    <keithrmanning@discussions.microsoft.com> wrote:

    > Am I missing something obvious?


    Yes: the fact that a table HAS NO ORDER.

    It should be viewed as an unordered "bucket" of data. You can create a
    Query with a sort, and use this Query as the basis of Forms, Reports,
    other Queries, exports, etc.

    I believe that if you Compact the database, the records in the table
    will be sorted into Primary Key order - but the moment you add a new
    record, the new record will be placed wherever Access finds
    convenient. You should NOT rely on tables being in *any* particular
    order.

    John W. Vinson[MVP]
     
  5. keithrmanning

    keithrmanning
    Expand Collapse
    Guest

    If I can do everything in a logical view, your response would be correct.
    However, for example, if I sort a view into the order I want and then
    restructure the table to add an autoincrement column, the records are
    numbered in the original physical order rather than the order I want. So, you
    see, physical order does make a difference for this and other functions.

    I can get the result I need by exporting to Excel, sorting there and
    reimporting into Access. I was hoping someone who does more Access than I
    would be able to tell me how to do the same thing in Access alone. Instead,
    you tell me that I shouldn't want to do what I want to do. Not helpful.

    "Rick B" wrote:

    > Yes. You don't work in a table and the order does not matter.
    >
    > Once created, you should rarely, if ever, open a table again. All your work
    > should be done in the forms, reports, and queries. You can then set the
    > sorting to any order you desire.
    >
    > --
    > Rick B
    >
    >
    >
    > "keithrmanning" <keithrmanning@discussions.microsoft.com> wrote in message
    > news:D53C2BA7-80C3-4F72-A1A0-523965E01F39@microsoft.com...
    > >I want to permanently (physically) sort the records in a table. All forms
    > >of
    > > sort/filter just seem to change the view and leave the records in the
    > > order
    > > they were entered. When I restructure the table, the records revert to the
    > > order they were entered.
    > >
    > > I can use a query to create a new table in a different physical order, but
    > > would rather sort the original table. Am I missing something obvious?
    > >
    > > Keith

    >
    >
    >
     
  6. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    >>I want to permanently (physically) sort the records in a table.
    Build a query with your sort order and save - there is your permanent record.

    If ascending or descinding will not do it for you add a field that you
    insert a number to sort on.

    "keithrmanning" wrote:

    > I want to permanently (physically) sort the records in a table. All forms of
    > sort/filter just seem to change the view and leave the records in the order
    > they were entered. When I restructure the table, the records revert to the
    > order they were entered.
    >
    > I can use a query to create a new table in a different physical order, but
    > would rather sort the original table. Am I missing something obvious?
    >
    > Keith
     
  7. keithrmanning

    keithrmanning
    Expand Collapse
    Guest

    How can you say a "table has no order" when an autoincrement column added to
    the table always numbers the rows in the same sequence - that is, the
    sequence the rows were entered in. Obviously, there is a "natural" order to
    the table. If I export the table to Excel, sort it an reimport it, the
    autoincrement now works the way I want it to.

    What you seem to be saying is that Access does not give me any direct way to
    control the "natural" sequence that it uses for some operations. If true,
    that is disappointing.

    Imagine you had a reference table that was going to be accessed tens of
    thousands of times a day and you wanted to do a binary search on it in order
    to speed response. If I can't control the physical sequence of rows in the
    table and had to use a logical view to get the records into sequence, the
    system could be forced to do literally ten to one hundred times the disk
    accesses to find my data compared to accessing a well-ordered physical set.
    So, there are definately times when you need to be able to "rely" on a table
    to be in the order you set it in - or pay a huge price in other areas.
    "Unordered buckets of data" are fine for simple, low-usage applications, but
    it is a pity if that is the only option Access provides. I'm still hoping
    someone else will tell me how to control physical order in the cases where it
    is important.

    Keith

    Keith

    "John Vinson" wrote:

    > On Mon, 22 May 2006 10:42:01 -0700, keithrmanning
    > <keithrmanning@discussions.microsoft.com> wrote:
    >
    > > Am I missing something obvious?

    >
    > Yes: the fact that a table HAS NO ORDER.
    >
    > It should be viewed as an unordered "bucket" of data. You can create a
    > Query with a sort, and use this Query as the basis of Forms, Reports,
    > other Queries, exports, etc.
    >
    > I believe that if you Compact the database, the records in the table
    > will be sorted into Primary Key order - but the moment you add a new
    > record, the new record will be placed wherever Access finds
    > convenient. You should NOT rely on tables being in *any* particular
    > order.
    >
    > John W. Vinson[MVP]
    >
     
  8. Arno R

    Arno R
    Expand Collapse
    Guest

    "keithrmanning" <keithrmanning@discussions.microsoft.com> schreef in bericht news:41D1D838-2665-4B68-B5C2-D6A264FDF14D@microsoft.com...
    >
    > I can get the result I need by exporting to Excel, sorting there and
    > reimporting into Access. I was hoping someone who does more Access than I
    > would be able to tell me how to do the same thing in Access alone. Instead,
    > you tell me that I shouldn't want to do what I want to do. Not helpful.
    >


    Not helpful at first sight maybe, but both Rick and John are right on this issue.
    Experienced developers are not going to teach you 'the wrong way'.
    Maybe someone like PCDatashit can pop in?

    Arno R
     
  9. Rick B

    Rick B
    Expand Collapse
    Guest

    If you want to sort the records in a particular order, then you would have
    to add a field to do so. You are free to add a new field to your table and
    include a number for each record.

    Not really sure why you'd do this in Access. Access is NOT a spreadsheet.

    What happens as you add new records? Will you always want them at the end,
    or will you forever be manipulating your new "sort" field?

    Just FYI, there are hundreds of previous posts on this very topic. Go take
    a look at them and maybe you'll find some responses that better suit your
    needs.

    --
    Rick B



    "keithrmanning" <keithrmanning@discussions.microsoft.com> wrote in message
    news:41D1D838-2665-4B68-B5C2-D6A264FDF14D@microsoft.com...
    > If I can do everything in a logical view, your response would be correct.
    > However, for example, if I sort a view into the order I want and then
    > restructure the table to add an autoincrement column, the records are
    > numbered in the original physical order rather than the order I want. So,
    > you
    > see, physical order does make a difference for this and other functions.
    >
    > I can get the result I need by exporting to Excel, sorting there and
    > reimporting into Access. I was hoping someone who does more Access than I
    > would be able to tell me how to do the same thing in Access alone.
    > Instead,
    > you tell me that I shouldn't want to do what I want to do. Not helpful.
    >
    > "Rick B" wrote:
    >
    >> Yes. You don't work in a table and the order does not matter.
    >>
    >> Once created, you should rarely, if ever, open a table again. All your
    >> work
    >> should be done in the forms, reports, and queries. You can then set the
    >> sorting to any order you desire.
    >>
    >> --
    >> Rick B
    >>
    >>
    >>
    >> "keithrmanning" <keithrmanning@discussions.microsoft.com> wrote in
    >> message
    >> news:D53C2BA7-80C3-4F72-A1A0-523965E01F39@microsoft.com...
    >> >I want to permanently (physically) sort the records in a table. All
    >> >forms
    >> >of
    >> > sort/filter just seem to change the view and leave the records in the
    >> > order
    >> > they were entered. When I restructure the table, the records revert to
    >> > the
    >> > order they were entered.
    >> >
    >> > I can use a query to create a new table in a different physical order,
    >> > but
    >> > would rather sort the original table. Am I missing something obvious?
    >> >
    >> > Keith

    >>
    >>
    >>
     
  10. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    >>Imagine you had a reference table that was going to be accessed tens of
    thousands of times a day and you wanted to do a binary search on it in order
    to speed response.
    Create an index.

    "keithrmanning" wrote:

    > How can you say a "table has no order" when an autoincrement column added to
    > the table always numbers the rows in the same sequence - that is, the
    > sequence the rows were entered in. Obviously, there is a "natural" order to
    > the table. If I export the table to Excel, sort it an reimport it, the
    > autoincrement now works the way I want it to.
    >
    > What you seem to be saying is that Access does not give me any direct way to
    > control the "natural" sequence that it uses for some operations. If true,
    > that is disappointing.
    >
    > Imagine you had a reference table that was going to be accessed tens of
    > thousands of times a day and you wanted to do a binary search on it in order
    > to speed response. If I can't control the physical sequence of rows in the
    > table and had to use a logical view to get the records into sequence, the
    > system could be forced to do literally ten to one hundred times the disk
    > accesses to find my data compared to accessing a well-ordered physical set.
    > So, there are definately times when you need to be able to "rely" on a table
    > to be in the order you set it in - or pay a huge price in other areas.
    > "Unordered buckets of data" are fine for simple, low-usage applications, but
    > it is a pity if that is the only option Access provides. I'm still hoping
    > someone else will tell me how to control physical order in the cases where it
    > is important.
    >
    > Keith
    >
    > Keith
    >
    > "John Vinson" wrote:
    >
    > > On Mon, 22 May 2006 10:42:01 -0700, keithrmanning
    > > <keithrmanning@discussions.microsoft.com> wrote:
    > >
    > > > Am I missing something obvious?

    > >
    > > Yes: the fact that a table HAS NO ORDER.
    > >
    > > It should be viewed as an unordered "bucket" of data. You can create a
    > > Query with a sort, and use this Query as the basis of Forms, Reports,
    > > other Queries, exports, etc.
    > >
    > > I believe that if you Compact the database, the records in the table
    > > will be sorted into Primary Key order - but the moment you add a new
    > > record, the new record will be placed wherever Access finds
    > > convenient. You should NOT rely on tables being in *any* particular
    > > order.
    > >
    > > John W. Vinson[MVP]
    > >
     
  11. xRoachx

    xRoachx
    Expand Collapse
    Guest

    Hi Keith -- A couple of things to note as what Rick and John have stated is
    correct. I don't know your experience with relational databases but my guess
    is you don't have that much based on your arguments. With that said, Access
    is used to build relational databases which act completely differently then a
    spreadsheet such as Excel -- Don't expect the same functionality!

    The next thing is, the table should only be used to store data, the order it
    is stored is irrelevant to relational databases. For items that will be
    searched on repeatedly, these should be indexed to speed up retrieval...but
    you cannot index everything.

    Also, the autonumber's only function is to create a unique identifier for a
    record, not a sequential number.

    Last, it would be helpful if you would provide more detail on exactly it is
    you want to accomplish. Based on your original post, your descripton seems
    to be heading down the wrong path; hence the reason for the original
    responses as we do not want to give you bad advice... :)

    "keithrmanning" wrote:

    > How can you say a "table has no order" when an autoincrement column added to
    > the table always numbers the rows in the same sequence - that is, the
    > sequence the rows were entered in. Obviously, there is a "natural" order to
    > the table. If I export the table to Excel, sort it an reimport it, the
    > autoincrement now works the way I want it to.
    >
    > What you seem to be saying is that Access does not give me any direct way to
    > control the "natural" sequence that it uses for some operations. If true,
    > that is disappointing.
    >
    > Imagine you had a reference table that was going to be accessed tens of
    > thousands of times a day and you wanted to do a binary search on it in order
    > to speed response. If I can't control the physical sequence of rows in the
    > table and had to use a logical view to get the records into sequence, the
    > system could be forced to do literally ten to one hundred times the disk
    > accesses to find my data compared to accessing a well-ordered physical set.
    > So, there are definately times when you need to be able to "rely" on a table
    > to be in the order you set it in - or pay a huge price in other areas.
    > "Unordered buckets of data" are fine for simple, low-usage applications, but
    > it is a pity if that is the only option Access provides. I'm still hoping
    > someone else will tell me how to control physical order in the cases where it
    > is important.
    >
    > Keith
    >
    > Keith
    >
    > "John Vinson" wrote:
    >
    > > On Mon, 22 May 2006 10:42:01 -0700, keithrmanning
    > > <keithrmanning@discussions.microsoft.com> wrote:
    > >
    > > > Am I missing something obvious?

    > >
    > > Yes: the fact that a table HAS NO ORDER.
    > >
    > > It should be viewed as an unordered "bucket" of data. You can create a
    > > Query with a sort, and use this Query as the basis of Forms, Reports,
    > > other Queries, exports, etc.
    > >
    > > I believe that if you Compact the database, the records in the table
    > > will be sorted into Primary Key order - but the moment you add a new
    > > record, the new record will be placed wherever Access finds
    > > convenient. You should NOT rely on tables being in *any* particular
    > > order.
    > >
    > > John W. Vinson[MVP]
    > >
     
  12. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Mon, 22 May 2006 13:14:02 -0700, keithrmanning
    <keithrmanning@discussions.microsoft.com> wrote:

    >Imagine you had a reference table that was going to be accessed tens of
    >thousands of times a day and you wanted to do a binary search on it in order
    >to speed response.


    I do so, very frequently.

    I use Access JET B-Tree indexes - which implement a VERY efficient
    binary search - on any fields which are to be used for searching or
    sorting.

    Doing so is MUCH more efficient than sorting the table and writing
    your own VBA code to try to beat Jet's search engine.

    John W. Vinson[MVP]
     
  13. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    A bit temperamental today? <g>
    That said I get pissed off to when givving sound advice & being butted off
    Autonumbers *are* truly "dumb" numbers - I must admit though that I abuse
    this fact for some *very* static tables in some of my apps due to query
    complexities
    (was way worse in Access 2.0, caused by max number of simultanous "file
    handles" - try multiple UNION's...)

    Pieter

    "Rick B" <Anonymous> wrote in message
    news:uK3lm3dfGHA.1520@TK2MSFTNGP03.phx.gbl...
    > If you want to sort the records in a particular order, then you would have
    > to add a field to do so. You are free to add a new field to your table
    > and include a number for each record.
    >
    > Not really sure why you'd do this in Access. Access is NOT a spreadsheet.
    >
    > What happens as you add new records? Will you always want them at the
    > end, or will you forever be manipulating your new "sort" field?
    >
    > Just FYI, there are hundreds of previous posts on this very topic. Go
    > take a look at them and maybe you'll find some responses that better suit
    > your needs.
    >
    > --
    > Rick B
    >
    >
    >
    > "keithrmanning" <keithrmanning@discussions.microsoft.com> wrote in message
    > news:41D1D838-2665-4B68-B5C2-D6A264FDF14D@microsoft.com...
    >> If I can do everything in a logical view, your response would be correct.
    >> However, for example, if I sort a view into the order I want and then
    >> restructure the table to add an autoincrement column, the records are
    >> numbered in the original physical order rather than the order I want. So,
    >> you
    >> see, physical order does make a difference for this and other functions.
    >>
    >> I can get the result I need by exporting to Excel, sorting there and
    >> reimporting into Access. I was hoping someone who does more Access than I
    >> would be able to tell me how to do the same thing in Access alone.
    >> Instead,
    >> you tell me that I shouldn't want to do what I want to do. Not helpful.
    >>
    >> "Rick B" wrote:
    >>
    >>> Yes. You don't work in a table and the order does not matter.
    >>>
    >>> Once created, you should rarely, if ever, open a table again. All your
    >>> work
    >>> should be done in the forms, reports, and queries. You can then set the
    >>> sorting to any order you desire.
    >>>
    >>> --
    >>> Rick B
    >>>
    >>>
    >>>
    >>> "keithrmanning" <keithrmanning@discussions.microsoft.com> wrote in
    >>> message
    >>> news:D53C2BA7-80C3-4F72-A1A0-523965E01F39@microsoft.com...
    >>> >I want to permanently (physically) sort the records in a table. All
    >>> >forms
    >>> >of
    >>> > sort/filter just seem to change the view and leave the records in the
    >>> > order
    >>> > they were entered. When I restructure the table, the records revert to
    >>> > the
    >>> > order they were entered.
    >>> >
    >>> > I can use a query to create a new table in a different physical order,
    >>> > but
    >>> > would rather sort the original table. Am I missing something obvious?
    >>> >
    >>> > Keith
    >>>
    >>>
    >>>

    >
    >
     
  14. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    "keithrmanning" <keithrmanning@discussions.microsoft.com> wrote in message
    news:392401AF-E21E-480D-8CF5-327D4A30AABA@microsoft.com...

    > How can you say a "table has no order" when an autoincrement column added
    > to
    > the table always numbers the rows in the same sequence - that is, the
    > sequence the rows were entered in.




    Yes, the auto increment will increase by a number for each record. However,
    that number is COMPLIRY different then the physical order of the records.
    The autonumber order is NOT related to the order that records are returned
    in if you open a table. Often, they are the same order, but this is only
    LUCK of the draw!!



    In other words, if you write out 4 records to disk (or you enter 4 records
    to disk, THERE IS NOT GARENTEE THAT THESE records will be returned in that
    order. EVEN WHEN YOU HAVE A AUTONUMBER field, the records STILL ARE NOT
    retuned in the order you entered them. (most of the time they are..but not
    always).



    In addition, if you have 4 people entering records, then order of entry is
    often not much use. What good is to look at the last 3 records, when they
    might have been entered by 3 different people? (Likely, each of those
    individuals might need to see their last records.but the physical order is
    not much use.is it?)



    So, the ONLY WAY to ensure order is to FORCE THE ORDER. Simply put, base the
    form, or report, or sub-form on a query THAT SETS THE ORDER. And, a good
    field to to use to set that order is the autonumber field, since it does
    increase by one each time. However, DO NOT CONFUSE the order of the
    autonumber field with that of the order records placed on the disk
    drive...as they are NOT THE same.



    So, to answer your question, simply use a auto number field, and base the
    form (or whatever) on a query that sets the order by the auto number field.
    You will NEVER have to set the order, NEVER have to export and NEVER has to
    sort the form in question if you do this.



    However, using this query will set the order for you, but it will NOT change
    the physical order of the records on the disk drive, and as mentioned, in a
    database system, the data is un-ordered...it is YOUR CHOICE to set the way
    you want the data engine to return the order..but there is NO physical order
    that is *related* to the actual order of data entry....



    > What you seem to be saying is that Access does not give me any direct way
    > to
    > control the "natural" sequence that it uses for some operations. If true,
    > that is disappointing.




    No, what they are saying that if you need a order, YOU must specify it. The
    most common order is by the autonubmer, and the other possible is by a
    timestamp field. However, you can NOT relay on the order of entry, as it is
    not consistent.

    >
    > Imagine you had a reference table that was going to be accessed tens of
    > thousands of times a day and you wanted to do a binary search on it in
    > order
    > to speed response.




    True, but then we also have balanced btrees, and they are going to work MUCH
    faster then if you hand code the retrieval of records anyway. And, you
    likely can't write code any faster then want the optimized indexing systems
    that are built in anyway.



    > If I can't control the physical sequence of rows in the
    > table and had to use a logical view to get the records into sequence, the
    > system could be forced to do literally ten to one hundred times the disk
    > accesses to find my data compared to accessing a well-ordered physical
    > set.




    That is true. Sql server does have a concept of cluster indexes, and this
    does force data to be grouped together on the disk drive (it don't force
    overall order.but does group data together). So, clustered indexes would not
    help the above.



    > So, there are definately times when you need to be able to "rely" on a
    > table
    > to be in the order you set it in - or pay a huge price in other areas.
    > "Unordered buckets of data" are fine for simple, low-usage applications,
    > but
    > it is a pity if that is the only option Access provides. I'm still hoping
    > someone else will tell me how to control physical order in the cases where
    > it
    > is important.




    I don't believe that Oracle, sql-server, Mysql, Sybase, or ANY of the major
    database vendors have anything different that that of a bunch of buckets of
    data. So, every major database system in the marketplace today works just
    like ms-access does. So, what people here are trying to point out is that a
    database system DOES NOT force order, nor does it retain some special
    ordering of data on the disk for you. YOU must tell the data engine HOW you
    want the data back.



    However, having said the above, for optimizing purposes, when you do a
    compact and repair in ms-access, the data is *physically* ordered BY primary
    key. So, you can do a compact and repair to set the records in primary key
    order. So, you could thus make the autonumber the primary key (which is
    often the case), and when you compact, your data would be in order. However,
    as others mentioned, you can NOT rely on this table order, but STILL MUST
    use a query to set the order. The reason for this is the INSTACNT you edit,
    delete, or even add a record, that record may not be OUT of physical order.



    So, you do have the ability to order the data by simply compacting the
    database. However, your code, your forms, your data processing routines,
    your export routines, virtually everything you do STILL MUST set the order.
    In other words, as all posters here said, you MUST set and define the order
    in which you want the data engine to retrieve data....you can NOT leave this
    issue up in the air.....



    This is an important concept to grasp, since you might be retrieving data
    from your desktop database, or a large corporate system in which 100's of
    records may have been added between you adding ONE record. So, in today's
    world, order of data is NOT maintained due to practical design issues that
    make the whole concept a moot point. In the old days, we have punched cards,
    or even magnetic tapes, or sequential files on a disk drive that DID IN FACT
    preserve order. However, these systems were NOT muti-user. So, in modern
    database systems, there is no order!!. There is several reasons for this,
    once of which in fact is that today records are VARAIBLE length. You can't
    know that the 5th record is going to resolve to a particular spot on the
    disk drive (in old systems, like old FoxPro, or even earlier dbaseIII
    systems, records were fixed length, and you could calculate the 5th record
    by a offset from the START of the file on the disk drive by going:



    Record position = Record Number X record size



    Today, that file, or data might not actually even exists on your computer,
    but perhaps on a corporate data server half way around the world (with 500
    other people using it). The physical order is not of your concern. IF you
    need a particular order of data, then you MUST DESING this concept into your
    appcation. So, about 15, or 20 years ago, data order was preserved in most
    systems, but the brave new world of client to server changed all this. This
    is as much a conceptual topic as it simply means you can't approach data
    like you did with FORTARN or even simply text files to store data.



    Anyway, doing a compact an repair would put your data in the same order as
    the primary key, and obviously a query that asks for this order would be
    speeded up substantially in this case. So, do remember that you can by
    compacting force a physical order, but you still have to use that query to
    reliably return those records in that order.



    And, since an auto number is only an internal number, you can't expose this
    number to your users, since when you export, or move the data, it has no
    meaning. Further, due to canceled additions, often the auto number will have
    gaps in it.



    So, you *CAN* use the auto number to set the order of data, but you can't
    give the auto number meaning, such as making it a customer number etc.



    The above advice applies to ANY of the major database systems that you will
    use.......get used to it.....




    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    pleaseNOOSpamKallal@msn.com
    http://www.members.shaw.ca/AlbertKallal
     
  15. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    Also using MS SQLServer /Oracle as a back end will give you what you desire
    through triggers
    but as someone stated (I'm getting bored with this) index your sorting field
    & everybody's happy

    Pieter

    "keithrmanning" <keithrmanning@discussions.microsoft.com> wrote in message
    news:392401AF-E21E-480D-8CF5-327D4A30AABA@microsoft.com...
    > How can you say a "table has no order" when an autoincrement column added
    > to
    > the table always numbers the rows in the same sequence - that is, the
    > sequence the rows were entered in. Obviously, there is a "natural" order
    > to
    > the table. If I export the table to Excel, sort it an reimport it, the
    > autoincrement now works the way I want it to.
    >
    > What you seem to be saying is that Access does not give me any direct way
    > to
    > control the "natural" sequence that it uses for some operations. If true,
    > that is disappointing.
    >
    > Imagine you had a reference table that was going to be accessed tens of
    > thousands of times a day and you wanted to do a binary search on it in
    > order
    > to speed response. If I can't control the physical sequence of rows in the
    > table and had to use a logical view to get the records into sequence, the
    > system could be forced to do literally ten to one hundred times the disk
    > accesses to find my data compared to accessing a well-ordered physical
    > set.
    > So, there are definately times when you need to be able to "rely" on a
    > table
    > to be in the order you set it in - or pay a huge price in other areas.
    > "Unordered buckets of data" are fine for simple, low-usage applications,
    > but
    > it is a pity if that is the only option Access provides. I'm still hoping
    > someone else will tell me how to control physical order in the cases where
    > it
    > is important.
    >
    > Keith
    >
    > Keith
    >
    > "John Vinson" wrote:
    >
    >> On Mon, 22 May 2006 10:42:01 -0700, keithrmanning
    >> <keithrmanning@discussions.microsoft.com> wrote:
    >>
    >> > Am I missing something obvious?

    >>
    >> Yes: the fact that a table HAS NO ORDER.
    >>
    >> It should be viewed as an unordered "bucket" of data. You can create a
    >> Query with a sort, and use this Query as the basis of Forms, Reports,
    >> other Queries, exports, etc.
    >>
    >> I believe that if you Compact the database, the records in the table
    >> will be sorted into Primary Key order - but the moment you add a new
    >> record, the new record will be placed wherever Access finds
    >> convenient. You should NOT rely on tables being in *any* particular
    >> order.
    >>
    >> John W. Vinson[MVP]
    >>
     
  16. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    keithrmanning wrote:
    > I want to permanently (physically) sort the records in a table.


    Ah you are talking about the clustered index a.k.a. physical ordering
    on disk.

    Please excuse the regulars here: they don't like being reminded that
    when you take away the Jet engine an 'Access database' is merely a flat
    file <g>.

    The answer you require is:

    1) identify the column(s) you want to determine the physical ordering
    on disk;
    2) make these columns the primary key for the table;
    3) compact the file.

    For the technical details, see:

    Compacting re-stores table records into their Primary Key order:
    http://support.microsoft.com/default.aspx?scid=kb;en-us;209769

    clustered-key compact method is based on the primary key of the table,
    new data entered will be in time order:
    http://support.microsoft.com/default.aspx?scid=kb;en-us;209769

    Why none of the regulars are proffering these details is because
    actually admitting that tables do have a physical order on the PK would
    invlove rejecting the popular habit of using an autonumber as a PK
    because it makes for a lousy clustered index, and none of the regulars
    want that to happen <g>.

    Jamie.

    --
     
  17. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    Jamie Collins wrote:
    > keithrmanning wrote:
    > > I want to permanently (physically) sort the records in a table.

    >
    > Ah you are talking about the clustered index a.k.a. physical ordering
    > on disk.
    >
    > Please excuse the regulars here: they don't like being reminded that
    > when you take away the Jet engine an 'Access database' is merely a
    > flat file <g>.
    >
    > The answer you require is:
    >
    > 1) identify the column(s) you want to determine the physical ordering
    > on disk;
    > 2) make these columns the primary key for the table;
    > 3) compact the file.
    >
    > For the technical details, see:
    >
    > Compacting re-stores table records into their Primary Key order:
    > http://support.microsoft.com/default.aspx?scid=kb;en-us;209769
    >
    > clustered-key compact method is based on the primary key of the table,
    > new data entered will be in time order:
    > http://support.microsoft.com/default.aspx?scid=kb;en-us;209769
    >
    > Why none of the regulars are proffering these details is because
    > actually admitting that tables do have a physical order on the PK
    > would invlove rejecting the popular habit of using an autonumber as a
    > PK because it makes for a lousy clustered index, and none of the
    > regulars want that to happen <g>.
    >
    > Jamie.


    Technical gymnastics. Are you really suggesting that for performing regular
    (normal use) activities that requiring that the data file be compacted before
    certain actions are performed has ANY practical viability? Regardless of
    whether Access (Jet) places records in any particular physical order on disk is
    totally irrelevent to the question "Should the design of my app be based on that
    order?" The clear answer to that is "No".

    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  18. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Rick Brandt wrote:
    > whether Access (Jet) places records in any particular physical order on disk is
    > totally irrelevent to the question "Should the design of my app be based on that
    > order?"


    You changed the question! The OP opened with, "I want to permanently
    (physically) sort the records in a table."

    But to answer *your* question, yes you should be aware of physical
    implementation issues when designing your apps. You do use (and give
    proper regard to) indexes, don't you e.g. creating an index on the
    referencing table of a foreign key? There is no good reason why the SQL
    engine does not to do this automatically but one should be aware that
    Jet doesn't and take appropriate action.

    In an ideal world we could implement out logical models without regard
    to physical implementation issues. Ah, maybe one day...

    Jamie.

    --
     
  19. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    Jamie Collins wrote:
    > Rick Brandt wrote:
    > > whether Access (Jet) places records in any particular physical
    > > order on disk is totally irrelevent to the question "Should the
    > > design of my app be based on that order?"

    >
    > You changed the question! The OP opened with, "I want to permanently
    > (physically) sort the records in a table."


    Since it only applies fresh after a compact there is nothing "permanent" about
    what you're suggesting.

    > But to answer *your* question, yes you should be aware of physical
    > implementation issues when designing your apps. You do use (and give
    > proper regard to) indexes, don't you e.g. creating an index on the
    > referencing table of a foreign key? [snip]


    Nothing to do with physical order of the table. Yes, I know about "clustered"
    indexes, but no app I have ever created "cares" one wit about them. A clustered
    index might make a few percentage points difference on the speed of certain
    queries, but that is not the same thing as having that query "depend on" the
    physical ordering of records.


    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  20. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Rick Brandt wrote:
    > Since it only applies fresh after a compact there is nothing "permanent" about
    > what you're suggesting.


    Good point. I should have included

    4) Ensure the table cannot be changed e.g. REVOKE permissions or make
    the file read only.

    <g>.

    Jamie.

    --
     
  21. Rick Wannall

    Rick Wannall
    Expand Collapse
    Guest

    If we're talking about data in Access, I don't think you have to do any
    recompacting to get the data to show up ordered by the PrimaryKey. At
    least, in my test case I was able to type in key values in descending order
    on successive rows, close the table, and reopen it, without compacting, to
    find the data sorted exactly as I expected.

    Does this behavior break down after some large number of records or
    something? I haven't ever seen it not work.
     

Share This Page