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())));
5  2308 
"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?
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
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
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)));
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Deano |
last post by:
OK, I'm working on a solution to the following problem but there are other
ways to skin a cat as they say...
Here's the table (simplified);
ID EmployeeName SalaryAcc
1 Brown ...
|
by: Tony Williams |
last post by:
I have a database that hold s figures for each quarter. I want to create a
query whereby the user can ask for the running total of the data upto the
quarter they enter into a prompt. The database...
|
by: deko |
last post by:
I have a number of queries that pull totals from different tables. How do I
sum the different total values from each query to get a grand total? I
tried using a Union query like this:
SELECT...
|
by: New Guy |
last post by:
I'm trying to work with a system that somebody else built and I am
confounded by the following problem:
There is a table of payments and a table of charges. Each client has
charges and payments...
|
by: BerkshireGuy |
last post by:
I have the following code:
Dim strSQL As String
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim intNumOfPaid, intNumOfHypoed, intNumOfNotTaken, intNumOfDeclined,
intNumOfWasted,...
|
by: BerkshireGuy |
last post by:
I have a form that acts like a dashboard to show summarized data.
Currently, this form gets its summarized values from a total's query.
When the user selects to run the dashboard, they should be...
|
by: JJM0926 |
last post by:
I'm trying to create a running totals query in access 97. I have
followed the directions on how to do it from Microsofts website
article id 138911. I took their code they had and replaced it with...
|
by: Micheal |
last post by:
Greetings Access Group,
Being relatively new to Access, I try to work through problems on my own and have been very successful, although I have a conundrum that I have been working on for two days...
|
by: elias.farah |
last post by:
Hello Everyone,
I'm having some very weird behavior on a couple of Access forms. (Not
all forms, just some of them).
The forms have been working for years, under Access XP/2003 etc, and
last...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |
