0

This example shows what works, however, there are six other positions besides System Operator in the same table and I need to count how many times this 'Person' is displayed in the other positions as well.

One record's 'Person' can be a System Operator and in another the 'Person' can be the Engineer. So I want a query that would be able to count how many times this 'Person' was an engineer or system operator in the same query.

When I add a Count() function for Engineer it only attempts to return values in which the person is both an Engineer and System Operator and I'm looking to count how many times this person was either distinctly.

Sample Data would include the following:

  • Table Name: TblEventPersonnel _______________

  • Table Fields: Start Date, Car, State, Test Manager, System Operator, Engineer One, Engineer Two, Trainee One, Trainee Two

  • Every Test Manager, System Operator, Engineer one, Engineer Two, Trainee One, Trainee Two is a 'Person' say "Bob"

  • And I need a query that can count how many times Bob is a System Operator, Engineer one, Engineer Two, Test Manager, Trainee one, Trainee two.

Ultimately, I need to generate a report or have a form that has a combo box with a list of people (Bob, Joe, Gregg) or all employees that when I select an individual it will tell me how many times that person was in each capacity. The code below returns the number 4. 

SELECT Count(tblEventPersonnel.[System Operator]) AS [CountOfSystem Operator]
FROM tblEventPersonnel
GROUP BY tblEventPersonnel.[System Operator]
HAVING (((tblEventPersonnel.[System Operator]) Like "Person"));
Parfait
  • 104,375
  • 17
  • 94
  • 125
delnav222
  • 19
  • 5
  • 3
    Just a note, but `LIKE` without any wildcards or patterns works exactly like `=` except that it takes longer. – Bacon Bits Jan 05 '18 at 18:54
  • 1
    Do you have the ability to change the database schema as this table design strays from best practices of database normalization? Otherwise your queries will be complex, storage inefficient, scalability limited. – Parfait Jan 05 '18 at 19:15
  • I do have the ability to change the database schema. It's an event scheduler and I wasn't sure how to organize this tables. It's a brand new database. I'm open to any ideas, not sure of the best way. – delnav222 Jan 05 '18 at 21:27

4 Answers4

1

You can use the SUM function instead of the COUNT function if your [System Operator] (and the other fields) use the value 1 to indicate that the person is a system operator and 0 to indicate that the person is not:

SELECT SUM(tblEventPersonnel.[System Operator]) AS [CountOfSystem Operator],
       SUM(tblEventPersonnel.[Engineer One]) AS [CountOfEngineerOne]
FROM tblEventPersonnel

However, in your case those fields store the person. You can use CASE WHEN inside the SUM function to make the counting conditional. If the table is normalized and the ID of the person is stored in those fields, you can use a simple > 0 as condition. But your table design doesn't seem to be normalized, because you're storing the name of the person instead of the ID. In this case you need to use IS NOT NULL as condition:

SELECT SUM(CASE WHEN tblEventPersonnel.[System Operator] IS NOT NULL THEN 1 END) AS [CountOfSystem Operator],
       SUM(CASE WHEN tblEventPersonnel.[Engineer One] IS NOT NULL THEN 1 END) AS [CountOfEngineerOne]
FROM tblEventPersonnel
WHERE tblEventPersonnel.[System Operator] = "Bob"
   OR tblEventPersonnel.[Engineer One] = "Bob";

MS Access doesn't support CASE WHEN, so if you're using MS Access, then use the IIF() function instead:

SELECT SUM(IIF(tblEventPersonnel.[System Operator] IS NOT NULL, 1, NULL)) AS [CountOfSystem Operator],
       SUM(IIF(tblEventPersonnel.[Engineer One] IS NOT, 1, NULL)) AS [CountOfEngineerOne]
FROM tblEventPersonnel
WHERE tblEventPersonnel.[System Operator] = "Bob"
   OR tblEventPersonnel.[Engineer One] = "Bob";
Racil Hilan
  • 24,690
  • 13
  • 50
  • 55
  • I attempted use of the third since I am uploading the query into the SQL view of MS Access query prior to inserting into VBA. This seemed to work well but not quite accurate enough. If Bob was a System Operator on one day and a Test Engineer on another day then it returns two record counts for both the System Operator and Test Engineer instead of one each. If Bob was both a Test Engineer and also a System Operator on the same day then it returns one of each, the correct answer. Except the same person will most likely not be in two different positions on the same day. – delnav222 Jan 05 '18 at 21:32
  • That's why we asked you for sample data. It's very hard to guess how your data looks like from your question. I'm glad you figured it out at the end. – Racil Hilan Jan 05 '18 at 22:35
0

You could use a sub query union

SELECT Count(CountofPerson) AS PositionCount FROM (SELECT Count(tblEventPersonnel.[System Operator]) AS [CountOfPerson] FROM tblEventPersonnel GROUP BY tblEventPersonnel.[System Operator] HAVING (tblEventPersonnel.[System Operator] Like "Person") UNION SELECT Count(tblEventPersonnel.[Engineer One]) AS [CountOfPerson] FROM tblEventPersonnel GROUP BY tblEventPersonnel.[Engineer One] HAVING (tblEventPersonnel.[Engineer One] Like "Person") );

Add other fields as needed.

Jeffrey
  • 528
  • 5
  • 14
0

I think the way the table is designed is not friendly for your current use. For me, the data would be easier to visualize and query again using a pivot. Here's a great article on how to effectively use pivot in sql server:

https://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query

Here's another discussion about this on stackoverflow:

Convert Rows to columns using 'Pivot' in SQL Server

Since you have this tagged as MS Access and SQL Server, here's another user's experience with pivot in MS ACCESS:

Pivoting data in MS Access

dsmeclipse
  • 36
  • 7
0

This code worked!

SELECT Sum(IIf(tblEventPersonnel.[System Operator]="Person",1,0)) AS [CountOfSystem Operator],
       Sum(IIf(tblEventPersonnel.[Test Engineer 1]="Person",1,0)) AS [CountOfTest Engineer 1]
FROM tblEventPersonnel;
Racil Hilan
  • 24,690
  • 13
  • 50
  • 55
delnav222
  • 19
  • 5