sandey sandey - 10 months ago 48
SQL Question

SQL Statement OR: show every row with the value in it malfunction

I have a very specific problem:

I have a List with Companies and different Roles to this companies in Access, everything in one Table, it look like this:

Company // Role 1 // Role 2 // Role 3 // Role 4 //

Comp1 // Ted // // // Jimmy //

Comp2 // Lin // Ted // Andy // //

and so on...

I need now to make a sql statement which gives me every company (row) in which Ted, as an example, has a role.

I tried it like this in Access under SQL - Statement:

SELECT Firmen.Firma, Firmen.[Role1], Firmen.[Role2], Firmen.[Role3]
FROM Firmen
WHERE (((Firmen.[Role1])="Ted") AND ((Firmen.[Role2])="Ted") AND ((Firmen.[Role3])="Ted"));


But then it shows only rows where Ted is in the first column, I know there is a maybe stupid mistake in it and I had this problem already but I cant remember how I solved it last time.

Would be nice if you could help, because I really need it for work

Thanks and a nice day

Answer Source

You are using the and logical operator instead of the or logical operator. Change it, and you should be OK. Or better yet, you could use the in operator:

SELECT Firmen.Firma, Firmen.[Role1], Firmen.[Role2], Firmen.[Role3]
FROM   Firmen
WHERE  "Ted" IN (Firmen.[Role1], Firmen.[Role2], Firmen.[Role3])
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download