aggregate functions

G

gmead7

I had a report in Access .mdb that provides summary data through an
expression as per below:

=Sum(IIf([Section_List]="196",[Related_Costs],0))

We have pushed the tables out to SQL Server and converted to Access Project
..adp
Now this expression won't work and yields this error when you try to run the
report:

"The Expression _____ is Invalid. Aggregate functions are only allowed in
ouput fields of the Record Source."

I'm seeking any help to fix this in adp / Access Project. thanks, GM
 
O

Ofer

Try this
Move the field to the detailed section with
=IIf([Section_List]="196",[Related_Costs],0)
Select running sum to true and visible = false for that field

At the footer, or where you want to display the summary write
=[FieldName] ' The one created at top
 
G

gmead7

Thanks - it was worth a try, but it returned the same error message.

Ofer said:
Try this
Move the field to the detailed section with
=IIf([Section_List]="196",[Related_Costs],0)
Select running sum to true and visible = false for that field

At the footer, or where you want to display the summary write
=[FieldName] ' The one created at top


gmead7 said:
I had a report in Access .mdb that provides summary data through an
expression as per below:

=Sum(IIf([Section_List]="196",[Related_Costs],0))

We have pushed the tables out to SQL Server and converted to Access Project
.adp
Now this expression won't work and yields this error when you try to run the
report:

"The Expression _____ is Invalid. Aggregate functions are only allowed in
ouput fields of the Record Source."

I'm seeking any help to fix this in adp / Access Project. thanks, GM
 
D

Duane Hookom

Try:
=Sum(Abs([Section_List]="196")*[Related_Costs])
This assumes Section_List is text.

--
Duane Hookom
MS Access MVP
--

gmead7 said:
Thanks - it was worth a try, but it returned the same error message.

Ofer said:
Try this
Move the field to the detailed section with
=IIf([Section_List]="196",[Related_Costs],0)
Select running sum to true and visible = false for that field

At the footer, or where you want to display the summary write
=[FieldName] ' The one created at top


gmead7 said:
I had a report in Access .mdb that provides summary data through an
expression as per below:

=Sum(IIf([Section_List]="196",[Related_Costs],0))

We have pushed the tables out to SQL Server and converted to Access
Project
.adp
Now this expression won't work and yields this error when you try to
run the
report:

"The Expression _____ is Invalid. Aggregate functions are only allowed
in
ouput fields of the Record Source."

I'm seeking any help to fix this in adp / Access Project. thanks, GM
 
G

gmead7

Duane, Can you help me understand what this argument does? It looks like it
takes the absolute value of a text field and multiplies it by 'Related Costs'
but I'm sure I'm not understanding this correctly. I want to sum up all the
Related costs where the Section list (code) is Section 196. I want to do
this in the Report Footer (I can do it in the groupings using a Sum statement)

thanks, George

Duane Hookom said:
Try:
=Sum(Abs([Section_List]="196")*[Related_Costs])
This assumes Section_List is text.

--
Duane Hookom
MS Access MVP
--

gmead7 said:
Thanks - it was worth a try, but it returned the same error message.

Ofer said:
Try this
Move the field to the detailed section with
=IIf([Section_List]="196",[Related_Costs],0)
Select running sum to true and visible = false for that field

At the footer, or where you want to display the summary write
=[FieldName] ' The one created at top


:

I had a report in Access .mdb that provides summary data through an
expression as per below:

=Sum(IIf([Section_List]="196",[Related_Costs],0))

We have pushed the tables out to SQL Server and converted to Access
Project
.adp
Now this expression won't work and yields this error when you try to
run the
report:

"The Expression _____ is Invalid. Aggregate functions are only allowed
in
ouput fields of the Record Source."

I'm seeking any help to fix this in adp / Access Project. thanks, GM
 
D

Duane Hookom

Whatever works in a group footer should work in a report footer. Most likely
the expression won't work in a Page Footer.

Abs(YourExpression) will evaluate to either True/-1 or False/0. Multiply the
Absolute value of the expression times the field that you would like to
sum/total and you get a sum of the records where the expression is true.

--
Duane Hookom
MS Access MVP


gmead7 said:
Duane, Can you help me understand what this argument does? It looks like
it
takes the absolute value of a text field and multiplies it by 'Related
Costs'
but I'm sure I'm not understanding this correctly. I want to sum up all
the
Related costs where the Section list (code) is Section 196. I want to do
this in the Report Footer (I can do it in the groupings using a Sum
statement)

thanks, George

Duane Hookom said:
Try:
=Sum(Abs([Section_List]="196")*[Related_Costs])
This assumes Section_List is text.

--
Duane Hookom
MS Access MVP
--

gmead7 said:
Thanks - it was worth a try, but it returned the same error message.

:

Try this
Move the field to the detailed section with
=IIf([Section_List]="196",[Related_Costs],0)
Select running sum to true and visible = false for that field

At the footer, or where you want to display the summary write
=[FieldName] ' The one created at top


:

I had a report in Access .mdb that provides summary data through an
expression as per below:

=Sum(IIf([Section_List]="196",[Related_Costs],0))

We have pushed the tables out to SQL Server and converted to Access
Project
.adp
Now this expression won't work and yields this error when you try to
run the
report:

"The Expression _____ is Invalid. Aggregate functions are only
allowed
in
ouput fields of the Record Source."

I'm seeking any help to fix this in adp / Access Project. thanks,
GM
 
G

gmead7

Thanks, I tried it and received the same message. I guess this is one of
those situations where "should work" and "will work" . For whatever reason
the .adp Project file does not like the nested expressions. It does the Sum
in the group by section because it's not nested; simply SUM[Related_costs].

I did find a work around by creating a function and then adding a subreport
to the report... it works, but I doubt if it's the best way to do it...

Duane Hookom said:
Whatever works in a group footer should work in a report footer. Most likely
the expression won't work in a Page Footer.

Abs(YourExpression) will evaluate to either True/-1 or False/0. Multiply the
Absolute value of the expression times the field that you would like to
sum/total and you get a sum of the records where the expression is true.

--
Duane Hookom
MS Access MVP


gmead7 said:
Duane, Can you help me understand what this argument does? It looks like
it
takes the absolute value of a text field and multiplies it by 'Related
Costs'
but I'm sure I'm not understanding this correctly. I want to sum up all
the
Related costs where the Section list (code) is Section 196. I want to do
this in the Report Footer (I can do it in the groupings using a Sum
statement)

thanks, George

Duane Hookom said:
Try:
=Sum(Abs([Section_List]="196")*[Related_Costs])
This assumes Section_List is text.

--
Duane Hookom
MS Access MVP
--

Thanks - it was worth a try, but it returned the same error message.

:

Try this
Move the field to the detailed section with
=IIf([Section_List]="196",[Related_Costs],0)
Select running sum to true and visible = false for that field

At the footer, or where you want to display the summary write
=[FieldName] ' The one created at top


:

I had a report in Access .mdb that provides summary data through an
expression as per below:

=Sum(IIf([Section_List]="196",[Related_Costs],0))

We have pushed the tables out to SQL Server and converted to Access
Project
.adp
Now this expression won't work and yields this error when you try to
run the
report:

"The Expression _____ is Invalid. Aggregate functions are only
allowed
in
ouput fields of the Record Source."

I'm seeking any help to fix this in adp / Access Project. thanks,
GM
 
D

Duane Hookom

What do you get if you try add this to your report's record source view:

Case When [Section_List]='196' Then [Related_Costs] Else 0 End As
Only196Costs

You should be able to sum the column Only196Costs in your report.

--
Duane Hookom
MS Access MVP
--

gmead7 said:
Thanks, I tried it and received the same message. I guess this is one of
those situations where "should work" and "will work" . For whatever
reason
the .adp Project file does not like the nested expressions. It does the
Sum
in the group by section because it's not nested; simply
SUM[Related_costs].

I did find a work around by creating a function and then adding a
subreport
to the report... it works, but I doubt if it's the best way to do it...

Duane Hookom said:
Whatever works in a group footer should work in a report footer. Most
likely
the expression won't work in a Page Footer.

Abs(YourExpression) will evaluate to either True/-1 or False/0. Multiply
the
Absolute value of the expression times the field that you would like to
sum/total and you get a sum of the records where the expression is true.

--
Duane Hookom
MS Access MVP


gmead7 said:
Duane, Can you help me understand what this argument does? It looks
like
it
takes the absolute value of a text field and multiplies it by 'Related
Costs'
but I'm sure I'm not understanding this correctly. I want to sum up
all
the
Related costs where the Section list (code) is Section 196. I want to
do
this in the Report Footer (I can do it in the groupings using a Sum
statement)

thanks, George

:

Try:
=Sum(Abs([Section_List]="196")*[Related_Costs])
This assumes Section_List is text.

--
Duane Hookom
MS Access MVP
--

Thanks - it was worth a try, but it returned the same error message.

:

Try this
Move the field to the detailed section with
=IIf([Section_List]="196",[Related_Costs],0)
Select running sum to true and visible = false for that field

At the footer, or where you want to display the summary write
=[FieldName] ' The one created at top


:

I had a report in Access .mdb that provides summary data through
an
expression as per below:

=Sum(IIf([Section_List]="196",[Related_Costs],0))

We have pushed the tables out to SQL Server and converted to
Access
Project
.adp
Now this expression won't work and yields this error when you try
to
run the
report:

"The Expression _____ is Invalid. Aggregate functions are only
allowed
in
ouput fields of the Record Source."

I'm seeking any help to fix this in adp / Access Project.
thanks,
GM
 
G

gmead7

Duane,

I apologize for not getting back sooner. I appreciate your suggestions
although I don't understand this most recent one. Where is the record source
view? I'm looking at properties and see the record source property. I'm
reluctant to do this as it's not just 196 that I'm trying to sum in my report
footer. I'm also trying to sum 196A, 196B, etc up to 196J so I suspect this
might not be a good option for me. For now I'm sticking with the Access .mdb
format which does seem to work fine with the IIF statement I orginally pasted
here.

thanks again,

George

Duane Hookom said:
What do you get if you try add this to your report's record source view:

Case When [Section_List]='196' Then [Related_Costs] Else 0 End As
Only196Costs

You should be able to sum the column Only196Costs in your report.

--
Duane Hookom
MS Access MVP
--

gmead7 said:
Thanks, I tried it and received the same message. I guess this is one of
those situations where "should work" and "will work" . For whatever
reason
the .adp Project file does not like the nested expressions. It does the
Sum
in the group by section because it's not nested; simply
SUM[Related_costs].

I did find a work around by creating a function and then adding a
subreport
to the report... it works, but I doubt if it's the best way to do it...

Duane Hookom said:
Whatever works in a group footer should work in a report footer. Most
likely
the expression won't work in a Page Footer.

Abs(YourExpression) will evaluate to either True/-1 or False/0. Multiply
the
Absolute value of the expression times the field that you would like to
sum/total and you get a sum of the records where the expression is true.

--
Duane Hookom
MS Access MVP


Duane, Can you help me understand what this argument does? It looks
like
it
takes the absolute value of a text field and multiplies it by 'Related
Costs'
but I'm sure I'm not understanding this correctly. I want to sum up
all
the
Related costs where the Section list (code) is Section 196. I want to
do
this in the Report Footer (I can do it in the groupings using a Sum
statement)

thanks, George

:

Try:
=Sum(Abs([Section_List]="196")*[Related_Costs])
This assumes Section_List is text.

--
Duane Hookom
MS Access MVP
--

Thanks - it was worth a try, but it returned the same error message.

:

Try this
Move the field to the detailed section with
=IIf([Section_List]="196",[Related_Costs],0)
Select running sum to true and visible = false for that field

At the footer, or where you want to display the summary write
=[FieldName] ' The one created at top


:

I had a report in Access .mdb that provides summary data through
an
expression as per below:

=Sum(IIf([Section_List]="196",[Related_Costs],0))

We have pushed the tables out to SQL Server and converted to
Access
Project
.adp
Now this expression won't work and yields this error when you try
to
run the
report:

"The Expression _____ is Invalid. Aggregate functions are only
allowed
in
ouput fields of the Record Source."

I'm seeking any help to fix this in adp / Access Project.
thanks,
GM
 
D

Duane Hookom

I seemed to have missed any mention of duplicated sums. I think if you need
this for several section_list values, consider using a totals query as the
record source of a subreport.

--
Duane Hookom
MS Access MVP


gmead7 said:
Duane,

I apologize for not getting back sooner. I appreciate your suggestions
although I don't understand this most recent one. Where is the record
source
view? I'm looking at properties and see the record source property. I'm
reluctant to do this as it's not just 196 that I'm trying to sum in my
report
footer. I'm also trying to sum 196A, 196B, etc up to 196J so I suspect
this
might not be a good option for me. For now I'm sticking with the Access
.mdb
format which does seem to work fine with the IIF statement I orginally
pasted
here.

thanks again,

George

Duane Hookom said:
What do you get if you try add this to your report's record source view:

Case When [Section_List]='196' Then [Related_Costs] Else 0 End As
Only196Costs

You should be able to sum the column Only196Costs in your report.

--
Duane Hookom
MS Access MVP
--

gmead7 said:
Thanks, I tried it and received the same message. I guess this is one
of
those situations where "should work" and "will work" . For whatever
reason
the .adp Project file does not like the nested expressions. It does
the
Sum
in the group by section because it's not nested; simply
SUM[Related_costs].

I did find a work around by creating a function and then adding a
subreport
to the report... it works, but I doubt if it's the best way to do it...

:

Whatever works in a group footer should work in a report footer. Most
likely
the expression won't work in a Page Footer.

Abs(YourExpression) will evaluate to either True/-1 or False/0.
Multiply
the
Absolute value of the expression times the field that you would like
to
sum/total and you get a sum of the records where the expression is
true.

--
Duane Hookom
MS Access MVP


Duane, Can you help me understand what this argument does? It
looks
like
it
takes the absolute value of a text field and multiplies it by
'Related
Costs'
but I'm sure I'm not understanding this correctly. I want to sum up
all
the
Related costs where the Section list (code) is Section 196. I want
to
do
this in the Report Footer (I can do it in the groupings using a Sum
statement)

thanks, George

:

Try:
=Sum(Abs([Section_List]="196")*[Related_Costs])
This assumes Section_List is text.

--
Duane Hookom
MS Access MVP
--

Thanks - it was worth a try, but it returned the same error
message.

:

Try this
Move the field to the detailed section with
=IIf([Section_List]="196",[Related_Costs],0)
Select running sum to true and visible = false for that field

At the footer, or where you want to display the summary write
=[FieldName] ' The one created at top


:

I had a report in Access .mdb that provides summary data
through
an
expression as per below:

=Sum(IIf([Section_List]="196",[Related_Costs],0))

We have pushed the tables out to SQL Server and converted to
Access
Project
.adp
Now this expression won't work and yields this error when you
try
to
run the
report:

"The Expression _____ is Invalid. Aggregate functions are
only
allowed
in
ouput fields of the Record Source."

I'm seeking any help to fix this in adp / Access Project.
thanks,
GM
 

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

Top