SQL statement problem. Please Help!

[INeedAccessHelp]

Here is the statement:

SELECT DISTINCT tblProgRequest.ProgReqID, tblProgramName.ProgType, BCTbl.BCAssignDate, tblDeveloper.DeveloperName
FROM ((tblProgRequest INNER JOIN tblProgramName ON tblProgRequest.ProgramName = tblProgramName.ProgramID) LEFT JOIN BCTbl ON tblProgRequest.BusSpecID = BCTbl.BCID) LEFT JOIN tblDeveloper ON tblProgRequest.ProgReqID = tblDeveloper.ProgReqID
ORDER BY tblProgRequest.ProgReqID DESC;

I want to this query to return unique records from "tblProgRequest". The problem comes in because each ProgReqID can have multiple DeveloperNames. So if a ProgReqID includes multiple DeveloperNames, the above query returns duplicate ProgReqID's if it includes multiple DeveloperNames. Even if a particular ProgReqID contains multiple DeveloperNames, I want the query to only return unique ProgReqID's. I hope this makes sense. This has been driving me nuts for 2 days now. Any help would be greatly appreciated. Thanks in advance.

--Rob

 

[tlbroadbent]

You need to select one of zero deveoper names. Either Remove developer from the query or use an aggregate (or totals) query and one of the following functions to select a single developer.

First, Last, Min, Max

Example:

SELECT
tblProgRequest.ProgReqID,
tblProgramName.ProgType,
BCTbl.BCAssignDate,
First(tblDeveloper.DeveloperName) As DevName
FROM ((tblProgRequest
INNER JOIN tblProgramName
ON tblProgRequest.ProgramName = tblProgramName.ProgramID)
LEFT JOIN BCTbl
ON tblProgRequest.BusSpecID = BCTbl.BCID)
LEFT JOIN tblDeveloper
ON tblProgRequest.ProgReqID = tblDeveloper.ProgReqID
GROUP BY
tblProgRequest.ProgReqID,
tblProgramName.ProgType,
BCTbl.BCAssignDate
ORDER BY tblProgRequest.ProgReqID DESC; Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.