Access should allow field names to be alphabetized

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.
--
Dave Hargis, Microsoft Access MVP


"Jamie Collins" wrote:

On Sep 22, 5:49 pm, "Douglas J. Steele"
wrote:
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

--

the table is physically stored in PK order

Are you sure? Or is it they are returned in PK order unless otherwise
specified?
--
Dave Hargis, Microsoft Access MVP


"David W. Fenton" wrote:

"George Nicholson" wrote in
:

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

On Sep 26, 7:52 pm, Klatuu wrote:
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.

--

On Sep 26, 7:52 pm, Klatuu wrote:
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.

--

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?

"Jamie Collins" wrote in message
ups.com...
On Sep 26, 7:52 pm, Klatuu wrote:
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.

--

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


"Jamie Collins" wrote:

On Sep 26, 7:52 pm, Klatuu wrote:
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.

--

On Sep 27, 2:04 pm, "BruceM" wrote:
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.

--

On Sep 27, 2:39 pm, Klatuu wrote:
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.

--