Use of Top Value Query

[seattlerust]

I have a problem that I thought could be solved with the Top Value query and probably a second query would also be necessary. Having doubts now. Here is the scenario:



I am updating a 15 year old stock management database using Access 2007. Each Friday I download closing prices for stocks in my portfolio from the web and import these data into a table called "CurrentPrice" . For various reasons, not all of the securities will be updated (some are bonds) and I will be left with some records with dates older than the most current Friday. I then insert this closing price data into a Report that shows the status of my portfolio with current prices where available, and older prices for other securities, mostly bonds, where current price is not as important, but still needed.

I am attempting to load the Report with queries. What I hope to be able to do is produce a recordset of all of the securities in table "CurrentPrice" with their latest price and the date of that price. Table "CurrentPrice" has three columns; "StockID", "DownloadDate", "ClosingPrice". All data is kept so each stock has many records of price and download date.

I have tried to query the table to gather each stock and its latest price using Top Value methods for the first query and then a second query to extract the most recent record, based on the last date. My Top Value query returns a recordset with only the securities that were updated at the last download and does not return securities with older download dates. That is the problem that I cannot solve.

Any suggestions will be appreciated. Thank you.

Seattlerust

[orange]

It sounds like you need to run a select query using the Max function.
and probably Group By.
see this for examples

http://www.techonthenet.com/sql/max.php

Good luck.