Query Multiple Criteria from Same Column

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.
Who is Participating?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
select s1.staffcode
from yourtable s1
join yourtable s2
  on s1.staffcode = s2.staffcode
 and s1.oucode = 1
 and s2.oucode = 2
SELECT StaffCode
FROM StaffOu
WHERE OuCode=1 OR OuCode=2;
k_adliAuthor Commented:
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.
Mike EghtebasDatabase and Application DeveloperCommented:
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.