Max values

[Guest]

Hi,

I am trying to create a query that only shows the record with the highest
value of a revision letter for each quote reference. I have selected max for
the value in the aggregate box but this doesn't do any thing. Below is the
code:


SELECT TAB_Quote.Quote_Ref, Max(TAB_Quote.Revision) AS MaxOfRevision,
TAB_Revision.Revision, TAB_Quote.Quote_ID
FROM TAB_Revision INNER JOIN TAB_Quote ON TAB_Revision.Revision_ID =
TAB_Quote.Revision
GROUP BY TAB_Quote.Quote_Ref, TAB_Revision.Revision, TAB_Quote.Quote_ID
ORDER BY TAB_Quote.Quote_Ref, TAB_Revision.Revision;

Thanks!

 

[Michel Walsh]

Hi,

If Quote is the "category" and we wish to keep the associated values, such
as QuoteID, QuoteComment, for the last (maximum) value of Revision:


SELECT a.Quote, a.Revision, LAST(a.QuoteID), LAST(a.QuoteComment)
FROM myTable As a INNER JOIN myTable As b
ON a.Quote=b.Quote
GROUP BY a.Quote, a.Revision
HAVING a.Revision = MAX(b.Revision)



Hoping it may help,
Vanderghast, Access MVP