TINA - TOTALS OF TOTALS IN A REPORT

T

Tina Marie

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???
 
G

George

Tina,

Assuming that you have a groupping level which shows details (records) below:

I assume also that you have an unbound object (textbox) to calculate the
total deposits for each row then in the grou footer or group header add
another txt box with =sum([YourTextBox]).

In case you dont have a group then add the above in the report header or
footer.

Hope this helps,

GeorgeCY

Ο χÏήστης "Tina Marie" έγγÏαψε:
 
K

ken

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
 
T

Tina Marie

Hi George ... I am doing that but comes up a prompt box ... something wrong
... I'll check Ken's answer ...
--
Thanks!!

T. Marie


George said:
Tina,

Assuming that you have a groupping level which shows details (records) below:

I assume also that you have an unbound object (textbox) to calculate the
total deposits for each row then in the grou footer or group header add
another txt box with =sum([YourTextBox]).

In case you dont have a group then add the above in the report header or
footer.

Hope this helps,

GeorgeCY

Ο χÏήστης "Tina Marie" έγγÏαψε:
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???
 
T

Tina Marie

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
 
K

Ken Snell MVP

Can you post the SQL statement of the report's RecordSource query?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Tina Marie said:
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

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???
 
T

Tina Marie

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


Ken Snell MVP said:
Can you post the SQL statement of the report's RecordSource query?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Tina Marie said:
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???
 
K

Ken Snell MVP

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/




Tina Marie said:
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


Ken Snell MVP said:
Can you post the SQL statement of the report's RecordSource query?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Tina Marie said:
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???
 
T

Tina Marie

Hey Ken ... yes there is only 1 line (row) per course ... because even though
I do have a detail line (its property visible is NO) .. so its just a summary
report ... that's why I do a count([strstudentid]) ... but since the one row
per course needs to show:

Advance I ....... 3 students ............$2000
Advance II .......2 students .............$1500

LHIN total .........5 students .............$3500 - this is what I want ...
but the report

wants to give me 3*2000 + 2*1500 = 9000 ... no, that's not what I want??

Does this make sense?
--
Thanks!!

T. Marie


Ken Snell MVP said:
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/




Tina Marie said:
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


Ken Snell MVP said:
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???
 
T

Tina Marie

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


Ken Snell MVP said:
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/




Tina Marie said:
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


Ken Snell MVP said:
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???
 
K

Ken Snell MVP

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/


Tina Marie said:
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


Ken Snell MVP said:
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/




Tina Marie said:
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???
 
T

Tina Marie

Hey Ken ... I wish it was that easy ... that I know how to do ... the problem
is:

the total cost field, yes, is calculated on the report but its not in the
detail (they don't want to see the detail .. this is a summary report, I have
one but its hidden) ... its in the course footer ... but problem is that
because its based on an iif statement that has 2 ways of calculating the
total cost, its a 'new' field in the report .. so when you try to say 'sum'
the new field, you get a prompt box because it doesn't 'see' the new field
during execution ... and you can't just put the whole statement down into the
next level of footer and put a sum around the whole thing because of the iif
.... it doesn't apply to that footer? Any ideas now?? Hard one ... Tina
--
Thanks!!

T. Marie


Ken Snell MVP said:
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/


Tina Marie said:
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


Ken Snell MVP said:
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???
 
T

Thomas HUBER

what is course footer
Tina Marie said:
Hey Ken ... I wish it was that easy ... that I know how to do ... the
problem
is:

the total cost field, yes, is calculated on the report but its not in the
detail (they don't want to see the detail .. this is a summary report, I
have
one but its hidden) ... its in the course footer ... but problem is that
because its based on an iif statement that has 2 ways of calculating the
total cost, its a 'new' field in the report .. so when you try to say
'sum'
the new field, you get a prompt box because it doesn't 'see' the new field
during execution ... and you can't just put the whole statement down into
the
next level of footer and put a sum around the whole thing because of the
iif
... it doesn't apply to that footer? Any ideas now?? Hard one ... Tina
--
Thanks!!

T. Marie


Ken Snell MVP said:
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/


Tina Marie said:
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???
 
K

Ken Snell MVP

Put a hidden textbox in the footer of the group that pertains to the section
where the calculation is done, and set the ControlSource of that textbox to
the correct expression to give you the sum you want.

Then have the ControlSource of the textbox in the report footer be this:
=NameOfHiddenTextboxInGroupFooter

Does that give you what you seek?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




Tina Marie said:
Hey Ken ... I wish it was that easy ... that I know how to do ... the
problem
is:

the total cost field, yes, is calculated on the report but its not in the
detail (they don't want to see the detail .. this is a summary report, I
have
one but its hidden) ... its in the course footer ... but problem is that
because its based on an iif statement that has 2 ways of calculating the
total cost, its a 'new' field in the report .. so when you try to say
'sum'
the new field, you get a prompt box because it doesn't 'see' the new field
during execution ... and you can't just put the whole statement down into
the
next level of footer and put a sum around the whole thing because of the
iif
... it doesn't apply to that footer? Any ideas now?? Hard one ... Tina
--
Thanks!!

T. Marie


Ken Snell MVP said:
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/


Tina Marie said:
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???
 
T

Tina Marie

Hey Ken ... well what I ended up doing ... was putting the grouping query
into another query which allowed me to calc the total cost and then using
that nested query as the report's source ... it worked ... your idea may have
too ... but I don't want to touch it now that's its working ... thanks for
your help though Ken ... T.
--
Thanks!!

T. Marie


Ken Snell MVP said:
Put a hidden textbox in the footer of the group that pertains to the section
where the calculation is done, and set the ControlSource of that textbox to
the correct expression to give you the sum you want.

Then have the ControlSource of the textbox in the report footer be this:
=NameOfHiddenTextboxInGroupFooter

Does that give you what you seek?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




Tina Marie said:
Hey Ken ... I wish it was that easy ... that I know how to do ... the
problem
is:

the total cost field, yes, is calculated on the report but its not in the
detail (they don't want to see the detail .. this is a summary report, I
have
one but its hidden) ... its in the course footer ... but problem is that
because its based on an iif statement that has 2 ways of calculating the
total cost, its a 'new' field in the report .. so when you try to say
'sum'
the new field, you get a prompt box because it doesn't 'see' the new field
during execution ... and you can't just put the whole statement down into
the
next level of footer and put a sum around the whole thing because of the
iif
... it doesn't apply to that footer? Any ideas now?? Hard one ... Tina
--
Thanks!!

T. Marie


Ken Snell MVP said:
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???
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top