return multiple records

A

alex

Hello experts,

using Access '03.

I have a tbl that contains two columns (e.g.,):

CASE ACTION
--------- -----------
x 1
x 2
y 1
z 2

I want to return all records where action in (1 AND 2). As you can
see below, based on the same case number.

Results should look like:

CASE ACTION
--------- -----------
x 1
x 2

If I use AND, I get nothing; if I use OR, I get everything.

Any thoughts?
alex
 
A

Allen Browne

Use a subquery to see if the other record exists.

Something like this:
SELECT Case, Action
FROM Table1
AND EXISTS
(SELECT Case
FROM Table1 AS Dupe
WHERE Dupe.Case = Table1.Case
AND Dupe.Action <> Table1.Action);

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

An alternative (and faster executing) solution would work if you can set up
Case and Action to be a unique index (i.e. you cannot have 2 records where
case is x and Action is 1.)

SELECT Case,
Min(Action) AS MinAction
Max(Action) AS MaxAction
Count(Action) AS HowMany
FROM Table1
GROUP BY Case
HAVING Count(Action) > 1;
 
A

alex

Use a subquery to see if the other record exists.

Something like this:
SELECT Case, Action
FROM Table1
AND EXISTS
    (SELECT Case
    FROM Table1 AS Dupe
    WHERE Dupe.Case = Table1.Case
    AND Dupe.Action <> Table1.Action);

If subqueries are new, here's an introduction:
   http://allenbrowne.com/subquery-01.html

An alternative (and faster executing) solution would work if you can set up
Case and Action to be a unique index (i.e. you cannot have 2 records where
case is x and Action is 1.)

SELECT Case,
    Min(Action) AS MinAction
    Max(Action) AS MaxAction
    Count(Action) AS HowMany
FROM Table1
GROUP BY Case
HAVING Count(Action) > 1;

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.














- Show quoted text -
Click to expand...

Thanks Allen for your help...I think the answer is definitely in a
subquery.

alex
 
Top