access sql ,retrieiving maximum date with relative  price

Posted on 2011-04-29
Medium Priority
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
  • 2
LVL 41

Accepted Solution

Sharath earned 2000 total points
ID: 35493307
try this.
       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


Expert Comment

ID: 35494150
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

ID: 35496627
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 41

Expert Comment

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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

850 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