Return all BUT maximum values

[Alex]

I have the following "find duplicates" query that returns the records with
the maximum value in the NPIEC field. What I need instead is for the query
to return the records with all BUT the maximum value. Min won't work because
I could have 3 records containing duplicates. If I have NPIEC values of 2, 4
& 5 in 3 duplicate records, I need the query to return the records with the
NPIEC values of 2 & 4. Can this be done? Thanks.

SELECT SASNoDemout.MODEL, SASNoDemout.PT, SASNoDemout.VERS,
Min(SASNoDemout.NPIEC) AS MinOfNPIEC, 1 AS NPIECHigh
FROM SASNoDemout
GROUP BY SASNoDemout.MODEL, SASNoDemout.PT, SASNoDemout.VERS, 1
HAVING (((SASNoDemout.MODEL) In (SELECT [MODEL] FROM [SASNoDemout] As Tmp
GROUP BY [MODEL],[PT],[VERS] HAVING Count(*)>1 And [PT] = [SASNoDemout].[PT]
And [VERS] = [SASNoDemout].[VERS])))
ORDER BY SASNoDemout.MODEL, SASNoDemout.PT, SASNoDemout.VERS;

 

[Ofer]

Try this
SELECT SASNoDemout.MODEL, SASNoDemout.PT, SASNoDemout.VERS,
SASNoDemout.NPIEC, 1 AS NPIECHigh
FROM SASNoDemout
Where NPIEC<> DMax("NPIEC","SASNoDemout","MODEL = " & [MODEL])

Assuming that you looking for the NPIEC for each Model,

if MODEL is string type then
SELECT SASNoDemout.MODEL, SASNoDemout.PT, SASNoDemout.VERS,
SASNoDemout.NPIEC, 1 AS NPIECHigh
FROM SASNoDemout
Where NPIEC<> DMax("NPIEC","SASNoDemout","MODEL = '" & [MODEL] & "'")

 

[Marshall Barton]

I have the following "find duplicates" query that returns the records with
the maximum value in the NPIEC field. What I need instead is for the query
to return the records with all BUT the maximum value. Min won't work because
I could have 3 records containing duplicates. If I have NPIEC values of 2, 4
& 5 in 3 duplicate records, I need the query to return the records with the
NPIEC values of 2 & 4. Can this be done? Thanks.

SELECT SASNoDemout.MODEL, SASNoDemout.PT, SASNoDemout.VERS,
Min(SASNoDemout.NPIEC) AS MinOfNPIEC, 1 AS NPIECHigh
FROM SASNoDemout
GROUP BY SASNoDemout.MODEL, SASNoDemout.PT, SASNoDemout.VERS, 1
HAVING (((SASNoDemout.MODEL) In (SELECT [MODEL] FROM [SASNoDemout] As Tmp
GROUP BY [MODEL],[PT],[VERS] HAVING Count(*)>1 And [PT] = [SASNoDemout].[PT]
And [VERS] = [SASNoDemout].[VERS])))
ORDER BY SASNoDemout.MODEL, SASNoDemout.PT, SASNoDemout.VERS;

Try something more like:

SELECT T.MODEL, T.PT, T.VERS
FROM SASNoDemout As T
WHERE T.NPIEC < (SELECT Max(X.NPIEC)
FROM SASNoDemout As X
WHERE T.MODEL = X.MODEL
AND T.PT = X.PT
AND T.VERS = X.VERS)

 

[Alex]

Perfect - thanks so much

I have the following "find duplicates" query that returns the records with
the maximum value in the NPIEC field. What I need instead is for the query
to return the records with all BUT the maximum value. Min won't work because
I could have 3 records containing duplicates. If I have NPIEC values of 2, 4
& 5 in 3 duplicate records, I need the query to return the records with the
NPIEC values of 2 & 4. Can this be done? Thanks.

SELECT SASNoDemout.MODEL, SASNoDemout.PT, SASNoDemout.VERS,
Min(SASNoDemout.NPIEC) AS MinOfNPIEC, 1 AS NPIECHigh
FROM SASNoDemout
GROUP BY SASNoDemout.MODEL, SASNoDemout.PT, SASNoDemout.VERS, 1
HAVING (((SASNoDemout.MODEL) In (SELECT [MODEL] FROM [SASNoDemout] As Tmp
GROUP BY [MODEL],[PT],[VERS] HAVING Count(*)>1 And [PT] = [SASNoDemout].[PT]
And [VERS] = [SASNoDemout].[VERS])))
ORDER BY SASNoDemout.MODEL, SASNoDemout.PT, SASNoDemout.VERS;

Try something more like:

SELECT T.MODEL, T.PT, T.VERS
FROM SASNoDemout As T
WHERE T.NPIEC < (SELECT Max(X.NPIEC)
FROM SASNoDemout As X
WHERE T.MODEL = X.MODEL
AND T.PT = X.PT
AND T.VERS = X.VERS)