If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need All In Table To Show
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 |
#2
|
|||
|
|||
Need All In Table To Show
Right click on your "Join" between the two tables and choose properties,
select the "Show all records in tbl_Dept. See if that works for you. -- Jeff C Live Well .. Be Happy In All You Do "Welthey" wrote: 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 |
#3
|
|||
|
|||
Need All In Table To Show
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 "Welthey" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|