Advanced Database – Create, run a query to append records to a table.
Appending records means taking them from one table and putting them into another.
In MS Access there is a built-in function to do this, but in Base you will have to use an SQL query.
Imagine we want to create an archive system for completed jobs.
To start with, create a new table called New Jobs which has the same fields as the Jobs table.
Open the New Jobs table and add 2 new records.
We are going to append these records into the Jobs table using an SQL statement
Close the table and select Tools->SQL… to open the SQL statement window.
Type in the command:
INSERT INTO "Jobs" ( "Job No", "Staff Number", "Job Description", "Date", "Car Reg No") SELECT "Job No", "Staff Number", "Job Description", "Date", "Car Reg No" FROM "New Jobs“;
This will insert the selected fields from New Jobs into Jobs.
Note the semi-colon (;) at the end. This means it will select all records to append. Without it, only the first record is selected.
Execute the command then close the SQL window.
Open the Jobs table and notice that jobs 20 and 21 have been copied from New Jobs into Jobs.
If you open the New Jobs table, the two records 20 and 21 are still in there. For this to work as an archive system you need to delete them too.
The next section teaches you how to delete records using a query.
In general, to append records to a table use the SQL syntax:
INSERT INTO name_table ( column1, column2, …)
SELECT column1, column2, … FROM name_table2 ;