How to get 2 separate totals in 1query?

[gdev]

Hi,

Please help.
I have the below fields in my query.

planinvestmentid
name
interest
amount
activity date

I want a total for each quarter in the current year and the sum from previous years not necessarily for each quarter.

Is it possible to calculate 2 separate totals in one query?
I am able to group by quarter for the current year. But I can't seemed to figure out, how to get the sum of the previous years.
I need to have both totals in a report.


Thanks a bunch
Gwen

 

[PHV]

Can you please post the SQL code you have so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gdev]

Hi
Thanks for responding

SELECT tTransaction.planinvestmentid, tParticipantInterest.Interest, Sum(tTransaction.transactAmt) AS SumOftransactAmt, Format([transactionDate],"q") AS [Activity Date], [interest]*[transactamt] AS [Participant Amount]
FROM tParticipantInterest INNER JOIN tTransaction ON tParticipantInterest.PlanInvestmentId = tTransaction.planinvestmentid
GROUP BY tTransaction.planinvestmentid, tParticipantInterest.Interest, Format([transactionDate],"q"), [interest]*[transactamt]
HAVING (((tTransaction.planinvestmentid) Is Not Null));

Hopefully this helps.

 

[PHV]

In which table is defined transactionDate ?
Are you sure this query returns the expected result ?
I wonder on [interest]*[transactamt] AS [Participant Amount]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

A starting point:
SELECT T.planinvestmentid, P.Interest, Sum(T.transactAmt) AS SumOftransactAmt, Format(T.transactionDate,'q') AS [Activity Date]
, P.Interest*Sum(T.transactAmt) AS [Participant Amount], Y.SumOfPreviousAmt
FROM (tParticipantInterest P
INNER JOIN tTransaction T ON P.PlanInvestmentId = T.planinvestmentid)
LEFT JOIN (
SELECT planinvestmentid, Sum(transactAmt) AS SumOfPreviousAmt
FROM tTransaction WHERE Year(transactionDate) < [Current year parameter]
GROUP BY planinvestmentid
) Y ON P.PlanInvestmentId = Y.planinvestmentid
WHERE T.planinvestmentid Is Not Null AND Year(T.transactionDate) = [Current year parameter]
GROUP BY T.planinvestmentid, P.Interest, Format(T.transactionDate,'q'), Y.SumOfPreviousAmt;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244