Extracting the most recent quote date for a part from a list of multiple parts using field formula

Hi,

I have a list of part ID (Part_ID) and for each part there are a number of quotes (Quote_Price) and a dated for each quote (Quote_Date, which is a date time value), for example:
Part_ID
      Quote_Price      Quote_Date
P1      150      28/01/2004 00:00:00
P1      160      01/08/2008 00:00:00
P1      240      28/01/2011 00:00:00
P2      20      01/07/2009 00:00:00
P2      15      24/03/2011 00:00:00

What I want to is build is a formula field that extracts the most recent quote date and the corresponding quote price for each part ID.  I have tried to create an array for all the quote dates listed for a part then tried to define the most recent date using the maximum function but this (or my coding!) hasn’t worked.  Any advice would be appreciated.
steve_yorkAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Base your report on a query like this:



SELECT t1.Part_ID, t1.Quote_Price, t1.Quote_Date
FROM SomeTable t1 INNER JOIN
    (SELECT t2.Part_ID, MAX(t2.Quote_Date) AS Quote_Date
    FROM SomeTable t2
    GROUP BY t2.Part_ID) z ON t1.Part_ID = z.Part_ID AND t1.Quote_Date = z.Quote_Date
ORDER BY t1.Part_ID

Open in new window



Be advised that that will return multiple records for a given part if that part has >1 quote for whatever the latest date value happens to be.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
Another way is to group the report by partId and sort the groups by date.
The record you want will be in the group header if sorted descending or the footer is sorted ascending.

mlmcc
0
steve_yorkAuthor Commented:
Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.