What table do I read to find all payments. I have tried these but get a different total then if I add up all the Payment lines on a “Transaction List by Customer”

select sum(TotalAmount) from ReceivePayment

select sum(TotalAmount-UnusedPayment) from ReceivePayment

select sum(TotalAmount) from ReceivePaymentLine

Try starting here:

SELECT ReceivePaymentLine.TxnDate as "Date",
Invoice.CustomerRefFullName as "Job",ReceivePaymentLine.AppliedToTxnRefNumber as "Inv No.",
Invoice.SubTotal as "Orig. Amt.", ReceivePaymentLine.AppliedToTxnDiscountAmount as "Discount",
ReceivePaymentLine.AppliedToTxnAmount as "Payment" FROM ReceivePaymentLine, Invoice
where ReceivePaymentLine.AppliedToTxnTxnID= Invoice.TxnID
and (ReceivePaymentLine.TxnDate >= {d '2007-06-01'} and ReceivePaymentLine.TxnDate < {d '2008-07-01'})
order by Invoice.CustomerRefFullName

Of course they most likely will not match as some payments may not be applied, should be:

SELECT     sum(TotalAmount) - sum(UnusedPayment) FROM ReceivePayment

I still get the wrong answer. Please tell me the select statement behind the TxnListByCustomer

The SQL for the TnxListByCustomer report is:

sp_report TxnListByCustomer show  RowData, TxnType, Date, RefNumber, Memo, Account, ClearedStatus, SplitAccount, Amount
parameters DateMacro = 'All' where RowType = 'DataRow'

however this also shows Sales Order, General Journals and Credit Memos not seen in the ReceivePayment table.

Yes, clearly I understand the sp_report TxnListByCustomer, I need to learn what tables this report reads so I can also read the same data and get the same answer. The sp_report is inadequate for my needs. I am asking what TABLES the sp_report SELECTS and what columns it gets the amounts from!

The QODBC tables are virtual, they don't exist in QuickBooks. sp_report directly calls the same QuickBooks reporting engine used in the QuickBooks User Interface via Intuit's qbXML SDK. There are no direct equivalents between many of reports and tables. Most reports comprise of data seen in multiple QODBC "virtual" tables and in some cases, only reports contain some data as Intuit have not provided full access to all data areas within QuickBooks.

The TnxListByCustomer report reads data from Sales Order, Invoice, General Journal, Credit Memo, ReceivePayment and their revalent LinkedTxn tables.