Awards
Join OfficeTipsAndMethods

Have some suggestions to share with the world?

Join OfficeTipsAndMethods and share your insight in a Comment. You must be a member to comment and all comments are moderated before being published. Membership is free and we would never share your personal information with anyone.

Become an OTM Member

Log In

YouTube Video Channel
OfficeFix
Article Categories

Posts Tagged ‘Append queries’

Action Queries–Append

QueryAppendAn append query, copies records from one data source (table or query) and appends them to another table. Only data in fields whose names are the same in the source and target is inserted into the destination table. If a field in the source does not exist in the destination, the data in that field simply goes nowhere. If a field in the destination does not exist in the source that field in the appended records will have a null value. As you will see in the example, however, the append query definition does let you specify a target field whose name is not an exact match to one of the source fields.

Scenario: You have received some data in an Excel worksheet that you want to import into your database. You have created a link to the Excel worksheet and named the link tblPeople. In your database, the table where you plan to maintain this type of information about people is tblHumanResources.

The first step is to create a query in design view using the source table:

image

In Access 2007 and 2010, look for the Append command in the Query Type group on the Ribbon Design tab.

image

QuerySelectQueryTypeIn Access 2003 and earlier, look for the Query Type icon on the Query Design toolbar. If this is the first action query you create the image will represent a select query. If you have previously created one of the other query types, the image will represent the type of the last query you created.

When you click the Append shortcut, the Append dialogue will open. Use the Table Name: dropdown to select the table to which you want to append data.

image

The query grid will change to include a new row, Append To:

image

Cells on the append row will be populated when there are matches in the field names. In this case the Human resources table has fields names Lastname, Firstname, Middlename that correspond to the differently named fields Lname, Fname, and MName in tblPeople. In order to ensure that all needed data is inserted into tblHumanResources, pick the relevant field names from the dropdowns in the Append To: row.

If you have specific requirements to include or exclude certain records, you would enter criteria into the appropriate grid columns.

The finished query to append records from tblPeople into tblHumanResources looks like this:

image

The SQL for this query is:

INSERT INTO tblHumanResources ( Lastname, Firstname, Middlename, Honorific, BirthDate )
SELECT tblPeople.Lname, tblPeople.Fname, tblPeople.Mname, tblPeople.Honorific, tblPeople.BirthDate
FROM tblPeople;

Caution: ViewDatasheetBefore running the query (and performing its action, appending data to tblHumanResources in this case) your should back up the database or table that will be modified. You should also view the query in datasheet view to ensure that it is selecting all the data, and only the data, that you want to append to the destination table.

 

Running the query: Once you have backed up the data and reviewed theQueryRun data to be appended, you can run the query. All selected records from tblPeople will be appended to tblHumanresources. In Access 2007 and 2010, the View and Run buttons are in the Results group of the Ribbon’s Design tab. In Access 2003 and early you will find these shortcuts on the Query Design tool bar.

 

image

After you run the query, the selected records will be appended to the destination table. If there are data validation rules set in the destination tables, any record whose data violates those rules will not be appended.