Moving table contents

Creating tables and queries

Moving table contents

Postby ertjeffrey » Sun Dec 26, 2010 5:17 am

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
ertjeffrey
 
Posts: 6
Joined: Wed Dec 22, 2010 6:15 am

Re: Moving table contents

Postby therabi » Sun Dec 26, 2010 5:49 am

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
User avatar
therabi
Volunteer
 
Posts: 763
Joined: Wed Sep 01, 2010 10:01 pm
Location: USA

Re: Moving table contents

Postby ertjeffrey » Sun Dec 26, 2010 7:26 pm

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
ertjeffrey
 
Posts: 6
Joined: Wed Dec 22, 2010 6:15 am

Re: Moving table contents

Postby therabi » Sun Dec 26, 2010 7:37 pm

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
User avatar
therabi
Volunteer
 
Posts: 763
Joined: Wed Sep 01, 2010 10:01 pm
Location: USA

Re: Moving table contents

Postby rudolfo » Sun Dec 26, 2010 8:28 pm

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   Expand viewCollapse view
+-------------+-----------+-----------+--------+
| 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.
rudolfo
Volunteer
 
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Moving table contents

Postby Sliderule » Sun Dec 26, 2010 10:26 pm

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.html#alter_table-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   Expand viewCollapse view
    -- 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.
User avatar
Sliderule
Volunteer
 
Posts: 1165
Joined: Thu Nov 29, 2007 9:46 am

Re: Moving table contents

Postby ertjeffrey » Mon Dec 27, 2010 1:47 am

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
ertjeffrey
 
Posts: 6
Joined: Wed Dec 22, 2010 6:15 am

Re: Moving table contents

Postby Sliderule » Mon Dec 27, 2010 1:59 am

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


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 3 guests