I am trying to move a field from the end of my table to somewhere in the middle. How do I do this?
2 Likes
I used to be able to do this after clicking Edit on the table’s context menu. Then I would cut and paste the field I wanted to move to first place. And then the one I wanted in second place. And so forth. Then I’d save the table. Exit the edit window. And save the file.
Now when I do that, the changes do not take effect.
I have no idea why. It’s weird and it’s frustrating.
(And, no, I haven’t changed any settings or upgraded the software. I’m using LO 6.0.6.2 on Win7.)
The order of rows and columns in a database table is insignificant. You can easily create queries that return the exact same data in any order of rows and columns you want. Then you can use a query instead of the table.
Open the query designer, add one table and add the columns in any order you want. Optionally you can use alias names to change the column names on the fly. This query with modified column order works in the exact same ways as the original table.
Using queries is probably the preferred way of accessing MS-Access tables because you can format the columns (like Base), even calculate new columns, and then reopen the formatted query. It works so well you don’t need reports for tables if you format the title, header and footer. In Base the formatting is lost when the query is reopened, so it’s not a viable option.
Create an input form with a table grid from that query with 3 or 4 clicks in the forms wizard.
…which is like putting a car on a train to travel to a destination instead of driving it.
You have to do this only one time and then you can use the car on rails “for ever”. You have raw data stored in tables. You have queries to return table contents in any order of rows and columns and you have input forms as a more convenient user interface. Even the most simple input form with a table grid has specialized input boxes for dates, times and numbers. A form has a toolbar with big push buttons. You may also add a navigation control which is a fixed toolbar attached to the form’s canvas.
Storage, Processing, Representation. This is what professional applications do separately.
Just my personal recommendation: Do not mess with the structures in the database backend! Use the Base frontend to get any subset of columns and rows in any order of columns and rows.
This is neither car nor train. It’s a helicopter. You can edit the same table data in every dimension along client names, articles, orders, invoices, costs, revenues, … The order of rows and columns has zero influence on the functionality.
@Villeroy thanks for your comments.
.
I strongly agree with your comment about transforming a copy of data rather than changing the original (my words). Calcs like units, date format, density from vol/weight etc. That and layout are the key things I have been able to do easily in MS-Access query.
.
I tried your suggestion and I feel you have over-sold it. If MS-Access query is helicopter the Base functionality of a query in a table grid in a form is an old train at best. Seems like forever juggling query/grid/form properties. There is really no need for it to be this primitive since a query is a grid having most of the display properties of a table, except most are lost when the query is closed. Saving Base query display properties would allow it to fly.
SQL. I don’t know of a way to do this within Base easily. I typically use SQL Workbench or MySQL Workbench depending on what database I’m using. If the DB is empty, it is fairly simple. Take the SQL source displayed, re-arrange (cut & paste) what you want, Drop the old & create the new. If, however, you have data in the DB, it can be tedious. Create a new table with what you want then with SQL, copy the data from one table to another field by field. As a side note, there is no logical reason that fields have any sequence within a database.
1 Like
Confirmed on LO 5.1.0.3, you can try to drag-drop the columns but the cursor changed to the You can't do this one, and doesn’t matter how you drag or drop. However, I checked the HSQLDB 1.8.0 syntax and it confirms you can change the order through SQL: http://www.hsqldb.org/doc/guide/ch09.html#alter_table-section. Although I can understand the reason for lack of such feature to be harder to figure out which column was moved where, it’s not impossible, just keep copies of the column names.
1 Like
I also want to note that it is as easy as drag & drop to move columns using MySQL Workbench.
I came across this (old) question trying to do the same thing. Not as professional but I found a work around that I found easier than using SQL. (Although I guess I should look at MySQL Workbench…)
Probably easier/safer If you haven’t got lots of data in your table and I would make sure I had a backup first and know the ‘field type’ /settings of each field.
I ‘imported’ (dragged the table into) a blank calc sheet. Added a new column and the new field name I wanted.
Deleted the table in Base, went back to Calc and exported (dragged back) the table to Base. Gave it the same name, used the column headings as field names etc. I did have to go through and reset the field type of each field.
I might have to edit a couple of queries and a form… but quicker than having to make a new table, typing in 15 field names etc.
I guess I should say that I had no data in the table - I was using a copy of a pre-existing database as a template and needed to add and change a couple of field names in one table. And also I import and export tables between Base and Calc quite often - I get data sent to me as an Excel file (don’t have a choice about it) that I want to add to a database.
Even easier. Drag the database table into a blank calc sheet, move columns as required and save as original file name. Open Base with the database table and the columns have been changed. Do backup first though.
Are you able to record yourself doing this? (I am not easily able to believe that merely saving an ODS file as an ODB file shall allow me to retain all its information.)
Hi. I have just tried again using the .dbf file. I had Calc open, a smaller window so I can see the .dbf, and dragged the .dbf file into Calc. I inserted a new column, copied a column into it and deleted the original column and the column has been moved. I then saved it back as a .dbf file. On opening it again the columns are as they were after the column move. I hope this helps.
Your way is possible for dbf and csv/tsv, as this files are actually tables and now you also state “save back as .dbf” instead of save under original file-name.
.
If you would do the same with an .odb-embedded database, wich can contain several tables, all queries, forms you will loose data!
.
Even for .dbf/csv etc be very careful. The Auto-detect “features” of calc can silently change data, for example if misread as Date (1234.05.06 is just text, but 12.05.06 is converted to an integer representing a date) and depending on your settings you may alter data.
.
I would guess nobody would try this with really big databases, but I should mention it anyway: A lot of people lost databat the end of their tables saving in .xls as the number of lines is limited. Calc files can be much bigger, but always check if all your data is back in the database.
.
Better: do as @lucky67 and modify an empty table. Data can be moved by SQL:
INSERT INTO new SELECT ... FROM old;
But as you’d have to type and fill the dots it is the same task as before …
1 Like
Hi. You are correct but I was only suggesting a simple solution for simple database set ups. I retired 14 years ago and so am not keeping up to date any more and only use Base for simple card address lists etc. Simple compared to the potential.
1 Like