By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,318 Members | 2,107 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,318 IT Pros & Developers. It's quick & easy.

Totals Query

P: n/a
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())));

Jun 27 '08 #1
Share this Question
Share on Google+
5 Replies


P: n/a

"colin spalding" <co***********@btopenworld.comwrote in message
news:f9**********************************@2g2000hs n.googlegroups.com...
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

Can you change GROUP BY to key on YearOfAcc in design view?
Jun 27 '08 #2

P: n/a
colin spalding wrote:
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())));
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
Jun 27 '08 #3

P: n/a
JvC
You can't group by the PremiumGBP. That forces the individual records. You
need to Sum PremiumGBP, and that will give the result you want.

John

"Salad" <oi*@vinegar.comwrote in message
news:i8******************************@earthlink.co m...
colin spalding wrote:
>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())));
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

Jun 27 '08 #4

P: n/a
On Jun 12, 2:22*pm, Salad <o...@vinegar.comwrote:
colin spalding wrote:
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())));

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 -
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)));
Jun 27 '08 #5

P: n/a
colin spalding wrote:
On Jun 12, 2:22 pm, Salad <o...@vinegar.comwrote:
>>colin spalding wrote:
>>>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())));

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 -


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)));
I have absolutely no idea why you are grouping on PolicyNo. Or why you
group by ValueDate? Do you? Do you want to show policy numbers for
those years? In your first example you had no policy no. Now you do.
Why don't you show your value_date as well so you can see why you get
multiple records in a year?

Here's what I'd do. I'd drag down ValueDate (2 times), OrigAmt, and
policynum. (4 columns).

Click View/Totals from the menu.

In the first ValueDate enter Year() around it...like
Year(ValueDate)
and set as GroupBy

In the second ValueDate, turn off show. Put your criteria (the
DateSerial) for this column. Change the Totals row to "Where"

Turn off Show for PolicyNum as well. Put in your criteria. Set to
"Where" as well.

For the Amount column, set to Totals row to Sum.

Run.

Very simple. Change column heading when you get the results you expect.
Ex:
Year Of Acc:Year(value_date)

Yo Lo Vi
http://www.youtube.com/watch?v=3cGU49EIkjc
Jun 27 '08 #6

This discussion thread is closed

Replies have been disabled for this discussion.