Query Help Returning Most Recent Date's Value

[cperry88]

I have set up a database to compare competitor prices to my product prices. I have a competitor price table and my price table. All parts have been cross referenced in a separate table and are related to one another.



Prices fluctuate throughout the year. When I have access to competitor pricing data, I store it in my database and insert a date into my date field for when the pricing is effective.

Ex. Competitor Product A is stored in my DB with 3 different price points and relevant effective dates, as is Competitor Product B. These product price dates are not necessarily the same.

I would like to run a query that will show my competitor's most recent pricing as it relates to my cross referenced part #'s.

Ex. Competitor Product A's most recent price point is from 11/5/2013. Competitor Product B's most recent is 1/4/2014. I need the following produced in a query:

Competitor Part # My Part # Competitor Price My Price Competitor Effective Date
Product A x - - 11/5/2013
Product B y - - 1/4/2014

I have attempted to set this up myself. Keeping in mind that I have 3 different price points for both competitor products, this was the result of my query:

Competitor Part # My Part # Competitor Price My Price Competitor Effective Date
Product A x - - 11/5/2013
Product A x - - 9/7/2013
Product A x - - 1/8/2013
Product B y - - 1/4/2014
Product B y - - 11/4/2013
Product B y - - 5/22/2013

I don't need to see this, I just need to see the most recent data for each competitor part.

Thanks in advance.

[June7]

Try a nested subquery that uses TOP qualifier. Review: http://allenbrowne.com/subquery-01.html#TopN