Sum command not working

  • Thread starter Thread starter HeartSA
  • Start date Start date
H

HeartSA

I have a query that counts occurrences, I created a report based on that
query. When I try to create a totals in the footer section, I get an #Error
message.

Here is the query in SQL form

SELECT StaffCount.Location, StaffCount.[Staff Count], Submitted.Submitted,
Resolved.Resolved, Active.Active
FROM Active RIGHT JOIN (Resolved INNER JOIN (Submitted INNER JOIN StaffCount
ON Submitted.Location = StaffCount.Location) ON Resolved.Location =
StaffCount.Location) ON Active.Location = StaffCount.Location
GROUP BY StaffCount.Location, StaffCount.[Staff Count], Submitted.Submitted,
Resolved.Resolved, Active.Active;

I use all the fields in the select statement above on the report. The query
is called "Joined Query".
I am attempting to total Submitted, Resolved and Active.

Syntax I tried in the text box

=Sum([Submitted].[Submitted])
=Sum([Submitted])
=Sum([Joined Query].[Submitted])

I get the #Error on all of these. Any help is much appreciated.
 
Why are you using an aggregate (totals) query (you aren't doing any summing,
counting, etc in the query)? Why not use DISTINCT as in the following as
the source for your report?

SELECT DISTINCT StaffCount.Location, StaffCount.[Staff Count],
Submitted.Submitted,
Resolved.Resolved, Active.Active
FROM Active RIGHT JOIN (Resolved INNER JOIN (Submitted INNER JOIN StaffCount
ON Submitted.Location = StaffCount.Location) ON Resolved.Location =
StaffCount.Location) ON Active.Location = StaffCount.Location

Also, which footer are you using? If you are trying to do this in the page
footer, that is probably why you are getting the Error message. You can use
the report footer or group footers.

You get Group footers by using the REPORT's Grouping and Sorting dialog.
View: Grouping and Sorting from the menu.
 
Does the query return the detail rows you expect?

Can you create a Totals query against the query and get the Sum of the
Submitted field? (You don't indicate what type of field/data this one is --
it could be an integer, counting the number of "submitteds", or a true/false
field, or a date/time field, e.g., date submitted.)

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Yes, the query returns the results that I want. All I want is a simple
total of all the columns. I was using the Page Footer and that is why I am
getting the #Error. I read this from other posts.

I tried playing around group and sorting, but I don't want to group on any
field, just get a total. I am lost. There is no field type that I can
determine.




Jeff Boyce said:
Does the query return the detail rows you expect?

Can you create a Totals query against the query and get the Sum of the
Submitted field? (You don't indicate what type of field/data this one
is --
it could be an integer, counting the number of "submitteds", or a
true/false
field, or a date/time field, e.g., date submitted.)

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


HeartSA said:
I have a query that counts occurrences, I created a report based on that
query. When I try to create a totals in the footer section, I get an #Error
message.

Here is the query in SQL form

SELECT StaffCount.Location, StaffCount.[Staff Count],
Submitted.Submitted,
Resolved.Resolved, Active.Active
FROM Active RIGHT JOIN (Resolved INNER JOIN (Submitted INNER JOIN StaffCount
ON Submitted.Location = StaffCount.Location) ON Resolved.Location =
StaffCount.Location) ON Active.Location = StaffCount.Location
GROUP BY StaffCount.Location, StaffCount.[Staff Count], Submitted.Submitted,
Resolved.Resolved, Active.Active;

I use all the fields in the select statement above on the report. The query
is called "Joined Query".
I am attempting to total Submitted, Resolved and Active.

Syntax I tried in the text box

=Sum([Submitted].[Submitted])
=Sum([Submitted])
=Sum([Joined Query].[Submitted])

I get the #Error on all of these. Any help is much appreciated.
Click to expand...
Click to expand...
 
I don't understand.

If you "follow" the source of the fields back to the tables they come from,
what data type are these fields you are trying to sum?

Why would using the Page Footer section cause the #Error?

Trying to remotely diagnose what isn't working about the (unseen and
unclear) way you are currently trying to make it work is quite literally
working in the dark...

Here's another potential approach -- since your query works, use a DSum()
function against the query, and put that (the DSum()) in your Page Footer
control.

That won't answer your question "why doesn't this work", but it may give you
a working solution.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


HeartSA said:
Yes, the query returns the results that I want. All I want is a simple
total of all the columns. I was using the Page Footer and that is why I am
getting the #Error. I read this from other posts.

I tried playing around group and sorting, but I don't want to group on any
field, just get a total. I am lost. There is no field type that I can
determine.




Jeff Boyce said:
Does the query return the detail rows you expect?

Can you create a Totals query against the query and get the Sum of the
Submitted field? (You don't indicate what type of field/data this one
is --
it could be an integer, counting the number of "submitteds", or a
true/false
field, or a date/time field, e.g., date submitted.)

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


HeartSA said:
I have a query that counts occurrences, I created a report based on that
query. When I try to create a totals in the footer section, I get an #Error
message.

Here is the query in SQL form

SELECT StaffCount.Location, StaffCount.[Staff Count],
Submitted.Submitted,
Resolved.Resolved, Active.Active
FROM Active RIGHT JOIN (Resolved INNER JOIN (Submitted INNER JOIN StaffCount
ON Submitted.Location = StaffCount.Location) ON Resolved.Location =
StaffCount.Location) ON Active.Location = StaffCount.Location
GROUP BY StaffCount.Location, StaffCount.[Staff Count], Submitted.Submitted,
Resolved.Resolved, Active.Active;

I use all the fields in the select statement above on the report. The query
is called "Joined Query".
I am attempting to total Submitted, Resolved and Active.

Syntax I tried in the text box

=Sum([Submitted].[Submitted])
=Sum([Submitted])
=Sum([Joined Query].[Submitted])

I get the #Error on all of these. Any help is much appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Try place your Sum()s in the Report Footer (or Report Header).
--
Duane Hookom
MS Access MVP

HeartSA said:
Yes, the query returns the results that I want. All I want is a simple
total of all the columns. I was using the Page Footer and that is why I
am getting the #Error. I read this from other posts.

I tried playing around group and sorting, but I don't want to group on any
field, just get a total. I am lost. There is no field type that I can
determine.




Jeff Boyce said:
Does the query return the detail rows you expect?

Can you create a Totals query against the query and get the Sum of the
Submitted field? (You don't indicate what type of field/data this one
is --
it could be an integer, counting the number of "submitteds", or a
true/false
field, or a date/time field, e.g., date submitted.)

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


HeartSA said:
I have a query that counts occurrences, I created a report based on that
query. When I try to create a totals in the footer section, I get an #Error
message.

Here is the query in SQL form

SELECT StaffCount.Location, StaffCount.[Staff Count],
Submitted.Submitted,
Resolved.Resolved, Active.Active
FROM Active RIGHT JOIN (Resolved INNER JOIN (Submitted INNER JOIN StaffCount
ON Submitted.Location = StaffCount.Location) ON Resolved.Location =
StaffCount.Location) ON Active.Location = StaffCount.Location
GROUP BY StaffCount.Location, StaffCount.[Staff Count], Submitted.Submitted,
Resolved.Resolved, Active.Active;

I use all the fields in the select statement above on the report. The query
is called "Joined Query".
I am attempting to total Submitted, Resolved and Active.

Syntax I tried in the text box

=Sum([Submitted].[Submitted])
=Sum([Submitted])
=Sum([Joined Query].[Submitted])

I get the #Error on all of these. Any help is much appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Are you trying to sum fields from multiple records, or are you trying to sum
fields within a single record? If the latter, an unbound text box with its
record source set to:
=[Number1] + [Number2]
should do the trick. Use your actual field names. Use Nz if any of the
fields could be null:
=Nz([Number1]) + Nz([Number2])

HeartSA said:
Yes, the query returns the results that I want. All I want is a simple
total of all the columns. I was using the Page Footer and that is why I
am getting the #Error. I read this from other posts.

I tried playing around group and sorting, but I don't want to group on any
field, just get a total. I am lost. There is no field type that I can
determine.




Jeff Boyce said:
Does the query return the detail rows you expect?

Can you create a Totals query against the query and get the Sum of the
Submitted field? (You don't indicate what type of field/data this one
is --
it could be an integer, counting the number of "submitteds", or a
true/false
field, or a date/time field, e.g., date submitted.)

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


HeartSA said:
I have a query that counts occurrences, I created a report based on that
query. When I try to create a totals in the footer section, I get an #Error
message.

Here is the query in SQL form

SELECT StaffCount.Location, StaffCount.[Staff Count],
Submitted.Submitted,
Resolved.Resolved, Active.Active
FROM Active RIGHT JOIN (Resolved INNER JOIN (Submitted INNER JOIN StaffCount
ON Submitted.Location = StaffCount.Location) ON Resolved.Location =
StaffCount.Location) ON Active.Location = StaffCount.Location
GROUP BY StaffCount.Location, StaffCount.[Staff Count], Submitted.Submitted,
Resolved.Resolved, Active.Active;

I use all the fields in the select statement above on the report. The query
is called "Joined Query".
I am attempting to total Submitted, Resolved and Active.

Syntax I tried in the text box

=Sum([Submitted].[Submitted])
=Sum([Submitted])
=Sum([Joined Query].[Submitted])

I get the #Error on all of these. Any help is much appreciated.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

update cross tab query in a report 7
Bi-monthly totalling query 2
Two Column Report 3
JOIN problem/question 1
Report combiones all records 3
Sum in report 10
Entering Report Parameters through a Form 2
Incorrect sum in MS Access query 1

Back
Top