Compare two tables in Access and find only matching data

Access for Microsoft 365 Access 2019 Access 2016 Access 2013 Access 2010 Access 2007

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 IDYearMajor
1234567892005MATH
2233344442005ENGL
9876543212005MATH
1357913572005HIST
1470258362005BIOL
7070707072005MATH
1234567892006MATH
2233344442006ENGL
9876543212006PSYC
1357913572006ARTH
1470258362006BIOL
7070707072006MATH

Class Enrollments

Student IDYearTermCurriculumCourse No.Grade
12345678920053MATH221A
12345678920053ENGL101B
12345678920061MATH242C
12345678920061MATH224C
22333444420053ENGL112A
22333444420053MATH120C
22333444420061POSC110A
22333444420061ENGL201B
98765432120053MATH120A
98765432120053PSYC101A
98765432120061MATH221B
98765432120061MATH242C
13579135720053HIST102A
13579135720053ARTH112A
13579135720061MATH120B
13579135720061MATH141C
14702583620053BIOL113B
14702583620053CHEM113B
14702583620061MATH120D
14702583620061STAT114B
70707070720053MATH221B
70707070720053STAT114A
70707070720061MATH242D
70707070720061MATH224C

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

Enter the sample data manually

  1. Enter a new or existing database.
  2. On the Create tab, in the Tables group, pick Table.Access Ribbon ImageAccess produces 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.

  1. Double-click the first cell in the header row, and then enter the name of the field in the sample table. Intuitively, Access symbolises blank fields in the header row with the text Add New Field, such as:A new field in a datasheet
  2. Select second field name. (You can also double-click the new cell.) Repeat this process for each field name.
  3. Type the data in the sample table. Whilst you input the data, Access deduces a data type for each field. Every field includes a specific data type, such as Number, Text, or Date/Time. Designating data types aids with maintaining credible data input and even assists with minimising errors, for instance, relaying a telephone number in a calculation. For these sample tables, enable Access to decipher the data type, but ensure you confirm the data type that Access concludes for each field.
  4. When you complete the data entry, choose Save, or press CTRL+S. The Save As dialogue box appears.
  5. In the Table Name box, state the name of the sample table, and then press OK. You apply the name of each sample table (for example, Student Majors) since the queries in the procedure sections of this article also refer to such 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

  1. Begin your spreadsheet programme, and design a new, blank file. If you use Excel, a new, blank workbook is made automatically once you launch the programme.
  2. Copy the first sample table from the last section and paste it into the first worksheet, starting at the first cell. Check that you copy the header row, because it includes the field names of the sample table.
  3. Depending on the technique provided by your spreadsheet programme, 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.
  4. Repeat steps 2 and 3, copying the second sample table to a fresh 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 programme.

  1. Save the workbook to a easily accessible location on your computer or your network, and go to the next set of steps.

Create database tables from the worksheets

  1. In a new or existing database:On the External Data tab, in the Import group, click Excel.
Access Ribbon Image

-or- Click More, and then select a spreadsheet programme from the list. The Get External Data – Programme Name Spreadsheet dialogue box appears.

  1. Pick Browse, find and open the spreadsheet file that you created in the previous steps, and then select OK. The Import Spreadsheet Wizard starts. Automatically, 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.
  2. Press Next.
  3. On the next page of the wizard, click the First Row Contains Column Headings checkbox, and then choose Next.
  4. On the next page, you can apply the text boxes and lists under Field Options to alter field names and data types, or to skip fields from the import operation. For this example, you can continue without editing anything. Select Next.
  5. On the next page, choose the No primary key option, and then pick Next.
  6. Pre-emptively, Access imports the name of the worksheet to your new table. Adopt the name in the Import to Table box, and then select Finish.
  7. On the Save Import Steps page, press Close to finish the wizard.
  8. Repeat steps 1 until 7 to the stage where you have generated a table from each worksheet in the spreadsheet file.

Compare the sample tables and find matching records by using joins

You are now prepared to compare the Class Enrollments table and the Student Majors table. Since you are yet to still define relationships between the two tables, you must design joins between the relevant fields in the query. The tables contain several fields in common, and you are required to make 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.

  1. Enter the database in which you saved the sample tables.
  2. On the Create tab, pick Query Design.
  3. In the Show Table dialogue box, double-click the table that has 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.
  4. Exit the Show Table dialogue box.
  5. Drag the Student ID field from the Class Enrollments table to the Student ID field of the Student Majors table. A line emerges between the two tables in the design grid, denoting that you have created a join. Double-click the line to open the Join Properties dialogue box.
  6. Review the three options in the Join Properties dialogue 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 dialogue box by selecting Cancel.
  7. You must 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.
  8. 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.*.

  1. In the Student Majors table, double-click the Major field to create it within the grid.
  2. Empty the checkbox in the Show row of the Major column in the query design grid.
  3. In the Criteria row of the Major column, enter MATH.
  4. On the Design tab, in the Results group, press Run. The query initiates, and then lists maths grades only for maths 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

  1. Access the database in which you saved the sample tables.
  2. In the Navigation Pane, right-click the Student Majors table, and then press Design View on the shortcut menu. The Student Majors table launches in Design view.
  3. In the Data Type column, edit the setting for Student ID from Number to Text.
  4. Exit the Student Majors table. Once you are prompted to save changes, choose 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.

  1. On the Create tab, in the Other group, pick Query Design.
  2. In the Show Table dialogue box, double-click Class Enrollments, and then double-click Student Majors.
  3. Dismiss the Show Table dialogue box.
  4. 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. Since these fields have consistent data types, you can benchmark them by applying joins. Joins are the favoured method for evaluating fields that contain the equal data type.
  5. Double-click the asterisk (*) on the Class Enrollments table to append each of that table’s fields to the query design grid.

Note: After 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.*.

  1. In the Student Majors table, double-click the Student ID field to import it to the grid.
  2. Unmark the checkbox in the Show row of the Student ID column of the design grid. In the Criteria row of the Student ID column, state Like [Class Enrollments].[Student ID].
  3. In the Student Majors table, double-click the Major field to append it to the grid.
  4. Blank the checkbox in the Show row of the Major column of the design grid. In the Criteria row, write MATH.
  5. On the Design tab, in the Results group, select Run. The query starts, and then presents maths grades solely for maths majors.

Top of Page

This information was compiled using information courtesy of © Microsoft 2020. All rights reserved.

Leave a Reply

%d bloggers like this: