Return all BUT maximum values

A

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;
 
O

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] & "'")
 
M

Marshall Barton

Alex said:
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)
 
A

Alex

Perfect - thanks so much

Marshall Barton said:
Alex said:
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top