Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

Using OR in sql query

I have the following query that produces my desired result. The issue is, when I try to open this in design view, it removes the section that matches departments from the employee table to the jobopenings table

SELECT tblEmployees.ClockNumber, tblJobCards.Department1, tblJobCards.Department2, tblJobCards.DateTimeSigned, tblJobOpenings.JobNumber, tblEmployees.LastName, tblEmployees.FirstName
FROM (tblEmployees INNER JOIN tblJobCards ON tblEmployees.ClockNumber = tblJobCards.ClockNumber) LEFT JOIN tblJobOpenings ON

(tblJobCards.Department1 = tblJobOpenings.Department or tblJobCards.Department2 = tblJobOpenings.Department);



How can I code this so design view doesn't remove the OR condition?
0
tremak
Asked:
tremak
  • 4
  • 3
1 Solution
 
8080_DiverCommented:
It may be as simple as adding a missng parenthesis.  Try the following.  If that doesn't work, open the query in the design view and cut and paste what the design view shows you.
SELECT E.ClockNumber, 
       JC.Department1, 
       JC.Department2, 
       JC.DateTimeSigned, 
       JO.JobNumber, 
       E.LastName, 
       E.FirstName
FROM   tblEmployees E
INNER JOIN tblJobCards JC
   ON     E.ClockNumber = JC.ClockNumber 
LEFT JOIN tblJobOpenings JO
   ON  (JC.Department1 = JO.Department) OR
       (JC.Department2 = JO.Department);

Open in new window

0
 
tremakAuthor Commented:
After I get a message stating MS Access can't represent the join expression (tblJobCards.Department1 = tblJobOpenings.Department or tblJobCards.Department2 = tblJobOpenings.Department)

the query doesn't change, but prevents me from opening it in Query Design

0
 
8080_DiverCommented:
Did you puit the query I provided into the SQL View?
Your query has the OR'ed conditions combined in one set of paarentheses and that may be where the issue is.  
Another option is to remove one of the two OR'ed conditions, open the query in design view and then try to add the condition back.
However, does the query work if you save it in the SQL View?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
8080_DiverCommented:
Come to think of it, in thinking about the manner in which the left outer join is represented in the design view, I think it may not be possible to represent that particular set of constraints using the technique that Access uses in the graphic designer.
What you may need to do is to create a second query that joins the tblJobCards and the table tblJobOpenings and then do the left outer join on that query.
0
 
tremakAuthor Commented:
Your query gave me errors so I quit trying

The query I have will work when saved in SQL view. I would just like to view in Query designer since it would make adding fields, etc easier.
0
 
8080_DiverCommented:
You might want to try the second approach I suggested, i.e. the creation of the secondary query that you use in doing the left outer join.  IT is a bit of a kludge but it works around the quirk in Access that you have run into.  It also lets you add columns more easily, although you may have to add them to the secondary query first in order to be able to add them to your main query.
0
 
tremakAuthor Commented:
I'll buy the second query advice. Points awarded
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now