Append data using query

[vintageireland]

In Microsoft Access you can run an Append Data Query, to move (append) records from one table to another table. How do I do this in Base? Here is my example -

I have a large database of stock for sale and sold, all unique items. I want to automate the archiving of the sold items from the In Stock table to a table called Sold. I have created a list box field in For Sale called “Select” containing the options of “In Stock” “Sold” and “Pending”. With Access, it was possible to create an Append Table Query that would automatically add any item selected as “Sold” to the Sold table, and I could also run a Delete Query to delete items flagged as “Sold” from the For Sale table. Is there any way of doing this using a query in Base?

[vintageireland]

I tracked down the answer in a tutorial - you can’t use the Sql window in Query Design view, you have to use Tools/Sql and execute the command from the Sql window. Very useful tutorial here: http://www.floppybunny.org/robin/web/virtualclassroom/chap8/libreoffice/libreoffice_base_tut6.pdf

However, am now wondering how you save the Sql command for repeated use, as it doesn’t save as a Query in the normal way. At the moment I have saved in a text document so I can copy and paste into the Sql window as needed, but this is a bit clumsy, would be handy to save it in the Base database.

[robertrmorris]

Very strongly agree with your last comment. Please add a save to database function to the SQL window.

[peterwt]

As you said a Query created in Query Design can only read the database - it can not modify the database, unlike Access.

Any Query that will modify the database needs to be run as direct SQL from the Tools/SQL. You can not save this for future use.

The only way I know of being able to save such a SQL statement is to use a Macro which is then saved. You can run SQL on a database using a Macro.

[BlueBike]

You can save your query as a query in Base. Just because you cannot use the graphic query builder does not mean that you cannot save the query as a query. You just have to build the query in the SQL query tool which is not a problem if you already has the code?
If you enter the query pane/tab you take the third option.