Skip to Content
0
Former Member
Feb 16, 2009 at 03:08 PM

Query: How do you add a Total field and a Calculation

36 Views

Hi Experts,

I have writen the following query with the Query Generator. I have 2 questions:

I need to display the Total of column T2.[OpenSum]. How do I write this in the Query?

I need the T2.[OpenSum] to be multiplied with T3.[Commission] - on the line and put into a newly created column and then have a total at the bottom. How do I do this?

SELECT T0.[DocNum] AS 'Incoming Payment', T0.[DocDate] AS ' Incoming Payment Date', T0.[CardCode], T0.[CardName],  T3.[SlpName], T3.[Commission], T1.[DocNum] AS 'Invoice Number', T2.[OpenSum] AS 'Invoice Line Amounts' 
FROM ORCT T0  INNER JOIN OINV T1 ON T0.DocEntry = T1.ReceiptNum INNER JOIN INV1 T2 ON T1.DocEntry = T2.DocEntry INNER JOIN OSLP T3 ON T1.SlpCode = T3.SlpCode 
WHERE T0.[DocDate] >=[%0] AND  T0.[DocDate] <=[%1] AND  T3.[SlpName] =[%2] 
ORDER BY T1.[DocNum]

Any help would be greatly appreciated!

Thanks!

Marli