How to filter a calculated field in a peport?

[kai]

Hi,
I use Access 2003. I created a report which lists customer oreders and
calculates total for each customer in the footer. I would like to filter the
result by customer oredr total, for i.e. Between $1000 and $2000 (enter the
range on the form whichs calls the report), but "OrderTotal" is a calculated
field, I cannot include it in my query. Is it possible to filter a
calculated field on the report?

Thanks a lot

Kai

 

[Marshall Barton]

I use Access 2003. I created a report which lists customer oreders and
calculates total for each customer in the footer. I would like to filter the
result by customer oredr total, for i.e. Between $1000 and $2000 (enter the
range on the form whichs calls the report), but "OrderTotal" is a calculated
field, I cannot include it in my query. Is it possible to filter a
calculated field on the report?

To filter or sort records you really need to do the
calculation in the query. Depending on how you calculate
the total. this may be easier than you think.

Maybe if you explained how the calculation is done and post
a copy of your existing query, someone will be able to
suggest a way to get the job done.

 

[kai]

Marsh,
Thank your for your help.
I group by CustomerID, and in grope footer, I add a texbox and set the
datasource =Sum([Cost]). The calculation was done on the report.

Thanks

Kai

 

[Marshall Barton]

I still need to see the report's record source query before
I can be specific.

In general, create a new query named CustomerCosts based on
the existing query:

SELECT CustomerID, Sum(Cost) As SumOfCost
FROM yourquery

Then create another query to use as the report's record
source:

SELECT yourquery.*, CustomerCosts.SumOfCost
FROM yourquery INNER JOIN CustomerCosts
ON yourquery.CustomerID = CustomerCosts.CustomerID
--
Marsh
MVP [MS Access]

I group by CustomerID, and in grope footer, I add a texbox and set the
datasource =Sum([Cost]). The calculation was done on the report.

To filter or sort records you really need to do the
calculation in the query. Depending on how you calculate
the total. this may be easier than you think.

Maybe if you explained how the calculation is done and post
a copy of your existing query, someone will be able to
suggest a way to get the job done.

 

[kai]

Marsh,
Thanks. I will try your idea to use two queries.

Kai

I still need to see the report's record source query before
I can be specific.

In general, create a new query named CustomerCosts based on
the existing query:

SELECT CustomerID, Sum(Cost) As SumOfCost
FROM yourquery

Then create another query to use as the report's record
source:

SELECT yourquery.*, CustomerCosts.SumOfCost
FROM yourquery INNER JOIN CustomerCosts
ON yourquery.CustomerID = CustomerCosts.CustomerID
--
Marsh
MVP [MS Access]

I group by CustomerID, and in grope footer, I add a texbox and set the
datasource =Sum([Cost]). The calculation was done on the report.

kai wrote:
I use Access 2003. I created a report which lists customer oreders and
calculates total for each customer in the footer. I would like to filter
the
result by customer oredr total, for i.e. Between $1000 and $2000 (enter
the
range on the form whichs calls the report), but "OrderTotal" is a
calculated
field, I cannot include it in my query. Is it possible to filter a
calculated field on the report?

"Marshall Barton" wrote
To filter or sort records you really need to do the
calculation in the query. Depending on how you calculate
the total. this may be easier than you think.

Maybe if you explained how the calculation is done and post
a copy of your existing query, someone will be able to
suggest a way to get the job done.