Count Question

G

Guest

First Off - I'm not very advanced when it comes to access. But I have two
tables: tbl_employees & tbl_violations. I want to find out how many times
an employee has a violation so I created this query using the group by (their
name) and count function (violation). It works correctly but I want the
query to return a zero next to the employees name if they don't have a
corresponding violation in the violations table. Please help!

SELECT Tbl_Employees.EmpFirstName & " " & [EmpLastName] AS fullname,
Count(Tbl_Violations.ViolationID) AS [Absense Count]
FROM Tbl_Employees INNER JOIN Tbl_Violations ON Tbl_Employees.EmployeeID =
Tbl_Violations.EmployeeID
WHERE (((Tbl_Violations.TypeID)=1) AND
((Tbl_Violations.DateofOccurance)>Date()-31))
GROUP BY Tbl_Employees.EmpFirstName & " " & [EmpLastName]
ORDER BY Count(Tbl_Violations.ViolationID) DESC;
 
G

Guest

Thanks Roger. I put in the left join but it's still returning the same
number of records and doesn't include any employees who don't have any
violations:

SELECT Tbl_Employees.EmpFirstName & " " & [EmpLastName] AS fullname,
Count(Tbl_Violations.ViolationID) AS [Absense Count]
FROM Tbl_Employees LEFT JOIN Tbl_Violations ON
Tbl_Employees.EmployeeID=Tbl_Violations.EmployeeID
WHERE (((Tbl_Violations.TypeID)=1) AND
((Tbl_Violations.DateofOccurance)>Date()-31))
GROUP BY Tbl_Employees.EmpFirstName & " " & [EmpLastName]
ORDER BY Count(Tbl_Violations.ViolationID) DESC;

Roger Carlson said:
Make your INNER JOIN a LEFT JOIN. It should be a simple as that.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Dextergiii said:
First Off - I'm not very advanced when it comes to access. But I have two
tables: tbl_employees & tbl_violations. I want to find out how many
times
an employee has a violation so I created this query using the group by
(their
name) and count function (violation). It works correctly but I want the
query to return a zero next to the employees name if they don't have a
corresponding violation in the violations table. Please help!

SELECT Tbl_Employees.EmpFirstName & " " & [EmpLastName] AS fullname,
Count(Tbl_Violations.ViolationID) AS [Absense Count]
FROM Tbl_Employees INNER JOIN Tbl_Violations ON Tbl_Employees.EmployeeID =
Tbl_Violations.EmployeeID
WHERE (((Tbl_Violations.TypeID)=1) AND
((Tbl_Violations.DateofOccurance)>Date()-31))
GROUP BY Tbl_Employees.EmpFirstName & " " & [EmpLastName]
ORDER BY Count(Tbl_Violations.ViolationID) DESC;
Click to expand...
Click to expand...
 
M

Michael Gramelspacher

Thanks Roger. I put in the left join but it's still returning the same
number of records and doesn't include any employees who don't have any
violations:

SELECT Tbl_Employees.EmpFirstName & " " & [EmpLastName] AS fullname,
Count(Tbl_Violations.ViolationID) AS [Absense Count]
FROM Tbl_Employees LEFT JOIN Tbl_Violations ON
Tbl_Employees.EmployeeID=Tbl_Violations.EmployeeID
WHERE (((Tbl_Violations.TypeID)=1) AND
((Tbl_Violations.DateofOccurance)>Date()-31))
GROUP BY Tbl_Employees.EmpFirstName & " " & [EmpLastName]
ORDER BY Count(Tbl_Violations.ViolationID) DESC;

Roger Carlson said:
Make your INNER JOIN a LEFT JOIN. It should be a simple as that.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Dextergiii said:
First Off - I'm not very advanced when it comes to access. But I have two
tables: tbl_employees & tbl_violations. I want to find out how many
times
an employee has a violation so I created this query using the group by
(their
name) and count function (violation). It works correctly but I want the
query to return a zero next to the employees name if they don't have a
corresponding violation in the violations table. Please help!

SELECT Tbl_Employees.EmpFirstName & " " & [EmpLastName] AS fullname,
Count(Tbl_Violations.ViolationID) AS [Absense Count]
FROM Tbl_Employees INNER JOIN Tbl_Violations ON Tbl_Employees.EmployeeID =
Tbl_Violations.EmployeeID
WHERE (((Tbl_Violations.TypeID)=1) AND
((Tbl_Violations.DateofOccurance)>Date()-31))
GROUP BY Tbl_Employees.EmpFirstName & " " & [EmpLastName]
ORDER BY Count(Tbl_Violations.ViolationID) DESC;
Click to expand...
Click to expand...
Click to expand...
try:

SELECT Tbl_Employees.EmpFirstName & " " & [EmpLastName] AS fullname,
COUNT(a.ViolationID)AS [Absense Count]
FROM Tbl_Employees
LEFT JOIN (SELECT * FROM Tbl_Violations WHERE Tbl_Violations.TypeID = 1)
AND Tbl_Violations.DateofOccurance > DATE() - 31) AS a
ON Tbl_Employees.EmployeeID = a.EmployeeID
GROUP BY Tbl_Employees.EmpFirstName & " " & [EmpLastName]
ORDER BY COUNT(a.ViolationID) DESC;
 
J

John Spencer

The problem is that you have applied criteria to the table on the right side
of the join. That negates the left join.

You can try using a subquery in the join clause

SELECT Tbl_Employees.EmpFirstName & " " & [EmpLastName] AS fullname
, T.AbsenceCount
FROM Tbl_Employees LEFT JOIN
(SELECT V.EmployeeID, Count(T.ViolationID) as AbsenceCount
FROM tbl_Violations as V
WHERE V.TypeID =1 and V.DateOfOccurance > Date() - 31
GROUP BY V.EmployeeID) as T ON
Tbl_Employees.EmployeeID=T.EmployeeID
ORDER BY T.AbsenceCount DESC;

Another way to do this is to build a query that returns the aggregated rows
from tbl_Violations and save that. Then using that and tbl_employees create
a LEFT JOIN query that returns the final result.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Dextergiii said:
Thanks Roger. I put in the left join but it's still returning the same
number of records and doesn't include any employees who don't have any
violations:

SELECT Tbl_Employees.EmpFirstName & " " & [EmpLastName] AS fullname,
Count(Tbl_Violations.ViolationID) AS [Absense Count]
FROM Tbl_Employees LEFT JOIN Tbl_Violations ON
Tbl_Employees.EmployeeID=Tbl_Violations.EmployeeID
WHERE (((Tbl_Violations.TypeID)=1) AND
((Tbl_Violations.DateofOccurance)>Date()-31))
GROUP BY Tbl_Employees.EmpFirstName & " " & [EmpLastName]
ORDER BY Count(Tbl_Violations.ViolationID) DESC;

Roger Carlson said:
Make your INNER JOIN a LEFT JOIN. It should be a simple as that.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Dextergiii said:
First Off - I'm not very advanced when it comes to access. But I have
two
tables: tbl_employees & tbl_violations. I want to find out how many
times
an employee has a violation so I created this query using the group by
(their
name) and count function (violation). It works correctly but I want
the
query to return a zero next to the employees name if they don't have a
corresponding violation in the violations table. Please help!

SELECT Tbl_Employees.EmpFirstName & " " & [EmpLastName] AS fullname,
Count(Tbl_Violations.ViolationID) AS [Absense Count]
FROM Tbl_Employees INNER JOIN Tbl_Violations ON
Tbl_Employees.EmployeeID =
Tbl_Violations.EmployeeID
WHERE (((Tbl_Violations.TypeID)=1) AND
((Tbl_Violations.DateofOccurance)>Date()-31))
GROUP BY Tbl_Employees.EmpFirstName & " " & [EmpLastName]
ORDER BY Count(Tbl_Violations.ViolationID) DESC;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks John - it works!

John Spencer said:
The problem is that you have applied criteria to the table on the right side
of the join. That negates the left join.

You can try using a subquery in the join clause

SELECT Tbl_Employees.EmpFirstName & " " & [EmpLastName] AS fullname
, T.AbsenceCount
FROM Tbl_Employees LEFT JOIN
(SELECT V.EmployeeID, Count(T.ViolationID) as AbsenceCount
FROM tbl_Violations as V
WHERE V.TypeID =1 and V.DateOfOccurance > Date() - 31
GROUP BY V.EmployeeID) as T ON
Tbl_Employees.EmployeeID=T.EmployeeID
ORDER BY T.AbsenceCount DESC;

Another way to do this is to build a query that returns the aggregated rows
from tbl_Violations and save that. Then using that and tbl_employees create
a LEFT JOIN query that returns the final result.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Dextergiii said:
Thanks Roger. I put in the left join but it's still returning the same
number of records and doesn't include any employees who don't have any
violations:

SELECT Tbl_Employees.EmpFirstName & " " & [EmpLastName] AS fullname,
Count(Tbl_Violations.ViolationID) AS [Absense Count]
FROM Tbl_Employees LEFT JOIN Tbl_Violations ON
Tbl_Employees.EmployeeID=Tbl_Violations.EmployeeID
WHERE (((Tbl_Violations.TypeID)=1) AND
((Tbl_Violations.DateofOccurance)>Date()-31))
GROUP BY Tbl_Employees.EmpFirstName & " " & [EmpLastName]
ORDER BY Count(Tbl_Violations.ViolationID) DESC;

Roger Carlson said:
Make your INNER JOIN a LEFT JOIN. It should be a simple as that.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


First Off - I'm not very advanced when it comes to access. But I have
two
tables: tbl_employees & tbl_violations. I want to find out how many
times
an employee has a violation so I created this query using the group by
(their
name) and count function (violation). It works correctly but I want
the
query to return a zero next to the employees name if they don't have a
corresponding violation in the violations table. Please help!

SELECT Tbl_Employees.EmpFirstName & " " & [EmpLastName] AS fullname,
Count(Tbl_Violations.ViolationID) AS [Absense Count]
FROM Tbl_Employees INNER JOIN Tbl_Violations ON
Tbl_Employees.EmployeeID =
Tbl_Violations.EmployeeID
WHERE (((Tbl_Violations.TypeID)=1) AND
((Tbl_Violations.DateofOccurance)>Date()-31))
GROUP BY Tbl_Employees.EmpFirstName & " " & [EmpLastName]
ORDER BY Count(Tbl_Violations.ViolationID) DESC;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query trouble...summing count...suggestions? 1
Summing a count field please help 1
Update query not working Please help 1
Summing a count field? 1
SQL - Count ? 3
Need to Count by two different criteria in same Totals query 4
query working very very slow 1
No result query feeding a query that does a calculation 2

Top