Sum in report

[paul p]

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/tutorial...b-3ca180f5bfdb/web-service-vs-web-worksp.aspx

 

[Dorian]

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".

 

[Marshall Barton]

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])

 

[paul p]

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 yo
are talking about the thingies on a form/report that ar
used to display a value, you should use the word "control"

Many times a control is bound to a field in th
form/report's record source so the bound control and th
field appear interchangable and the different meanings i
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 o
controls. So, what you need to use to calculate the tota
in the report footer text box is an expression that does no
use the name of any control. From your postscript, wher
you said the value in the [TOTALE IMPONIBILE] control i
calculated by the expression =[TOTALE ORE] * [PAGA ORARIA]
I think the expression for the total should be
=Sum([TOTALE ORE] * [PAGA ORARIA]

-
Mars
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/tutorial...ed-356344c686ee/recovering-a-nonbootable.aspx

 

[Marshall Barton]

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))

 

[paul p]

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/tutorial...8-c59ac103cfcf/a-custom-autopostback-asp.aspx

 

[Marshall Barton]

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.

Doing the calculation in the query is a valid way to do this
and some people would even recommend doing it that way.

I explained how to do it a text box in my reply on 1/1, but
apparently you are not seeing it so here's a copy of that
reply :
-------------------------------------------------------------------------------
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))

 

[Duane Hookom]

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?

 

[paul p]

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 the
provided an expression (on 1/1/2010) that should/might work. You seem to hav
ignored all of this. Can you please test his suggestion

-
Duane Hooko
Microsoft Access MV

:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
MSDE (Microsoft Data Engine) Performance
http://www.eggheadcafe.com/tutorial...97-8cf9b615b9c4/msde-microsoft-data-engi.aspx

 

[Marshall Barton]

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.

 

[paul p]

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/tutorial...f2-e68b28dbe836/restart-aspnet-apps-prog.aspx