A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Sum in report



 
 
Thread Tools Display Modes
  #1  
Old December 31st, 2009, 11:09 AM posted to microsoft.public.access.reports
paul p
external usenet poster
 
Posts: 6
Default Sum in report

Hi there,
I'm new to Access and I have a little question concerning sum in reports.
I set up a little db which stores infos about invoices of a freelancer. I have 2 tables in which I store clients and invoice data.
Now I'm creating a report to list all the invoices.
By using the standard 'create report' command in Access2007, I get a list of all of my records, which is fine.
I would like to sum up the values of the invoice price column but I have some problems.
When I select a value on the column TOT.IMPONIBILE, the only option available in the Total dropdown list in Access is Count.
By clicking it I get a field in the report footer with control =Count(*), which returns me the number of rows-records.
I tried to change it in =Sum([TOTALE IMPONIBILE]) which is the name of the field I want to sum up but as soon as I go to view my report, it asks me to provide parameter value for TOTALE IMPONIBILE.

Any help would be greatly appreciated.
Thanks in advance and Happy New Year to the forum!

Paul

PS: while the values for date, invoice number, client, object, amount hours, ?/h are retrieved by tables data, the values for the fields TOTALE IMPONIBILE are obtained by a formula (that is =[TOTALE ORE]*[PAGA ORARIA] )


Submitted via EggHeadCafe - Software Developer Portal of Choice
Web Service vs Web Workspace Part I
http://www.eggheadcafe.com/tutorials...eb-worksp.aspx
  #2  
Old December 31st, 2009, 03:47 PM posted to microsoft.public.access.reports
Dorian
external usenet poster
 
Posts: 542
Default Sum in report

Try changing =Count(*) to =Sum(*)
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"paul p" wrote:

Hi there,
I'm new to Access and I have a little question concerning sum in reports.
I set up a little db which stores infos about invoices of a freelancer. I have 2 tables in which I store clients and invoice data.
Now I'm creating a report to list all the invoices.
By using the standard 'create report' command in Access2007, I get a list of all of my records, which is fine.
I would like to sum up the values of the invoice price column but I have some problems.
When I select a value on the column TOT.IMPONIBILE, the only option available in the Total dropdown list in Access is Count.
By clicking it I get a field in the report footer with control =Count(*), which returns me the number of rows-records.
I tried to change it in =Sum([TOTALE IMPONIBILE]) which is the name of the field I want to sum up but as soon as I go to view my report, it asks me to provide parameter value for TOTALE IMPONIBILE.

Any help would be greatly appreciated.
Thanks in advance and Happy New Year to the forum!

Paul

PS: while the values for date, invoice number, client, object, amount hours, ?/h are retrieved by tables data, the values for the fields TOTALE IMPONIBILE are obtained by a formula (that is =[TOTALE ORE]*[PAGA ORARIA] )


Submitted via EggHeadCafe - Software Developer Portal of Choice
Web Service vs Web Workspace Part I
http://www.eggheadcafe.com/tutorials...eb-worksp.aspx
.

  #3  
Old December 31st, 2009, 03:53 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Sum in report

paul p wrote:
I'm new to Access and I have a little question concerning sum in reports.
I set up a little db which stores infos about invoices of a freelancer.


I have 2 tables in which I store clients and invoice data.
Now I'm creating a report to list all the invoices.
By using the standard 'create report' command in Access2007, I get
a list of all of my records, which is fine.


I would like to sum up the values of the invoice price column
but I have some problems. When I select a value on the column
TOT.IMPONIBILE, the only option available in the Total dropdown
list in Access is Count. By clicking it I get a field in the report footer
with control =Count(*), which returns me the number of rows-records.


I tried to change it in =Sum([TOTALE IMPONIBILE]) which is the
name of the field I want to sum up but as soon as I go to view
my report, it asks me to provide parameter value for TOTALE IMPONIBILE.


A very important thing to remember is that the word "field"
is used to referr to a column in a table/query. When you
are talking about the thingies on a form/report that are
used to display a value, you should use the word "control".

Many times a control is bound to a field in the
form/report's record source so the bound control and the
field appear interchangable and the different meanings is
usually distinguishable by the context of the discussion.

Keeping all that in mind, the aggregate functions (Count,
Sum, etc) only operate on fields, they are unaware of
controls. So, what you need to use to calculate the total
in the report footer text box is an expression that does not
use the name of any control. From your postscript, where
you said the value in the [TOTALE IMPONIBILE] control is
calculated by the expression =[TOTALE ORE] * [PAGA ORARIA].
I think the expression for the total should be:
=Sum([TOTALE ORE] * [PAGA ORARIA])

--
Marsh
MVP [MS Access]
  #4  
Old January 1st, 2010, 11:31 AM posted to microsoft.public.access.reports
paul p
external usenet poster
 
Posts: 6
Default Got it!

Thank you guys!
I tried the easy solution Dorian advised but didn't work.
=Sum([TOTALE ORE] * [PAGA ORARIA]) did the 'trick'.
Thank you for the explanation Marsh, as a simple word user I refer to fields as mere objects which retrieve/ask data.
I got the difference in Access, thanks.

ehm..actually I have another question on the same topic..
my report has another column, which lists the total Invoice price (including VAT), and I would like to sum up all the values listed.
I tried to use your solution by taking the value of the total Invoice (TotFattura control) and wrap it in a Sum function but it didn't work, and it asks me to provide a value for the parameter TOTALE IMPONIBILE.

My TotFattura control value is obtained by the formula
=[TOTALE IMPONIBILE]*(120/100)
and it works fine.
It adds up a 20% on the price I calculated with the formula you advised.
In the Footer control I wrote
=Sum([TOTALE IMPONIBILE]*(120/100))
but it doesn't work

Thanks again for your time and by the way Happy New Year

PP



Marshall Barton wrote:

paul p wrote:A very important thing to remember is that the word "field"is
31-Dec-09

paul p wrote:




A very important thing to remember is that the word "field"
is used to referr to a column in a table/query. When you
are talking about the thingies on a form/report that are
used to display a value, you should use the word "control".

Many times a control is bound to a field in the
form/report's record source so the bound control and the
field appear interchangable and the different meanings is
usually distinguishable by the context of the discussion.

Keeping all that in mind, the aggregate functions (Count,
Sum, etc) only operate on fields, they are unaware of
controls. So, what you need to use to calculate the total
in the report footer text box is an expression that does not
use the name of any control. From your postscript, where
you said the value in the [TOTALE IMPONIBILE] control is
calculated by the expression =[TOTALE ORE] * [PAGA ORARIA].
I think the expression for the total should be:
=Sum([TOTALE ORE] * [PAGA ORARIA])

--
Marsh
MVP [MS Access]

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Recovering a Non-Bootable Windows Server 2003
http://www.eggheadcafe.com/tutorials...nbootable.aspx
  #5  
Old January 1st, 2010, 03:21 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Got it!

That is exactly the same problem so maybe you need to get a
hammer and pound it into your head ;-)
"Aggregate functions (Count, Sum, etc)
only operate on fields, they are
unaware of controls."

Since [TOTALE IMPONIBILE] is a control, you can not use it
in an aggregate function. Use the expression in the
calculated control and keep doing that back through the
controls until the aggregate function uses ONLY record
source fields:

=Sum(([TOTALE ORE] * [PAGA ORARIA]) * (120/100))
--
Marsh
MVP [MS Access]


paul p wrote:
=Sum([TOTALE ORE] * [PAGA ORARIA]) did the 'trick'.
Thank you for the explanation Marsh, as a simple word user I refer to fields as mere objects which retrieve/ask data.
I got the difference in Access, thanks.

ehm..actually I have another question on the same topic..
my report has another column, which lists the total Invoice price (including VAT), and I would like to sum up all the values listed.
I tried to use your solution by taking the value of the total Invoice (TotFattura control) and wrap it in a Sum function but it didn't work, and it asks me to provide a value for the parameter TOTALE IMPONIBILE.

My TotFattura control value is obtained by the formula
=[TOTALE IMPONIBILE]*(120/100)
and it works fine.
It adds up a 20% on the price I calculated with the formula you advised.
In the Footer control I wrote
=Sum([TOTALE IMPONIBILE]*(120/100))
but it doesn't work


Marshall Barton wrote:
A very important thing to remember is that the word "field"
is used to referr to a column in a table/query. When you
are talking about the thingies on a form/report that are
used to display a value, you should use the word "control".

Many times a control is bound to a field in the
form/report's record source so the bound control and the
field appear interchangable and the different meanings is
usually distinguishable by the context of the discussion.

Keeping all that in mind, the aggregate functions (Count,
Sum, etc) only operate on fields, they are unaware of
controls. So, what you need to use to calculate the total
in the report footer text box is an expression that does not
use the name of any control. From your postscript, where
you said the value in the [TOTALE IMPONIBILE] control is
calculated by the expression =[TOTALE ORE] * [PAGA ORARIA].
I think the expression for the total should be:
=Sum([TOTALE ORE] * [PAGA ORARIA])


  #6  
Old January 4th, 2010, 12:00 PM posted to microsoft.public.access.reports
paul p
external usenet poster
 
Posts: 6
Default Any hint guys?

Why the formula I wrote in the last message doesn't work as expected, like the one Marsh posted for my previous question?

As I wrote:
My TotFattura control value is obtained by the formula

=[TOTALE IMPONIBILE]*(120/100)

and it works fine.

It adds up a 20% on the price I calculated with the formula you advised.

In the Footer control I wrote

=Sum([TOTALE IMPONIBILE]*(120/100))

but it doesn't work


I tried to do the calculations in a query and retrieve data from there, but it's a workaround, and I wonder how would it be possible to achieve it within a form.

thank you
regards

Paul




paul p wrote:

Got it!
01-Jan-10

Thank you guys!
I tried the easy solution Dorian advised but didn't work.
=Sum([TOTALE ORE] * [PAGA ORARIA]) did the 'trick'.
Thank you for the explanation Marsh, as a simple word user I refer to fields as mere objects which retrieve/ask data.
I got the difference in Access, thanks.

ehm..actually I have another question on the same topic..
my report has another column, which lists the total Invoice price (including VAT), and I would like to sum up all the values listed.
I tried to use your solution by taking the value of the total Invoice (TotFattura control) and wrap it in a Sum function but it didn't work, and it asks me to provide a value for the parameter TOTALE IMPONIBILE.

My TotFattura control value is obtained by the formula
=[TOTALE IMPONIBILE]*(120/100)
and it works fine.
It adds up a 20% on the price I calculated with the formula you advised.
In the Footer control I wrote
=Sum([TOTALE IMPONIBILE]*(120/100))
but it doesn't work

Thanks again for your time and by the way Happy New Year

PP

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
A custom Autopostback ASP.NET ServerControl
http://www.eggheadcafe.com/tutorials...tback-asp.aspx
  #7  
Old January 4th, 2010, 05:14 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Any hint guys?

Marsh has told you more than once that you can't sum a control. He then
provided an expression (on 1/1/2010) that should/might work. You seem to have
ignored all of this. Can you please test his suggestion?

--
Duane Hookom
Microsoft Access MVP


"paul p" wrote:

Why the formula I wrote in the last message doesn't work as expected, like the one Marsh posted for my previous question?

As I wrote:
My TotFattura control value is obtained by the formula

=[TOTALE IMPONIBILE]*(120/100)

and it works fine.

It adds up a 20% on the price I calculated with the formula you advised.

In the Footer control I wrote

=Sum([TOTALE IMPONIBILE]*(120/100))

but it doesn't work


I tried to do the calculations in a query and retrieve data from there, but it's a workaround, and I wonder how would it be possible to achieve it within a form.

thank you
regards

Paul




paul p wrote:

Got it!
01-Jan-10

Thank you guys!
I tried the easy solution Dorian advised but didn't work.
=Sum([TOTALE ORE] * [PAGA ORARIA]) did the 'trick'.
Thank you for the explanation Marsh, as a simple word user I refer to fields as mere objects which retrieve/ask data.
I got the difference in Access, thanks.

ehm..actually I have another question on the same topic..
my report has another column, which lists the total Invoice price (including VAT), and I would like to sum up all the values listed.
I tried to use your solution by taking the value of the total Invoice (TotFattura control) and wrap it in a Sum function but it didn't work, and it asks me to provide a value for the parameter TOTALE IMPONIBILE.

My TotFattura control value is obtained by the formula
=[TOTALE IMPONIBILE]*(120/100)
and it works fine.
It adds up a 20% on the price I calculated with the formula you advised.
In the Footer control I wrote
=Sum([TOTALE IMPONIBILE]*(120/100))
but it doesn't work

Thanks again for your time and by the way Happy New Year

PP

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
A custom Autopostback ASP.NET ServerControl
http://www.eggheadcafe.com/tutorials...tback-asp.aspx
.

  #8  
Old January 4th, 2010, 11:47 PM posted to microsoft.public.access.reports
paul p
external usenet poster
 
Posts: 6
Default clarification

Duane, thanks for your support.
I tested Marsh suggestion, and it actually worked for my previous question, as I wrote in my penultimate post.
And since it worked, I tried to use the same concept for the other formula I need.

In this case, I don't have two controls to multiply, but just one on which I need to add a 20% (VAT).

Marsh proposed =Sum([TOTALE ORE] * [PAGA ORARIA]) to sum up all the controls in which the expression was =[TOTALE ORE] * [PAGA ORARIA]. And it worked.

Now, I need to sum up all the values included in the controls in the TOTALE FATTURA (Tot Invoice) column, which values are the result of the expression =[TOTALE IMPONIBILE]*(120/100).
I thought the same approach Marsh proposed might have worked but it didn't.
In fact, I tried =Sum ( [TOTALE IMPONIBILE] * (120/100) ) but Access asks me to provide a value for the parameter TOTALE IMPONIBILE.

Could you tell me what am I missing?
I'm sorry for my poor understanding in this matter but I'm willing to learn, and I thank you for your time.

Regards,
Paul



Duane Hookom wrote:

Marsh has told you more than once that you cannot sum a control.
04-Jan-10

Marsh has told you more than once that you cannot sum a control. He then
provided an expression (on 1/1/2010) that should/might work. You seem to have
ignored all of this. Can you please test his suggestion?

--
Duane Hookom
Microsoft Access MVP


"paul p" wrote:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
MSDE (Microsoft Data Engine) Performance
http://www.eggheadcafe.com/tutorials...data-engi.aspx
  #9  
Old January 5th, 2010, 12:28 AM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default clarification

paul p wrote:

Duane, thanks for your support.
I tested Marsh suggestion, and it actually worked for my previous question, as I wrote in my penultimate post.
And since it worked, I tried to use the same concept for the other formula I need.

In this case, I don't have two controls to multiply, but just one on which I need to add a 20% (VAT).

Marsh proposed =Sum([TOTALE ORE] * [PAGA ORARIA]) to sum up all the controls in which the expression was =[TOTALE ORE] * [PAGA ORARIA]. And it worked.

Now, I need to sum up all the values included in the controls in the TOTALE FATTURA (Tot Invoice) column, which values are the result of the expression =[TOTALE IMPONIBILE]*(120/100).
I thought the same approach Marsh proposed might have worked but it didn't.
In fact, I tried =Sum ( [TOTALE IMPONIBILE] * (120/100) ) but Access asks me to provide a value for the parameter TOTALE IMPONIBILE.

Could you tell me what am I missing?



Either you are missing my two replies to this second
question or you are not reading it carefully enough to see
the difference from your first question.

--
Marsh
MVP [MS Access]
  #10  
Old January 7th, 2010, 09:42 PM posted to microsoft.public.access.reports
paul p
external usenet poster
 
Posts: 6
Default I did it!

I did it!
=Sum([TOTALE IMPONIBILE]*(120/100)) didn't work as I said, but by changing [TOTALE IMPONIBILE] (which is a control) with the previous expression I was considering with Marsh I solved.
My final working expression is
=Sum(([TOTALE ORE]*[PAGA ORARIA])*(120/100))
[TOTALE ORE]*[PAGA ORARIA] was the expression which calculated the value of the [TOTALE IMPONIBILE] control.

bye
Paul



paul p wrote:

clarification
04-Jan-10

Duane, thanks for your support.
I tested Marsh suggestion, and it actually worked for my previous question, as I wrote in my penultimate post.
And since it worked, I tried to use the same concept for the other formula I need.

In this case, I don't have two controls to multiply, but just one on which I need to add a 20% (VAT).

Marsh proposed =Sum([TOTALE ORE] * [PAGA ORARIA]) to sum up all the controls in which the expression was =[TOTALE ORE] * [PAGA ORARIA]. And it worked.

Now, I need to sum up all the values included in the controls in the TOTALE FATTURA (Tot Invoice) column, which values are the result of the expression =[TOTALE IMPONIBILE]*(120/100).
I thought the same approach Marsh proposed might have worked but it didn't.
In fact, I tried =Sum ( [TOTALE IMPONIBILE] * (120/100) ) but Access asks me to provide a value for the parameter TOTALE IMPONIBILE.

Could you tell me what am I missing?
I'm sorry for my poor understanding in this matter but I'm willing to learn, and I thank you for your time.

Regards,
Paul

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Restart ASP.NET apps Programmatically, Log Application_End Events, and use Web Management logging
http://www.eggheadcafe.com/tutorials...apps-prog.aspx
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:35 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.