Total Expression Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

As part of my database, I have a table called ASSESS where employee's
assessment scores are recorded, each field is a different assessment area.

I have created a Query to total these assessments per Employee. The query
looks like this:
Col1 Col2
Field EmployID Total:
Sum([Assess]![Lead]+[Assess]![Voice]+[Assess]![Talk])
Table Assess
Total Group By Expression

When I run the Query, no totals area appearing.

What must I add to Col2 to make it work?

Thanks
 
Field: Total: Nz(Assess.Lead,0) + Nz(Assess.Voice,0), + Nz(Assess.Talk,0)

First, the SUM function works on Columns (vertically) not Rows
(horizontally)

Second, if any of the values in the columns are null (blank) then the
addition of the amounts will be blank. Using the NZ function forces a zero
when the field value is null and thereby avoids the problem.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Hi John

Thanks for setting me straight.

I entered your formula in the field block and receive this error:

"You tried to execute a query that does not include the specified expression
'Nz([Assess].[Lead],0)+Nz([Assess].[Voice],0)+Nz([Assess].[Talk],0)' as part
of an aggregate function"

Can you help on teh meaning of this and, more importantly how do I correct it?

Many thanks for your help so far.

Charles

John Spencer said:
Field: Total: Nz(Assess.Lead,0) + Nz(Assess.Voice,0), + Nz(Assess.Talk,0)

First, the SUM function works on Columns (vertically) not Rows
(horizontally)

Second, if any of the values in the columns are null (blank) then the
addition of the amounts will be blank. Using the NZ function forces a zero
when the field value is null and thereby avoids the problem.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Charles said:
As part of my database, I have a table called ASSESS where employee's
assessment scores are recorded, each field is a different assessment area.

I have created a Query to total these assessments per Employee. The query
looks like this:
Col1 Col2
Field EmployID Total:Sum([Assess]![Lead]+[Assess]![Voice]+[Assess]![Talk])
Table Assess
Total Group By Expression

When I run the Query, no totals area appearing.

What must I add to Col2 to make it work?

Thanks
Click to expand...
Click to expand...
 
Ok, it sounds as if you have an aggregate query going on. So you may need
to use something along the lines of the following.

Nz(Sum(Assess.Lead),0) + Nz(Sum(Assess.Voice),0) + ...

or

Sum(Nz([Assess].[Lead],0)+Nz([Assess].[Voice],0)+Nz([Assess].[Talk],0))


I suggest you post the entire SQL statement (Menu - View: SQL) and a
description of what you are trying to accomplish. It may be that you don't
want an aggregate (group by) query at all - if you are trying to sum a row -
or that you do need the aggregate query and are trying to add the sums
together for multiple rows.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Charles said:
Hi John

Thanks for setting me straight.

I entered your formula in the field block and receive this error:

"You tried to execute a query that does not include the specified
expression
'Nz([Assess].[Lead],0)+Nz([Assess].[Voice],0)+Nz([Assess].[Talk],0)' as
part
of an aggregate function"

Can you help on teh meaning of this and, more importantly how do I correct
it?

Many thanks for your help so far.

Charles

John Spencer said:
Field: Total: Nz(Assess.Lead,0) + Nz(Assess.Voice,0), + Nz(Assess.Talk,0)

First, the SUM function works on Columns (vertically) not Rows
(horizontally)

Second, if any of the values in the columns are null (blank) then the
addition of the amounts will be blank. Using the NZ function forces a
zero
when the field value is null and thereby avoids the problem.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Charles said:
As part of my database, I have a table called ASSESS where employee's
assessment scores are recorded, each field is a different assessment
area.

I have created a Query to total these assessments per Employee. The
query
looks like this:
Col1 Col2
Field EmployID
Total:Sum([Assess]![Lead]+[Assess]![Voice]+[Assess]![Talk])
Table Assess
Total Group By Expression

When I run the Query, no totals area appearing.

What must I add to Col2 to make it work?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Hi John

Thanks very much for all the help; fiddling around using your suggestions I
have got the correct result - I changed Expression to Group By.

I really appreciate the time everyone puts into answering my often
straightforward questions.

Regards

Charles

John Spencer said:
Ok, it sounds as if you have an aggregate query going on. So you may need
to use something along the lines of the following.

Nz(Sum(Assess.Lead),0) + Nz(Sum(Assess.Voice),0) + ...

or

Sum(Nz([Assess].[Lead],0)+Nz([Assess].[Voice],0)+Nz([Assess].[Talk],0))


I suggest you post the entire SQL statement (Menu - View: SQL) and a
description of what you are trying to accomplish. It may be that you don't
want an aggregate (group by) query at all - if you are trying to sum a row -
or that you do need the aggregate query and are trying to add the sums
together for multiple rows.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Charles said:
Hi John

Thanks for setting me straight.

I entered your formula in the field block and receive this error:

"You tried to execute a query that does not include the specified
expression
'Nz([Assess].[Lead],0)+Nz([Assess].[Voice],0)+Nz([Assess].[Talk],0)' as
part
of an aggregate function"

Can you help on teh meaning of this and, more importantly how do I correct
it?

Many thanks for your help so far.

Charles

John Spencer said:
Field: Total: Nz(Assess.Lead,0) + Nz(Assess.Voice,0), + Nz(Assess.Talk,0)

First, the SUM function works on Columns (vertically) not Rows
(horizontally)

Second, if any of the values in the columns are null (blank) then the
addition of the amounts will be blank. Using the NZ function forces a
zero
when the field value is null and thereby avoids the problem.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

As part of my database, I have a table called ASSESS where employee's
assessment scores are recorded, each field is a different assessment
area.

I have created a Query to total these assessments per Employee. The
query
looks like this:
Col1 Col2
Field EmployID
Total:Sum([Assess]![Lead]+[Assess]![Voice]+[Assess]![Talk])
Table Assess
Total Group By Expression

When I run the Query, no totals area appearing.

What must I add to Col2 to make it work?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top