Struggling with this query

[ramangill]

Basically it is easy. I have about 20 records with 3 columns. ID, Username and TX. TX values are random characters. I need to write a query where TX is either one of 3 TX values and where TX is one of 5 values and where TX is one of 7 values. If neither of the groupings have a match then the query should return 0 rows. For some reason this simple logic always returns 0. I can not figure it out.



tx values are

a1
a2
a3
b1
b2
b3
b4
etc, etc, etc.

Any suggestions. I was doing something like:

select * from abc
where tx in ('a1,a2,a3) and tx in (b1,b2,b4) and etc, etc.

Logically I am hoping it would bring the rows that match all of the conditions in which this case it should however it returns nothing.

[June7]

The 'and' operator won't work because no record can match the criteria. Each record has only one TX value, how can any record match all 3 criteria?

Perhaps you want the OR operator?

[ramangill]

But my goal is to meet the criteria in set 1 and criteria of set 2 and criteria of set 3. I understand what your saying and it makes sense. But how would I make this into SQL logic. If at least one of the criteria sets does not match then I want the overall query to return 0 rows. If at least one match is found in each criteria set then return those rows. Hope this clears it up and you can assist. Looking forward to the response.

[June7]

Did you try the OR operator instead AND? Still not what you want? You showed an example of raw data, now show what you want for output.

[ramangill]

Raw data:

ID	username	SAPTX
1	parm
2	parm	xk99
3	parm
4	parm	me21n
5	parm	me22n
6	parm	me2l
7	parm	me59
8	parm	me29n
9	parm	miro
10	parm	f-43
11	parm	f110
12	parm	f-53
13	parm	fb60

What I am trying to do is:

SELECT * from
tab
where
saptx in ('xk01','xk02','xk99')
and
saptx in ('me21n','me22n','me2l','me59','me29n')
and
saptx in ('miro','f-43')
and
saptx in ('f110','f-53','fb60')

So the above query will return 11 rows.

But if row ID 2 had a value of SAPTX = 'abc' then the above query should be giving me 0 rows. Basically I am trying to do a minus query like I would in Oracle and I was able to get it working in Oracle fine, but the requirement is MS Access and I am trying. I know now the query will not work, but I am trying get to the point where I can roll with it.
Any ideas?

[ramangill]

I was working with this query as well:

select * from test a
where a.saptx in ('xk01','xk02','xk99')
union
select * from test b
where b.saptx in ('me21n','me22n','me2l','me59','me29n')
union
select * from test c
where c.saptx in ('miro','f-43')
union
select * from test d
where d.saptx in ('f110','f-53','fb60')

Trying to get this to some sort of a MINUS like where if one of the UNIONS returns 0 rows then the whole query should return 0 rows.
Any thoughts?

[June7]

Selecting records based on values in other records of same table is tricky. Usually requires nested subquery or domain aggregate function calls. The latter can be very slow performance.

[ramangill]

Any ideas on how or a start?

[June7]

Consider:

Query1
SELECT tab.username, Sum(saptx In ('xk01','xk02','xk99')) AS a, Sum(saptx In ('me21n','me22n','me2l','me59','me29n')) AS b, Sum(saptx In ('miro','f-43')) AS c, Sum(saptx In ('f110','f-53','fb60')) AS d
FROM tab
WHERE ((Not (tab.[SAPTX]) Is Null))
GROUP BY tab.username;

Query2
SELECT tab.ID, tab.username, tab.SAPTX, Query1.a, Query1.b, Query1.c, Query1.d
FROM Query1 INNER JOIN tab ON Query1.username = tab.username
WHERE (((Query1.a)<>0) AND ((Query1.b)<>0) AND ((Query1.c)<>0) AND ((Query1.d)<>0));