Can I combine a WHERE and an INNER JOIN?
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:
Select *
FROM CONTRACT
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:
Select *
FROM CONTRACT
INNER JOIN Date_Patterns
on CONTRACT.Notes = Date_Patterns.Pattern.
So.....
#1. What I'd like to do is be able to combine these two queries into a single query. Is that possible?
Or....
#2. if I can't use the INNER JOIN approach in the same query, how about something like this to put it all together:
SELECT *
FROM CONTRACT
WHERE ((CONTRACT.ExpirationDate)
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?
- 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:
Select *
FROM CONTRACT
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:
Select *
FROM CONTRACT
INNER JOIN Date_Patterns
on CONTRACT.Notes = Date_Patterns.Pattern.
So.....
#1. What I'd like to do is be able to combine these two queries into a single query. Is that possible?
Or....
#2. if I can't use the INNER JOIN approach in the same query, how about something like this to put it all together:
SELECT *
FROM CONTRACT
WHERE ((CONTRACT.ExpirationDate)
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK, I tried it as a WHERE and that seemed to work. Thanks.
I'll be posting another question about nested INNER JOINS.
I'll be posting another question about nested INNER JOINS.
ASKER
and CONTRACT.ExpirationDate is between #5/1/2011# And #5/1/2015#
Is that supposed to be a separate WHERE clause? Or is it somehow part of the INNER JOIN, as in "INNER JOIN Statement-A and Statement-B"?