Compare two tables in Access and find only matching data

Sometimes you may want to review records from one Access table only if there are corresponding records in another Access table that contain one or more fields with matching data. For example, you may want to review the employee records of employees who have processed at least one order to determine which employees are eligible for a bonus. Or, you may want to review contact information for customers who live in the same city as an employee so that you can match employees with a customers for in-person meetings.

When you want to compare two Access tables and find matching data, you can either:

Create a query that joins fields from each table where those fields contain corresponding information, either by using an existing relationship or by using a join that you create for the purpose of the query. This method provides optimum performance (the speed with which the query returns results), but you cannot join fields that have dissimilar data types.
Create a query that compares fields by using one field as a criterion for the other. Using a field as a criterion for another field is generally slower than using joins, because joins eliminate rows from a query's results before the underlying tables are read, whereas criteria are applied to a query's results after the underlying tables are read. However, you can use a field as a field criterion to compare fields that have dissimilar data types, which you cannot do by using joins.

This article discusses how to compare two tables to identify matching data, and provides sample data that you can use with example procedures.

What do you want to do?

Compare two tables by using joins
Compare two tables by using a field as a criterion

Compare two tables by using joins

To compare two tables by using joins, you create a select query that includes both tables. If there is not already an existing relationship between the tables on the fields that contain the corresponding data, you create a join on the fields that you want to examine for matches. You can create as many joins as you want, but each pair of joined fields must be of the same or compatible data type.

Suppose that you are an institutional researcher at a college and you want see how recent curriculum changes in the math department have affected students' grades. You are specifically interested in the grades of students who are math majors. You already have a table that stores student major data and a table that stores class enrollment data. Grade data is stored in the Class Enrollments table, and student major data is stored in the Student Majors table. To see how grades have changed for math majors since the recent curriculum changes, you need to review records from the enrollments table that have corresponding records in the majors table.

Prepare sample data

In this example, you build a query that determines how recent curriculum changes in the math department have affected math students' grades. You use the following two sample tables, Student Majors and Class Enrollments. Add these two sample tables, Student Majors and Class Enrollments, to a database.

Access provides several ways to add these sample tables to a database. You can enter the data manually, you can copy each table into a spreadsheet program (such as Microsoft Office Excel 2007), and then import the worksheets into Access, or you can paste the data into a text editor, such as Notepad, and then import the data from the resulting text files.

The steps in this section explain how to enter data manually in a blank datasheet, and also explain how to copy the sample tables to Excel, and then import those tables into Access.

Student Majors

Student ID	Year	Major
123456789	2005	MATH
223334444	2005	ENGL
987654321	2005	MATH
135791357	2005	HIST
147025836	2005	BIOL
707070707	2005	MATH
123456789	2006	MATH
223334444	2006	ENGL
987654321	2006	PSYC
135791357	2006	ARTH
147025836	2006	BIOL
707070707	2006	MATH

Class Enrollments

Student ID	Year	Term	Curriculum	Course No.	Grade
123456789	2005	3	MATH	221	A
123456789	2005	3	ENGL	101	B
123456789	2006	1	MATH	242	C
123456789	2006	1	MATH	224	C
223334444	2005	3	ENGL	112	A
223334444	2005	3	MATH	120	C
223334444	2006	1	POSC	110	A
223334444	2006	1	ENGL	201	B
987654321	2005	3	MATH	120	A
987654321	2005	3	PSYC	101	A
987654321	2006	1	MATH	221	B
987654321	2006	1	MATH	242	C
135791357	2005	3	HIST	102	A
135791357	2005	3	ARTH	112	A
135791357	2006	1	MATH	120	B
135791357	2006	1	MATH	141	C
147025836	2005	3	BIOL	113	B
147025836	2005	3	CHEM	113	B
147025836	2006	1	MATH	120	D
147025836	2006	1	STAT	114	B
707070707	2005	3	MATH	221	B
707070707	2005	3	STAT	114	A
707070707	2006	1	MATH	242	D
707070707	2006	1	MATH	224	C

If you want to use a spreadsheet program to enter the sample data, you can skip the following section.

Enter the sample data manually

Open a new or existing database.
On the Create tab, in the Tables group, click Table.

Access adds a new, blank table to your database.

Note: You do not need to follow this step if you open a new, blank database, but you will need to follow it whenever you need to add a table to the database.
Double-click the first cell in the header row, and then type the name of the field in the sample table.

By default, Access denotes blank fields in the header row with the text Add New Field, such as:
Use the arrow keys to move to the next blank header cell, and then type the second field name. (You can also double-click the new cell.) Repeat this step for each field name.
Enter the data in the sample table.

As you enter the data, Access infers a data type for each field. Each field has a specific data type, such as Number, Text, or Date/Time. Setting data types helps ensure accurate data entry and also helps prevent mistakes, such as using a telephone number in a calculation. For these sample tables, allow Access to infer the data type, but be sure to review the data type that Access infers for each field.
After you finish entering the data, click Save, or press CTRL+S.

The Save As dialog box appears.
In the Table Name box, type the name of the sample table, and then click OK.

You use the name of each sample table (for example, Student Majors) because the queries in the procedure sections of this article also use those names.

After you finish entering the sample data, you are ready to compare the two tables.

Skip the following section (Create the sample worksheets), unless you want to learn how to create a worksheet that is based on the sample data from the tables in the preceding section.

Create the sample worksheets

Start your spreadsheet program, and create a new, blank file. If you use Excel, a new, blank workbook is created by default when you start the program.
Copy the first sample table from the preceding section and paste it into the first worksheet, starting at the first cell. Be sure that you copy the header row, because it contains the field names of the sample table.
Using the technique provided by your spreadsheet program, give the worksheet the same name as the sample table. For example, when you paste the Class Enrollments sample data, name the worksheet Class Enrollments.
Repeat steps 2 and 3, copying the second sample table to a blank worksheet and renaming the worksheet.

Note: You may need to add worksheets to your spreadsheet file. For information about adding worksheets to your spreadsheet file, see the help for your spreadsheet program.
Save the workbook to a convenient location on your computer or your network, and go to the next set of steps.

Create database tables from the worksheets

In a new or existing database:

On the External Data tab, in the Import group, click Excel.

-or-

Click More, and then select a spreadsheet program from the list.

The Get External Data - Program Name Spreadsheet dialog box appears.
Click Browse, locate and open the spreadsheet file that you created in the previous steps, and then click OK.

The Import Spreadsheet Wizard starts.

By default, the wizard selects the first worksheet in the workbook (Student Majors, if you followed the steps in the previous section), and data from that worksheet appears in the lower section of the wizard page.
Click Next.
On the next page of the wizard, select the First Row Contains Column Headings check box, and then click Next.
On the next page, you can use the text boxes and lists under Field Options to change field names and data types, or to omit fields from the import operation. For this example, you do not need to change anything. Click Next.
On the next page, select the No primary key option, and then click Next.
By default, Access applies the name of the worksheet to your new table. Accept the name in the Import to Table box, and then click Finish.
On the Save Import Steps page, click Close to complete the wizard.
Repeat steps 1 through 7 until you have created a table from each worksheet in the spreadsheet file.

Compare the sample tables and find matching records by using joins

Now you are ready to compare the Class Enrollments table and the Student Majors table. Because you have not defined relationships between the two tables, you need to create joins between the appropriate fields in the query. The tables have more than one field in common, and you will need to create a join for each pair of common fields: Student ID, Year, and Curriculum (Class Enrollments table) and Major (Student Majors table). In this example, you are only interested in math majors, so you will also use a field criterion to limit the query results.

Open the database in which you saved the sample tables.
On the Create tab, click Query Design.
Double-click the table that contains the records that you want to display — in this example, the Class Enrollments table, and then double-click the table to which you are comparing it — in this example, the Student Majors table.
Drag the Student ID field from the Class Enrollments table to the Student ID field of the Student Majors table. A line appears between the two tables in the design grid, indicating that you have created a join. Double-click the line to open the Join Properties dialog box.
Review the three options in the Join Properties dialog box. By default, option 1 is selected. In some cases, you need to adjust the join properties to include extra rows from one table. Because you are trying to find only matching data, leave the join set to option 1. Close the Join Properties dialog box by clicking Cancel.
You will need to create two other joins. Create these joins by dragging the Year field from the Class Enrollments table to the Year field of the Student Majors table, and then by dragging the Curriculum field from the Class Enrollments table to the Major field on the Student Majors table.
In the Class Enrollments table, double-click the asterisk (*) to add all of the table's fields to the query design grid.

Note: When you use the asterisk to add all fields, only one column appears in the design grid. The column that appears has the name of the table, followed by a period (.) and an asterisk (*). In this example, the column is named Class Enrollments.*.
In the Student Majors table, double-click the Major field to add it to the grid.
Clear the check box in the Show row of the Major column in the query design grid.
In the Criteria row of the Major column, type MATH.
On the Design tab, in the Results group, click Run.

The query runs, and then displays math grades only for math majors.

Top of Page

Compare two tables by using a field as a criterion

Sometimes you may want to compare tables on the basis of fields that have matching data, but have different data types. For example, a field in one table may have a Number data type, and you want to compare that field to a field in another table that has a Text data type. Fields that contain similar data yet have different field types can result when numbers are stored as text, either by design, or for other reasons, such as importing data from another program. Because you cannot create joins between fields that have different data types, you will need to use a different method to compare the fields. You can compare two fields that have different data types by using one field as a criterion for the other.

Suppose that you are an institutional researcher at a college and you want see how recent curriculum changes in the math department have affected students' grades. You are specifically interested in the grades of students who are math majors. You already have a Student Majors table and a Class Enrollments table. Grade data is stored in the Class Enrollments table, and student major data is stored in the Student Majors table. To see how grades have changed for math majors, you need to look at records from the enrollments table that have corresponding records in the majors table. However, one of the fields that you want to use to compare the tables has a different data type from its counterpart.

To compare two tables by using a field as a criterion, you create a select query that includes both tables. You include the fields that you want to display, and you also include the field that corresponds to the field that you want to use as a criterion. You then create a criterion to compare the tables. You can create as many criteria to compare fields as you want.

To illustrate this method, you will use the sample tables from the previous section, but you will change the data type of the Student ID field of the sample Student Majors table from Number to Text. Because you cannot create a join between two fields that have different data types, you will have to compare the two Student ID fields by using one field as a criterion for the other.

Change the data type of the Student Majors Student ID field

Open the database in which you saved the sample tables.
In the Navigation Pane, right-click the Student Majors table, and then click Design View on the shortcut menu.

The Student Majors table opens in Design view.
In the Data Type column, change the setting for Student ID from Number to Text.
Close the Student Majors table. When you are prompted to save changes, click Yes.

Compare the sample tables and find matching records by using a field criterion

The following procedure shows how to compare the two Student ID fields by using the field from Class Enrollments as a criterion for the field from Student Majors. By using the Like keyword, you can compare the fields, even though they have different data types.

On the Create tab, in the Other group, click Query Design.
Double-click Class Enrollments, and then double-click Student Majors.
Drag the Year field from the Class Enrollments table to the Year field of the Student Majors table, and then drag the Curriculum field from the Class Enrollments table to the Major field of the Student Majors table. Because these fields have the same data types, you can compare them by using joins. Joins are the preferred method for comparing fields that have the same data type.
Double-click the asterisk (*) on the Class Enrollments table to add all of that table's fields to the query design grid.

Note: When you use the asterisk to add all fields, only one column appears in the design grid. The column that appears has the name of the table, followed by a period (.) and an asterisk (*). In this example, the column is named Class Enrollments.*.
In the Student Majors table, double-click the Student ID field to add it to the grid.
Clear the check box in the Show row of the Student ID column of the design grid. In the Criteria row of the Student ID column, type Like [Class Enrollments].[Student ID].
In the Student Majors table, double-click the Major field to add it to the grid.
Clear the check box in the Show row of the Major column of the design grid. In the Criteria row, type MATH.
On the Design tab, in the Results group, click Run.

The query runs, and then displays math grades only for math majors.