Ask Your Question
0

append data using query

asked 2015-10-30 21:52:59 +0200

vintageireland gravatar image

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?

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
0

answered 2015-11-01 21:27:19 +0200

vintageireland gravatar image

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/...

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.

edit flag offensive delete link more
0

answered 2015-11-02 16:32:08 +0200

peterwt gravatar image

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.

edit flag offensive delete link more
0

answered 2015-11-04 09:55:49 +0200

BlueBike gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-10-30 21:52:59 +0200

Seen: 1,232 times

Last updated: Nov 02 '15