By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,968 Members | 1,661 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,968 IT Pros & Developers. It's quick & easy.

Multiple Criteria For a Single Query Field Question USING AND

P: 1
I am creating a database for my company with a list of candidates for hire and all their information and Certifications.
I am trying to set a criteria on the certification field so that when I lookup/search certain values the query gives me only the candidates with those certifications.

I used the "Or" statement and it gives me candidates with certification 1 and not certification 2 or vise versa
and also the ones that have both.. I only want the candidates with both certifications..
Also when I use the "And" statement it give me nothing in my query.
I am using a "one to many" relationship with my tables..
(Candidates and Certification)
the Certification table repeats the Candidate ID# for every certification that the Candidate has.

If anyone can help me with a solution I have tried everything and running out of options. lol
Jun 10 '15 #1
Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,430
What you need to do is do the OR filter. But then group by the candidate and count the certifications and return only those that have a count equal to 2.
Jun 10 '15 #2

NeoPa
Expert Mod 15k+
P: 31,769
What Rabbit says is correct.

Consider the results of each record returned. None of them can have multiple values in a single record. Your query is to find a group of records which contain both X certification and Y certification.

So, design a query to get all where either of these certifications is found, then group by the candidate and see how many records each has. Only a candidate with two records in the group has both certifications.

Does that make sense?
Jun 11 '15 #3

Post your reply

Sign in to post your reply or Sign up for a free account.