I have the following sql statement in Data environment that currently gives me this report. I am using vb6sp6 and access 2002 db.
Quote:
HTML Code:
SPACENO| NAME |TOTALDUE| CHECKAMT| DIFFERENCE
10 Joe 300 100 200
20 Mary 300 300 0
30 Dave 300
40 Tom 300 300 0
total = 200
|
Quote:
basically, total due - checkamt will equal diff., if there are duplicate spaceno
then the two checkamt will add up and only show one spaceno. The the total of all diff is calculated at the end of the report. I dont need any sql for the total.
|
Code:
SELECT gas_bill.spaceno, max(gas_bill.name_gas) as name_gas, Max(gas_bill.total_due) as total_due, Sum(receipts.check_amt) as check_amt,
(Max(gas_bill.total_due) -Sum(receipts.check_amt)) as diffl
FROM receipts RIGHT JOIN gas_bill ON receipts.spaceno=gas_bill.spaceno group by gas_bill.spaceno
Quote:
|
I need to add another column called cashamt to do the same as check amt to have the same function and using the same diff column to show the results.
|
Quote:
HTML Code:
SPACENO| NAME |TOTALDUE| CHECKAMT| CASHAMT | DIFF
10 Joe 300 100 200
20 Mary 300 300 0
30 Dave 300
40 Tom 300 300 0
50 AL 300 300 0
total = 200
|
dBforums
>
sql query to substract to columns
09-23-05, 17:50
Linear Mode
