I have a table like one below with discrete configurations and need to search config field multiple times to get best configuration match.
For example in a table below I would like to list only the items that have config (SPECIAL2 & SPECIAL 7). Correct answer should be item (C,D). Please advise on SQL to achieve that. I am using access.
ITEM CONFIG
A A
A SPECIAL1
A SPECIAL2
A SPECIAL3
A SPECIAL11
A SPECIAL12
B B
B SPECIAL5
B SPECIAL6
B SPECIAL7
B SPECIAL8
C C
C SPECIAL2
C SPECIAL6
C SPECIAL7
C SPECIAL22
C SPECIAL23
C SPECIAL24
D D
D SPECIAL1
D SPECIAL2
D SPECIAL7
Following query did not work. Using OR works but givems me extra results that I don`t want.
SELECT CONFIG.[ITEM], CONFIG.[CONFIG1]
FROM CONFIG
WHERE (((CONFIG.[CONFIG1])="SPECIAL2" And (CONFIG.[CONFIG1])="SPECIAL7"));
Really appreciate any help with this. Thanks.
For example in a table below I would like to list only the items that have config (SPECIAL2 & SPECIAL 7). Correct answer should be item (C,D). Please advise on SQL to achieve that. I am using access.
ITEM CONFIG
A A
A SPECIAL1
A SPECIAL2
A SPECIAL3
A SPECIAL11
A SPECIAL12
B B
B SPECIAL5
B SPECIAL6
B SPECIAL7
B SPECIAL8
C C
C SPECIAL2
C SPECIAL6
C SPECIAL7
C SPECIAL22
C SPECIAL23
C SPECIAL24
D D
D SPECIAL1
D SPECIAL2
D SPECIAL7
Following query did not work. Using OR works but givems me extra results that I don`t want.
SELECT CONFIG.[ITEM], CONFIG.[CONFIG1]
FROM CONFIG
WHERE (((CONFIG.[CONFIG1])="SPECIAL2" And (CONFIG.[CONFIG1])="SPECIAL7"));
Really appreciate any help with this. Thanks.