Summary Function Max or Last

[starflt1701]

Hi, I've got a simple one, I think, but I can't figure out the solution. I have a table of data of purchase orders against which I have multiple receipt days. I'm trying to build a query that uses either the Max or Last total function on the date filed to determine the last receipt date.

This works fine on those POs with multiple receipt dates, but fails to include any POs with only a single receipt date. How do I get the query to include all?

Thanks.

 

[TheAceMan1]

How are ya starflt1701 . . .

[blue]Max[/blue] is value based.
[blue]Last[/blue] is position based.

Sounds like [blue]Max[/blue] to me!

BTW: Welcome to [blue]Tek-Tips![/blue] Do have a look at one of the links at the bottom of my post. The links will help you [blue]ask better questions[/blue], get [blue]quick responses[/blue], [blue]better answers[/blue], and insite into [blue]etiquette[/blue] here in the forums. Again . . . Welcome to [blue]Tek-Tips![/blue] [blue]Its Worthy Reading![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PHV]

This works fine ..., but fails ...
Which SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

starflt1701 . . .

Hope this is a good guess ([blue]you![/blue] substitute proper names in [purple]purple[/purple]) :

[blue]SELECT A.[[purple][B][I]POFieldName[/I][/B][/purple]], Max(A.[[purple][B][I]SvcDate[/I][/B][/purple]]) AS MaxDates
FROM [[purple][B][I]YourTableName[/I][/B][/purple]] AS A
GROUP BY A.[purple][B][I][POFieldName][/I][/B][/purple];[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]