[Solved] Insert new field between existing fields

Discuss the database features

[Solved] Insert new field between existing fields

Postby HCallahan » Fri Nov 09, 2018 4:25 pm

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
HCallahan
 
Posts: 4
Joined: Fri Nov 09, 2018 4:18 pm

Re: insert new field between existing fields

Postby eremmel » Fri Nov 09, 2018 5:56 pm

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.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1011
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: insert new field between existing fields

Postby HCallahan » Fri Nov 09, 2018 6:00 pm

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
HCallahan
 
Posts: 4
Joined: Fri Nov 09, 2018 4:18 pm

Re: insert new field between existing fields

Postby HCallahan » Fri Nov 09, 2018 6:09 pm

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
HCallahan
 
Posts: 4
Joined: Fri Nov 09, 2018 4:18 pm

Re: insert new field between existing fields

Postby Sliderule » Fri Nov 09, 2018 6:41 pm

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:

  1. Open your Open Office / Libre Office Base file ( *.odb )

  2. From the Menu: Tools -> SQL...

  3. In the Command to execute box, enter:

    Code: Select all   Expand viewCollapse view
    ALTER TABLE "My_Table" ADD COLUMN "ZIP" VARCHAR(5) BEFORE "Phone_Number"

  4. Press the Execute button

  5. Press the Close button

  6. From the Menu: View -> Refresh Tables

  7. Smile and say: "That was easy, now all I have to do is let the forum know the issue is resolved.
Explanation:
  1. The above SQL command will add the new column with the defined type ( for example VARCHAR(5) prior to the column "Phone_Number" .

  2. 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.
User avatar
Sliderule
Volunteer
 
Posts: 1151
Joined: Thu Nov 29, 2007 9:46 am

Re: insert new field between existing fields - SOLVED!!!

Postby HCallahan » Fri Nov 09, 2018 7:01 pm

Sliderule THANK YOU
OpenOffice 4.1.5 on Mac Mojave
HCallahan
 
Posts: 4
Joined: Fri Nov 09, 2018 4:18 pm


Return to Base

Who is online

Users browsing this forum: No registered users and 0 guests