How to Append With an Unmatched Query in Access
Queries are used to extract a specific set of data from a relational database, such as a file created by Microsoft Office Access. Each record within a relational database has one or more fields, which often can be linked to other tables within that same database.
If two tables have similar data, you may run a mismatch query (also known as an "unmatched query") to display the records that are different. Further, you can use a special query called an "append query" to append records to a database. Because you cannot simultaneously write to and read from a data table, combining the mismatch and append queries together requires that the data to be appended cannot be written to the tables being searched.
Other People Are Reading
Things You'll Need
- Microsoft Office Access
- Database file containing at least three data tables
Instructions
-
Microsoft Office 2007 or Newer
-
1
Open Microsoft Office Access. Click the Office button (which looks like a round Windows "Start" button), and select "Open." Select the database file you wish to open, and click the "Open" button.
-
2
Click the "Create" tab, and select "Query Design." Select the name of the tables you wish to compare, and click "Add." Click "Close" to continue.
-
-
3
In the top half of the query design window, find the common field between the two tables. If the databases have multiple common fields, choose the common field you wish to search by. Drag the name of the field from one of the tables to the location of its counterpart on the other table. A black line will appear between the two fields.
-
4
Right-click on the black line joining the fields-in-common. Select "Join Properties" from the pop-up menu. Select the relationship you want the tables to have (such as "Include All Records From 'Table1' and Only Those Records From 'Table2' Where the Joined Fields are Equal") and click "OK."
-
5
Double-click both fields-in-common so both display in the lower half of the query design window. Type the phrase "Is Null" in the field-in-common coming from Table2. Type "Expr1:" (without quotation marks) in front of the name of the field. For example, if both fields are named "Field1", change the field coming from Table2 to read "Expr1: Field1" (without quotation marks).
-
6
Click the "Append" button. Select the name of the table you wish to append to (such as "Table3") and click the "OK" button. Click the "Run" button (button with an exclamation point). The database will notify you when the query is complete; click "Yes" to append the records.
Microsoft Office 2003 or Previous
-
7
Open Microsoft Office Access. Click "File" and then click "Open." Select the database file you wish to open, and click the "Open" button.
-
8
Click the "Queries" object in the database window, and double-click "Create Query in Design view." Select the name of the tables you wish to compare, and click "Add." Click "Close" to continue.
-
9
In the top half of the query design window, find the common field between the two tables. If the databases have multiple common fields, choose the common field you wish to search by. Drag the name of the field from one of the tables to the location of its counterpart on the other table. A black line will appear between the two fields.
-
10
Right-click on the black line joining the fields-in-common. Select "Join Properties" from the pop-up menu. Select the relationship you want the tables to have (such as "Include All Records From 'Table1' and Only Those Records From 'Table2' Where the Joined Fields are Equal") and click "OK."
-
11
Double-click both fields-in-common so that both display in the lower half of the query design window. Type the phrase "Is Null" in the field-in-common coming from Table2. Type "Expr1:" (without quotation marks) in front of the name of the field. For example, if both fields are named "Field1", change the field coming from Table2 to read "Expr1: Field1" (without quotation marks).
-
12
Click the database type button (the button immediately to the left of the exclamation point button). Select the name of the table you wish to append to (such as "Table3") and click the "OK" button. Click the "Run" button (button having an exclamation point). The database will notify you when the query is complete; click "Yes" to append the records.
-
1
Tips & Warnings
Adding "Expr1:" to the front of the mismatch field helps prevent an error from occurring. If the fields-in-common from all three tables are identically named, the append query will display the error "Duplicate Output Destination" and the operation will fail.
