Page 1 of 1
[Solved] Insert new field between existing fields
Posted: Fri Nov 09, 2018 4:25 pm
by HCallahan
I've looked everywhere. I have created a table, entered data, but forgot to put the zip code field in. I need to put the Zip field in after the state and before phone number. I've tried everything except creating a new table. Can this be done?
Re: insert new field between existing fields
Posted: Fri Nov 09, 2018 5:56 pm
by eremmel
For a database the location of the columns are not important. Just add it to the end. Use SELECT statement to query the table and put the fields in the right order.
Re: insert new field between existing fields
Posted: Fri Nov 09, 2018 6:00 pm
by HCallahan
I new to doing this. Are you saying that once I do the query the order will be changed in the table the next time I access?
Re: insert new field between existing fields
Posted: Fri Nov 09, 2018 6:09 pm
by HCallahan
I tried adding "zip code" to the database and it tells me I cannot quit and save because "zip code" already exists, which it doesn't. I don't see SELECT. Where is it located?
Re: insert new field between existing fields
Posted: Fri Nov 09, 2018 6:41 pm
by Sliderule
HCallahan wrote:I've looked everywhere. I have created a table, entered data, but forgot to put the zip code field in. I need to put the Zip field in after the state and before phone number. I've tried everything except creating a new table. Can this be done?
One way to accomplish what you want is:
Assuming you want to add a new column
ZIP to your table
"My_Table" before the column
"Phone_Number" . . . you should follow these steps:
- Open your Open Office / Libre Office Base file ( *.odb )
- From the Menu: Tools -> SQL...
- In the Command to execute box, enter:
Code: Select all
ALTER TABLE "My_Table" ADD COLUMN "ZIP" VARCHAR(5) BEFORE "Phone_Number"
- Press the Execute button
- Press the Close button
- From the Menu: View -> Refresh Tables
- Smile and say: "That was easy, now all I have to do is let the forum know the issue is resolved.
Explanation:
- The above SQL command will add the new column with the defined type ( for example VARCHAR(5) prior to the column "Phone_Number" .
- It is CRITICAL that each item between double quotes are defined exactly the way you have defined them in your database, including CASE: UPPER, Mixed, lower. For example, mixed case, "Phone_Number" is NOT the same as UPPER case "PHONE_NUMBER".
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add
[Solved] in your
1st post Subject (edit button top right) if this issue has been resolved.
Re: insert new field between existing fields - SOLVED!!!
Posted: Fri Nov 09, 2018 7:01 pm
by HCallahan
Sliderule THANK YOU