I am using the following query to show the Max values in a query, which works well. What I really need to do though, is to show all of the records, but to create and set a field to 1 if it is the NPIEC is Max. I have searched the postings with no luck. I don't want to update a table, I'd just like to update the query. Any suggestions would be much appreciated. Thanks.
SELECT SASNoDemout.MODEL, SASNoDemout.TOPLEVEL, SASNoDemout.PARTA, SASNoDemout.PARTB, SASNoDemout.PT, Max(SASNoDemout.NPIEC) AS MaxOfNPIEC
FROM SASNoDemout
WHERE (((SASNoDemout.DemoutZero)<>"1"))
GROUP BY SASNoDemout.MODEL, SASNoDemout.TOPLEVEL, SASNoDemout.PARTA, SASNoDemout.PARTB, SASNoDemout.PT
ORDER BY SASNoDemout.MODEL, SASNoDemout.TOPLEVEL, SASNoDemout.PARTA, SASNoDemout.PARTB;
SELECT SASNoDemout.MODEL, SASNoDemout.TOPLEVEL, SASNoDemout.PARTA, SASNoDemout.PARTB, SASNoDemout.PT, Max(SASNoDemout.NPIEC) AS MaxOfNPIEC
FROM SASNoDemout
WHERE (((SASNoDemout.DemoutZero)<>"1"))
GROUP BY SASNoDemout.MODEL, SASNoDemout.TOPLEVEL, SASNoDemout.PARTA, SASNoDemout.PARTB, SASNoDemout.PT
ORDER BY SASNoDemout.MODEL, SASNoDemout.TOPLEVEL, SASNoDemout.PARTA, SASNoDemout.PARTB;