Hello all,
I was asked to create a query to show only the employees from a certain table for an already existing query. So was able to add the table to the query and it worked great. But now they want to show all of the employees that exclude the employees in the table. I thought that would be easy by just changing the join between the tables. But I am not getting the correct results.
the first query that works is filtering the data to only show 115 employees out of the 526. here is the sql below. If I change the join between the current employees table and the new table called ReportsTo I get 501 records. that does not equal 526. How do I fix this query? Is it a join thing or rewriting the query?
Thank You
Jim Wagner
SELECT SCHEDULEDATA.Scheduletype, SCHEDULEDATA.[Empl Fte Pct], SCHEDULEDATA.TRAVELREDDAY, SCHEDULEDATA.vacflag, [R&D-CURRENTEMPLOYEES].[Person Nm], [R&D-CURRENTEMPLOYEES].[Person Id], [R&D-CURRENTEMPLOYEES].[Empl Stat Cd], [R&D-CURRENTEMPLOYEES].[Empl Stat Sd], [R&D-CURRENTEMPLOYEES].[Dept Id], [R&D-CURRENTEMPLOYEES].[Dept Ld], [R&D-CURRENTEMPLOYEES].[Paygrp Cd], [R&D-CURRENTEMPLOYEES].[Paygrp Ld], [R&D-CURRENTEMPLOYEES].[Empl Type Cd], [R&D-CURRENTEMPLOYEES].[Empl Type Ld], [R&D-CURRENTEMPLOYEES].[Fica Stat Cd], [R&D-CURRENTEMPLOYEES].[Pos Cd], [R&D-CURRENTEMPLOYEES].[Flsa Stat Cd], [R&D-CURRENTEMPLOYEES].[Hrly Rt], DateAdd("d",0,[Forms]![frmMainTabbedForm]![txtEnterPayWeekEndDate]) AS NewDate, Format(DateAdd("d",-6,[Forms]![frmMainTabbedForm]![txtEnterPayWeekEndDate]),"mm/dd") AS Monday, Format(DateAdd("d",-5,[Forms]![frmMainTabbedForm]![txtEnterPayWeekEndDate]),"mm/dd") AS Tuesday, Format(DateAdd("d",-4,[Forms]![frmMainTabbedForm]![txtEnterPayWeekEndDate]),"mm/dd") AS Wednesday, Format(DateAdd("d",-3,[Forms]![frmMainTabbedForm]![txtEnterPayWeekEndDate]),"mm/dd") AS Thursday, Format(DateAdd("d",-2,[Forms]![frmMainTabbedForm]![txtEnterPayWeekEndDate]),"mm/dd") AS Friday, Format(DateAdd("d",-1,[Forms]![frmMainTabbedForm]![txtEnterPayWeekEndDate]),"mm/dd") AS Saturday, Format(DateAdd("d",0,[Forms]![frmMainTabbedForm]![txtEnterPayWeekEndDate]),"mm/dd") AS Sunday, AccountCodesForTimesheets.[New Acct Cd], AccountCodesForTimesheets.Maint1, AccountCodesForTimesheets.Maint2, AccountCodesForTimesheets.Svc1, AccountCodesForTimesheets.Svc2, AccountCodesForTimesheets.Standby, SCHEDULEDATA.compflag, ReportsTo.[Reports to], ReportsTo.[Reports to Title], ReportsTo.[Employee Position #], ReportsTo.[Sub dept]
FROM (SCHEDULEDATA INNER JOIN (AccountCodesForTimesheets INNER JOIN [R&D-CURRENTEMPLOYEES] ON AccountCodesForTimesheets.[Position Nbr] = [R&D-CURRENTEMPLOYEES].[Pos Cd]) ON SCHEDULEDATA.[Person Id] = [R&D-CURRENTEMPLOYEES].[Person Id]) INNER JOIN ReportsTo ON [R&D-CURRENTEMPLOYEES].[Person Id] = ReportsTo.[Empl ID]
GROUP BY SCHEDULEDATA.Scheduletype, SCHEDULEDATA.[Empl Fte Pct], SCHEDULEDATA.TRAVELREDDAY, SCHEDULEDATA.vacflag, [R&D-CURRENTEMPLOYEES].[Person Nm], [R&D-CURRENTEMPLOYEES].[Person Id], [R&D-CURRENTEMPLOYEES].[Empl Stat Cd], [R&D-CURRENTEMPLOYEES].[Empl Stat Sd], [R&D-CURRENTEMPLOYEES].[Dept Id], [R&D-CURRENTEMPLOYEES].[Dept Ld], [R&D-CURRENTEMPLOYEES].[Paygrp Cd], [R&D-CURRENTEMPLOYEES].[Paygrp Ld], [R&D-CURRENTEMPLOYEES].[Empl Type Cd], [R&D-CURRENTEMPLOYEES].[Empl Type Ld], [R&D-CURRENTEMPLOYEES].[Fica Stat Cd], [R&D-CURRENTEMPLOYEES].[Pos Cd], [R&D-CURRENTEMPLOYEES].[Flsa Stat Cd], [R&D-CURRENTEMPLOYEES].[Hrly Rt], DateAdd("d",0,[Forms]![frmMainTabbedForm]![txtEnterPayWeekEndDate]), Format(DateAdd("d",-6,[Forms]![frmMainTabbedForm]![txtEnterPayWeekEndDate]),"mm/dd"), Format(DateAdd("d",-5,[Forms]![frmMainTabbedForm]![txtEnterPayWeekEndDate]),"mm/dd"), Format(DateAdd("d",-4,[Forms]![frmMainTabbedForm]![txtEnterPayWeekEndDate]),"mm/dd"), Format(DateAdd("d",-3,[Forms]![frmMainTabbedForm]![txtEnterPayWeekEndDate]),"mm/dd"), Format(DateAdd("d",-2,[Forms]![frmMainTabbedForm]![txtEnterPayWeekEndDate]),"mm/dd"), Format(DateAdd("d",-1,[Forms]![frmMainTabbedForm]![txtEnterPayWeekEndDate]),"mm/dd"), Format(DateAdd("d",0,[Forms]![frmMainTabbedForm]![txtEnterPayWeekEndDate]),"mm/dd"), AccountCodesForTimesheets.[New Acct Cd], AccountCodesForTimesheets.Maint1, AccountCodesForTimesheets.Maint2, AccountCodesForTimesheets.Svc1, AccountCodesForTimesheets.Svc2, AccountCodesForTimesheets.Standby, SCHEDULEDATA.compflag, ReportsTo.[Reports to], ReportsTo.[Reports to Title], ReportsTo.[Employee Position #], ReportsTo.[Sub dept]
HAVING ((([R&D-CURRENTEMPLOYEES].[Paygrp Cd]) Not Like "STU"))
ORDER BY [R&D-CURRENTEMPLOYEES].[Person Id];