Query Multiple Criteria from Same Column

Posted on 2007-07-30
Last Modified: 2010-03-20
I have a MS Access table to store a Staff Code (StaffCode) and Operating Unit Code (OuCode). Basically, this is how my datas would look like in the table (StaffOu).

StaffCode (String)          OuCode (Integer)
S001                                        1
S001                                        2
S002                                        1
S002                                        2
S003                                        1
S004                                        3

As u can see from my table, the StaffCode and OuCode combination is a unique entry. My question is, how can i query for the Staffcode(s) to select those records with both OuCode 1 and 2 present. This is what i've done so far and it returned an empty result.

SELECT StaffCode
FROM StaffOu
WHERE OuCode=1 AND OuCode=2;

Hope to find the right SQL syntax for this. Thanks.
Question by:k_adli
    LVL 61

    Expert Comment

    SELECT StaffCode
    FROM StaffOu
    WHERE OuCode=1 OR OuCode=2;

    Author Comment

    Hi mbizup,

    Thanks for the prompt reply. But I think that's not the code that I wanted. Maybe I should have been clearer earlier.

    If i had written the code given by you, it would return the staff whose StaffCode = 3 as well. I only want those StaffCodes belonging to BOTH Operating Unit 1 and Operating Unit 2.

    Thanks once again.
    LVL 142

    Accepted Solution

    select s1.staffcode
    from yourtable s1
    join yourtable s2
      on s1.staffcode = s2.staffcode
     and s1.oucode = 1
     and s2.oucode = 2
    LVL 33

    Expert Comment

    by:Mike Eghtebas
    I think we need to do some prepration first.

    Make two list boxes:  lstStaffCode and lstOuCode both multiselect.

    You may want make selection from both, one or neither of them before opening your query. If selection is made from both, it will filter data accordingly. If the selections is made from one, it will ignore the other listbox. If no criteria is selected, it will show all records ignoring both list boxes.

    Using QueryDef your query qSearch (for example) save as qSearchTemp will be updated with SQL with criteria installed in it (by scanning the list boxes) and finally runnig:

    DoCmd.OpenQuery "qSearchTemp"

    If this sound right to you, we can work on it.


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    This video discusses moving either the default database or any database to a new volume.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now