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  

Need Help Summing Sales and Donations in Report Footer



 
 
Thread Tools Display Modes
  #1  
Old August 8th, 2007, 05:46 AM posted to microsoft.public.access.reports
znibk
external usenet poster
 
Posts: 115
Default 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  
Old August 8th, 2007, 09:50 PM posted to microsoft.public.access.reports
Larry Linson
external usenet poster
 
Posts: 3,112
Default 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  
Old August 9th, 2007, 05:00 AM posted to microsoft.public.access.reports
znibk
external usenet poster
 
Posts: 115
Default 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  
Old August 12th, 2007, 08:03 AM posted to microsoft.public.access.reports
Larry Linson
external usenet poster
 
Posts: 3,112
Default 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

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 02:33 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.