How to obtain a sum of counts

I have created the following query (see attachments). The query currently gives the total number of students seen by each staff member. What I would like it to do is obtain the sum of all the counts. Towards, this end I added an additional column (the rightmost column in the image) and added the field label, "Counts", and  the aggregate function, "Sum". However as soon as I entered those values, Access automatically changes the column name to  "Expr1:[Counts]", which isn't what I want. I am not sure what I am doing wrong and what I should do to obtain the correct result.
Query-Output.jpg
Query-Output.jpg
geeta_m9Asked:
Who is Participating?
 
SharathData EngineerCommented:
SELECT [AppointmentReport-1].Field2, [AppointmentReport-1].Staff, Count([AppointmentReport-1].Field9) AS Counts
FROM [AppointmentReport-1]
where [AppointmentReport-1].Staff="Staff"
GROUP BY [AppointmentReport-1].Field2, [AppointmentReport-1].Staff
union all
SELECT 'All' Field2,[AppointmentReport-1].Staff, Count([AppointmentReport-1].Field9) AS Counts
FROM [AppointmentReport-1]
where [AppointmentReport-1].Staff="Staff"
GROUP BY [AppointmentReport-1].Staff

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
you posted the same image...

how exactly, you want the output to look like ?

post the sql statement of your query..
0
 
geeta_m9Author Commented:
Sorry, I am now attaching the second image.
Query-Design.jpg
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rey Obrero (Capricorn1)Commented:
how exactly, you want the output to look like ?


better if you upload a sample db with the table
0
 
geeta_m9Author Commented:
I would like the output to look the way it is now, but with the grand total included, i.e.,
62+59+117+57+106 = 401
0
 
Rey Obrero (Capricorn1)Commented:
and where do you want to see the total 401 appear?
0
 
geeta_m9Author Commented:
Is it possible to have it appear only once in a separate row or column?
0
 
geeta_m9Author Commented:
I didn't know how to do it in one query, so I ended up creating a second query and using the previous query  as the data  source (see attached). Not the most elegant approach but at least it worked.
Sum-of-Counts.jpg
0
 
Rey Obrero (Capricorn1)Commented:
i can not work on the image you are posting, post the SQL statement of the query you are using,


or better upload a sample db with the table
0
 
SharathData EngineerCommented:
Can you try like this?
select Field2,Staff,count(*) as Counts
  from your_table
 group by Field2,Staff
 union all
 select 'All' as Field2,Staff,count(*) as Counts
   from your_table
   group by Staff

Open in new window

0
 
geeta_m9Author Commented:
Here is the code from the first query:

SELECT [AppointmentReport-1].Field2, [AppointmentReport-1].Staff, Count([AppointmentReport-1].Field9) AS Counts
FROM [AppointmentReport-1]
GROUP BY [AppointmentReport-1].Field2, [AppointmentReport-1].Staff
HAVING ((([AppointmentReport-1].Staff)="Staff") AND ((Count([AppointmentReport-1].Field9)) Is Not Null));

What I would like to do is to add some code that will give me the sum of all the counts for the different groups (or staff members). In other words, it will add up the number of students for each staff member and give me the grand total.
0
 
geeta_m9Author Commented:
When I tried running your query, I get the error: Syntax error (missing operator) in query expression "All" Field2'.

What is the 'All' in line 6 do?
0
 
Rey Obrero (Capricorn1)Commented:
change

SELECT 'All' Field2,

with


SELECT 'All' as Field2,
0
 
geeta_m9Author Commented:
It works now. Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.