A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Removing Sort Order?



 
 
Thread Tools Display Modes
  #1  
Old August 5th, 2006, 01:34 AM posted to microsoft.public.access.tablesdbdesign
Debris
external usenet poster
 
Posts: 31
Default Removing Sort Order?

Hello,

Hope I'm using my database design terms correctly, and that this make sense.

In a secondary table, I'm using the Lookup Wizard to look up a values in a
primary table. In the primary table, there is only one field, and that
field is the primary key (i.e, I'm not using AutoNum to create a numerical
primary key, I'm using the text itself as the key).

I entered my records in the primary table a specific order, but Access keeps
rearranging them in alphabetical order, and I don't want that. The Remove
Filter/Sort command doesn't seem to do the trick.

Example:

In the Ratings field of my ProductInformation Table, I choose from a list of
ratings found in the Ratings Table.
The choices are Excellent, Average, Poor, but Access immediately rearranges
my records to Average, Excellent, Poor (i.e. alphabetical), and this is the
order they appear in the drop-down menu in the ProductInformation Table.

Any suggestions?

Thanks,

D Bris


  #2  
Old August 5th, 2006, 02:13 AM posted to microsoft.public.access.tablesdbdesign
John Vinson
external usenet poster
 
Posts: 4,033
Default Removing Sort Order?

On Sat, 05 Aug 2006 00:34:23 GMT, "Debris"
wrote:

Hello,

Hope I'm using my database design terms correctly, and that this make sense.

In a secondary table, I'm using the Lookup Wizard to look up a values in a
primary table. In the primary table, there is only one field, and that
field is the primary key (i.e, I'm not using AutoNum to create a numerical
primary key, I'm using the text itself as the key).

I entered my records in the primary table a specific order, but Access keeps
rearranging them in alphabetical order, and I don't want that. The Remove
Filter/Sort command doesn't seem to do the trick.


A Table is displayed sorted in primary key order. It's actually STORED
in whatever order Access finds convenient. If you want the records in
a particular order, you must - no choice! - add another field to the
table as a sort key, and base your combo box on a query sorting by
this field.

Note that the Lookup Wizard is really limited and many developers
recommend against ever using it. See
http://www.mvps.org/access/lookupfields.htm for a critique. You can
use a Form with a combo box; table datasheets are *not* ideal for data
entry or editing.


John W. Vinson[MVP]
  #3  
Old August 5th, 2006, 02:17 AM posted to microsoft.public.access.tablesdbdesign
Rick Brandt
external usenet poster
 
Posts: 4,354
Default Removing Sort Order?

Debris wrote:
Hello,

Hope I'm using my database design terms correctly, and that this make
sense.
In a secondary table, I'm using the Lookup Wizard to look up a values
in a primary table. In the primary table, there is only one field,
and that field is the primary key (i.e, I'm not using AutoNum to
create a numerical primary key, I'm using the text itself as the key).

I entered my records in the primary table a specific order, but
Access keeps rearranging them in alphabetical order, and I don't want
that. The Remove Filter/Sort command doesn't seem to do the trick.


Tables have no guaranteed order and the default that you usually get is by the
PK value which is what you are getting. Add a number field and enter numbers
into it that you can sort on that give you the order you want.

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


  #4  
Old August 5th, 2006, 02:18 AM posted to microsoft.public.access.tablesdbdesign
Duane Hookom
external usenet poster
 
Posts: 2,251
Default Removing Sort Order?

If you want to view records in a specific order, add a field/value to the
table that allows you to specify the order.

--
Duane Hookom
MS Access MVP

"Debris" wrote in message
ink.net...
Hello,

Hope I'm using my database design terms correctly, and that this make
sense.

In a secondary table, I'm using the Lookup Wizard to look up a values in a
primary table. In the primary table, there is only one field, and that
field is the primary key (i.e, I'm not using AutoNum to create a numerical
primary key, I'm using the text itself as the key).

I entered my records in the primary table a specific order, but Access
keeps rearranging them in alphabetical order, and I don't want that. The
Remove Filter/Sort command doesn't seem to do the trick.

Example:

In the Ratings field of my ProductInformation Table, I choose from a list
of ratings found in the Ratings Table.
The choices are Excellent, Average, Poor, but Access immediately
rearranges my records to Average, Excellent, Poor (i.e. alphabetical), and
this is the order they appear in the drop-down menu in the
ProductInformation Table.

Any suggestions?

Thanks,

D Bris



  #5  
Old August 5th, 2006, 02:22 AM posted to microsoft.public.access.tablesdbdesign
tina
external usenet poster
 
Posts: 1,997
Default Removing Sort Order?

all primary key fields are indexed, so the values are going to be in a
specific order - since the field is Text data type, the order will be
alphabetic. if you want to *display* the values in an order of your own
choosing, add a SortBy field to the table with a data type of Number, field
size Byte. number each record in the order you want it sorted.

in a combo box control (in a form), you can set the RowSource property to

SELECT NameOfRatingsField FROM NameOfRatingsTable ORDER BY SortBy;

substitute the correct name of the ratings table, and the correct name of
the rating field in the table, of course.

and last but not least, i strongly recommend that you do not use a Lookup
field in any table in your database. see
http://www.mvps.org/access/lookupfields.htm for more information. you can
use a combo box control in a *form*, as noted above; data entry should be
done in forms anyway, not directly in tables.

hth


"Debris" wrote in message
ink.net...
Hello,

Hope I'm using my database design terms correctly, and that this make

sense.

In a secondary table, I'm using the Lookup Wizard to look up a values in a
primary table. In the primary table, there is only one field, and that
field is the primary key (i.e, I'm not using AutoNum to create a numerical
primary key, I'm using the text itself as the key).

I entered my records in the primary table a specific order, but Access

keeps
rearranging them in alphabetical order, and I don't want that. The Remove
Filter/Sort command doesn't seem to do the trick.

Example:

In the Ratings field of my ProductInformation Table, I choose from a list

of
ratings found in the Ratings Table.
The choices are Excellent, Average, Poor, but Access immediately

rearranges
my records to Average, Excellent, Poor (i.e. alphabetical), and this is

the
order they appear in the drop-down menu in the ProductInformation Table.

Any suggestions?

Thanks,

D Bris




  #6  
Old August 5th, 2006, 04:59 AM posted to microsoft.public.access.tablesdbdesign
Debris
external usenet poster
 
Posts: 31
Default Removing Sort Order?

Hello,

Thanks to everyone for their responses. I can feel myself wandering off
into the deep end of the pool, so to speak, so bear with me if these
questions are a bit simplistic.

First, a tactical question: should I add a "SortBy" number field, or should
I just let Access create a primary key for me using AutoNum? Or, should I
add the SortBy number field, populate it manually, and make *it* the PK?

Next, a philosophical one: totally agree w/ the use of forms and a combo
box. But, I guess I'm confused -- shouldn't the combo box be looking up
values stored in a table? I define a relationship between tables using the
Lookup Wizard, then modify it (i.e. enforce referential integrity) using the
Edit Relationships box. Am I totally off base here? Again, stepping off
into the the deep end...

Thanks,

D Bris


"John Vinson" wrote in message
...
On Sat, 05 Aug 2006 00:34:23 GMT, "Debris"
wrote:

Hello,

Hope I'm using my database design terms correctly, and that this make
sense.

In a secondary table, I'm using the Lookup Wizard to look up a values in a
primary table. In the primary table, there is only one field, and that
field is the primary key (i.e, I'm not using AutoNum to create a numerical
primary key, I'm using the text itself as the key).

I entered my records in the primary table a specific order, but Access
keeps
rearranging them in alphabetical order, and I don't want that. The Remove
Filter/Sort command doesn't seem to do the trick.


A Table is displayed sorted in primary key order. It's actually STORED
in whatever order Access finds convenient. If you want the records in
a particular order, you must - no choice! - add another field to the
table as a sort key, and base your combo box on a query sorting by
this field.

Note that the Lookup Wizard is really limited and many developers
recommend against ever using it. See
http://www.mvps.org/access/lookupfields.htm for a critique. You can
use a Form with a combo box; table datasheets are *not* ideal for data
entry or editing.


John W. Vinson[MVP]




  #7  
Old August 5th, 2006, 05:50 AM posted to microsoft.public.access.tablesdbdesign
tina
external usenet poster
 
Posts: 1,997
Default Removing Sort Order?

comments inline.

"Debris" wrote in message
ink.net...
Hello,

Thanks to everyone for their responses. I can feel myself wandering off
into the deep end of the pool, so to speak, so bear with me if these
questions are a bit simplistic.

First, a tactical question: should I add a "SortBy" number field, or

should
I just let Access create a primary key for me using AutoNum? Or, should I
add the SortBy number field, populate it manually, and make *it* the PK?


at some point in the future, you may add more selections to this list.
whether you use an Autonumber primary key, or manually populate a numeric
primary key, you're "locked in" to that number sequence - which may not
return the order you want, on either an ascending or descending sort. with a
separate SortBy field, you can change the sort order of the records at any
time, any way you choose, giving you complete control, and complete
flexibility to meet changing needs.


Next, a philosophical one: totally agree w/ the use of forms and a combo
box. But, I guess I'm confused -- shouldn't the combo box be looking up
values stored in a table?


yes.

I define a relationship between tables using the
Lookup Wizard, then modify it (i.e. enforce referential integrity) using

the
Edit Relationships box.


you don't need a Lookup Wizard to link your tables - just open the
Relationships window and do it manually, including enforcing referential
integrity. note: be careful about setting the CascadeDelete option. it
definitely has its' uses, but many times you will *not* want an automatic
deletion of "child" records; what you'll want, instead, is to disallow
deletion of a "parent" record when one or more child records exist. this is
accomplished by enforcing referential integrity and *not* checkmarking the
CascadeDelete option.

hth

Am I totally off base here? Again, stepping off
into the the deep end...

Thanks,

D Bris


"John Vinson" wrote in message
...
On Sat, 05 Aug 2006 00:34:23 GMT, "Debris"
wrote:

Hello,

Hope I'm using my database design terms correctly, and that this make
sense.

In a secondary table, I'm using the Lookup Wizard to look up a values in

a
primary table. In the primary table, there is only one field, and that
field is the primary key (i.e, I'm not using AutoNum to create a

numerical
primary key, I'm using the text itself as the key).

I entered my records in the primary table a specific order, but Access
keeps
rearranging them in alphabetical order, and I don't want that. The

Remove
Filter/Sort command doesn't seem to do the trick.


A Table is displayed sorted in primary key order. It's actually STORED
in whatever order Access finds convenient. If you want the records in
a particular order, you must - no choice! - add another field to the
table as a sort key, and base your combo box on a query sorting by
this field.

Note that the Lookup Wizard is really limited and many developers
recommend against ever using it. See
http://www.mvps.org/access/lookupfields.htm for a critique. You can
use a Form with a combo box; table datasheets are *not* ideal for data
entry or editing.


John W. Vinson[MVP]






  #8  
Old August 7th, 2006, 03:14 AM posted to microsoft.public.access.tablesdbdesign
Debris
external usenet poster
 
Posts: 31
Default Removing Sort Order?

Thanks. Excellent point -- I couldn't go back re-sort the list (especially
add an item between two existing items) if I were using AutoNum and letting
Access set the PK for me.

The SortOrder works great. Next question, though, is that on a *Report* I
can't seem to sort my categories with out also including / printing the
SortOrder field. I guess I could set the column width to zero, but that
seems sort of... whatever.

BTW, what I'm doing is extracting hundreds of transactions for a given
project, sorting them into several categories and subcategories (two
levels), and totalling up the transaction amounts for each cat/subcat. --
ie,

Phase 1 Engineering....$
Phase 1 Materials....$
Phase 1 Labor....$
Phase 2 Engineering....$
Phase 2 Materials....$

and so on. And I want the categories to appear in a certain
(non-alphabetical!) order, hence the original question.

Thanks



"tina" wrote in message
...
comments inline.

"Debris" wrote in message
ink.net...
Hello,

Thanks to everyone for their responses. I can feel myself wandering off
into the deep end of the pool, so to speak, so bear with me if these
questions are a bit simplistic.

First, a tactical question: should I add a "SortBy" number field, or

should
I just let Access create a primary key for me using AutoNum? Or, should
I
add the SortBy number field, populate it manually, and make *it* the PK?


at some point in the future, you may add more selections to this list.
whether you use an Autonumber primary key, or manually populate a numeric
primary key, you're "locked in" to that number sequence - which may not
return the order you want, on either an ascending or descending sort. with
a
separate SortBy field, you can change the sort order of the records at any
time, any way you choose, giving you complete control, and complete
flexibility to meet changing needs.


Next, a philosophical one: totally agree w/ the use of forms and a combo
box. But, I guess I'm confused -- shouldn't the combo box be looking up
values stored in a table?


yes.

I define a relationship between tables using the
Lookup Wizard, then modify it (i.e. enforce referential integrity) using

the
Edit Relationships box.


you don't need a Lookup Wizard to link your tables - just open the
Relationships window and do it manually, including enforcing referential
integrity. note: be careful about setting the CascadeDelete option. it
definitely has its' uses, but many times you will *not* want an automatic
deletion of "child" records; what you'll want, instead, is to disallow
deletion of a "parent" record when one or more child records exist. this
is
accomplished by enforcing referential integrity and *not* checkmarking the
CascadeDelete option.

hth

Am I totally off base here? Again, stepping off
into the the deep end...

Thanks,

D Bris


"John Vinson" wrote in message
...
On Sat, 05 Aug 2006 00:34:23 GMT, "Debris"
wrote:

Hello,

Hope I'm using my database design terms correctly, and that this make
sense.

In a secondary table, I'm using the Lookup Wizard to look up a values
in

a
primary table. In the primary table, there is only one field, and that
field is the primary key (i.e, I'm not using AutoNum to create a

numerical
primary key, I'm using the text itself as the key).

I entered my records in the primary table a specific order, but Access
keeps
rearranging them in alphabetical order, and I don't want that. The

Remove
Filter/Sort command doesn't seem to do the trick.

A Table is displayed sorted in primary key order. It's actually STORED
in whatever order Access finds convenient. If you want the records in
a particular order, you must - no choice! - add another field to the
table as a sort key, and base your combo box on a query sorting by
this field.

Note that the Lookup Wizard is really limited and many developers
recommend against ever using it. See
http://www.mvps.org/access/lookupfields.htm for a critique. You can
use a Form with a combo box; table datasheets are *not* ideal for data
entry or editing.


John W. Vinson[MVP]








  #9  
Old August 7th, 2006, 10:31 AM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default Removing Sort Order?


Debris wrote:
Hope I'm using my database design terms correctly, and that this make sense.

In a secondary table, I'm using the Lookup Wizard to look up a values in a
primary table. In the primary table, there is only one field


In the SQL standards and in the SQL literature, the 'primary' table is
called the referenced table and the 'secondary' table is the
referencing table; these can be considered the definitive terms. More
controversial is 'field': the literature and the purists will use
'columns' and 'rows' but the terms 'fields' and 'records' are so widely
used that they should be considered synonyms.

Jamie.

--

  #10  
Old August 7th, 2006, 05:32 PM posted to microsoft.public.access.tablesdbdesign
mnature
external usenet poster
 
Posts: 67
Default Removing Sort Order?

BTW, what I'm doing is extracting hundreds of transactions for a given
project, sorting them into several categories and subcategories (two
levels), and totalling up the transaction amounts for each cat/subcat. --


For a project, it would seem that an obvious field to use for sorting would
be a date field, such as the date that an item is received, or the date that
a particular job is completed. Then a sort on the date field would place any
new entry in its proper place chronologically on a list. A date field can
also be useful for sorting out what has not been completed.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sort Order On A report [email protected] Setting Up & Running Reports 2 February 3rd, 2006 05:54 PM
Need Suggestions, brain gone blank Gina Whipp Database Design 11 January 26th, 2006 01:19 PM
Need Suggestions, brain gone blank Gina Whipp Running & Setting Up Queries 11 January 26th, 2006 01:19 PM
I have two identical pivot tables with different sort order of th. WilliamJFoster General Discussion 1 April 7th, 2005 03:48 AM
X Axis Sort Order Jason Charts and Charting 2 October 9th, 2003 04:12 PM


All times are GMT +1. The time now is 05:42 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.