CODEWITHSUNDEEP
ms-accessms-access-2010
rohrl77
rohrl77

Reputation: 3337

Find last price in query and display in report

I have been trying to crack this problem for a while now, but all the example I have found (and there are many) don't seem to work for me.

Problem description: I have a report displaying Stock Information that is based on a query in Access. The report is opened via a command button that populates an SQL String that is passed to the DoCmd.OpenReport.

In the report I need to display the last available price that the stock had.

Attempted Solution: I have attempted to use DMAX() to find the last available date in the query. The code in the query builder looks like this: LastDate: DMax("Date";"tblFunds_TS_Data")

The SQL Code for this query is as follows:

SELECT tblFunds_TS_Data.ISIN, DMax("Date","tblFunds_TS_Data") AS LastDate
FROM tblFunds_TS_Data
GROUP BY tblFunds_TS_Data.ISIN, DMax("Date","tblFunds_TS_Data");

This gives me the last date for each stock, but not the actual stock price.

Question: How can I get the last stock price to be displayed for each Stock?

Upvotes: 0

Views: 506

Answers (3)

HansUp
HansUp

Reputation: 97131

Confirm this query gives you the latest date for each ISIN:

SELECT t1.ISIN, Max(t1.Date) AS LastDate
FROM tblFunds_TS_Data AS t1
GROUP BY t1.ISIN;

Assuming it does give you the correct results, use it as a subquery which you join back to tblFunds_TS_Data so that you can retrieve the matching Price for each of those ISIN/LastDate combinations:

SELECT sub.ISIN, sub.LastDate, t2.Price
FROM
    (
        SELECT t1.ISIN, Max(t1.Date) AS LastDate
        FROM tblFunds_TS_Data AS t1
        GROUP BY t1.ISIN
    ) AS sub
    INNER JOIN tblFunds_TS_Data AS t2
    ON sub.ISIN = t2.ISIN AND sub.LastDate = t2.Date;

Upvotes: 1

rohrl77
rohrl77

Reputation: 3337

In order to display the last price on the last date, I ended up putting the DMAX function in the criteria section in design view. With the last date thus being the criteria, the price of each stock will always be displayed.

Here is my SQL Code:

SELECT tblFunds_TS_Data.ISIN, tblFunds_TS_Data.Date, tblFunds_TS_Data.Price
FROM tblFunds_TS_Data
GROUP BY tblFunds_TS_Data.ISIN, tblFunds_TS_Data.Date, tblFunds_TS_Data.Price
HAVING (((tblFunds_TS_Data.Date)=DMax("Date","tblFunds_TS_Data")));

Upvotes: 1

Gustav
Gustav

Reputation: 55921

Use DLookup:

LastPrice: DLookUp("[FieldWithPrice]";"[tblFunds_TS_Data]";"[Date]=DMax('[Date]','[tblFunds_TS_Data]')")

Note please the mix of localized and non-localized delimeters because DMax now is inside the DLookUp criteria.

Upvotes: 1

Related Questions