I'm trying to write a query that brings up a list of P&L transactions for a specified time period and subtotals by account. All looked okay except one account total was off. In doing some research (looking at the query transactions underlying the totals), I found the amount was an invoice that had been entered as one amount and was then modified. My query is bringing up the transaction for a modified invoice amount AND the invoice amount prior to the modification. Why would this be happening, and how can I filter it out? I tried to look at the txndeleted table to see if that would help, but the system hangs and it won't open. I was wondering if there was a table that I could join to that would help me filter out figures that are either deleted or have been modified...?

My SQL expression, if it matters is:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT Transaction.AccountRefFullName, Sum(Transaction.Amount) AS SumOfAmount, Account.AccountType
FROM [Transaction] INNER JOIN Account ON Transaction.AccountRefListID = Account.ListID
WHERE (((Transaction.TxnDate)>=[Start Date] And (Transaction.TxnDate)<=[End Date]))
GROUP BY Transaction.AccountRefFullName, Account.AccountType
HAVING (((Account.AccountType) In ("Income","Expense","CostOfGoodsSold","OtherIncome","OtherExpense")));

I was also thinking that there might be a way to filter out by transaction number within the transaction table itself - to get only the most recent modified version of the transaction number, that is. But my attempts to use "Max" haven't produced the desired result.

I appreciate your help.

Sally

The Transaction table acts as a audit file, if you were to modify the invoice ten times in the given time period, then there will be eleven entries for that invoice. Normally you would simply call up the P&L transactions like this:

sp_report ProfitAndLossDetail show Text, Blank, TxnType, Date, RefNumber, Name, Memo, ClearedStatus, SplitAccount, Debit, Credit, RunningBalance parameters  DateFrom = {d'2007-01-01'}, DateTo={d'2007-05-16'}