Re: [MS_AccessPros] Remove Duplicate record in query

 

Hi Jim,

Can we assume the Student Positions table has the multiple records per employee? If so, do all records for a person have the same value for [Student Positions].[Acct Cd]? If my assumption is correct and the [Student Positions].[Acct Cd] have unique values, which one do you want to display in the output of the query?

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: Monday, December 11, 2017 9:50 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Remove Duplicate record in query

 

Hello all,

I have a query for timesheets that uses two tables which are Student Positions and the R&D-CURRENTEMPLOYEES.

One table has an employee with two records. The two records are because it the employee is on a split account to be paid from. most of the employees are on split accounts. But this employee has two records in the results of the query. The records are correct but the user does not need two timesheets for the same employee. How would I not show one of the records?

Thank You

Jim Wagner

Here is the SQL

SELECT DISTINCT [R&D-CURRENTEMPLOYEES].[Person Id], [R&D-CURRENTEMPLOYEES].[Dept Ld], [Student Positions].[Acct Cd], [R&D-CURRENTEMPLOYEES].[Person Nm], [R&D-CURRENTEMPLOYEES].[Dept Id], [R&D-CURRENTEMPLOYEES].[Jobcode Ld], [R&D-CURRENTEMPLOYEES].[Empl Stat Cd], [R&D-CURRENTEMPLOYEES].[Empl Type Cd], [R&D-CURRENTEMPLOYEES].[Empl Type Ld], [R&D-CURRENTEMPLOYEES].[Paygrp Ld], 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, [R&D-CURRENTEMPLOYEES].[Pos Cd]
FROM [Student Positions] INNER JOIN [R&D-CURRENTEMPLOYEES] ON [Student Positions].[Position Nbr] = [R&D-CURRENTEMPLOYEES].[Pos Cd]
WHERE ((([R&D-CURRENTEMPLOYEES].[Paygrp Ld]) Like "Stu*"))
ORDER BY [R&D-CURRENTEMPLOYEES].[Pos Cd];

__._,_.___

---

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

.

__,_._,___