Query based on 2 data parameters

[jpkeller55]

I am not sure how to go about this in Access 2003. I want to run a query that will return only those records were sequence 1 = 7999 and sequence 2 = 8412. So with the following data:
ID Name Seq Payer
12456 Jones 1 7999
12456 Jones 2 8412
45278 Smith 1 7999
76293 Wilson 1 4568
92344 Abbott 1 7999
92344 Abbott 2 8412
33345 Black 1 7999
33345 Black 2 8896


These records are returned:
ID Name Seq Payer
12456 Jones 1 7999
12456 Jones 2 8412
92344 Abbott 1 7999
92344 Abbott 2 8412

Thanks for any help!

 

[PWise]

select ....
from tablename
where (seq=1 and payer = 7999) or (seq=2 and payer = 8412)

 

[jpkeller55]

When I run this query I get the following records:


12456 Jones 1 7999
12456 Jones 2 8412
45278 Smith 1 7999
92344 Abbott 1 7999
92344 Abbott 2 8412
33345 Black 1 7999


I only want the records in red to show were each ID has a sequence 1 that equals 7999 and a sequence 2 = 8412. Is this possible?

 

[PWise]

Select ...
from tablename
inner join (select id from tablename where seq=1 and payer = 7999 )seq1
on Seq1.id=tablename.id
inner join (select id from tablename where seq=2 and payer = 8412 )seq2
on Seq2.id=tablename.id

 

[PHV]

SELECT * FROM yourTable
WHERE ID In (SELECT ID FROM yourTable WHERE (Seq=1 AND Payer=7999) OR (Seq=2 AND Payer=8412) GROUP BY ID HAVING Count(*)=2)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PWise]

PHV:

your query will return a name with 2 (Seq=1 AND Payer=7999)