Matching Database Values to Sections of Website to Upload Changes

Sometimes you may want to review records from ane Access tabular array only if there are corresponding records in another Access table that contain i or more fields with matching data. For example, you may want to review the employee records of employees who accept processed at to the lowest degree one order to make up one's mind which employees are eligible for a bonus. Or, you may want to review contact data for customers who alive in the same city every bit an employee then that y'all can match employees with a customers for in-person meetings.

When you want to compare two Admission tables and find matching information, yous 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 lot create for the purpose of the query. This method provides optimum operation (the speed with which the query returns results), but you lot cannot join fields that accept dissimilar data types.

• Create a query that compares fields by using 1 field as a benchmark for the other. Using a field as a criterion for another field is by and large 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 subsequently the underlying tables are read. Withal, y'all tin use a field as a field criterion to compare fields that have dissimilar data types, which you cannot do past using joins.

This commodity discusses how to compare ii tables to place matching data, and provides sample data that you tin use with example procedures.

What exercise yous want to do?

• Compare 2 tables past using joins

• Compare two tables past using a field as a criterion

Compare two tables by using joins

To compare two tables by using joins, you lot 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 respective data, you create a bring together on the fields that you want to examine for matches. You can create as many joins as you lot want, just each pair of joined fields must be of the aforementioned or compatible data type.

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

Prepare sample information

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

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

The steps in this department explain how to enter data manually in a blank datasheet, and also explain how to re-create 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

Educatee ID	Yr	Term	Curriculum	Class No.	Grade
123456789	2005	3	MATH	221	A
123456789	2005	three	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	one	ENGL	201	B
987654321	2005	3	MATH	120	A
987654321	2005	3	PSYC	101	A
987654321	2006	1	MATH	221	B
987654321	2006	i	MATH	242	C
135791357	2005	three	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 utilise a spreadsheet plan to enter the sample data, you lot can skip the following department.

Enter the sample data manually

1. Open a new or existing database.

2. On the Create tab, in the Tables group, click Tabular array.

   

   Access adds a new, blank table to your database.

   Annotation:You practice not demand to follow this stride if y'all open a new, bare database, but you will demand to follow information technology whenever y'all need to add together a tabular array to the database.

3. Double-click the first cell in the header row, and 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:

   

4. Utilize the arrow keys to motion to the next blank header jail cell, and so type the second field name. (Y'all can also double-click the new cell.) Repeat this footstep for each field name.

5. Enter the information in the sample tabular array.

   As you enter the data, Access infers a data blazon for each field. Each field has a specific information type, such every bit Number, Text, or Appointment/Time. Setting data types helps ensure accurate data entry and also helps prevent mistakes, such equally using a phone number in a calculation. For these sample tables, let Access to infer the information blazon, simply exist certain to review the data type that Access infers for each field.

6. After you lot finish inbound the data, click Salve, or press CTRL+Due south.

   The Salvage As dialog box appears.

7. In the Table Name box, blazon the name of the sample tabular array, so click OK.

   You employ the name of each sample table (for example, Student Majors) considering the queries in the procedure sections of this article likewise utilize those names.

After y'all cease entering the sample data, you are set up to compare the ii tables.

Skip the following section (Create the sample worksheets), unless you lot 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. Start your spreadsheet program, and create a new, blank file. If you lot utilize Excel, a new, bare workbook is created by default when you lot start the program.

2. Copy the first sample table from the preceding department and paste it into the get-go worksheet, starting at the start prison cell. Be certain that you re-create the header row, considering it contains the field names of the sample table.

3. Using the technique provided by your spreadsheet program, give the worksheet the aforementioned name as the sample table. For example, when y'all paste the Class Enrollments sample information, name the worksheet Grade Enrollments.

4. Repeat steps 2 and 3, copying the second sample table to a bare worksheet and renaming the worksheet.

   Annotation:You may need to add together worksheets to your spreadsheet file. For data about calculation worksheets to your spreadsheet file, see the help for your spreadsheet program.

5. Relieve the workbook to a user-friendly location on your estimator or your network, and go to the side by side 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.

   

   -or-

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

   The Get External Data - Program Name Spreadsheet dialog box appears.

2. Click Browse, locate and open the spreadsheet file that yous created in the previous steps, and so 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 magician page.

3. Click Next.

4. On the next page of the wizard, select the First Row Contains Column Headings cheque box, and then click Side by side.

5. On the next page, you can utilize 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 demand to alter annihilation. Click Next.

6. On the next page, select the No primary key option, and and then click Next.

7. By default, Access applies the name of the worksheet to your new tabular array. Have the proper name in the Import to Tabular array box, and then click Stop.

8. On the Save Import Steps page, click Close to complete the wizard.

9. Repeat steps 1 through 7 until you lot accept created a tabular array from each worksheet in the spreadsheet file.

Compare the sample tables and find matching records by using joins

Now yous are gear up to compare the Class Enrollments table and the Student Majors tabular array. Because you accept non defined relationships between the two tables, you need to create joins betwixt the appropriate fields in the query. The tables take more than i field in common, and you will need to create a join for each pair of common fields: Student ID, Year, and Curriculum (Course Enrollments table) and Major (Pupil Majors table). In this example, you lot are only interested in math majors, so you lot will also apply a field benchmark to limit the query results.

1. Open the database in which you saved the sample tables.

2. On the Create tab, click Query Pattern.

3. Double-click the tabular array that contains the records that you want to display — in this example, the Class Enrollments table, and and then double-click the table to which you are comparing information technology — in this instance, the Student Majors table.

4. Drag the Student ID field from the Grade Enrollments table to the Student ID field of the Student Majors table. A line appears betwixt 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.

5. Review the three options in the Bring together Properties dialog box. By default, option one is selected. In some cases, you need to adjust the join properties to include extra rows from one table. Because y'all are trying to observe only matching data, leave the join prepare to option 1. Close the Join Properties dialog box by clicking Abolish.

6. You volition need to create ii other joins. Create these joins by dragging the Yr field from the Class Enrollments table to the Year field of the Student Majors table, so by dragging the Curriculum field from the Class Enrollments table to the Major field on the Student Majors table.

7. In the Class Enrollments table, double-click the asterisk (*) to add all of the table'south fields to the query design grid.

   Note:When you apply the asterisk to add all fields, simply one column appears in the pattern grid. The column that appears has the name of the tabular array, followed by a menstruation (.) and an asterisk (*). In this example, the column is named Grade Enrollments.*.

8. In the Student Majors tabular array, double-click the Major field to add it to the grid.

9. Clear the check box in the Show row of the Major cavalcade in the query design filigree.

10. In the Criteria row of the Major column, type MATH.

11. On the Design tab, in the Results group, click Run.

    The query runs, and so displays math grades simply for math majors.

Top of Page

Compare 2 tables by using a field equally a criterion

Sometimes you may want to compare tables on the basis of fields that take matching information, 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 some other table that has a Text data type. Fields that contain similar data withal accept dissimilar field types can result when numbers are stored equally text, either by design, or for other reasons, such as importing information from some other program. Because you cannot create joins between fields that have different data types, you volition need to utilise a different method to compare the fields. Yous tin compare 2 fields that have dissimilar information types by using one field as a benchmark for the other.

Suppose that yous are an institutional researcher at a college and you want see how recent curriculum changes in the math department have affected students' grades. You lot are specifically interested in the grades of students who are math majors. You already take a Student Majors table and a Class Enrollments table. Grade data is stored in the Course Enrollments table, and student major data is stored in the Student Majors table. To see how grades take inverse for math majors, you need to look at records from the enrollments tabular array that have corresponding records in the majors table. However, one of the fields that you want to utilise to compare the tables has a unlike data blazon from its analogue.

To compare ii tables past using a field as a criterion, y'all create a select query that includes both tables. You include the fields that you want to display, and yous also include the field that corresponds to the field that you desire to use equally a criterion. You lot and so create a benchmark to compare the tables. Yous can create as many criteria to compare fields as you want.

To illustrate this method, you will utilise the sample tables from the previous section, only you will change the information blazon of the Educatee ID field of the sample Student Majors tabular array from Number to Text. Because y'all cannot create a bring together between two fields that have unlike data types, you volition have to compare the two Educatee ID fields by using one field equally a benchmark for the other.

Modify the data blazon of the Student Majors Pupil ID field

1. Open the database in which you saved the sample tables.

2. In the Navigation Pane, right-click the Educatee Majors table, and then click Design View on the shortcut menu.

   The Student Majors table opens in Design view.

3. In the Data Type column, change the setting for Student ID from Number to Text.

4. 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 past using the field from Class Enrollments as a benchmark for the field from Student Majors. By using the Like keyword, you tin compare the fields, even though they take dissimilar data types.

1. On the Create tab, in the Other group, click Query Pattern.

2. Double-click Class Enrollments, and and then double-click Student Majors.

3. Drag the Year field from the Class Enrollments table to the Year field of the Student Majors tabular array, and and so drag the Curriculum field from the Class Enrollments table to the Major field of the Educatee Majors table. Considering these fields have the same data types, yous can compare them past using joins. Joins are the preferred method for comparing fields that have the aforementioned data blazon.

4. Double-click the asterisk (*) on the Class Enrollments table to add together all of that table's fields to the query design filigree.

   Note:When you use the asterisk to add together all fields, only 1 cavalcade appears in the design filigree. The cavalcade that appears has the name of the tabular array, followed by a period (.) and an asterisk (*). In this example, the column is named Form Enrollments.*.

5. In the Student Majors table, double-click the Student ID field to add information technology to the grid.

6. Clear the check box in the Bear witness row of the Pupil ID column of the design filigree. In the Criteria row of the Student ID cavalcade, blazon Like [Class Enrollments].[Educatee ID].

7. In the Student Majors tabular array, double-click the Major field to add information technology to the grid.

8. Clear the check box in the Show row of the Major column of the design grid. In the Criteria row, blazon MATH.

9. On the Design tab, in the Results group, click Run.

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

Tiptop of Page

fortunepragnot.blogspot.com

Source: https://support.microsoft.com/en-us/office/compare-two-tables-in-access-and-find-only-matching-data-16f301ac-40c1-43bc-80db-263f9a51eb4f

Share this post