Creating an Append Query in Microsoft Access

Creating an Append Query in Microsoft Access

This article talks about the importance of the Append Query and the appropriate way of creating them in Microsoft Access.

In Microsoft Access 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 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 before running it.

Microsoft Access 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.image1

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

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

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 DesignView from the View drop-down menu on the Home tab.image4

Step 8: Click the Append button and a dialog box will show.image5

image6

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 torow 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 MS Access, 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.

 

Ben Beitler

https://www.accessdatabasetutorial.com/

ben@accessdatabasetutorial.com

United Kingdom
London

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s