[Solved] Very basic table question - moving columns

Creating tables and queries
Post Reply
bwhitman
Posts: 4
Joined: Mon Apr 03, 2017 11:51 pm

[Solved] Very basic table question - moving columns

Post by bwhitman »

I built a table and now want to re-order some of the columns. How do I do that? I tried cutting and pasting in the design window, but they just went back to where they were originally.
Thank you,
Last edited by bwhitman on Mon May 15, 2017 6:12 pm, edited 1 time in total.
OpenOffice 4.1.1
Windows 7
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Very basic table question - moving columns

Post by Sliderule »

¿ Why would you want "to re-order some of the columns" ?

The order of the columns in a table has no logical impact on the table contents or use of the table.
  1. You could create a VIEW from your current table, and, have that VIEW in the display order you prefer.
  2. You could create a QUERY from your current table, and, have that QUERY in the display order you prefer.
  3. Issue SQL Commands to add new columns to the current table, with fake names, in the display order you prefer, UPDATE that table with the data from the table for each new column, DROP the original columns, and, RENAME the new columns to the desired column names.
  4. CREATE a new table, with the column names in the display order you want, and, drop the original table on the new table, and, MATCH the column names from the original table to the new table.
I hope that 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.
bwhitman
Posts: 4
Joined: Mon Apr 03, 2017 11:51 pm

Re: Very basic table question - moving columns

Post by bwhitman »

Thank you - I have to push this out to "reluctant" users and making everything look as much like their existing spreadsheet as possible will improve the odds of acceptance. How do you create a VIEW from the current table? If all else fails, I will create a new table but was hoping to avoid that.

Thanks,
OpenOffice 4.1.1
Windows 7
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Very basic table question - moving columns

Post by Sliderule »

bwhitman wrote:
How do you create a VIEW from the current table?
  1. Create a Query with the columns you want, displayed in the order you want.
  2. Save the Query.
  3. Right Click on the saved Query , and, from the drop-down menu, choose: Create as View
  4. Assign the name you want.
  5. The new VIEW will be 'listed' when you click on Tables icon on the left
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: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Very basic table question - moving columns

Post by Sliderule »

Perhaps another technique to accomplish what you want can be accomplished with the following steps:
  1. Open your OpenOffice / LibreOffice Base file ( *.odb )
  2. Click on the Tables icon on the left
  3. Using your mouse, click on the table you want to change the column order, and, while holding down the mouse drag it to a white area of the screen
  4. In the Copy table dialog, assign a NEW name for the table
  5. Under Options, use the radio button, Definition and data
  6. Click on the Next> button
  7. Choose the order of the columns you want from the Existing columns using the > button
  8. Click on the Next> button
  9. Click on the Create button
  10. You now have both the original table, and, the newly created table . . . so . . .
    1. Right click on your original table and choose Delete
    2. Right click on your original table and choose Rename so it is the name of the original table you just deleted
  11. From the Menu: Tools -> SQL ... and in the Command to execute box enter:

    Code: Select all

    checkpoint defrag
    Press the Execute button
    HSQL 1.8 Documentation [url]http://www.hsqldb.org/doc/1.8/guide/ch09.html#checkpoint-section[/url] wrote:
    CHECKPOINT

    CHECKPOINT [DEFRAG 2 ];

    Closes the database files, rewrites the script file, deletes the log file and opens the database.
    If DEFRAG is specified, this command also shrinks the .data file to its minimal size.
  12. Save your OpenOffice / LibreOffice database file ( *.odb )
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.
Post Reply