Pulling All records according to one record

[cjany]

I have a query that pulls all properties by a single owner. EX.

Name Account# Exemption
John Smith 123456 H
John Smith 654321
Jane Doe 234561
Jane Doe 165432

If an owner has one account with an "H" exemption, I want to pull all accounts for that person. If a person does not have an "H" exemption, I don't want to pull any accounts. So in the above example I want all accounts for John Smith since he has an exemption, but none for Jane Doe.

Here's my SQL:
SELECT qryAllTargetLetters.EI2PRCLID, qryAllTargetLetters.NAMELF, qryAllTargetLetters.COMPOFCR, qryAllTargetLetters.ALTYP, qryAllTargetLetters.TaxDue, qryAllTargetLetters.EI2CASNUM, qryAllTargetLetters.SUMOFEI2ACTFEE, qryAllTargetLetters.TotalDue, qryAllTargetLetters.TXEXMCOD
FROM qryAllTargetLetters INNER JOIN qryMultiOwners ON qryAllTargetLetters.NAMELF = qryMultiOwners.NAMELF;

(EI2PRCLID is the account number in the above example, TXEXMCOD is the exemption)

Any ideas?

 

[lespaul]

(Typed not tested)
One way:

SELECT A.EI2PRCLID, A.NAMELF, A.COMPOFCR, A.ALTYP, A.TaxDue, A.EI2CASNUM, A.SUMOFEI2ACTFEE, A.TotalDue, A.TXEXMCOD
FROM qryAllTargetLetters A INNER JOIN qryMultiOwners M ON qryAllTargetLetters.NAMELF = qryMultiOwners.NAMELF WHERE A.NAMELF IN (SELECT NAME FROM qryAllTargetLetters WHERE TXEXMCOD = "H");

Another way:

SELECT A.EI2PRCLID, A.NAMELF, A.COMPOFCR, A.ALTYP, A.TaxDue, A.EI2CASNUM, A.SUMOFEI2ACTFEE, A.TotalDue, A.TXEXMCOD
FROM qryAllTargetLetters A INNER JOIN qryMultiOwners M ON qryAllTargetLetters.NAMELF = qryMultiOwners.NAMELF INNER JOIN (SELECT NAMELF FROM qryAllTargetLetters WHERE TXEXMCOD = "H") As N ON A.NAMELF = N.NAMELF);

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[JPMontreal]

You will have to make a query like that:

Select *
From TheTable
Where [Name] in (select [Name] from TheTable where Exemption =”H”)


Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com