[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Can I combine a WHERE and an INNER JOIN?

Posted on 2011-04-25
Medium Priority
Last Modified: 2012-05-11
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 *
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 *
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?
Question by:swpa_wnt
  • 2
LVL 33

Accepted Solution

knightEknight earned 1000 total points
ID: 35460648
Do you mean this?

select *
INNER JOIN Date_Patterns
on CONTRACT.Notes = Date_Patterns.Pattern
and CONTRACT.ExpirationDate is between #5/1/2011# And #5/1/2015#

Author Comment

ID: 35461122
Your last line:

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"?

Author Comment

ID: 35461375
OK, I tried it as a WHERE and that seemed to work.  Thanks.

I'll be posting another question about nested INNER JOINS.


Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question