How to compare two tables and find records without matches

From https://support.office.com/en-us/article/Compare-two-tables-and-find-records-without-matches-cb20ad48-4eba-402a-b20d-eaf10a5d1cb4

Use the unmatched query wizard to compare two tables:

On the Create tab, in the Other group, click Query Wizard.


In the New Query dialog box, double-click Find Unmatched Query Wizard.


On the first page of the wizard, select the table that has unmatched records, and then click Next. For example, if you want to see a list of Northwind 2007 products that have never been sold, select the Products table.


On the second page, select the table that is related, and then click Next. To follow the example, select the Order Details table.


On the third page, select the fields that relate the tables, click < = >, and then click Next. You can choose only one field from each table. To follow the example, select ID from the Products table and Product ID from the Order Details table. Verify that the correct fields are matched by reviewing the text in the Matching fields box.


Note that the ID and the Product ID fields may already be selected because of existing relationships built in to the template.


On the fourth page, double-click the fields that you want to see from the first table, and then click Next. To follow the example, select the ID and Product Name fields.


On the fifth page, you can choose to view the results or to modify the design of your query. In this example, click View the results. Accept the suggested name for the query, and then click Finish.


You may want to modify your query's design to add other criteria, to change the sort order, or to add or to remove fields. For information about modifying a Find Unmatched query, read the following section; or, for more general information about creating and modifying queries, see the links in the See Also section.

Create and modify a Find Unmatched Query to compare by more than one field

1. On the Create tab, in the Other group, click Query Wizard.
2. In the New Query dialog box, double-click Find Unmatched Query Wizard.
3. On the first page of the wizard, select the table that has unmatched records, and then click Next. For example, if you want to see a list of Northwind 2007 products that have never been sold, select the Products table.
4. On the second page, select the table that is related, and then click Next. To follow the example, select the Order Details table.
5. On the third page, select the fields that relate the tables, click < = >, and then click Next. You can choose only one field from each table. To follow the example, select ID from the Products table and Product ID from the Order Details table. Verify that the correct fields are matched by reviewing the text in the Matching fields box. You can join the remaining fields after you have completed the wizard.
   Note that the ID and the Product ID fields may already be selected because of existing relationships built in to the template.
6. On the fourth page, double-click the fields that you want to see from the first table, and then click Next. To follow the example, select the ID and Product Name fields.
7. On the fifth page, click Modify the design, and then click Finish.
   The query opens in Design view.
8. In the query design grid, note that the two tables are joined on the fields (in this example, ID and Product ID) that you specified on the third page of the wizard. Create a join for each remaining pair of related fields by dragging them from the first table (the table that has unmatched records) to the second table. For this example, drag the List Price field from the Products table to the Unit Price field from the Order Details table.
9. Double-click a join (the line connecting the fields) to display the Join Properties dialog box. For each join, choose the option that includes all the records from the Products table, and then click OK.
   In the query design grid, note that each join now has an arrow at one end.
   
   1. When you create the join between the List Price and Unit Price fields, it restricts output from both tables. Only those records with matching data in the fields in both tables are included in the query results.
   2. After you edit the join properties, the join only restricts the table that the arrow points to. All records in the table that the arrow points from are included in the query results.
   NOTE: Make sure that all of the arrows on the joins are pointing in the same direction.
10. For the table that has related records (in this example, the Order Details table), double-click each field that is joined to the first table, except for the field that you chose on the third page of the wizard (in this case, the Product ID field). For each of these fields, clear the check box in the Show row, and type Is Null in the Criteria row.
11. Optionally, add criteria to the other query fields, or create calculated fields that are based on values from the first table.
12. On the Design tab, in the Results group, click Run.
    The query returns the names of products that are not part of any existing orders.

Create your own query to find unmatched records

1. On the Create tab, in the Other group, click Query Design.
2. In the Show Table dialog box, double-click the table that has unmatched records, and then double-click the table that has related records.
3. Close the Show Table dialog box.
4. In the query design grid, the two tables should have lines, called joins, connecting them by their related fields. If the joins are not there, create them by dragging each related field from the first table (the table that has unmtached records) to the second table (the table that has related records).
5. Double-click a join to open the Join Properties dialog box. For each join, choose option 2, and then click OK.
   In the query design grid, the joins change so that they have arrows at one end.
   NOTE: Make sure that all of the joins are pointing in the same direction. The query will not run if the joins point in different directions, and may not run if any join is not an arrow. The joins should point away from the table that has unmtached records.
6. In the table that has unmatched records, double-click the fields that you want the query to return.
   Optionally, enter criteria for any of these fields, or create calculated fields.
7. For the table that has related records, double-click each field that is joined to the first table. For each of these fields, clear the box in the Show row, and type Is Null in the Criteria row.
8. On the Design tab, in the Results group, click Run.

How to Import an Excel Spreadsheet Into Access With a Macro

From http://smallbusiness.chron.com/import-excel-spreadsheet-access-macro-42538.html
by Kevin Lee, studioD
Step 1

Launch Access and open one of your databases.

Step 2

Move to the Tables pane and double-click the table you wish to load. The table’s fields appear in columns running across the top of the update pane. You’ll probably find it easier to work with your data if the column headings in your Excel spreadsheet match the names of the fields in your Access database.

Step 3

Launch Excel and open the spreadsheet you wish to load into Access. Verify that the spreadsheet’s column names match the Access table’s field names. If the spreadsheet has no column headings, right-click the spreadsheet’s first row and select “Insert.” Excel adds a new empty row to the top of the spreadsheet. Type a column heading for each column in the spreadsheet. For example, if the field names in your Access table are Name and Age, use those names as column headings in the spreadsheet.

Create Macro

Step 1

Return to Access and click “Create.” Double-click the “Macro” button on the Ribbon to view the Macro Builder.

Step 2

Click “Show All Actions” and then click “Action Catalog” to display the Action Catalog pane if it is not visible. This pane displays a list of macro categories.

Step 3

Move to the Action Catalog Pane and double-click “Data Import/Export” to view the macros within that category. The ImportExportSpreadsheet form appears. This form allows you to set up the parameters Access needs to import your spreadsheet.

if you dont see this action, go to Macro Tools > Design > Show All Actions

Show All Actions

Step 4

Type the name of your table in the “Table name” text box. Type the path name of your spreadsheet in the “File name” text box. For example, if it is a file named “Sales.xlsx” residing in a folder named “Spreadsheets” on your “C” drive, type the following into the “File Name” text box: .. C:\Spreadsheets\Sales.xslx

Step 5

Type “Yes” in the Has Field Names text box and press “Ctrl-S.” Type a name for your macro in the “Macro Name” text box and click “OK.”

Run Macro

Step 1

Click the tool bar’s “Database Tools” button and then click “Run Macros” to view the Run Macro popup window.

Step 2

Click the “Macro Name” drop-down menu and select the macro you created.

Step 3

Click “OK” to run the macro. Excel imports the spreadsheet data into the table.

Tips

• If you don’t see the imported spreadsheet data immediately, move to the Tables pane, right-click your table and select “Open” to refresh its view.
• or move to the Tables pane, then switch to Design view and then back to Datasheet view.
• Double-check your spreadsheet and Access table to verify that the number of fields in the table match the number of columns in the spreadsheet.