alphonsobasemore
asked on
How Do I Reorder The Fields Of A Table?
Hello Everyone,
I want to insert a field into a table and then I want to change the order of the field I inserted and another field. Technically all I want to do is reorder just two fields, because the field at the end of the table is just a primary key or identifier for each record; all the other fields are important and the order does matter. Therefore, I want the fields I add to be inserted before the last field or column in each table. I've looked over several postings related to this issue and I've found that most of the postings mentioned that this isn't possible, but one posting did mention that you could change the coding of the ALTER command like so:
ALTER TABLE the_table
CHANGE COLUMN email email VARCHAR(255) AFTER id;
, which was posted by VoteyDisciple in this posting: https://www.experts-exchange.com/questions/22074916/Ordering-fields.html
.
I was wondering if this posting is considered correct, a mistake or does this code refer to another sql engine other than MS Access, because I can't seem to get the code to work on my database and I just wanted to know if this code could be altered to accomplish what I desire or if there existed another technique to accomplish what I want.
Thanks
I want to insert a field into a table and then I want to change the order of the field I inserted and another field. Technically all I want to do is reorder just two fields, because the field at the end of the table is just a primary key or identifier for each record; all the other fields are important and the order does matter. Therefore, I want the fields I add to be inserted before the last field or column in each table. I've looked over several postings related to this issue and I've found that most of the postings mentioned that this isn't possible, but one posting did mention that you could change the coding of the ALTER command like so:
ALTER TABLE the_table
CHANGE COLUMN email email VARCHAR(255) AFTER id;
, which was posted by VoteyDisciple in this posting: https://www.experts-exchange.com/questions/22074916/Ordering-fields.html
.
I was wondering if this posting is considered correct, a mistake or does this code refer to another sql engine other than MS Access, because I can't seem to get the code to work on my database and I just wanted to know if this code could be altered to accomplish what I desire or if there existed another technique to accomplish what I want.
Thanks
Just open the design view of the table. You can select fields to drag-and-drop in any order you want.
You cannot, and should never need to, change the order of the fields in the table definition, at least not in code. You can always retrieve the fields in ANY ORDER you want, by naming the fields in the order you need, in an SQL Query statement.
Fields in The table (YourTable): Field1, Field2, Filed3, Field4
in the Query: Select Field4, Field2, Field1, Field3 from YourTable
MS Access does NOT allow you to change the order of the fields, using DDL such as that you show.
AW
Fields in The table (YourTable): Field1, Field2, Filed3, Field4
in the Query: Select Field4, Field2, Field1, Field3 from YourTable
MS Access does NOT allow you to change the order of the fields, using DDL such as that you show.
AW
if it really matters, that you want a certain order of fields in your table.
you can do this.
1. add the field using the alter table statement
2. create a make table query, selecting the the fields in the order of you want.
3. delete the original table
4. rename the new table
IMHO, it is not worth the effort.
my 2¢
you can do this.
1. add the field using the alter table statement
2. create a make table query, selecting the the fields in the order of you want.
3. delete the original table
4. rename the new table
IMHO, it is not worth the effort.
my 2¢
You know you can create a new table from an old table with the fields in any order.
Select fld4, fld3, fld2, fld1 INTO newTable FROM oldTable;
Select fld4, fld3, fld2, fld1 INTO newTable FROM oldTable;
ASKER
I believe that Arthur Wood is right! There is no way to really change the order of the fields in the table definitions.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ah well, too slow
ASKER
I'll try that Rocki Roads!
ASKER
Thanks Rocki Roads!
That OrdinalPositioning technique seems to solve the problem! Thank you again! I forgat how to rescore or regrade blogs. If someone knows how to please post it so I can give Rocki Roads the credit for his answer.
That OrdinalPositioning technique seems to solve the problem! Thank you again! I forgat how to rescore or regrade blogs. If someone knows how to please post it so I can give Rocki Roads the credit for his answer.
No worries. The question was open when I was typing my post. U must of accepted whilst I was typing.
You can request questions to be reopened if u want to redistribute points by posting a quesion in community support.
You can request questions to be reopened if u want to redistribute points by posting a quesion in community support.
ASKER
Thanks for helping me regrade this posting the answer worked perfectly!
thanks and cheers for points/grade also
To 're-order' fields in code you would have to delete and add the same field. The added field would then be at the 'end' of the table.
But it really should NOT matter about the field order.
It might be nice to have then in a particular sequence but it doesn't have any importance in what you do in your application.
Of course you can change the order in the table definition screen just by dragging theme field to the position you want it.
Pete