You're doing the Total Cost calculation in the report, in a textbox on
the
report (in the Detail section)? If yes, just copy the expression from
that
textbox, copy it into the ControlSource of a textbox in the report's
footer,
and add a Sum function around the entire expression. Does that work?
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Hey Ken ... okay, I've been able to figure out most of the fields by
doing
the calcs in the query (totals button) ... but ... in a particular row
(which
has been totalled) to be one row (3 detail records turn into 1 record
in
the
footer) ... however, in the row, I have to do a calculation that
depends
on
whether the course is a 'regular' course or an 'onsite' course ...
This is the Total Cost:
in the case of regular ... take the fields,
studentsreimbursed*amtreimbursed
in the case of onsite ... take the course cost-totaldeposits
+(studentsreimbursed*amtreimbursed)
now, all this works ... but ... now I want another group total
underneath
for the whole region ... since the Total Cost is a calculation, you
can't
do
a sum([ofthefield]) in the region footer .. ???
--
Thanks!!
T. Marie
:
Be sure that you have a control bound to the curStudentDeposit field
(even
if the control is blank); reports often won't see a field from the
RecordSource unless it's bound to a control in the report.
Instead of using Count times the deposit value, wouldn't it work just
the
same if you just sum the curStudentDeposit field's values; assuming,
that
is, that you have one row in the Group for each student? So the
expression
for the textbox in the Group Footer would be
=Sum([curStudentDeposit])
Otherwise, if you have one row for each course, and you have a Count
within
each row, then try this expression:
=Sum((Count([strStudentID])*[curStudentDeposit]))
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Hi Ken ... I use a query ... but switched to sql view .. its nasty
...
I'll
send you a word doc with the report published in word as well as the
design
...
SELECT tblCourseHistory.intFiscalYear, tblCourseHistory.strSession,
tblEligibilityPreRegistration.strLHIN, tblCourses.strCourseName,
tblCourseHistory.strCourseType, tblCourseHistory.strStudentID,
tblCourseHistory.curStudentDeposit,
tblPurchaseOnSiteCourses.strPurchaseOnSiteCost,
tblCourseHistory.strReimbursed,
tblCourseHistory.curAmountReimbursed,
[intFiscalYear] & " " & tblcoursehistory!strsession AS YearSession,
tblSessions.strSessionID, Abs(IIf([strreimbursed]="Yes",-1,0)) AS
Reim
FROM ((tblCourses RIGHT JOIN (tblEligibilityPreRegistration LEFT
JOIN
tblCourseHistory ON tblEligibilityPreRegistration.strStudentID =
tblCourseHistory.strStudentID) ON tblCourses.strCourseCode =
tblCourseHistory.strCourseCode) LEFT JOIN tblPurchaseOnSiteCourses
ON
tblCourses.strCourseCode =
tblPurchaseOnSiteCourses.strPurchaseOnSiteCourseCode) LEFT JOIN
tblSessions
ON tblCourseHistory.strSession = tblSessions.strSession
WHERE (((tblCourseHistory.intFiscalYear) Like [Enter Year or leave
blank
for
all:] & "*") AND ((tblCourseHistory.strSession) Like [Enter Session
or
leave
blank for all:] & "*") AND ((tblEligibilityPreRegistration.strLHIN)
Like
[Enter LHIN or leave blank for all:] & "*"))
ORDER BY tblSessions.strSessionID;
--
Thanks!!
T. Marie
:
Can you post the SQL statement of the report's RecordSource query?
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Hey Ken ... I did try your way too ... e.g.
I took #ofregisteredstudent * student deposit = total student
deposits
...
now student deposit is a field that has not been grouped ... the
deposit
should be the same for all students of that course ...
then for the LHIN (like an area) group level ... I did unbound
controls
and
the expressions were:
=count(#ofregisteredstudent), sum(student deposit) ... which was
a
problem
because I only want it once ... i.e. just show 3 students * $200
=
$600
for
one course and then say 5 students * $300 = $$1500 for another
course
but
then in the total line for LHIN group, it would be 3+5=8 students
(that's
working), $200+$300=$500 for student deposits (which to me makes
no
sense)
... and then $600 + $1500=$2100 and I've tried =sum(name of the
total
cost
field) then I tried =[field that was the total student
count]*[field
that
was
the total deposits] ... ahhhhhhhhhhhhhhhhh ... what am I doing
wrong??
--
Thanks!!
T. Marie
:
You should simply be able to repeat the expression you've used
for
the
computed control in the course group footer. All you are doing
is
aggregating the data at a different group level. Say for
instance
a
report is based on a Sales table with columns Area, Store,
Product,
UnitPrice and Quantity and summaries sales by store and by area,
in
the detail section, if shown, a computed control to show the
gross
sale price per transaction would be:
=[UnitPrice]*[Quantity]
The total sales per store in a computed control in the store
group
footer would be:
=Sum([UnitPrice]*[Quantity])
To return the total sales per area in the area group footer
would
use
exactly the same expression. And to return the total sales
overall
in
the report footer would again use exactly the same expression,
i.e.
the expression is based on the original columns, not on the
computed
control used at an lower group level or in the detail section.
Ken Sheridan
Stafford, England
On May 10, 3:29 pm, Tina Marie
<
[email protected]>
wrote:
Hello ... I have a report where I am doing calculations across
a
row
... e.g.
total students x deposits = total deposits ...
Now ... that above row is based on a sum of all students
within a
particular
course ... in other words, I've done a detail line but then
turned
it
off
Now ... I want to per LHIN (a group) to sum all courses within
a
LHIN
and I
can't use the new field names I've created ... can't use a
sum(
...)
of
the
above ...
Not that familiar with SQL or VBA ... ANY HELP???
