Solved: Not a vba question - just normal query

[grichey]

I have a table with records of invoices. I'm trying to run a query to look for invoices that cancel each other. For example, there is one for 10,000 then further down the table there's another for -10,000 for the Account and the same Vendor. I have made the query to pull a copy of the negatives to another table already but I'm having trouble working out how find the corresponding + values in the original table.

[akn112]

would dsum work?

For example for account akn112 it would work like:

akn112total=dsum("[Amount]","tblRecords","[AccountName]='akn112'")

It would sum all the numbers under the field amount where the accountname is akn112

[rconverse]

If you already have them in a positve and negative table, then multply one of the tables by the -1 and then run a compare query. That will give youall the records that exactly cancel each other out.

HTH
Roger

[geekgirlau]

I'm trying to run a query to look for invoices that cancel each other.

Is that the end result that you're after, or did you just want the current balance for each customer? If you want a current balance, just create a group query - group by customer, sum the invoice amount. If you just want to see customers with outstanding invoices, set the criteria for the sum of the invoice amount to <>0.

[grichey]

Nah, I'm not trying to sum the totals, I'm trying to find actual records that cancel for example someone accidently created an invoice with the wrong amount then did a reverse.