access sql ,retrieiving maximum date with relative  price

Posted on 2011-04-29
Last Modified: 2012-06-27
 I am trying to retreive ,for the latest purchase of an item "xx" from every vendor ,the maximum date and the related price for that record .Please see the SQL:

" SELECT Max(vendoritemstable2.TRNSDATE) AS MaxOfTRNSDATE, Last(vendoritemstable2.PRICE) AS LastOfPRICE, vendoritemstable2.CARD
FROM vendoritemstable2
WHERE (((vendoritemstable2.ITEMCODE)='xx'))  GROUP BY vendoritemstable2.CARD"

however the price I am getting for the maximum date is unrelated to that date ,it belongs to another records .I tried to use a select inside the select from ... but I didn't know how to do it .

I need to retrieve the fields for that record with maximum date in 1 select statement or in 1 process

Question by:fernando50
    LVL 40

    Accepted Solution

    try this.
    SELECT t1.CARD, 
           t1.TRNSDATE AS MaxOfTRNSDATE, 
           t1.PRICE    AS LastOfPRICE 
      FROM vendoritemstable2 AS t1 
     WHERE t1.ITEMCODE = 'xx' 
                             FROM vendoritemstable2 AS t2 
                            WHERE t1.CARD = t2.CARD 
                                  AND t2.ITEMCODE = 'xx')

    Open in new window

    LVL 1

    Expert Comment

    Sharath_123's SQL above looks to be heading the same way as my attached Access example.  In its raw state, my database will list all items but just the most recent entry per each vendor. If you stick a particular item code ('xx') in the "Item" criteria of the query.  If "xx" can be (has been) supplied by more than one vendor you'll get more than one result line.  I'm not sure if that's what you intended.

    Hope it helps though.

    Author Comment

    Sharath_123:Thank you This will work fine for a specific item "xx" (I used xx as a test so not to get a lot of records )

    But what if i need to  retreive ,for the latest purchase of every item (group by item) from every vendor ,the maximum date and the related price for that record .

     Could you modify the select statement so it will give for every product (group by item) the same result ?
    LVL 40

    Expert Comment

    Are you able to modify the sql or still looking for solution?

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Creating and Managing Databases with phpMyAdmin in cPanel.
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now