Max of Date with/without grouping?

[Guest]

Hello everyone,

I need to know how to get the Latest date, which I take it would be the max,
in a Purchase Order file, that includes the below. At the moment, when I run
it, it can give me more than 1 record for the same item, only because that
item can have different prices or vendors, again, I need those records, but I
only need the one that relates to the last purchased date for that item.

Item Vendor Dock Date Unit Price
B120 Dell 3/25/2006 326
B120 Gateway 3/26/2006 325
B121 EMachines 4/19/2006 397

 

[Allen Browne]

See:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

Take your pick of the 4 solutions.

 

[Guest]

SELECT Item, Max([Dock Date]) AS MaxDockDate
FROM [Purchase Order]
GROUP BY Item;

Above is one answer. I'm assuming that by Purchase Order file you really
mean table. Also if you need the Vendor and Unit Price, that will take a more
complicated subquery.

 

[Guest]

I actually went ahead and kept messing around and came out with the same
results. But that is a really helpfull site for others who may not want to
scratch their head for as long as I did. Thanks Allen.

 

[Michel Walsh]

Hi,


.... so you did find a solution there or you still search for one?

Maybe

SELECT a.*, (SELECT MAX(dateTime) FROM myTable as b WHERE a.item=b.item)
FROM myTable As a


is a solution in your case, as it keeps all the original record, and find
the max you seem to seek.



Hoping it may help,
Vanderghast, Access MVP