Can I combine a WHERE and an INNER JOIN?
Posted on 2011-04-25
Kind of a two-part question, as I'm not sure which approach is more feasible to do what I need to do, in Access 2007. I'm trying to find an efficient way to do the following in a single query, if possible:
- search through a table of Contracts to find those that expire within the next 4 years.
- search through the same table of Contracts to find those where a text field may contain a date string or the word(s) expire, expiration, etc.
The current query we use is only set up to search for the expiration dates, like this:
WHERE CONTRACT.ExpirationDate is between #5/1/2011# And #5/1/2015#
I am now trying to add the functionality of the string search. To this end, I have set up a Date_Patterns table which contains parameters for the strings to look for. In a previous EE thread, it was suggested I set up this table and do an INNER JOIN, like this:
INNER JOIN Date_Patterns
on CONTRACT.Notes = Date_Patterns.Pattern.
#1. What I'd like to do is be able to combine these two queries into a single query. Is that possible?
#2. if I can't use the INNER JOIN approach in the same query, how about something like this to put it all together:
WHERE ((CONTRACT.ExpirationDate) Between #5/1/2011# And #5/1/2015#) OR ((CONTRACT.Notes) LIKE Date_Patterns.Pattern)
Of course in this latter approach, when I run the query I am prompted to provide a value for Patterns, probably because the SELECT statement isn't selecting anything from the Date_Patterns table. However, I don't need the Patterns in my query results, I only need to use them to compare to Contract.Notes. Any suggestions here?