Need Help Summing Sales and Donations in Report Footer

[Guest]

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

 

[Larry Linson]

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

 

[Guest]

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

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

 

[Larry Linson]

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

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

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