Table Invoice will have 1 record with an invoice number.  Table Trans can have multiple records with an invoice number that exists within the Invoice table.  When one of the Trans records has ACCOUNT 12345 or 65432 then I need to print the invoice amount minus the trans amount.  If either of those two accounts don't exist then I just need to print the invoice amt.  There may be 1 or both accounts in the TRANS table.

 

Let me state the logic I need to follow.

 

Print INVOICE.INVOICE_AMT - TRANS.AMOUNT

IF EXISTS TRANS.ACCOUNT = 12345 or 65432.

IF NO TRANS.ACCOUNT = 12345 or 65432

THEN Print  INVOICE.INVOICE_AMT

 

I am trying to use a formula because I have to sum up all invoices for a particular company for each year.  So in 2010, I could have a company with 5 separate invoices (INVOICE) that may have 2 or more line items (TRANS).  If I can get the above logic to work for an individual invoice, then I can use the sum function on the formula to get the rest.

 

Any help would be greatly appreciated.