Need All In Table To Show

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to have all the departments in the department table to show whether
they have information in them or not. The query that I am using is

SELECT tblAllWorkLoggedRecords.Date, tblDepartment.Department,
Sum(tblAllWorkLoggedRecords.[VOLUME RECEIVED]) AS [SumOfVOLUME RECEIVED]
FROM tblAllWorkLoggedRecords RIGHT JOIN tblDepartment ON
tblAllWorkLoggedRecords.DEPARTMENT = tblDepartment.Department
GROUP BY tblAllWorkLoggedRecords.Date, tblDepartment.Department
HAVING (((tblAllWorkLoggedRecords.Date)=[Enter date: mm/dd/yyyy]));


I am getting the information that I need but it is only showing the
departments that actually have information but not showing the departments
that have zero

Any help would be great appreciated
 
Applying the criteria to the tblAllWorkLoggedRecords negates the right join.
Try the following which should return rows where
tblAllWorkLoggedRecords.Date is null as well as records where the date is a
match

SELECT tblAllWorkLoggedRecords.Date, tblDepartment.Department,
Sum(tblAllWorkLoggedRecords.[VOLUME RECEIVED]) AS [SumOfVOLUME RECEIVED]
FROM tblAllWorkLoggedRecords RIGHT JOIN tblDepartment ON
tblAllWorkLoggedRecords.DEPARTMENT = tblDepartment.Department
WHERE tblAllWorkLoggedRecords.Date=[Enter date: mm/dd/yyyy]
OR tblAllWorkLoggedRecords.Date is Null
GROUP BY tblAllWorkLoggedRecords.Date, tblDepartment.Department
 
Back
Top