Use sum from tableA as field in tableB report?

[Former Member]

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.

Any suggestions?

Mark

[Former Member · Accepted solution]

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

[Former Member]

Group the records on tableB on customer, and select SUM(deposits), something like:

SELECT tableB.customer, SUM(tableB.deposits) FROM tableB GROUP BY tableB.customer

This can be joined on customer with tableA.

[Former Member]

Thanks Ioan,

That is the SQL I have in my Command now. In the code below, CDS_STATDAY is table B, Meta_ID is the customer number and CompEarned would be deposits (more or less0.

Select dbo.CDS_STATDAY.Meta_ID, Sum(dbo.CDS_STATDAY.CompEarned)

From dbo.CDS_STATDAY

Group By dbo.CDS_STATDAY.Meta_ID

I have an inner join in the Database Expert link section "CDS_STATBALANCE.Meta_ID --> Command.Meta_ID". I am trying to use the result in the detail section of my Crystal report and end up crashing to the desktop. I assumed my structure or syntax was wrong to cause the crash.

Mark