Creating An Append Query In Microsoft Access

In Microsoft access database and all the other database management systems, queries as the heart of the software system which can execute numerous actions to make your database more systematised and functional. Queries prove to be useful for working on various tasks including returning recordsets in an ordered and filtered way, updating values, editing or deleting data, and even making a new table in the Microsoft access database.

Action Queries can be used for adding, changing, or deleting numerous records from a table, record or field at a click of a button.

The additional advantage of an Action Query is that user can preview their query results in Microsoft access database before running it.

Microsoft access database provides the following types of Action Queries:

• Append
• Update
• Delete
• Make-Table

Note that users cannot undo an action query and therefore, they must create a backup of the data that they want to update using the query.

Steps for creating an Append Query

Append query is a kind of (SQL statement) action query which lets users add records to a table. It is also called an Insert Query since it uses ‘INSERT INTO’ command as its SQL syntax. Users can use Append Query to add new tables or data to another table. It can likewise be used for fetching data from multiple tables.

To make this process extra clear, let’s use two of our existing tables — StaffID and StaffSalary.

• Step 1: Open the database that has the records that you want to copy.
• Step 2: Click the Create tab and select Query Design to open the query designer.
• Step 3: At this point, you should be able to see the Show Table dialog box. Double-click the tables or queries that hold the records that you want to copy. For our example, we are going to use StaffID and StaffSalary. Click Close.
• Step 4: Double-click each field that you want to append. Each of the fields you select will be shown in the Field row of the query design grid.
• Step 5: Click Run on the Design tab to see the query result.
• Step 6: Verify that the query returned the records that you want to copy. If you see the need to add or remove some fields, return to Design view and add or delete the fields.
• Step 7: Convert the select query to an append query. To do this, select Design View from the View drop-down menu on the Home tab.
• Step 8: Click the Append button and a dialog box will show.
• Step 9: Next, you need to specify whether to append records to a table in the current database or to a table in another database. If the table is in the current database, click Current Database in the dialog box and choose the table from the drop-down list. If the table is in another database, click Another Database then click Browse to select the destination database. Once you’ve chosen the database, select or enter the name of the destination table in the Table Name box. For our example, we will use the Staff table. After selecting, click the OK button.
• Step 10: You then choose the destination fields. The way you select destination fields depends on how you created your SELECT query. If you added all the fields from your source table, Access will add all the fields in the destination table to the Append to row in the design grid. If you added every field to the query or used expressions, and the field names in the source and destination tables match, Access will automatically add the matching destination fields to the Append to row in the query. If you added individual fields or used expressions, and any of the names in the source and destination tables do not match, Access will add the matching fields and leave unmatched fields blank. In this case, you can click a cell in the Append to row and choose a destination field.
• Step 11: Preview the query before running. Do this by switching to Datasheet View.
• Step 12: After that, return to Design View and click Run to append the records.

You will then be able to see all the additional data which have been appended to the destination table.

In case you encounter a crash in Microsoft access database, do not hesitate to use a specialised ‘accdb’ recovery tool. It can be the only thing to save you from the hassles of data loss. But remember, taking backups first will save you time and inconvenience.

Visit : www.accessdatabasetutorial.com/creating-append-query-microsoft-access-database/