Moving table contents

Creating tables and queries
Post Reply
ertjeffrey
Posts: 6
Joined: Wed Dec 22, 2010 6:15 am

Moving table contents

Post by ertjeffrey »

When creating a table how can I move all cells down one block? For example whenever I hit "Insert Rows" it just puts the new one at the bottom. How can I get it to insert a row in the middle of the table?
Open Office Version 3.1 on Windows 7 32bit
User avatar
therabi
Volunteer
Posts: 763
Joined: Wed Sep 01, 2010 10:01 pm
Location: USA

Re: Moving table contents

Post by therabi »

Select the row where you want the row inserted, place the mouse pointer to the right of this row. The pointer should change to an arrow pointing to the row, a pop-up saying "Select table row". Right click, in the context menu that opens select Row> Insert... , in the pop-up enter the number of rows to be inserted.

HTH
OpenOffice.org v3.3, LibO v3.32 on Ubuntu 10.10 and Win7
ertjeffrey
Posts: 6
Joined: Wed Dec 22, 2010 6:15 am

Re: Moving table contents

Post by ertjeffrey »

Not working as you described it...

Little more information regarding what I want to do:

Example of my Table
First Name
Company Name
Phone Number
Last Name

1) I want to move "Last Name" to below "First Name"
2) I would like to insert "Address" between "Company Name" and "Phone Number"

Hope this helps
Open Office Version 3.1 on Windows 7 32bit
User avatar
therabi
Volunteer
Posts: 763
Joined: Wed Sep 01, 2010 10:01 pm
Location: USA

Re: Moving table contents

Post by therabi »

Please ignore me. I am not looking at where this was posted. I was thinking in the Writer area. Sorry for the confusion. I will go away now.

Sorry.
OpenOffice.org v3.3, LibO v3.32 on Ubuntu 10.10 and Win7
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Moving table contents

Post by rudolfo »

In the context of database tables what you describe in your second post as example is a tupel not a table. A database table is something that follows the dimensions N x M. Your tupel is 1 x M. It describes the attributes of the address data of one person. Typically the attributes of something will be represented by one row or record in a database table. You will have a rather fixed number of M attributes for all your N Persons. N can be increased easily, as inserting more records into a database is a common task. Addding new attributes is more complex as it requires a change in the structure of the database table.
Okay, this might sound very theoretical. So I show you a more practical attempt: Base allows you to open some standard databases including the address book of the most common email programs MS Outlook and Mozilla Thunderbird.
Start OpenOffice, choose New -> Database
Tick the last of the three choices (Connect to an existing database)
Choose the address book of your email programm
Click Next and Finish (you will be ask for a database name -- this is a filename, you might name it my-addressbook or someting similar.

Note: "Register the database" means to make this database easily available to all OpenOffice Programs (typically a address database would be useful for creating address labels in Writer)

Click on tables to see how these typical and optimized kind of address tables are organized. As they follow a "Handle all different address standards in different countries" this table has a lot of columns (attributes in my parlance from above). Too keep a good overview you will surely pick out the most importatnt columns. For this you create a query in Base. In a query you can define which columns you want to see (first_name, last_name, city, phone, email, company) and in which order you want to see them. But it is always one person/address in one row like:

Code: Select all

+-------------+-----------+-----------+--------+
| first_name  | last_name | city      | phone  |
+-------------+-----------+-----------+--------+
| Christopher | Columbus  | Venice    |        |
| John        | Wayne     | Hollywood |  ..... |
+-------------+-----------+-----------+--------+
If you really want to have your address data in the way you described it

Christopher
Columbus
Venice

you should rather use a spreadsheet and not a database table. But believe me, there are good reason why he standard addressbooks are integrated into a OOo Base document and not imported into a OOo Calc spreadsheet. So give yourself some time and learn a bit about databases.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Moving table contents

Post by Sliderule »

ertjeffrey :

If I understand your question . . . what you want is to 'rearrange' the Column ( sometimes called Field ) sequence in your Table.

I will assume . . . since you did NOT say . . . that you are using the OpenOffice 'default' database . . . you can 'confirm' this after opening your OpenOffice Base file ( *.odb ) and if on the status bar at the bottom it says: Embedded database and HSQL database engine . . . in that case . . . the following should accomplish your task.

But before any further explanation . . . the HSQL DOCUMENTATION . . . is found at:

http://www.hsqldb.org/doc/guide/ch09.ht ... le-section

Now, the commands below are NOT available in the OpenOffice Base GUI ( Graphical User Interface ) . . . but . . . may be accomplished by issuing some commands DIRECTLY to the database engine. In all of the examples below . . . I am assuming your Table name is . . . "MyTable" . . . and . . . the current fields in your table are:
  1. "First Name"
  2. "Company Name"
  3. "Phone Number"
  4. "Last Name"
and you desire your table "MyTable" layout to be defined as:
  1. "First Name"
  2. "Last Name"
  3. "Company Name"
  4. "Address"
  5. "Phone Number"
OK, that is fine, and, yes, it can be accomplished.

The 'steps' would be:
  1. Make a BACKUP copy of your OpenOffice Base file ( *.odb ) . . . this is always an IMPORTANT procedure prior to making database modifications :bravo:
  2. Open your OpenOffice Base file ( *.odb )
  3. Click on Tables under Database
  4. From the Menu:

    Tools -> SQL...
  5. In the Command to execute box . . . copy and paste the following, BUT, you will have CHANGE "MyTable" to your REAL table name . . . including CASE ( UPPER, Mixed, lower ), since, "MyTable" is NOT the same as "mytable" :

    Code: Select all

    -- Comment  . . . ADD a NEW column in the desired location
    Alter Table "MyTable" Add Column "Last Name New" VARCHAR_IGNORECASE(50) BEFORE "Company Name";
    
    -- Comment . . . Populate the NEW COLUMN with the contents of the original column
    Update "MyTable" Set "Last Nmae New" = "Last Name";
    
    -- Comment . . . Remove the ORIGINAL column from the table
    Alter Table "MyTable" Drop Column "Last Name";
    
    -- Comment . . . Change the name of the NEW column like it was in the ORIGINAL
    Alter Table "MyTable" Alter Column "Last Name New" RENAME TO "Last Name";
    
    -- Comment . . . ADD a NEWcolumn in the desired location 
    Alter Table "MyTable" Add Column "Address" VARCHAR_IGNORECASE(100) BEFORE "Phone Number";
    
    -- Comment . . . Shrink the size of the database to recover space used from dropped column
    Checkpoint Defrag;
    
    
  6. Press the Execute button
  7. Press the OK button
  8. Press the Close button
  9. From the Menu:

    View -> Refresh Tables
  10. Of course, you will want to SAVE your OpenOffice Base file.
  11. Now say:

    "Gee Sliderule, that was easier than climbing Mt Kilamangro." :crazy:

    Now, all I have left to do is . . . mark this issue as [Solved] for the forum.
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved. in your first post Title ( edit button ) if your issue has been fixed / resolved.
ertjeffrey
Posts: 6
Joined: Wed Dec 22, 2010 6:15 am

Re: Moving table contents

Post by ertjeffrey »

Getting closer, but not yet...

If I open the Table it shows the updates.
If I go to edit the same Table it does not show the updates.

Thanks so far everyone
Open Office Version 3.1 on Windows 7 32bit
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Moving table contents

Post by Sliderule »

Did you perform step #9 above ( the reason is the DIRECT updates -- table definitions are sent to the database SCHEMA, but, OpenOffice only 'rereads' the database SCHEMA when it starts / opens the database, OR, when you DIRECTLY tell it to do this . . . from the Menu: View -> Refresh Tables ) . . . and . . . SAVE your OpenOffice Base file?

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Post Reply