[Solved] Insert new field between existing fields
[Solved] Insert new field between existing fields
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?
Last edited by HCallahan on Fri Nov 09, 2018 7:00 pm, edited 1 time in total.
OpenOffice 4.1.5 on Mac Mojave
Re: insert new field between existing fields
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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Re: insert new field between existing fields
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?
OpenOffice 4.1.5 on Mac Mojave
Re: insert new field between existing fields
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?
OpenOffice 4.1.5 on Mac Mojave
Re: insert new field between existing fields
One way to accomplish what you want is: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?
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.
- 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".
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!!!
Sliderule THANK YOU
OpenOffice 4.1.5 on Mac Mojave