Limiting records

[cjany]

I have a query that pulls all records with an InstrumentCode of TARGETLT13. From those records I have another query that pulls the ActionCodes PPA, EXTENSION and TARGETLT13. All of these records will have TARGETLT13 actioncode, but some may have PPA or Extension actionCode. So I may have several records for one account number that would have a TARGETLT13 and PPA or EXTENSION.

Example:
AcctNumber InstCode ActCode DueDate
511 TargetLT13 TargetLT13 5/16/2006
511 TargetLT13 PPA 11/30/2006
609 TargetLT13 TargetLT13 5/26/2006

I need to send out bills for these accounts except for the accounts with a current PPA or Extension as in account number 511 above. If my query calls for PPA's or Extensions with a due date of < 7/7/2006, it will only take out the PPA record above and leave the TargetLT13 record, when I need to eliminate the entire account. But I do want to send a bill out to account 609 since there is no PPA or EXTENSION.

I'm using Access 2000.

Any help will be appreciated.

 

[Golom]

Select AcctNumber, InstCode, actCode, DueDate

From myTable A

Where NOT EXISTS
      (Select 1 From myTable X
       Where X.AcctNumber = A.AcctNumber 
         AND X.ActionCode = 'PPA')

   AND [COLOR=red]... other Where clauses ...[/color]