Help!! Looking for an if statement

[MonsterCR7]

Hi All,
I am looking for a formula to put into my access 2007 query screen to be able to recreate an aging report to show only open invoices.

EXAMPLE:
CUST # CUST NAME DOC TYPE APPLY TO NO TOTAL AMOUNT
1 JOHNNYS INVOICE 48015 $600.00
1 JOHNNYS PAYMENT 48015 -$600.00
31 PAULIES INVOICE 48056 $2750.00
31 PAULIES CREDIT MEMO 48056 -$1750.00
31 PAULIES PAYMENT 48056 $1000.00


SO ON AND SO FORTH....What im struggling to do is insert another column that will check the table for the same apply to no and if there are duplicates to then add the totals from both and if that is equal to or greater than 0 then the new column will insert text OPEN if not CLOSED or PAID

CANNOT base the report off customer number because a customer number can have upwards of over 100 invoices, so the formula would have to be based off the apply to number...

PLEASE HELP!!!!
THANKS

 

[MonsterCR7]

Thank you! The two step processed worked to perfection! Thanks again!

 

[Joe4]

Your welcome! Glad we got it all sorted out!
Does the whole process make sense (how its working)?
Feel free to ask any questions about how it works, if you have them.