Summation in a report

[abouddan]

Hi all

I am using Access2000 and I am creating a report based on a query.

Query1=" select Id,name,Amount from Eployees"

This query returns many records, so in the report this result must be shown as if continuous. I did that by inserting a report header and footer. But the preblem is that I want the the summation of a particular field 'Amount'. So what I did is creating a text box in the report footer and in it's Data/Row Source I wrote " =Sum([text1])" and it went ok.

The problem rose when the field (text1) is not bounded directly to the query, when in it's row source is writen some code.like:' =iif(amount<1000,name,"") '.
The results in text1 are just what I need but In that text box in the report footer nothing happens.
What am I missing?

[MMcCarthy]

Hi all

I am using Access2000 and I am creating a report based on a query.

Query1=" select Id,name,Amount from Eployees"

This query returns many records, so in the report this result must be shown as if continuous. I did that by inserting a report header and footer. But the preblem is that I want the the summation of a particular field 'Amount'. So what I did is creating a text box in the report footer and in it's Data/Row Source I wrote " =Sum([text1])" and it went ok.

The problem rose when the field (text1) is not bounded directly to the query, when in it's row source is writen some code.like:' =iif(amount<1000,name,"") '.
The results in text1 are just what I need but In that text box in the report footer nothing happens.
What am I missing?

You can't sum a calcuated field by textbox name. Try something like this ...

=Sum(IIf(amount<1000,amount,0))

[abouddan]

You can't sum a calcuated field by textbox name. Try something like this ...

=Sum(IIf(amount<1000,amount,0))

Maybe I didn't axplain well. In the detail section I have a text 'Text1' that calculates a value. It's row source looks like: =IIf(amount<1000,amount,0)
The 'amount' is a field from the query.
Outside of the Details section, In the report footer I have an other TextBox 'Text2' having in it's row source: = Sum(Text1)

The summation is not working when in Text1 the data is calculated otherwise when Text1 is directly bounded to the query and having 'amount' as row source everything goes right.

[MMcCarthy]

Maybe I didn't axplain well. In the detail section I have a text 'Text1' that calculates a value. It's row source looks like: =IIf(amount<1000,amount,0)
The 'amount' is a field from the query.
Outside of the Details section, In the report footer I have an other TextBox 'Text2' having in it's row source: = Sum(Text1)

The summation is not working when in Text1 the data is calculated otherwise when Text1 is directly bounded to the query and having 'amount' as row source everything goes right.

Did you try putting what I gave you in Text2?

[abouddan]

Did you try putting what I gave you in Text2?

Thanks Mary for the support

I tried what you said and it worked very well. But I want to ask: how did that happened? If you can explain. Knowing that Text2 is running once so I expect to calculate the last Record only.

[MMcCarthy]

Thanks Mary for the support

I tried what you said and it worked very well. But I want to ask: how did that happened? If you can explain. Knowing that Text2 is running once so I expect to calculate the last Record only.

Don't understand what you mean by the last sentence.

The reason it works is that ...

You can't sum() on Text1 because it is a calculated field and you can't aggregate (sum, count, etc.) on calculated fields. However, you can sum() on the calculation itself.

[abouddan]

Don't understand what you mean by the last sentence.

The reason it works is that ...

You can't sum() on Text1 because it is a calculated field and you can't aggregate (sum, count, etc.) on calculated fields. However, you can sum() on the calculation itself.

Thanks for the explanation
What I ment is that Text2 is filled once at the openning of the report, true?
so how can he caculate the result of a query that returns many records?

[MMcCarthy]

Thanks for the explanation
What I ment is that Text2 is filled once at the openning of the report, true?
so how can he caculate the result of a query that returns many records?

Text2 is in the report footer so is calculated after the fill of all details