moving columns in table...

[saraua]

I have a massive table with a lot of queries built off this table. I would
like to rearrange the order of the columns in this table, but want to know if
I do so, will I then have to change every query that uses this table? I am
mostly concerned about update queries.
thanks,

 

[Clifford Bass]

Hi,

You will be fine rearranging the order of columns. Udates are done to
columns based on their names, not position. You can verify this by viewing
the SQL of the update queries. You will see something like this:

update tblSomeTable
set SomeColumn = "Some Value"
where KeyColumn = "Other Value";

Where you may have a problem is if you have written your own insert
(append) queries and not specified the column names. Append queries created
by the query tool would look something like this:

insert into tblSomeTable
(KeyColumn, SomeColumn)
values
("Key Value", "Other Value");

or

insert into tblSomeTable
(KeyColumn, SomeColumn)
select ColumnA, ColumnB
from tblOtherTable;

As such, column order is not important. You can do an insert that does
not include the colum list as long as you have matched the data to insert
with the current columns in the table.

insert into tblSomeTable
values
("Key Value", "Other Value");

or

insert into tblSomeTable
select ColumnA, ColumnB
from tblOtherTable;

So, have at it. But first make a backup of your database just in case
of problems.

Clifford Bass

 

[Jeff Boyce]

As I recall, simply moving the columns/fields doesn't affect the names of
those columns. The queries use the names, not the absolute position.

But you've aroused my curiosity... Since Access tables are really only
intended to store data, why does it matter what the order of columns is?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Gina Whipp]

saraua,

Not sure why you want to move columns around in the table unless you are...
and I really hope not... using the tables for data entry. However, the
*order* in which you have columns is of no concern to your queries UNLESS
you also want them in the same order??? The ONLY issue would be is if you
decided to change a field name in your table.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[John Spencer]

No, moving the fields around in the table will not have change how the queries
work. Unless you are using the * option to show all fields, it won't even
change the order that the fields are displayed.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[saraua]

Thanks everyone. Even though the data is in a table and yes, I can run
queries in any column order I like, my team still looks at the data in this
table and it would be easier if data was in a logical order.

 

[Jeff Boyce]

I was afraid that was the case...

Access tables store data. Access forms display data.

Giving your users a way to 'muck about' in the raw data in the tables is
asking for them to (accidentally) mess something up. They may want to, and
may be accustomed to, working directly with the data (think spreadsheet),
but this is not a safe use of a relational database.

Good luck!

--

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

Thanks everyone. Even though the data is in a table and yes, I can run
queries in any column order I like, my team still looks at the data in this
table and it would be easier if data was in a logical order.

You can give them a Query which looks, feels, and works exactly like a table,
with the fields in any order you want.

I agree with my colleagues that the users should not be in the table - or even
in a query - but I realize that it's not always possible.