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

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

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.
1 Solution
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.
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
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.


Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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