Totals Query

How do I design a totals query that instead of the following result
>
YearOfAcc PremiumGBP
2004 9,142,306.95
2004 1,481,153.21
2005 11,981,987.85
2006 20,653,195.20
2006 -521,140.08
2006 -455,788.20
2006 -212,507.35
2006 -204,018.46
2007 785,231.92
2007 15,704,638.36
>
>
Brings back:
YearOfAcc PremiumGBP
2004 10,623,460.16
2005 11,981,987.85
2006 19,259,741.11
2007 16,489,870.28

How do I design a totals query that instead of the following result
>
YearOfAcc PremiumGBP
2004 9,142,306.95
2004 1,481,153.21
2005 11,981,987.85
2006 20,653,195.20
2006 -521,140.08
2006 -455,788.20
2006 -212,507.35
2006 -204,018.46
2007 785,231.92
2007 15,704,638.36
>
>
Brings back:
YearOfAcc PremiumGBP
2004 10,623,460.16
2005 11,981,987.85
2006 19,259,741.11
2007 16,489,870.28
>
>
The SQL I have currently is:
>
SELECT DISTINCTROW DatePart("yyyy",[value_date]) AS YearOfAcc,
PremiumReceivedPayment.original_amount AS PremiumGBP
FROM PremiumReceivedPayment
GROUP BY PremiumReceivedPayment.policy_no,
PremiumReceivedPayment.value_date, DatePart("yyyy",[value_date]),
PremiumReceivedPayment.original_amount
HAVING (((PremiumReceivedPayment.policy_no)=[Forms]![frmPolicy]!
[policy_no]) AND ((PremiumReceivedPayment.value_date) Between Date()
And DateAdd("yyyy",-5,Date())));
>

colin spalding wrote:
>Open up your query in the builder.
I'd probably remove the property setting for Unique/Distinct records.
>
I might change your DatePart() to something like
Year([ValueDate])
simply for brevity.
>
Next, I'd uncheck the Show checkbox for PolicyNo
>
I'd set OriginalAmount to Sum, not group.
>
You're subtracting 5 years from today's date. Did you want only records
in 2003 (2008-5) to be 6/12/2003 onward or did you want 1/1/2003 to get a
full year? You could use
DateSerial(Year(date())-5,1,1)
to get the first of the year.
>
That should get you started.
>
Doctoring
http://www.youtube.com/watch?v=oNRWjXbM5mE

colin spalding wrote:>>>>>
Open up your query in the builder.
I'd probably remove the property setting for Unique/Distinct records.
>
I might change your DatePart() to something like
* * * * Year([ValueDate])
simply for brevity.
>
Next, I'd uncheck the Show checkbox for PolicyNo
>
I'd set OriginalAmount to Sum, not group.
>
You're subtracting 5 years from today's date. *Did you want only records
in 2003 (2008-5) to be 6/12/2003 onward or did you want 1/1/2003 to get
a full year? *You could use
* * * * DateSerial(Year(date())-5,1,1)
to get the first of the year.
>
That should get you started.
>
Doctoringhttp://www.youtube.com/watch?v=oNRWjXbM5mE- Hide quoted text -
>
- Show quoted text -

On Jun 12, 2:22 pm, Salad <o...@vinegar.comwrote:
>>
>
Thanks that was really helpful and improved things a lot, but now the
result I get is as follows:
>
policy_no YearOfAcc PremiumGBP
M99UK1480 2003 9,115,764.00
M99UK1480 2004 9,142,306.95
M99UK1480 2004 1,481,153.21
M99UK1480 2005 11,981,987.85
M99UK1480 2006 20,653,195.20
M99UK1480 2006 -1,393,454.09
M99UK1480 2007 16,489,870.28
>
As you can see, I am still getting two entries for 2004 and 2006 when
what I need is a sum for all years.
>
My SQL syntax is as follows:
>
SELECT PremiumReceivedPayment.policy_no,
PremiumReceivedPayment.value_date AS YearOfAcc,
Sum(PremiumReceivedPayment.original_amount) AS PremiumGBP
FROM PremiumReceivedPayment
GROUP BY PremiumReceivedPayment.policy_no,
PremiumReceivedPayment.value_date
HAVING (((PremiumReceivedPayment.policy_no)=[Forms]![frmPolicy]!
[policy_no]) AND
((PremiumReceivedPayment.value_date)>DateSerial(Ye ar(Date())-5,1,1)));