MS Access Query / Design

Question

I have a table with instances of employee events. Each row has an ID, employee name and an eventNumber among other data. I would like to find and count all instances of where “employee name” occurs in this table, thus counting all the events associated with that employee.

If there are 15 unique employees , I suppose I could write 15 separate queries to get this done. But, is there a more efficient way to do this?

Also, I would like to display the total number of events for each employee that gets returned from these queries in another table or visual display of some sorts. This is where I really don’t know what to do as I haven’t used access much but am much more familiar with SQL.

Thanks for everyone who could take some time to chime in with some advice. I appreciate it a lot

Answer 1

This is fairly straight-forward in SQL. You can GROUP BY the employee name to get an overall count. Adding GROUP BY to a SQL statement will allow you to perform aggregate functions on one or more columns. Aggregate functions include COUNT, SUM, MIN, and MAX.

Documentation about GROUP BY can be found here: https://support.office.com/en-us/article/group-by-clause-84eeb766-25d2-4aa1-8eea-002bb65ef3a0

A couple examples:

This query groups by EmployeeName to get the total count of all rows in table by employee name.

SELECT
    EmployeeName
    , COUNT(*) AS NumberOfEvents
FROM table
GROUP BY EmployeeName

Additionally, if you would like to get counts by eventid and employee name, you can group by both employee name and eventid. The query will then show the count of each type of event for each employee name.

SELECT
    EmployeeName
    , EventId
    , COUNT(*) AS NumberOfEvents
FROM table
GROUP BY 
    EmployeeName
    , EventId

If you would like to filter the results so that only one employee, 'Leroy Jenkins' is shown then you can add a WHERE clause as well, like this:

SELECT
    EmployeeName
    , COUNT(*) AS NumberOfEvents
FROM table
WHERE EmployeeName = "Leroy Jenkins"
GROUP BY EmployeeName

---

Responding to @WesG's comment asking for clarification.

The aggregate functions, like COUNT, are "rolling up" several rows in a table and displaying a single value for those rows. If your SELECT statement does not contain a GROUP BY clause, then the aggregate functions are working on all rows in the table.

When you do have a GROUP BY clause, then the aggregate functions will work on each group of rows that all have the same values in the columns by which you are grouping. Also, if you include a GROUP BY clause, then you may not SELECT columns that you are not GROUPing BY.

So, if you have a table like this:

CREATE TABLE [dbo].[AuditTrailLogs](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [EventType] nvarchar(20) NOT NULL,
    [EventId] [int] NOT NULL,
    [Message] [nvarchar](max) NULL,
    [WhoDidIt] [nvarchar](50) NULL,
    [WhenOccurredUtc] [datetime] NOT NULL,
    [EntityPayload] [nvarchar](max) NULL,
)

You could count the total number of rows in the table like this:

SELECT COUNT(*) AS NumLogEntries
FROM dbo.AuditTrailLogs

If you wanted to count rows by the user who made the entry:

SELECT WhoDidIt, COUNT(*) AS NumLogEntries
FROM dbo.AuditTrailLogs
GROUP BY WhoDidIt

Then Access is going to split the rows in the table into a number of groups and COUNT will return the number log entries for each distinct value of WhoDidIt in the table.

If you want to see the totals by user who made the log entry and event type, then you could group by both columns:

SELECT WhoDidIt, EventType, COUNT(*) AS NumLogEntries
FROM dbo.AuditTrailLogs
GROUP BY WhoDidIt, EventType

Now, the NumLogEntries in each row of the result set will be the total of each event type that each user made.

Access will complain if you try to do something like this, though:

SELECT Id, WhoDidIt, EventType, COUNT(*) AS NumLogEntries
FROM dbo.AuditTrailLogs
GROUP BY WhoDidIt, EventType

If you think about it, this query doesn't make sense. The COUNT aggregate functions says you want to roll up multiple rows and display a single value for all those rows, however, you're also asking to see a value that isn't part of a group and is different for each row in the table.

Answer 2

something like

select employeeName, count(*) as nbEvents  
from myTable
group by employeeName