asked on

Can I nest INNER JOINs in sql/Access?

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:

SELECT *
FROM CONTRACT_MOD
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.

SOLUTION

Kent Dyer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ralmada

try adding some brackets and also for the patterns you migh consider adding "*" there

SELECT * 
FROM (CONTRACT_MOD
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)
      );

Open in new window

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

swpa_wnt

ASKER

kdyer,

That would explain why Access sometimes complains about the INNER JOIN. lol However, it does seem to run it okay. I used to work in SQL, so I just naturally used it.

Sharath and ralmada,

You all have suggested very similar code. So here's what I've got now:

====================================
SELECT *

FROM ((CONTRACT_MOD_ADD

INNER JOIN CONTRACT
On CONTRACT.ContractNumber = CONTRACT_MOD_ADD.ParentContractNumber)

INNER JOIN Date_Patterns
On CONTRACT_MOD_ADD.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)
);
====================================

This might work -- but I can't tell, as I just encountered another problem. The WHEN statement includes a condition for CONTRACT.ExpirationInd... but CONTRACT.ExpirationInd is NOT being selected in the query, so Access asks me to supply a value for it when I run the query. It appears I need to somehow pull that into my set. If I explicitly include CONTRACT.ExpirationInd in my SELECT statement, would that sort of override the need for the INNER JOIN with the CONTRACT table?

ralmada

>>so Access asks me to supply a value for it when I run the query<<

Please check the spelling of the field in your query. Usually Access ask your for a parameter when it doesn't recognize the field as such.

ralmada

Or probably the field is in CONTRACT_MOD_ADD not CONTRACT.... Please double check that

swpa_wnt

ASKER

Ah, yes, the field is ExpiredInd, not ExpirationInd.

I've got to leave work, but will report back here tomorrow if I see the results are still not right. Thanks for your help so far.

swpa_wnt

ASKER

Well, it appears we may be going with a new table to facilitate our reporting solution, since the table structure we have now doesn't really track the data we need -- and this new approach will negate my query development thus far.

But generally I think I had it working, so I'll be awarding points. Thanks for the help.