Use sum from tableA as field in tableB report?
I need to create a report that calculates the following for each customer.
tableA.beginbalance - tableA.expenses + sum(tableB.deposits)
tableA has one record per customer. tableB has multiple records for each customer
Not sure how to approach this. I've looked at subreports and using SQL in an Add Command with no luck. I'm using Crystal 9.
You can add the join inside the Command so you don't have to join the Command with the tableA. This worked for an example I made:
SELECT tableA.*, CustomerDeposit.Deposit FROM (SELECT tableB.customer, SUM(tableB.deposits) AS Deposit FROM tableB GROUP BY tableB.customer) AS CustomerDeposit INNER JOIN tableA ON tableA.customer = CustomerDeposit.customer