Can I nest INNER JOINs in sql/Access?
Posted on 2011-04-25
In Access 2007, is it possible to nest INNER JOINS in a single query?
here's the gist of my query:
We have contracts and contract mods stored in separate tables (one to many relationship, where CONTRACT.ContractNumber = CONTRACT_MOD.ParentContractNumber).
I need to select contract mods where: a) the expiration date is empty or within a certain date range; b) the mod has one of several strings appearing within a field called Notes; and c) the parent contract is not expired.
I have set up the text string parameters in a table called Date_Patterns so I can do an INNER JOIN with that table (CONTRACT_MOD.Notes LIKE Date_Patterns.Pattern). However......... To get the proper set of mods and parent contract data, I first need to do an INNER JOIN between CONTRACT and CONTRACT_MOD. Thus, my query looks something like this:
INNER JOIN CONTRACT On CONTRACT.ContractNumber = CONTRACT_MOD.ParentContractNumber
INNER JOIN Date_Patterns On CONTRACT_MOD.Notes LIKE Date_Patterns.Pattern
WHERE ((((CONTRACT_MOD_ADD.ExpirationDate) = "") OR ((CONTRACT_MOD_ADD.ExpirationDate) Between #5/1/2011# And #5/1/2015#)) AND (CONTRACT.ExpirationInd)=No));
But I'm getting syntax errors on the "union query" -- even though there is no explicit union query. Is there a proper syntactical way to nest these INNER JOINS?
Thanks for any direction.