Access should allow field names to be alphabetized

[Norman Simoneau from ProSo.Net]

When in Table Design please add a way to alphabetize the fieldname list.
It's very tedious to manually move the fields up /down one by one to sort the
fields in the table design. I would like the fields to stay sorted when
viewed in table view. Thank you.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...55a&dg=microsoft.public.access.tablesdbdesign

 

[Douglas J. Steele]

Why? What difference does it make what order the fields are in a table? You
shouldn't be working directly with the tables.

You can always create a query that has the fields in whatever order you
want, and use the query rather than the table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


"Norman Simoneau from ProSo.Net"

 

[Norman Simoneau from ProSo.Net]

I want the query to automatically have the order when I add "*" for all
fields so I do not have to alphabetize it then too.

 

[Rick Brandt]

I want the query to automatically have the order when I add "*" for
all fields so I do not have to alphabetize it then too.

But there is absolutely no "real" reason for fields to be in alphabetical order
in a table or query since they should never be used to look at the data.

 

[tina]

since field names are completely subjective, automatic sorting would be a
pain for most developers anyway. i mean, when i'm adding fields to a table,
it might be handy for FirstName to automatically move ahead of LastName, but
do i want MiddleInitial to automatically move *after* LastName - not really!
as Rick and Doug pointed out, field order shouldn't make a difference at the
table or query level - except as a convenience in Design view, where a
*logical* order can help the developer find fields more quickly during
development. but Access can't possibly impose that logical order, because it
will be based on the purpose of the specific table in the specific
database - not to mention the preferences of the specific developer.

hth


"Norman Simoneau from ProSo.Net"

 

[David W. Fenton]

=?Utf-8?B?Tm9ybWFuIFNpbW9uZWF1IGZyb20gUHJvU28uTmV0?=

When in Table Design please add a way to alphabetize the fieldname
list.

This request suggest to me that you have a denormalized data
structure, fields named with repeating values, such as MyField01,
MyField02 and so forth. That would indicate a design error on your
part, and thus would negate the value of your suggestion.

I can't think of any reason why alphabetizing fieldnames would be
useful, except in a picklist, perhaps. And in a combo box populated
with a field list, I think it's pretty easy to find the field you
want using the AutoComplete feature.

 

[George Nicholson]

I have a vague recollection that having your PrimaryKey as the first (or
close to first?) field has a (beneficial) impact on performance.

Is my memory playing games with me again?

 

[Guest]

I haven't heard anything about that in Jet 4, but then again,
who cared about performance with Jet 4?

There were certainly problems about using the DAO methods
to add fields to a table, which led to performance problems with
the last fields in a table, but that was only in one version, and a
long time ago. Didn't the reported performance problems with
field names that had the same starting characters turn out to be
the same problem - an artefact of the test procedure and the DAO
createfield problem?

(david)

I have a vague recollection that having your PrimaryKey as the first (or
close to first?) field has a (beneficial) impact on performance.

Is my memory playing games with me again?

 

[David W. Fenton]

I have a vague recollection that having your PrimaryKey as the
first (or close to first?) field has a (beneficial) impact on
performance.

Is my memory playing games with me again?

I don't know that your memory is working or not, but I have never
heard any such thing. In a Jet db, the table is physically stored in
PK order, but it wouldn't matter, I think, whether the PK is first,
last or somewhere in the middle.

 

[Jamie Collins]

Why? What difference does it make what order the fields are in a table?

If you want an example where it makes a difference, here's one:

ACC2000: Can't Create Relationship with Multiple-Field Primary Key
http://support.microsoft.com/kb/208353
"The order of the primary key fields in Design view of the table is
different from the order of the fields in the PrimaryKey index."

Of course, if you create your tables using SQL DDL there's no issue...

You
shouldn't be working directly with the tables.

In Design view? So you agree that SQL DDL is the way to go? ;-)

Jamie

--

 

[Klatuu]

the table is physically stored in PK order

Are you sure? Or is it they are returned in PK order unless otherwise
specified?

 

[Klatuu]

You're nit picking Jamie, the answer is in the site you referred:
Order the field names in the Relationships dialog box to match the order of
the field names in the PrimaryKey index.

 

[Jamie Collins]

You're nit picking Jamie

I thought that's what you did with little kids' hair when they've been
in school a few weeks? (my revenge for those tulips you sent me
earlier <g>)

The OP's request misses the point of the SQL language i.e.

SELECT A, B, C
FROM MyTable
ORDER BY 1;

is semantically equivalent to

SELECT B, C, A
FROM MyTable
ORDER BY 3;

So the OP must surely be talking in terms of visual presentation in
the Access user interface. In that context, I tried to address the
*respondent's* question, "What difference does it make what order the
fields are in a table [in the Access interface]?"

[Was that a rhetorical question i.e. a waste of my time trying to
answer? I'm kinda dumb with that stuff; I place a lot of faith in
human nature and easily get hurt and fall for practical jokes as a
result.]

I think you'll find George Nicholson tried to do the same in this
thread: "I have a vague recollection that having your PrimaryKey as
the first (or close to first?) field has a (beneficial) impact on
performance." This sounded familiar to me too so I did a bit of
digging but found nothing on this but did find the "Can't Create
Relationship" bug and you know the rest.

BTW I really don't know what to make of, "You shouldn't be working
directly with the tables." which just sounds ridiculous: you are an
Access user, you want to view the properties of a table's column, what
are you supposed to work with if not the table directly? You are an
Access user, you want to query the table, but you shouldn't actually
use the table...?"

Your charge sticks but I don't appear to be the only one ...

Jamie.

--

 

[Jamie Collins]

the table is physically stored in PK order

Are you sure?

There are many KB articles which confirm this e.g. see:

New Features in Microsoft Jet Version 3.0
http://support.microsoft.com/kb/137039
"Compacting the database now results in the indices being stored in a
clustered-index format. While the clustered index isn't maintained
until the next compact, performance is still improved...The new
clustered-key compact method is based on the primary key of the table.
New data entered will be in time order."

In practical terms, physical ordering on disk (clustering) will give
performance advantage to BETWEEN and GROUP BY constructs (because the
contiguous/equal values will be on contiguous pages) or improve
concurrency with a random autonumber (because values generated
chronologically are more likely to end up on disparate pages). Which
begs the question, why would you want to make your incrementing
autonumber column your PRIMARY KEY? Worst possible effect for
concurrency and when was the last time you used BETWEEN or GROUP BY on
a set of unique values?

Jamie.

--

 

[BruceM]

The part about "working directly with tables" refers, as you probably know,
to having the users open tables to work on data. They should be using forms
to work with the data, and (in general) reports to print. Of course it
doesn't refer to development.
In context of the discussion, I think Doug's point was that the alphabetic
order of fields in table design view is not a relevant design consideration.
Tina went on to point out that the field names are arbitrary, and that an
alphabetic listing is therefore arbitrary. She also metioned that a
developer may choose a logical (for purposes of the particular database)
order for convenience, but that the order could be based on any number of
considerations.
Perhaps it would be handy in some cases if Access could be made to list the
fields in alphabetical order or according to data type or whatever, but I
expect in most cases a developer will choose an order that makes sense for a
particular database. Of all the things I wish would be added to Access,
that one is very low on the list.
If the OP has so many fields that dragging them around in table design view
is a major hassle, maybe the database design needs a closer look. I think
somebody mentioned that, too.
Come on, Jamie, do you really think anybody was suggesting that a developer
shouldn't work with the table, or that a user shouldn't be able to construct
queries?

You're nit picking Jamie

I thought that's what you did with little kids' hair when they've been
in school a few weeks? (my revenge for those tulips you sent me
earlier <g>)

The OP's request misses the point of the SQL language i.e.

SELECT A, B, C
FROM MyTable
ORDER BY 1;

is semantically equivalent to

SELECT B, C, A
FROM MyTable
ORDER BY 3;

So the OP must surely be talking in terms of visual presentation in
the Access user interface. In that context, I tried to address the
*respondent's* question, "What difference does it make what order the
fields are in a table [in the Access interface]?"

[Was that a rhetorical question i.e. a waste of my time trying to
answer? I'm kinda dumb with that stuff; I place a lot of faith in
human nature and easily get hurt and fall for practical jokes as a
result.]

I think you'll find George Nicholson tried to do the same in this
thread: "I have a vague recollection that having your PrimaryKey as
the first (or close to first?) field has a (beneficial) impact on
performance." This sounded familiar to me too so I did a bit of
digging but found nothing on this but did find the "Can't Create
Relationship" bug and you know the rest.

BTW I really don't know what to make of, "You shouldn't be working
directly with the tables." which just sounds ridiculous: you are an
Access user, you want to view the properties of a table's column, what
are you supposed to work with if not the table directly? You are an
Access user, you want to query the table, but you shouldn't actually
use the table...?"

Your charge sticks but I don't appear to be the only one ...

Jamie.

 

[Klatuu]

No intent to offend Jamie. I meant it as a friendly dig.
BTW, send your kids to a better school
--
Dave Hargis, Microsoft Access MVP

You're nit picking Jamie

I thought that's what you did with little kids' hair when they've been
in school a few weeks? (my revenge for those tulips you sent me
earlier <g>)

The OP's request misses the point of the SQL language i.e.

SELECT A, B, C
FROM MyTable
ORDER BY 1;

is semantically equivalent to

SELECT B, C, A
FROM MyTable
ORDER BY 3;

So the OP must surely be talking in terms of visual presentation in
the Access user interface. In that context, I tried to address the
*respondent's* question, "What difference does it make what order the
fields are in a table [in the Access interface]?"

[Was that a rhetorical question i.e. a waste of my time trying to
answer? I'm kinda dumb with that stuff; I place a lot of faith in
human nature and easily get hurt and fall for practical jokes as a
result.]

I think you'll find George Nicholson tried to do the same in this
thread: "I have a vague recollection that having your PrimaryKey as
the first (or close to first?) field has a (beneficial) impact on
performance." This sounded familiar to me too so I did a bit of
digging but found nothing on this but did find the "Can't Create
Relationship" bug and you know the rest.

BTW I really don't know what to make of, "You shouldn't be working
directly with the tables." which just sounds ridiculous: you are an
Access user, you want to view the properties of a table's column, what
are you supposed to work with if not the table directly? You are an
Access user, you want to query the table, but you shouldn't actually
use the table...?"

Your charge sticks but I don't appear to be the only one ...

Jamie.

 

[Jamie Collins]

Come on, Jamie, do you really think anybody was suggesting that a developer
shouldn't work with the table, or that a user shouldn't be able to construct
queries?

Consider my classification of uses for Access: 1) a forms-based RAD
platform for data-centric applications using Jet (Forms, Reports); 2)
a management studio for the design of Jet database objects (tables,
Queries). There are variations on a theme but those are the main
ones.

It is clear to me that that the OP is talking about the latter
classification i.e. management studio for Jet tables. I think we can
safely assume they consider themselves as the 'developer'. In direct
response they are advised, "You shouldn't be working directly with the
tables." In all honesty, I can't come up with any interpretation in
context where the advice makes any sense.

Without getting to 'deep' (I noticed your wink <g>), one cannot really
work "directly with the tables" but the table merely a concept of SQL
DMBS.

The 'datasheet' view of a table in the Access interface** is an Access
Form with a recordset that queries the underlying SQL table and shows
the results in a grid; OK so it's an Access form provided by the
Access interface itself (e.g. you cannot change its design) but its
still merely a Form. The Design view of a table is merely a special
dialog categorizing displaying various details from the underlying
INFORMATION_SCHEMA VIEWs (or equivalent) in a common format --
consider that this could be for a linked table so clearly this isn't
the 'real' table either. Look much closer and we get into the realms
of ones-and-zeros and magnetic regions on cobalt-based alloy etc.

[** I try to remember to say "Access interface" rather than "Access
user interface" because some 'developers' don't see themselves as
'users'.]

I can think of a few things in the SQL language that rely on columns'
ordinal positions but none I would use in production code e.g.

SELECT *
FROM MyTable
ORDER BY 3;

INSERT INTO MyTable VALUES (1, 'Day', NOW());

Finally, a confession: I have my own home-made SQL management studio
and in the business objects can be found

Public Enum jcColumnOrderEnum
jcColumnOrderNotSpecified
jcColumnOrderOrdinalPosition
jcColumnOrderAlpha
End Enum

...so at some point I too must have wanted to see columns in alpha
order <g>! I think your comment about this being indicative of tables
with too many columns (likely denormalized) is spot on.

Jamie.

--

 

[Jamie Collins]

No intent to offend

None taken

BTW, send your kids to a better school

What, on the wage of a humble SQL coder sob sob. Anyway, isn't it only
the ones with *clean* hair they like...?

Jamie.

--