Access 2013: Form Calculations

I am creating a new form that I will be using to show how many employees we have in each department.

I have a table with totals at the bottom. How do I show the Table field name with the total on a form.

Or should I be using a Query? If so, How do I show the query field name with the total on a form.
CMILLERAsked:
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you create a Datasheet form based on your table, the Totals Rows should show at the bottom.

Note there were some issues with Access 2013 showing the Totals Row, so be sure you're fully up to date with all Windows/Office updates
Jeffrey CoachmanMIS LiasonCommented:
I may be missing something but...
in a standard datasheet view, the field names already appear at the top of the column.
In other words, I am having difficulty visualizing what you need here...

To me it would seem that all you would need is a Totals Query.
SELECT Count(YourTable.eID) AS CountOfeID, YourTable.eDept
FROM YourTable
GROUP BY YourTable.eDept;
Untitled.png
CMILLERAuthor Commented:
I am trying to build something more visual for dept managers. actually this is just the first step of what I am trying to figure out
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Jeffrey CoachmanMIS LiasonCommented:
Ok thanks
I will let you continue with Scott to avoid confusion...
;-)

Jeff
CMILLERAuthor Commented:
Scott,

All I want to show is a single label with a total for each dept.

Basically a dash board
CMILLERAuthor Commented:
Jeff,

Thanks for the totals query info.
Richard DanekeTrainerCommented:
So, a quick answer is to base the form on a crosstab query.   The Query Wizard will guide you through the process of creating a crosstab query on your employees.

Once created, use the Form button to create a form based on that query.

In the example attached, the Departments Example form shows the count of employees in each department.
IssuesAndTasks.accdb

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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The Crosstab suggested by Richard will work, but they tend to be somewhat limiting in that they're not dynamic - if you add Columns, you'd have to recreate everything.

I'd build something like this with multiple recordsets - for example, I'd determine what data I need to show (perhaps all Employees for Supervisor1), then get that data with standard Group By recordsets, and then display that on the form in some manner. Perhaps you could use temporary tables to store the data in a denormalized manner, and then create a form/report that shows this (note too that you can embed a Report in a Form in Access 2010+).
CMILLERAuthor Commented:
Richard,

Thanks for the example, I think it will work fine for what I am doing.
Richard DanekeTrainerCommented:
Good to hear.  Thank you for the points and the feedback.

Good luck on your journey to learn more about Access!
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 Access

From novice to tech pro — start learning today.