select records with latest date

[Guest]

I have a table that records all purchases.

partnumber, date, price

I need to select the latest price for each partnumber

I use the criteria form and properties / not sql

ive tried last, max, top, inr the criteria and none works as expected...

thanks

 

[Tom Ellison]

Dear Steve:

You will need a correlated subquery (or a domain function) to do this.
I prefer to teach the correlated subquery approach because it is
portable to other SQL engines.

SELECT partnumber, [date], price
FROM ATable T
WHERE [date] =
(SELECT MAX([date]) FROM ATable T1
WHERE T1.partnumber = T.partnumber)

The last 2 lines in this query are the correlated subquery. It is
based on a simple query that would look like this:

SELECT partnumber, MAX([date])
FROM ATable
GROUP BY partnumber

In fact you could make an INNER JOIN to the above and achieve the same
results:

SELECT T1.partnumber, T1.[date], T1.price
FROM ATable T1
INNER JOIN (SELECT partnumber, MAX([date]) FROM ATable
GROUP BY partnumber) T2 ON T2.partnumber = T1.partnumber

In any case, if there exists more than one row in ATable (or whatever
you have named this table) for the same "latest" date (or date/time),
then you can certainly get more than one price for a given partnumber.
This is due to the fact that the answer is ambiguous in such a case.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Guest]

can this be done in the criteria pane...not in sql?

 

[Tom Ellison]

Dear Steve:

A subquery can be entered in the Design Grid (which includes the
criteria display) but when entered there the SQL for it must be
entered. The Design Grid does not support sub-Design Grids for
subqueries.

In any case, we almost always deal in SQL here because text can be
posted in a newsgroup.

You can put in a query like I showed you into the SQL view and then
switch to the grid to see how it looks. That would tell you just how
to enter it there. For this reason, just transmitting you the SQL to
be used is very effective.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts