Understanding How Access Sorts Records
in [Help]
|
Prev: set and get image metadata
Next: Report fails to run if textbox =Sum([Amt]) is used in Group Footer - runs fine without it.
From: Takeadoe on 30 Jun 2008 10:56 Greetings, I'm hoping that someone can help me understand the method behind MS Access' ordering of records within a table. It does not appear that the records added last are always at the bottom when I open the Table. It is important that the records stay in the order in which they were added to the table. They clearly are not, but I can't seem to figure out why the records are ending up in the order that they are. Can anyone shed some light on this? Thank you very much! Mike
From: MLH on 30 Jun 2008 11:06 your primary keyfield is likely causing the order of the records to be displayed, by default, in some order OTHER than that in which the records were entered. Best use a query to reorder the records in the order that suits your needs. Then, anytime you wish to refer to records in the table, refer to them indirectly by use of the query used to order them the way you like 'em. Contrary to what beginners think, it is NOT important that "records stay in the order in which they were added". The fact that they STAY there is enough. I would com- plain if they didn't stay there. Reordering them is painless and instantaneous. You're not dealing with a shoebox full of paper receipts here. It's a database. Sorting a shoe box by hand is a job. Running a query to sort records quickly is not. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx On Mon, 30 Jun 2008 07:56:53 -0700 (PDT), Takeadoe <mtonkovich(a)msn.com> wrote: >Greetings, > >I'm hoping that someone can help me understand the method behind MS >Access' ordering of records within a table. It does not appear that >the records added last are always at the bottom when I open the >Table. It is important that the records stay in the order in which >they were added to the table. They clearly are not, but I can't seem >to figure out why the records are ending up in the order that they >are. Can anyone shed some light on this? > >Thank you very much! > >Mike
From: Takeadoe on 30 Jun 2008 11:19 On Jun 30, 11:06 am, MLH <C...(a)NorthState.net> wrote: > your primary keyfield is likely causing the order of the records > to be displayed, by default, in some order OTHER than that > in which the records were entered. Best use a query to reorder > the records in the order that suits your needs. > > Then, anytime you wish to refer to records in the table, > refer to them indirectly by use of the query used to order > them the way you like 'em. > > Contrary to what beginners think, it is NOT important that > "records stay in the order in which they were added". > The fact that they STAY there is enough. I would com- > plain if they didn't stay there. Reordering them is painless > and instantaneous. You're not dealing with a shoebox > full of paper receipts here. It's a database. Sorting a shoe > box by hand is a job. Running a query to sort records > quickly is not. > xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx > > On Mon, 30 Jun 2008 07:56:53 -0700 (PDT), Takeadoe > > > > <mtonkov...(a)msn.com> wrote: > >Greetings, > > >I'm hoping that someone can help me understand the method behind MS > >Access' ordering of records within a table. It does not appear that > >the records added last are always at the bottom when I open the > >Table. It is important that the records stay in the order in which > >they were added to the table. They clearly are not, but I can't seem > >to figure out why the records are ending up in the order that they > >are. Can anyone shed some light on this? > > >Thank you very much! > > >Mike- Hide quoted text - > > - Show quoted text - MLH - Thanks for the quick and thorough reply. Everything you noted makes sense. I left out a couple of details, however. There are some incomplete records being entered and as it turns out, they are missing the value that I would be sorting on. Long story short, the records in this table in question were placed there by another software package. The records are scanned in batches and the records within each batch are automatically assigned a page number. However, when data is added to the table by hand, there are no batch and page number values. Therefore, their order in the database (bottom of the table) tells me what I need to know about the order in which they were added. Also, I have no primary key, at least as far as I can tell in the design view. Finally, order is important because records that are added together have something in common with each other and when the records are missing values for certain fields, I can use this ordering to help populate some missing fields. Mike
From: MLH on 30 Jun 2008 11:22 On Mon, 30 Jun 2008 08:19:17 -0700 (PDT), Takeadoe <mtonkovich(a)msn.com> wrote: >On Jun 30, 11:06�am, MLH <C...(a)NorthState.net> wrote: >> your primary keyfield is likely causing the order of the records >> to be displayed, by default, in some order OTHER than that >> in which the records were entered. Best use a query to reorder >> the records in the order that suits your needs. >> >> Then, anytime you wish to refer to records in the table, >> refer to them indirectly by use of the query used to order >> them the way you like 'em. >> >> Contrary to what beginners think, it is NOT important that >> "records stay in the order in which they were added". >> The fact that they STAY there is enough. I would com- >> plain if they didn't stay there. Reordering them is painless >> and instantaneous. You're not dealing with a shoebox >> full of paper receipts here. It's a database. Sorting a shoe >> box by hand is a job. Running a query to sort records >> quickly is not. >> xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx >> >> On Mon, 30 Jun 2008 07:56:53 -0700 (PDT), Takeadoe >> >> >> >> <mtonkov...(a)msn.com> wrote: >> >Greetings, >> >> >I'm hoping that someone can help me understand the method behind MS >> >Access' ordering of records within a table. �It does not appear that >> >the records added last are always at the bottom when I open the >> >Table. �It is important that the records stay in the order in which >> >they were added to the table. �They clearly are not, but I can't seem >> >to figure out why the records are ending up in the order that they >> >are. �Can anyone shed some light on this? >> >> >Thank you very much! >> >> >Mike- Hide quoted text - >> >> - Show quoted text - > >MLH - Thanks for the quick and thorough reply. Everything you noted >makes sense. I left out a couple of details, however. There are some >incomplete records being entered and as it turns out, they are missing >the value that I would be sorting on. Long story short, the records >in this table in question were placed there by another software >package. The records are scanned in batches and the records within >each batch are automatically assigned a page number. However, when >data is added to the table by hand, there are no batch and page number >values. Therefore, their order in the database (bottom of the table) >tells me what I need to know about the order in which they were >added. Also, I have no primary key, at least as far as I can tell in >the design view. Finally, order is important because records that are >added together have something in common with each other and when the >records are missing values for certain fields, I can use this ordering >to help populate some missing fields. > >Mike Perhaps adding a field [TDStamp] with a default value of Now() would provide the missing element you need. People who enter records by hand would not have to remember to fill in this field's value. Would be done automatically. Later, sorting on the field would virtually ensure your ability to reproduce the specific order you seek. Hope it works for you.
From: Takeadoe on 30 Jun 2008 11:32
On Jun 30, 11:22 am, MLH <C...(a)NorthState.net> wrote: > On Mon, 30 Jun 2008 08:19:17 -0700 (PDT), Takeadoe > > > > > > <mtonkov...(a)msn.com> wrote: > >On Jun 30, 11:06 am, MLH <C...(a)NorthState.net> wrote: > >> your primary keyfield is likely causing the order of the records > >> to be displayed, by default, in some order OTHER than that > >> in which the records were entered. Best use a query to reorder > >> the records in the order that suits your needs. > > >> Then, anytime you wish to refer to records in the table, > >> refer to them indirectly by use of the query used to order > >> them the way you like 'em. > > >> Contrary to what beginners think, it is NOT important that > >> "records stay in the order in which they were added". > >> The fact that they STAY there is enough. I would com- > >> plain if they didn't stay there. Reordering them is painless > >> and instantaneous. You're not dealing with a shoebox > >> full of paper receipts here. It's a database. Sorting a shoe > >> box by hand is a job. Running a query to sort records > >> quickly is not. > >> xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx > > >> On Mon, 30 Jun 2008 07:56:53 -0700 (PDT), Takeadoe > > >> <mtonkov...(a)msn.com> wrote: > >> >Greetings, > > >> >I'm hoping that someone can help me understand the method behind MS > >> >Access' ordering of records within a table. It does not appear that > >> >the records added last are always at the bottom when I open the > >> >Table. It is important that the records stay in the order in which > >> >they were added to the table. They clearly are not, but I can't seem > >> >to figure out why the records are ending up in the order that they > >> >are. Can anyone shed some light on this? > > >> >Thank you very much! > > >> >Mike- Hide quoted text - > > >> - Show quoted text - > > >MLH - Thanks for the quick and thorough reply. Everything you noted > >makes sense. I left out a couple of details, however. There are some > >incomplete records being entered and as it turns out, they are missing > >the value that I would be sorting on. Long story short, the records > >in this table in question were placed there by another software > >package. The records are scanned in batches and the records within > >each batch are automatically assigned a page number. However, when > >data is added to the table by hand, there are no batch and page number > >values. Therefore, their order in the database (bottom of the table) > >tells me what I need to know about the order in which they were > >added. Also, I have no primary key, at least as far as I can tell in > >the design view. Finally, order is important because records that are > >added together have something in common with each other and when the > >records are missing values for certain fields, I can use this ordering > >to help populate some missing fields. > > >Mike > > Perhaps adding a field [TDStamp] with a default value of Now() would > provide the missing element you need. People who enter records by > hand would not have to remember to fill in this field's value. Would > be done automatically. Later, sorting on the field would virtually > ensure your ability to reproduce the specific order you seek. Hope > it works for you.- Hide quoted text - > > - Show quoted text - I think that sounds perfect! Thanks again for all of your help. Mike |