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
|
|||
|
|||
Need Help Summing Sales and Donations in Report Footer
I am trying to get a Grand Deposit Total of Sales, Cash Donations, Other
Income, and Other Sales in the report footer. I have tried adding a new field in the query and referencing that field name in the report footer ie: AGDT. I used the sum in the Total Row of the Query and put Nz([ASalesDep],0)+Nz([CD],0)+Nz([OI],0)+Nz([OS],0) and I get a blank return when I run the query. As I said before, I have tried putting just the field name [AGDT] in the report footer and I have tried putting that same expression in the report footer, and also receive a blank spot on my report. Anyone, any suggetions? As always, your help is appreciated. k |
#2
|
|||
|
|||
Need Help Summing Sales and Donations in Report Footer
"znibk" wrote I am trying to get a Grand Deposit Total of Sales, Cash Donations, Other Income, and Other Sales in the report footer. I have tried adding a new field in the query and referencing that field name in the report footer ie: AGDT. I used the sum in the Total Row of the Query What "Total Row" of what Query? and put Nz([ASalesDep],0)+Nz([CD],0)+Nz([OI],0)+ Nz([OS],0) and I get a blank return when I run the query. As I said before, I have tried putting just the field name [AGDT] in the report footer and I have tried putting that same expression in the report footer, and also receive a blank spot on my report. If the calculation you used to create [AGDT] does not contain a value, why would you expect that putting the Field in the Control Source of a Control _anywhere_ on the report to show anything? Anyone, any suggetions? As always, your help is appreciated. k This seems to be our day in m.p.a.reports to be expected to correct errors without any description of the underlying data. Tell us what data you have, how it's structured, and what you have in the report (e.g., the parts that are working)... oh, yes, copy and past the SQL for the Query that is the RecordSource of the Report, too. Then maybe someone will have a remote chance of making a useful suggestion. Larry Linson Microsoft Access MVP |
#3
|
|||
|
|||
Need Help Summing Sales and Donations in Report Footer
Larry,
Learned something: How to view the SQL statement in Design View. Maybe someday, I'll even be able to totally understand what it all says. Here it is. SELECT tbl_Income.Bank, tbl_Income.IncType, tbl_Income.IncDate, tbl_Income.SS, tbl_Income.More, tbl_Income.Less, [SS]+Nz([More],0)-Nz([Less],0) AS ASalesDep, tbl_Income.Contributor, tbl_Income.ACD, Sum([ASalesDep]+[ACDDep]+Nz([AOSDep],0)+Nz([AOIDep],0)) AS AGTD, tbl_Income.ACD AS ACDDep, tbl_Income.AOS, tbl_Income.AOS AS AOSDep, tbl_Income.AOI, tbl_Income.AOI AS AOIDep, tbl_Income.LCD, tbl_Income.LCD AS LCDep FROM tbl_Income GROUP BY tbl_Income.Bank, tbl_Income.IncType, tbl_Income.IncDate, tbl_Income.SS, tbl_Income.More, tbl_Income.Less, [SS]+Nz([More],0)-Nz([Less],0), tbl_Income.Contributor, tbl_Income.ACD, tbl_Income.ACD, tbl_Income.AOS, tbl_Income.AOS, tbl_Income.AOI, tbl_Income.AOI, tbl_Income.LCD, tbl_Income.LCD HAVING (((tbl_Income.IncDate) Between [Enter Start Date] And [Enter End Date])); Incidentlly, when I try to add the tblBank, etc, and pull down the fieldname bank, so that in my report, I have only a text box, the only thing that the query shows is the cashdonations, not any of the storesales. I have to go into the report, as in the form, change the textbox for tbl_Income.bank, to a combo box and then use the (I think it is a wizard icon) the . . . by Row Souce to choose tbl_bank and into the query, put the tbl_bank.FIELDID, and tbl_bank.bank. (Hope that was the way to state the problem/question. Larry, I hope I have given you the information you want and you are able to help me out. Also hope you are able to help me out about the report. I don't want to have to use the combo box in the report, as I would like to have a statement in the footers which indicate what the ="Grand Total [Bank] Deposit " is. That involves, as stated in my original question, adding the sales deposit, cash deposit, other sales deposits, and other income. Thanks and good luck. k "Larry Linson" wrote: "znibk" wrote I am trying to get a Grand Deposit Total of Sales, Cash Donations, Other Income, and Other Sales in the report footer. I have tried adding a new field in the query and referencing that field name in the report footer ie: AGDT. I used the sum in the Total Row of the Query What "Total Row" of what Query? and put Nz([ASalesDep],0)+Nz([CD],0)+Nz([OI],0)+ Nz([OS],0) and I get a blank return when I run the query. As I said before, I have tried putting just the field name [AGDT] in the report footer and I have tried putting that same expression in the report footer, and also receive a blank spot on my report. If the calculation you used to create [AGDT] does not contain a value, why would you expect that putting the Field in the Control Source of a Control _anywhere_ on the report to show anything? Anyone, any suggetions? As always, your help is appreciated. k This seems to be our day in m.p.a.reports to be expected to correct errors without any description of the underlying data. Tell us what data you have, how it's structured, and what you have in the report (e.g., the parts that are working)... oh, yes, copy and past the SQL for the Query that is the RecordSource of the Report, too. Then maybe someone will have a remote chance of making a useful suggestion. Larry Linson Microsoft Access MVP |
#4
|
|||
|
|||
Need Help Summing Sales and Donations in Report Footer
I suggested you needed some specific information (the data you have, and how
it is structured -- that would be tables and fields, and what they mean.), then, as an afterthought, I suggested you also include the SQL. Really, the tables, the fields, and what the fields mean, and what you are trying to accomplish... a field named "More", "Less", or "SS" may be very meaningful to you but it isn't to me. I am reasonably certain that in my wildest nightmares, I've never tried to group a Totals Query on that many fields -- it boggles my mind, and almost suggests to me that the only grouping you're actually getting is on individual records, in which case, a totals query doesn't make a lot of sense to me. I can't imagine a properly-designed relational table where it would take that many fields to uniquely identify a record. On the other hand, maybe I just don't have a sufficiently vivid imagination. Larry Linson Microsoft Access MVP "znibk" wrote in message ... Larry, Learned something: How to view the SQL statement in Design View. Maybe someday, I'll even be able to totally understand what it all says. Here it is. SELECT tbl_Income.Bank, tbl_Income.IncType, tbl_Income.IncDate, tbl_Income.SS, tbl_Income.More, tbl_Income.Less, [SS]+Nz([More],0)-Nz([Less],0) AS ASalesDep, tbl_Income.Contributor, tbl_Income.ACD, Sum([ASalesDep]+[ACDDep]+Nz([AOSDep],0)+Nz([AOIDep],0)) AS AGTD, tbl_Income.ACD AS ACDDep, tbl_Income.AOS, tbl_Income.AOS AS AOSDep, tbl_Income.AOI, tbl_Income.AOI AS AOIDep, tbl_Income.LCD, tbl_Income.LCD AS LCDep FROM tbl_Income GROUP BY tbl_Income.Bank, tbl_Income.IncType, tbl_Income.IncDate, tbl_Income.SS, tbl_Income.More, tbl_Income.Less, [SS]+Nz([More],0)-Nz([Less],0), tbl_Income.Contributor, tbl_Income.ACD, tbl_Income.ACD, tbl_Income.AOS, tbl_Income.AOS, tbl_Income.AOI, tbl_Income.AOI, tbl_Income.LCD, tbl_Income.LCD HAVING (((tbl_Income.IncDate) Between [Enter Start Date] And [Enter End Date])); Incidentlly, when I try to add the tblBank, etc, and pull down the fieldname bank, so that in my report, I have only a text box, the only thing that the query shows is the cashdonations, not any of the storesales. I have to go into the report, as in the form, change the textbox for tbl_Income.bank, to a combo box and then use the (I think it is a wizard icon) the . . . by Row Souce to choose tbl_bank and into the query, put the tbl_bank.FIELDID, and tbl_bank.bank. (Hope that was the way to state the problem/question. Larry, I hope I have given you the information you want and you are able to help me out. Also hope you are able to help me out about the report. I don't want to have to use the combo box in the report, as I would like to have a statement in the footers which indicate what the ="Grand Total [Bank] Deposit " is. That involves, as stated in my original question, adding the sales deposit, cash deposit, other sales deposits, and other income. Thanks and good luck. k "Larry Linson" wrote: "znibk" wrote I am trying to get a Grand Deposit Total of Sales, Cash Donations, Other Income, and Other Sales in the report footer. I have tried adding a new field in the query and referencing that field name in the report footer ie: AGDT. I used the sum in the Total Row of the Query What "Total Row" of what Query? and put Nz([ASalesDep],0)+Nz([CD],0)+Nz([OI],0)+ Nz([OS],0) and I get a blank return when I run the query. As I said before, I have tried putting just the field name [AGDT] in the report footer and I have tried putting that same expression in the report footer, and also receive a blank spot on my report. If the calculation you used to create [AGDT] does not contain a value, why would you expect that putting the Field in the Control Source of a Control _anywhere_ on the report to show anything? Anyone, any suggetions? As always, your help is appreciated. k This seems to be our day in m.p.a.reports to be expected to correct errors without any description of the underlying data. Tell us what data you have, how it's structured, and what you have in the report (e.g., the parts that are working)... oh, yes, copy and past the SQL for the Query that is the RecordSource of the Report, too. Then maybe someone will have a remote chance of making a useful suggestion. Larry Linson Microsoft Access MVP |
Thread Tools | |
Display Modes | |
|
|