I've been searching the forum for an answer and see that there are a lot of knowledgable people out there but didn't find an answer to my problem so I'm hoping someone can help.

I have a table that has PartNo, Unit_Price, Updated_Date. It is filled with every purchase we have made for the past two years, so there are multiple records for the same part numbers with different prices and different dates. I'm looking to pull the most recent dated record for each part number. For example:
PartNo Unit_Price Updated_Date
123 7.89 08/12/07
123 7.91 10/15/07
123 8.02 1/10/08

I would like it to return the bottom record only (and do this for each of the hundereds of part numbers we have) so I end up with a table that looks like...
123 8.02 1/10/08
456 1.52 1/22/08
789 10.59 8/02/06
and so on... :D

Any ideas:confused:

Try something like

SELECT YourTableName.PartNo, YourTableName.UnitPrice, YourTableName.Updated_Date
FROM YourTableName
WHERE (((YourTableName.Updated_Date)=DMax("Updated_Date","YourTableName","[PartNo]=" & [PartNo])));

Thanks for the quick reply CraigDolphin!

I'm unfamilar with the DMAX function. I get the following error:
Syntax error (missing operator) in query expression '[PartNo]=042-05M'.

Any thoughts?


Try something like

SELECT YourTableName.PartNo, YourTableName.UnitPrice, YourTableName.Updated_Date
FROM YourTableName
WHERE (((YourTableName.Updated_Date)=DMax("Updated_Date","YourTableName","[PartNo]=" & [PartNo])));

Yes. I think you should post sample data that reflects the data you're actually using ;)

Your sample showed a numeric PartNo (e.g., 123) whereas this tells me that your partnumbers are actually a string of letters and numbers (e.g., 042-05M).

You are getting the error because the Dmax function was looking for a numeric datatype in the where clause.

Change it to this:
SELECT YourTableName.PartNo, YourTableName.UnitPrice, YourTableName.Updated_Date
FROM YourTableName
WHERE (((YourTableName.Updated_Date)=DMax("Updated_Date","YourTableName","[PartNo]='" & [PartNo] & "'")));

Note the addition of single quotes around the part number. This lets the db know to expect a text string instead of a number. If the value were a date, you'd use # either side of the field name instead of single quotes.

Fair enough, next time I'll give a more accurate picture of the data involved :)

That did the trick. It is returning the same data for all the parts that had multiple records, but I'm not dependant on the exact date I just needed to get the last ordered unit price.

Thanks a ton for you help!!!! :D

Oops, I ment that it is returning the same date (not data).

The way I would do it involves 2 queries. The first:

SELECT PartNo, Max(Updated_Date) AS MaxDate
FROM YourTableName
GROUP BY PartNo

Then a second query joining your table against the first query on the 2 fields in the SELECT clause will enable you to return the price from the max date by part.

Thanks Paul. That was the direction I was headed but wasn't sure how to get there. I'm going to use your suggestion to solve another issue I have in a different query.

This forum rocks!!!

:cool: