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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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..
geeta_m9Author Commented:
Sorry, I am now attaching the second image.
Query-Design.jpg
Rey Obrero (Capricorn1)Commented:
how exactly, you want the output to look like ?


better if you upload a sample db with the table
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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
Rey Obrero (Capricorn1)Commented:
and where do you want to see the total 401 appear?
geeta_m9Author Commented:
Is it possible to have it appear only once in a separate row or column?
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
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
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

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.
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?
Rey Obrero (Capricorn1)Commented:
change

SELECT 'All' Field2,

with


SELECT 'All' as Field2,
geeta_m9Author Commented:
It works now. Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.