I work for an IT company and I pull reports for the service desk. As you can image we have very detailed (and many) DBS. I am trying to pull a query with were it doesn't pull duplicate records for tickets. the DB creates several entries into for one ticket every time someone touches/updates it.
I am not great with the SQL side, so I build everything in design view, but I can get around in it when I need to.
I have tried SELECT DISTINCT, First(), setting Unique Value Yes.....
Here is my SQL:
Originally Posted by My SQL
SELECT DISTINCT First(TicketAssignment_vw.TicketNumber) AS FirstOfTicketNumber, tblRoster.iTrackerTeam, tblRoster.Shift, tblRoster.infoPACCTicketAssignee, [JournalRecent150_vw]![JournalDate]-[TicketAssignment_vw]![AssignedDate] AS TTA
FROM JournalRecent150_vw INNER JOIN (tblRoster INNER JOIN (TicketActivity_vw INNER JOIN TicketAssignment_vw ON TicketActivity_vw.TicketNumber = TicketAssignment_vw.TicketNumber) ON tblRoster.infoPACCTicketAssignee = TicketAssignment_vw.AcknowledgedBy) ON JournalRecent150_vw.TicketNumber = TicketAssignment_vw.TicketNumber
WHERE (((TicketAssignment_vw.AcknowledgedDate) Between [StartDate] And [EndDate]) AND ((TicketActivity_vw.CallType) Like "Incident") AND ((tblRoster.Status)<>"Terminated"))
GROUP BY tblRoster.iTrackerTeam, tblRoster.Shift, tblRoster.infoPACCTicketAssignee, [JournalRecent150_vw]![JournalDate]-[TicketAssignment_vw]![AssignedDate]
ORDER BY tblRoster.iTrackerTeam DESC , tblRoster.Shift, tblRoster.infoPACCTicketAssignee;