Lowest Cost at the latest Change Date

[Luis]

I need some help again, please...I have the following
table:
Code Vendor Cost Change Date
h305 Eckerd $213 12/01/04
h305 Walgreens $340 12/01/04
h305 AVC $380 12/01/04
h305 AVC $260 12/05/04
h305 Eckerd $230 12/13/04
h305 Walgreens $245 12/16/04

How can I pull the lowest price at the latest change date
for each vendor...and also how can I get lowest price for
at latest change date among all three vendors...

I have used a simple query for my first question by
setting change date total to max, but it does not work.
It gives me 2 rows for Eckerd, Walgreens, AVC...

For my second question, I tried using a crosstab query,
but it does not work...Can someone please hel me?

Thank you in advance,

Luis

 

[Allen Browne]

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

The article discusses 4 ways to get this.

You may need to apply a combination of ideas or stack some queries. For
example, this will query gives the ID (primary key) of the record that has
the most recent price for each vendor. By saving that query, and using it as
a "table" in another query, joined to the original table, you could get the
best price from all vendors:

SELECT Table2.Code, Table2.Vendor,
(SELECT TOP 1 Dupe.ID FROM Table2 AS Dupe
WHERE (Dupe.Code = Table2.Code)
AND (Dupe.Vendor = Table2.Vendor)
ORDER BY Dupe.ChangeDate DESC, Dupe.ID DESC ) AS LastPrice
FROM Table2
GROUP BY Table2.Code, Table2.Vendor;

BTW, there can be some practical frustrations working with subqueries in JET
(Access). You may experience crashes (shut down by Windows), or if you build
a report on this it may barf with "multi-level group by not allowed. Try the
other approaches in the article above if you experience these issues, or try
saving intermediate queries.