SQL SELECT Query in MS Access based on most recent date

Question

I am trying to find Bid prices in the PriceFeed table that are higher/equal to input price on the form in MS Access at the most recent date/time. I want to select the stock details from the Stock table with the same symbol as that from the PriceFeed table.

The user input textbox is txtMini, and the form name is findPrice.

The problem is that this SQL statement only returns one result - the most recent stock. I want to find the most recent price and associated details for all stock symbols that are in the PriceFeed table, not just one stock.

SELECT
    PriceFeed.StockSymbol
  , PriceFeed.DateTime
  , PriceFeed.Bid
  , Stock.StockDescription
  , StockType.TypeDescription
  , Category.CategoryDescription
  , CurrencyID.Currency
  , Stock.AvailableUnits
FROM
    PriceFeed
  , Stock
  , StockType
  , Category
  , CurrencyID
WHERE
    PriceFeed.Bid         >=[Forms]![findPrice]![txtMini]
    AND PriceFeed.DateTime =
    (SELECT MAX(PriceFeed.DateTime) FROM PriceFeed
    )
    AND PriceFeed.StockSymbol    = Stock.Symbol
    AND Stock.TypeID             = StockType.TypeID
    AND Stock.BusinessCategoryID = Category.CategoryID
    AND Stock.CurrencyID         = CurrencyID.CurrencyID
;

How to format SQL Code. Split your problem into smaller ones. First get the most recent price for all stocks. (hint: group by). — ComputerVersteher

Wolfgang Kais Wolfgang Kais · Accepted Answer · 2018-09-09T12:33:27

I guess that each stock has its own "most recent date/time", so you might try to change the WHERE condition for the DateTime into this:

PriceFeed.DateTime = (
    SELECT MAX(DateTime) FROM PriceFeed AS tmp WHERE StockSymbol = PriceFeed.StockSymbol
)

SQL SELECT Query in MS Access based on most recent date

1 Answers