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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|