Applecore Pages on Microsoft Access

Creating a Query that show unmatched records based on two or more criteria

One of the good things about Access is that it has several wizards built-in, that make it easy for the user to create things without knowing anything at all about coding. One of these is the 'Find Unmatched Query Wizard', which allows you to find records in one table that do not have related records in another table. However, this wizard only allows you to use one criteria to select the non-matching records.

If you have 2 or more criteria that you wish to use, then you must modify the query that is produced by the wizard when using one criteria. In the example, we will be using two tables, tblNew, which contains ClassCode and ValueCode, and tblOld, which contains the same fields. tblNew may have some extra records in that we wish to look at. Below are the steps involved:

  • Firstly, create a new unmatched Query, using the wizard, selecting tblNew to have the results shown in the query, tblOld to have the related records, initially matched on ClassCode, and to show all fields in the results. The resultant SQL will look like:
    SELECT DISTINCTROW tblNew.IDField, tblNew.ClassCode, tblNew.ValueCode, tblNew.Field1, tblNew.Field2
    FROM tblNew LEFT JOIN tblOld ON tblNew.ClassCode = tblOld.ClassCode
    WHERE (((tblOld.ClassCode) Is Null));
  • Next open the query in design mode and go into SQL View. There are two modifications that need to be made to add an extra criteria, which are highlighted below:
    SELECT DISTINCTROW tblNew.IDField, tblNew.ClassCode, tblNew.ValueCode, tblNew.Field1, tblNew.Field2
    FROM tblNew LEFT JOIN tblOld ON tblNew.ClassCode = tblOld.ClassCode AND tblNew.ValueCode=tblOld.ValueCode
    WHERE (((tblOld.ClassCode) Is Null) AND (tblOld.ValueCode) Is Null);
Now, if you run the query, you will only see records from tblNew where there are no records in tblOld with the same ClassCode and ValueCode values.

Top

 


HOME | NEW | TABLES | QUERIES | FORMS | REPORTS | GENERAL | API | DOWNLOADS | TUTORIAL | RESOURCES
E-MAIL
Copyright & Disclaimer

 

Last modified at 06/06/2006 15:00:05