Calculating values in reports.

[Cam]

Hello All,

I want to create a report from a query that calculate the
total from each categories, total of categories excluding
one category and grand total.
The fields are: ToolID, Date, Stages, Time, where ToolID
is the key field, there are four stages where we record
the time and Time is in hours.
In my current report, I have the followings:
Stage = ASSEMBLY
ToolID Date Time
0100 9/2/03 10
0101 9/3/03 08
0102 9/3/03 12
Total: 30

Stage = CALIBRATION
ToolID Date Time
0105 9/4/03 05
0104 9/6/03 13
Total: 18

Stage = PART DELAY
ToolID Date Time
0105 9/4/03 05
0104 9/8/03 04
Total: 09

Grand Total: 57 Hours

I also would like to calculate the Total hours for
ASSEMBLY and CALIBRATION only. How do I do this? Thanks

 

[Marshall Barton]

I want to create a report from a query that calculate the
total from each categories, total of categories excluding
one category and grand total.
The fields are: ToolID, Date, Stages, Time, where ToolID
is the key field, there are four stages where we record
the time and Time is in hours.
In my current report, I have the followings:
Stage = ASSEMBLY
ToolID Date Time
0100 9/2/03 10
0101 9/3/03 08
0102 9/3/03 12
Total: 30

Stage = CALIBRATION
ToolID Date Time
0105 9/4/03 05
0104 9/6/03 13
Total: 18

Stage = PART DELAY
ToolID Date Time
0105 9/4/03 05
0104 9/8/03 04
Total: 09

Grand Total: 57 Hours

I also would like to calculate the Total hours for
ASSEMBLY and CALIBRATION only. How do I do this? Thanks

All totals across multiple groups must be calculated in the
report footer.

The expression for a text box for the grand total of all
times would be: =Sum([timefield])

The total of all groups except Part Delay:
=(Sum(IIf([Stage] <> "Part Delay", [timefield], 0)

Note that Date and Time are Access function names. If you
are using those as field names in a table, both you and
Access will be confused by the ambiguous meaning of those
(and other) Access keywords.

 

[Cam]

Hello Marsh,

I put in your suggested expression.
=(Sum(IIf([Stage]<>"Parts Delay",[Time],0)))

But, when I executed, it gave me this error.
The expression is typed incorrectly, or it is too complex
to be evaluated. For example, a numeric expression may
contain too many complicated elements. Try simplifying the
expression by assigning parts of the expression to
variables. (Error 3071)

What can I do to fix this issue? Thanks again.

-----Original Message-----

I want to create a report from a query that calculate the
total from each categories, total of categories excluding
one category and grand total.
The fields are: ToolID, Date, Stages, Time, where ToolID
is the key field, there are four stages where we record
the time and Time is in hours.
In my current report, I have the followings:
Stage = ASSEMBLY
ToolID Date Time
0100 9/2/03 10
0101 9/3/03 08
0102 9/3/03 12
Total: 30

Stage = CALIBRATION
ToolID Date Time
0105 9/4/03 05
0104 9/6/03 13
Total: 18

Stage = PART DELAY
ToolID Date Time
0105 9/4/03 05
0104 9/8/03 04
Total: 09

Grand Total: 57 Hours

I also would like to calculate the Total hours for
ASSEMBLY and CALIBRATION only. How do I do this? Thanks

All totals across multiple groups must be calculated in the
report footer.

The expression for a text box for the grand total of all
times would be: =Sum([timefield])

The total of all groups except Part Delay:
=(Sum(IIf([Stage] <> "Part Delay", [timefield], 0)

Note that Date and Time are Access function names. If you
are using those as field names in a table, both you and
Access will be confused by the ambiguous meaning of those
(and other) Access keywords.

 

[Marshall Barton]

I put in your suggested expression.
=(Sum(IIf([Stage]<>"Parts Delay",[Time],0)))

But, when I executed, it gave me this error.
The expression is typed incorrectly, or it is too complex
to be evaluated. For example, a numeric expression may
contain too many complicated elements. Try simplifying the
expression by assigning parts of the expression to
variables. (Error 3071)

What can I do to fix this issue? Thanks again.

I don't know, the only way I can get that error from that
kind of expression (using A97) is if Stage is not a valid
name of a field in the report's record source table/query
(but that should also cause a prompt for a value). Double
check the field names and make sure you do not have any
controls (text boxes) with those names.

The only other thing that strikes me as odd is your use of a
field named TIME, which is an Access keyword.
--
Marsh
MVP [MS Access]

-----Original Message-----

I want to create a report from a query that calculate the
total from each categories, total of categories excluding
one category and grand total.
The fields are: ToolID, Date, Stages, Time, where ToolID
is the key field, there are four stages where we record
the time and Time is in hours.
In my current report, I have the followings:
Stage = ASSEMBLY
ToolID Date Time
0100 9/2/03 10
0101 9/3/03 08
0102 9/3/03 12
Total: 30

Stage = CALIBRATION
ToolID Date Time
0105 9/4/03 05
0104 9/6/03 13
Total: 18

Stage = PART DELAY
ToolID Date Time
0105 9/4/03 05
0104 9/8/03 04
Total: 09

Grand Total: 57 Hours

I also would like to calculate the Total hours for
ASSEMBLY and CALIBRATION only. How do I do this? Thanks

All totals across multiple groups must be calculated in the
report footer.

The expression for a text box for the grand total of all
times would be: =Sum([timefield])

The total of all groups except Part Delay:
=(Sum(IIf([Stage] <> "Part Delay", [timefield], 0)

Note that Date and Time are Access function names. If you
are using those as field names in a table, both you and
Access will be confused by the ambiguous meaning of those
(and other) Access keywords.