How To Reorder Fields in a Table?

MichaelCrawford · Posted: Mon Jun 23, 2008 9:04 am Post subject: How To Reorder Fields in a Table?

I have an address book in a database. But when I view its table, the fields (columns) are arranged left-to-right in the order I created them. This turns out to be somewhat random, and not at all what you'd want for data entry into the table.

For example I have an AutoField called Customer ID. It's in the middle somewhere. I'd like to make it the leftmost field.

One would think that I could drag the column header to a new position, and I get UI feedback as if this would work, but it doesn't.

Is there a way to rearrange my fields? Thanks!
_________________
Please download my Creative Commons piano compositions.

QuazzieEvil · Posted: Mon Jun 23, 2008 9:15 am Post subject:

The only ways I can think of are: 1) create a view or query, and add the columns in the order you want and 2) create a form with a Table grid, and add columns to the table grid in the order you want.

MichaelCrawford · Posted: Mon Jun 23, 2008 9:25 am Post subject:

Hey, thanks for your help.

For some reason I couldn't create a view at all. The Help says Views can only be created if the back end allows them - my database is one created by OOo itself, not an ODBC or what have you.

But creating a form with a table grid in it worked. It turned out that I still added a field in the wrong order, but the form wizard has a button that raises or lowers a field relative to the others. Works great!
_________________
Please download my Creative Commons piano compositions.

QuazzieEvil · Posted: Mon Jun 23, 2008 9:30 am Post subject:

when you are in design mode, you can move columns in a tablegrid by dragging them about.

VenturSum · Posted: Sun Jun 29, 2008 4:59 pm Post subject:

QuazzieEvil,

It seems that I can not move columns about. In MS Access I can easily move them about. But in Open Office I can't. Are there settings that allow your intallation of OpenOffice to have this feature??

Than you,

John in Annapolis, MD

QuazzieEvil · Posted: Mon Jun 30, 2008 8:48 am Post subject:

are you refering to the table or the table control? in the table control, the form must be in design mode.
_________________
Free Docs @ http://www.baseprogramming.com/resources.html
Book @ lulu.com http://www.lulu.com/content/2455551

Qabyss · Newbie Joined: 01 Dec 2010 Posts: 1

From the database management console, right-click on the table who's items you want to re-order and copy it.. Then paste it, when you do this, you will create a duplicate table and during the process of creating the duplicate you have the opportunity to re-order your items! Smile

~Q

MSPhobe · Posted: Sat Dec 11, 2010 12:42 pm Post subject:

I would advise against the previous post as an "answer" in most cases.

Because....

It isn't a good idea to work directly with tables, on a day-to-day basis.

That "bad news" is minor, given the Good News that it is easy to create a form for interacting with the data in your table. The form can even look like the display you get when you work directly with the table. ("Datasheet" format, if memory serves).

AND: You can re-order the columns in the FORM to your heart's content.

===
I have to enter data from two different brokerage accounts into a single database in my own computer. The two firms present the data in different orders. No problem... I have two forms for entering data into my one table of stock market purchases and sales. One form, with the fields in one order, for entering the data presented to me by the first firm, and a second form, fields in a different order, for entering the data presented to me by the second firm.

Each form "copies" the field sequence of the source document I am harvesting data from, so the harvest process is simple... and it is therefor harder to make mistakes.

===
The order of the fields IN THE UNDERLYING TABLE doesn't matter... does it? (Apart from the field which is the primary key for the table having(?) to be first(?))

clearcomms · Newbie Joined: 14 Dec 2010 Posts: 3

My problem is simple and I'm sure far from unusual. I have an existing database, imported from an MS Access database that had been running for some years. It is membership records. Now we need to add two new columns for 2011 Easy.

I add them in Design View. The two columns are at the bottom of the list. I drag them to the place in the list where I wish them to appear (logically, right after 2010!) and save the database.

Trouble is when I open the database proper, the columns are still over on the right hand side, even though if I go to Design View they are where they should be, in the middle.

Where am I going wrong, and why can't I just drag the columns in the database, as I can in some well-known databases?

MSPhobe · Posted: Tue Dec 14, 2010 10:04 am Post subject:

I think you may be confusing "database" and "table".

And I think my previous post, the post before the one before this one, gives you a solution to what you want... What do others who know about tables AND forms think?

The following may be useful...

http://sheepdogguides.com/fdb/fdb1intro.htm

Sliderule · Posted: Tue Dec 14, 2010 10:08 am Post subject:

clearcomms:

You did NOT say, but, I will assume the database engine you are using is HSQL . . . you can confirm this by looking at the bottom, on the Status Bar line, after opening your OpenOffice Base file ( *.odb ).

If it says . . . Embedded database and HSQL database engine, the following instructions apply. I really do know what you meant when you said:

clearcomms · Newbie Joined: 14 Dec 2010 Posts: 3

I really do appreciate these two very quick and helpful replies.

I shall get onto this in the morning, and I'm sure i can make one of them work for the (non-expert) who is actually running the Membership database.

And to clarify things, we are NOT running the Access database - I extracted the data and then imported it into a new OoDb database, so we are running the HSQL engine.

Thanks again, guys!

clearcomms · Newbie Joined: 14 Dec 2010 Posts: 3

Sliderule's solution works perfectly, and is simple - who could ask for more.

I've tested it successfully on a "spare" database, so we'll be able to update our Membership Records easily.

And for me at least this is by far the best way to handle it - simpler than doing a form.

Many thanks for your help

dacm · Super User Joined: 07 Jan 2010 Posts: 734

Thank you sliderule! I'm still experimenting with the SQL method, but this column reordering is also possible using the Base GUI for the most part. I made a quick tutorial here but it's specific to the "embedded database" using the "HSQL database engine."

The GUI method can be done with a "file/server" mode database as well, but there's no 'Rename' Table function available in that case, so you'll have to use 'Copy table' a second time to change the name. And these external databases (file/server mode) require the external database workaround to re-establish the Auto-Incrementing primary key feature. So I may do a "file/server" (external database) version of the tutorial, but I'll have to play with the SQL method to see which seems easier.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Migrating 'Embedded databases'