Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Eliminating Duplicates in SQL Query

Status
Not open for further replies.

TheOneRing

Programmer
Feb 27, 2002
8
AU
I have two tables.

tblServiceTask and tblServiceTaskTech

tblServiceTask holds the billed labour etc for each task and tblServiceTaskTech holds all the times for each technician that worked on a particular task. Now if I do a query joining these tables together, then I can get duplicate values and therefore duplicate dollar amounts. Is there any easy way to eliminate the duplicates without having to write and VBCODE to search for them.

This is the code that I currently use and then I use vbcode to search through and list the duplicates. Is there an easier way:

SELECT tblServiceTask.strServiceTaskNumber, tblServiceTask.dblBilledLabour, tblServiceTask.dblBilledTravel, tblServiceTask.dblBilledParts, tblServiceTask.dblPartsCost, tblServiceTaskTech.dblBilledLabourHours, tblServiceTaskTech.dblBilledTravelHours, tblServiceTask.dtmServiceTaskEndDate
FROM tblServiceTask INNER JOIN tblServiceTaskTech ON tblServiceTask.lngServiceTaskID = tblServiceTaskTech.lngServiceTaskID
WHERE (((tblServiceTask.dtmServiceTaskEndDate) Between CDate('01-02-02') And CDate('28-02-02')));

thanks,
Darren
 
It appears that all you need to do is the following:

At the end of your select statement, put in a COUNT(*).

Then, after the WHERE, put in

GROUP BY tblServiceTask.strServiceTaskNumber, tblServiceTask.dblBilledLabour, tblServiceTask.dblBilledTravel, tblServiceTask.dblBilledParts, tblServiceTask.dblPartsCost, tblServiceTaskTech.dblBilledLabourHours, tblServiceTaskTech.dblBilledTravelHours, tblServiceTask.dtmServiceTaskEndDate
FROM tblServiceTask INNER JOIN tblServiceTaskTech ON tblServiceTask.lngServiceTaskID = tblServiceTaskTech.lngServiceTaskID
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top