List three count columns from a boolean field in a table.

[Tom Enders]

I am creating a database that keeps track of observations made on employees by their manager. This database has two tables, relevant to this query.



tblEmployees looks like the attached tblEmployees.png file. (On the left)
tblObservations looks like the attached tblObservations.png file. (On the right)

The employee field in tblObservations is a foreign key to tblEmployees.

I would like the query to return a concatenated field containing LastName, FirstName, category, the total number of entries for an employee in a category, the number of positive entries for an employee in a category, the number of negative (positive is false) entries for an employee in a category.

The closest I have gotten is to create three cross tab queries: the sum of all entries per employee per category, the sum of all positive entries per employee per category, the sum of all negative entries per employee per category. Then I created a fourth query which, I intended to, display the results of the three sums from those three queries.

Each of the first three queries works flawlessly. The fourth query displayed the same number for negative, positive, and total. The number was the same as the number from the total query.

Additional Info: I do have a category table which simply contains a list of category names so that category in tblEmployees is a look up ensuring that my category names do not suffer from misspellings and typos.

Any assistance will be greatly appreciated.

[ranman256]

you really should clarify your ID fields....
EmpID, or OservID

but to get your item....you need 2 queries.
Q1 to get the sum
(in the query, turn on summation)
select employee, category, COUNT(ID) from tOberve

Q2, join the sum to your employee table and make the concat field:
select tEmp.First & tEmp.Last & Q1.Catagory as ConcatFld , Q1.CountOfID from tEmps, Q1

the above sql is close so use the query builder to be exact.

[Tom Enders]

you really should clarify your ID fields....
EmpID, or OservID

Yeah I know that's a best practice, however I always put the table names in my queries and I'm the only one who will ever use this database so this is good enough.

but to get your item....you need 2 queries.
Q1 to get the sum
(in the query, turn on summation)
select employee, category, COUNT(ID) from tOberve

Q2, join the sum to your employee table and make the concat field:
select tEmp.First & tEmp.Last & Q1.Catagory as ConcatFld , Q1.CountOfID from tEmps, Q1

the above sql is close so use the query builder to be exact.

I solved it with these two queries:

SELECT [LastName] & ", " & [FirstName] AS Name, 
tblObservations.Category, 
IIf([Positive]=True,1,0) AS Pos, 
IIf([Positive]=False,1,0) AS Neg
FROM tblEmployees INNER JOIN tblObservations ON tblEmployees.ID = tblObservations.Employee
ORDER BY [LastName] & ", " & [FirstName];

The Pos column has a 1 if Positive is true and a 0 if positive is false.
The Neg column has a 1 if Positive is false and a 0 if positive is true.

SELECT qryObsrv_Name_Cat_Pos_Neg.Name, 
qryObsrv_Name_Cat_Pos_Neg.Category, 
Count(qryObsrv_Name_Cat_Pos_Neg.Category) AS Total, 
Sum(qryObsrv_Name_Cat_Pos_Neg.Pos) AS Pos, 
Sum(qryObsrv_Name_Cat_Pos_Neg.Neg) AS Neg, 
([Pos]/[Total])*100 & "%" AS Score
FROM qryObsrv_Name_Cat_Pos_Neg
GROUP BY qryObsrv_Name_Cat_Pos_Neg.Name, qryObsrv_Name_Cat_Pos_Neg.Category
ORDER BY qryObsrv_Name_Cat_Pos_Neg.Name, qryObsrv_Name_Cat_Pos_Neg.Category;

The Pos column in this query sums the Pos column from the previous query, thereby providing the total positive entries.
The Neg column in this query sums the Neg column from the previous query, thereby providing the total of the negative entries.
The Total column in this query counts the number of entries with each category name, thereby providing the total of both positive and negative entries.
The Score column provides the percentage of entries that are positive for each category per employee.