MS Query - Use of Max?
 

Hi,

I have the following query which worked fine until we introduced
versioning on records. Now if there is a second version the record
will appear twice.

What I'd like to be able to do is include the intVersionID field and
if a record has, e.g. 2 versions, only bring out the largest number of
the record. In access formally I believe I could do this using Max,
however not sure how to use this when it's going via MS Query. The
data is then returned to excel, hence the posting in the Excel group -
however will repost if there's a more appropriate group to post in?

Thanks, Al.

SELECT vwDataProblems.intProblemLogNo AS 'PL #',
vwDataProblems.intChangeLogNo AS 'CCL #', vwDataProblems.strStatus AS
'Status', vwDataProblems.strWorkPackage AS 'W/Pack',
vwDataProblems.dtmDateRequired AS 'Date Rqd',
vwDataProblems.strPriority AS 'Priority',
vwDataProblems.strProblemOwnerName AS 'Owner',
vwDataProblems.strChangeType AS 'Change Type',
vwDataProblems.strProblemDetails AS 'Details',
vwDataProblems.strBusinessBenefits AS 'Bus Benefits',
vwDataProblems.strISReview AS 'IS Review',
vwDataProblems.dblTotalResourceDaysRequired AS 'Resource Total',
vwDataProblems.strProblemSummary AS 'Summary',
vwDataProblems.strPanelComments AS 'Panel Comments',
vwDataProblems.intID, vwDataProblems.dtmCreationDate
FROM FUSION_REPORTING.dbo.vwDataProblems vwDataProblems
WHERE (vwDataProblems.strSystem='ezBuy') AND
(vwDataProblems.bitStatusClosed=0) AND
(vwDataProblems.strStatus<'Existing Change Request Raised') OR
(vwDataProblems.strSystem='ezBuy') AND
(vwDataProblems.bitStatusClosed=1) AND (vwDataProblems.strStatus Like
'%Deferred%')
ORDER BY vwDataProblems.intProblemLogNo