Jim,
What happens if you take the Acct Cd out of the query? If you need a position holder, just substitute a space like:
If that doesn't work, change the query to a totals/group by.
Sent: Monday, December 11, 2017 10:20 AM
To: Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]
Subject: 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
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 (4) |
Tidak ada komentar:
Posting Komentar