Why do queries automaticly rearange columns?

  • Thread starter Thread starter Frank1927
  • Start date Start date
F

Frank1927

Why are columns are in a different sequence each time I run certain queries
and how can I stop it?
 
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
 
For a query, in Jet, the alias has to be to the left of any
computed column using that alias
Click to expand...

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

- Steve
 
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:
 
The example I used, in Northwind:

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



Vanderghast, Access MVP
 
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
 
Back
Top