6. MICROSOFT ACCESS QUERIES – ADVANCED

6.8.6: Non-Equi Join

A non-equi join is any join where the join criteria does not specify equals, “=”.

Suppose we wish to list all persons in the Genealogy database who are younger than, say, Peter Chan. One approach to getting the results is to join the row for Peter Chan to another row in Person where the birthdate of Peter Chan is greater than the birthdate of the other person. This type of join would be a “greater than” join as opposed to an equi- join. Proceed in the following way:

      • Add Person to the relationships area twice so there is a Person table and a Person_1 table. If there are any relationship lines delete them.
      • In the criteria line for Person fields: for firstName type “Peter” and for LastName type “Chan”.
      • In the criteria line for birthDate in Person_1 type “> [Person].[birthDate]”
image
Figure 6.42: Non-equi join

In this way you are creating a “greater than” join.

      • Include attributes from Person_1 to display these younger people.
      • Run your query.

 

Exercises

1) Consider the Company database and create a query to list anyone younger than Tanya Dickson.

 

2) Modify the example in this section to list those people who are older than Peter Chan.

License

Icon for the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License

Relational Databases and Microsoft Access 365 Copyright © by Ron McFadyn is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License, except where otherwise noted.

Share This Book