Re: [MS_AccessPros] Help with Excluding employees from a table

 

Hi Jim,

Does every employee have a matching record in ReportsTo?

Is there a reason for using a GROUP BY when there are no aggregates in the query? Are you just attempting to get unique employee records?

I'm not sure what you mean by "all of the employees that exclude the employees in the table".

Duane

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, January 31, 2018 3:40 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Help with Excluding employees from a table

 

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];

__._,_.___

---

Posted by: Duane Hookom <duanehookom@hotmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___