Why do queries automaticly rearange columns?

[Frank1927]

Why are columns are in a different sequence each time I run certain queries
and how can I stop it?

 

[Michel Walsh]

Use a form. Controls won't move all around your form.

A table does not have to respect the 'column' ordering you use at their
creation time. For a query, in Jet, the alias has to be to the left of any
computed column using that alias, but other than that, columns 'may' move
around.

Don't use table or query to VIEW data for production (ok for debugging
though, but not for the final production): use a form.


Vanderghast, Access MVP

 

[Stephen K. Young]

For a query, in Jet, the alias has to be to the left of any
computed column using that alias

That is not true in my experience. The computed column can be anywhere
relative to the alias. Just tested it in Access 2003.

- Steve

 

[Michel Walsh]

Indeed, it seems the actual behavior is that you can define the alias at the
right of its use. On the other hand, now, if you use an alias having the
same name as a field, even if you are at the left of the alias definition,
you get the alias, not the field, neither an error:

 

[Michel Walsh]

The example I used, in Northwind:

SELECT CustomerID & Freight, CustomerID & Orders.Freight, "Hello" AS Freight
FROM Orders;



Vanderghast, Access MVP

 

[Stephen K. Young]

Well, I can see how that query would confuse Access. You have created an
alias name of "Freight" that is identical to the existing field name
Orders.Freight, so Access as no way to distinguish the alias. This has
nothing to do with left/right placement.

If you use a different alias name, Access has no difficulty figuring it out,
whether the alias is placed to the left or right.

The point being -- I believe that left/right placement has no effect on
using an alias in other calculated fields.

- Steve