i have modified a sales commission report to include tender information by adding a JOIN to the tenderentry table, here is my TablesQueried section from my report:
FROM TransactionEntry WITH(NOLOCK) INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber LEFT JOIN SalesRep WITH(NOLOCK) ON TransactionEntry.SalesRepID = SalesRep.ID LEFT JOIN Customer WITH (NOLOCK) ON [Transaction].CustomerID = Customer.ID LEFT JOIN Item WITH(NOLOCK) ON TransactionEntry.ItemID = Item.ID LEFT JOIN TenderEntry WITH(NOLOCK) ON TransactionEntry.TransactionNumber TenderEntry.TransactionNumber LEFT JOIN AccountReceivable WITH(NOLOCK) ON TransactionEntry.TransactionNumber = AccountReceivable.TransactionNumber LEFT JOIN c_CommPaid WITH(NOLOCK) ON TransactionEntry.ID = c_CommPaid.TransID
the problem is, now that i have added the JOIN to the TenderEntry table, I am getting duplicate rows in my report. This is because the tenderentry table sometimes has more than one entry for one transactionnumber. For example, if there is a transaction where the customer overtenders in cash and receives change back, there are two entries to the tenderentry table for the same transaction - the only difference in the entries is that the amount on one is the amount the customer tendered with, and the second entry is the change the customer got back.
is there something I can do so that i can still pull data into my report from this table, but eliminate the duplicate rows it is causing?
I thought about adding a WHERE clause to SelCriteria section, something like WHERE TenderEntry.Amount > 0, in order to eliminate one of those records it is pulling into the report. The problem with that is, sometimes there is a return or a void transaction that has a negative amount written to the tenderentry table, and this NEEDS to be pulled into this report.
anyone have another idea??
thank you, kevin