Access

[Guest]

I have created a query to produce a report. I am trying to calculate a total
for 'cost-errors and number-errors' which are fields in the table. in one of
the cells I have the following code. When I execute the all I get is the last
entry for 'cost-errors'. Will someone help me understand what I am doing
wrong? If it helps I included the SQL Select statement.

TotalCost: CCur(+[esthist].[cost-errors])

SELECT DISTINCTROW esthist.[bid-number], esthist.[bid-suffix],
esthist.[job-name], esthist.tier, esthist.[est-hours], esthist.[act-hours],
esthist.[number-errors], esthist.[cost-errors], esthist.[discount-price],
esthist.[discount-date], esthist.[date-est], esthist.[date-check],
esthist.estimator, CCur(+[esthist].[cost-errors]) AS TotalCost
FROM esthist
WHERE (((esthist.estimator)=[Forms]![ReportSelect]![estselector]))
ORDER BY esthist.estimator;

 

[james_keegan via AccessMonster.com]

It sounds like you want a total, and are just getting the value in the last
record.

Look in the help under 'running total'. You will need a 'footer' in your
report, and you will need a hidden field in the detail section to calculate
the sum of [cost-errors] across all records selected by the query.

good luck!

jk

I have created a query to produce a report. I am trying to calculate a total
for 'cost-errors and number-errors' which are fields in the table. in one of
the cells I have the following code. When I execute the all I get is the last
entry for 'cost-errors'. Will someone help me understand what I am doing
wrong? If it helps I included the SQL Select statement.

TotalCost: CCur(+[esthist].[cost-errors])

SELECT DISTINCTROW esthist.[bid-number], esthist.[bid-suffix],
esthist.[job-name], esthist.tier, esthist.[est-hours], esthist.[act-hours],
esthist.[number-errors], esthist.[cost-errors], esthist.[discount-price],
esthist.[discount-date], esthist.[date-est], esthist.[date-check],
esthist.estimator, CCur(+[esthist].[cost-errors]) AS TotalCost
FROM esthist
WHERE (((esthist.estimator)=[Forms]![ReportSelect]![estselector]))
ORDER BY esthist.estimator;

 

[Guest]

Hi James, thanks for your response. I have not been able to make your
suggestion work. I coded it and it did not work. Is there a way to make what
I have done work. I got the example from the NorthWind demo. It does almost
what I need to do, please help if you can.
--
Norm Bohana

It sounds like you want a total, and are just getting the value in the last
record.

Look in the help under 'running total'. You will need a 'footer' in your
report, and you will need a hidden field in the detail section to calculate
the sum of [cost-errors] across all records selected by the query.

good luck!

jk

I have created a query to produce a report. I am trying to calculate a total
for 'cost-errors and number-errors' which are fields in the table. in one of
the cells I have the following code. When I execute the all I get is the last
entry for 'cost-errors'. Will someone help me understand what I am doing
wrong? If it helps I included the SQL Select statement.

TotalCost: CCur(+[esthist].[cost-errors])

SELECT DISTINCTROW esthist.[bid-number], esthist.[bid-suffix],
esthist.[job-name], esthist.tier, esthist.[est-hours], esthist.[act-hours],
esthist.[number-errors], esthist.[cost-errors], esthist.[discount-price],
esthist.[discount-date], esthist.[date-est], esthist.[date-check],
esthist.estimator, CCur(+[esthist].[cost-errors]) AS TotalCost
FROM esthist
WHERE (((esthist.estimator)=[Forms]![ReportSelect]![estselector]))
ORDER BY esthist.estimator;

 

[james_keegan via AccessMonster.com]

I think I need to back up and make sure I understand what you really need
here.

Do you want this calculated total errors across estimators to merely print
out on the report?

If you just need it for the report, you don't need it in the Select statement.
Just select the rows you need, and let the report calculate the error totals
on the fly.

I had the magicword wrong - which is what I get from pulling it out of my
head instead of looking it up!

There is a property of text boxes in reports called 'RunningSum'. This
property can be set to 'no', or 'over all' or 'over group'.

Here's what you need to do.

In the detail section of the report, where you list each estimate's bid
number and job number and errors and such, and add ANOTHER text-control
that's bound to [cost-errors], only set the 'running sum' property to 'over
group', and set it's 'visible' property to 'false' (unless you want to SEE
the running totals!). Name this control something like
'txtCostErrorRunningSum'.

Make sure you've set the report to group on [estimator], turn the group
footer ON, and put a text control in the group footer of the report (create
one if you have to) and set it's control source to "=[txtCostErrorRunningSum]
"

And there's your total for each estimator!

HTH!

jk

 

[Guest]

Thanks for your help, I solved the problem what happened was I put the
expression afte the page footer, and it should been after the Report Footer.
The problem is fixed again Thank you.