Sum different fields in a query

  • Thread starter Thread starter Marinda
  • Start date Start date
M

Marinda

Hi, iam trying to sum different fields in a row to give me the total of those
field in a new field. For example:

Field - Normal Dayshift - 12
Field - Normal Nightshift - 0
Field - Sunday Dayshift - 0
Field - Sunday Nightshift - 0
New Field - Total Hours - 12

If I use the function: Sum ( [Normal Dayshift] + [Normal Nightshift] +
[Sunday Dayshift] + [Sunday Nightshift] )
it gives me the error message: "You tried to execute a query that does not
include the specific expression 'Type' as part of the aggregate function.
I used to do this in a spreadsheet and my functions worked there, what am I
doing wrong in access? Please help if you can.
 
Marinda said:
Hi, iam trying to sum different fields in a row to give me the total of those
field in a new field. For example:

Field - Normal Dayshift - 12
Field - Normal Nightshift - 0
Field - Sunday Dayshift - 0
Field - Sunday Nightshift - 0
New Field - Total Hours - 12

If I use the function: Sum ( [Normal Dayshift] + [Normal Nightshift] +
[Sunday Dayshift] + [Sunday Nightshift] )
it gives me the error message: "You tried to execute a query that does not
include the specific expression 'Type' as part of the aggregate function.
I used to do this in a spreadsheet and my functions worked there, what am I
doing wrong in access?
Click to expand...


Maybe what you are doing wrong is thinking that a database
is just a giant spreadsheet. Databases are a whole
different kind of critter, so what you know about
spreadsheets no longer applies.

The Sum aggregate function in the SQL language adds up the
value in a field across every record in the query's dataset.
To add the value from several fields in each record, you
have to write it:

Total Hours: [Normal Dayshift] + [Normal Nightshift] +
[Sunday Dayshift] + [Sunday Nightshift]

If any of those fields might be Null, then use the Nz
function:

Total Hours: Nz([Normal Dayshift],0) + Nz([Normal
Nightshift],0) + Nz([Sunday Dayshift],0) + Nz([Sunday
Nightshift],0)
 
Total Hours: Nz([Dayshift], 0) + Nz([Normal Nightshift], 0) + Nz([Sunday
Dayshift], 0) + Nz([Sunday Nightshift], 0)

You can not add a Null to something as it will result in Null. The Nz
function change a Null to a zero.
 
Normally that indicates that a field named type is not included in the
Group by Clause of a totals query.

Can you post the SQL statement of your query? Hint: Menu: View: SQL

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Thanks Karl, it worked perfectly...you're great.
--
Thanks
Marinda


KARL DEWEY said:
Total Hours: Nz([Dayshift], 0) + Nz([Normal Nightshift], 0) + Nz([Sunday
Dayshift], 0) + Nz([Sunday Nightshift], 0)

You can not add a Null to something as it will result in Null. The Nz
function change a Null to a zero.
--
KARL DEWEY
Build a little - Test a little


Marinda said:
Hi, iam trying to sum different fields in a row to give me the total of those
field in a new field. For example:

Field - Normal Dayshift - 12
Field - Normal Nightshift - 0
Field - Sunday Dayshift - 0
Field - Sunday Nightshift - 0
New Field - Total Hours - 12

If I use the function: Sum ( [Normal Dayshift] + [Normal Nightshift] +
[Sunday Dayshift] + [Sunday Nightshift] )
it gives me the error message: "You tried to execute a query that does not
include the specific expression 'Type' as part of the aggregate function.
I used to do this in a spreadsheet and my functions worked there, what am I
doing wrong in access? Please help if you can.
Click to expand...
Click to expand...
 
Back
Top