Too hard for me...Help

[p_verrecas]

hi all,
I'm just beginning to know the world of Access so maybe the solution for my problem is pretty simple.
I have a huge database with Itemcodes, Costprice of materials and EffectiveDate (date on which new prices are valid). These dates represent past dates as well as future dates. The one that interests me the most is the most recent date in the past.
What I would need is a query that filters out the most recent date per itemcode because right now there are several prices with several validity dates. Eventually it should result in a database that shows an item with one validitydate and one price.
Right now the same itemcodes are repeated because different validitydates are related to it.
I really hope somebody can help me out on this.
Thanks in advance.

 

[Kempes]

Add both fields to a new query, right click and choose Totals.
Change the Date total to Max from the Drop down list. Leave the other as group by.

This should work If I understand your question correctly.

 

[neileg]

If you want to exclude dates in the future you need to use a criterion on your EffectiveDate field

 <= Date()