Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #11168

Re: Problem with query

Show all headers | View raw

On 07/10/2015 23:46:45, Mike P wrote:
> On Saturday, October 3, 2015 at 12:21:40 PM UTC+1, Phil wrote:
>> I have a table Valuations
>> ValuationID
>> InvestmentID   'Key to Investment
>> ValDate             ' Valuation Date
>> SharePrice
>>
>> I want to create a query that shows the latest date and price and the
>> previous date and price so that in principal I can see the daily change.
>> However it could be several weeks between the 2 dates.
>>
>> I have created 2 queries that give the correct results, but they take ages to
>> run.
>>
>> SELECT Investments.AccID, Valuations.InvestmentID, Investments.SecurityName,
>> DateDiff("d",[ValDate],Date()) AS Expr2, Valuations.ValDate,
>> Valuations.SharePrice FROM Investments INNER JOIN Valuations ON
>> Investments.InvestmentID = Valuations.InvestmentID WHERE
>> (((Investments.InvestmentClosed)=False)) GROUP BY Investments.AccID,
>> Valuations.InvestmentID, Investments.SecurityName,
>> DateDiff("d",[ValDate],Date()), Valuations.ValDate, Valuations.SharePrice
>> HAVING (((DateDiff("d",[ValDate],Date()))>0)) ORDER BY Investments.AccID,
>> Investments.SecurityName, DateDiff("d",[ValDate],Date());
>>
>>
>> SELECT Query3.SecurityName, Query3.ValDate, Query3.SharePrice, Query3.Expr2
>> FROM Query3
>> WHERE (((Query3.Expr2)=DMin("Expr2","Query3","InvestmentID = " &
>> [Valuations]![InvestmentID])));
>>
>> Any ideas, or is it likely to quicker to run in VBA
>>
>> Phil
> 
> Hi Phil,
> 
> Will this query do it?
> 
> SELECT V1.InvestmentID, V1.ValDate, V1.SharePrice
> FROM Valuations AS V1
> WHERE V1.ValuationID In (select top 2 ValuationID from Valuations as V2
> where V2.InvestmentID = V1.InvestmentID order by ValDate DESC) ORDER BY
> V1.InvestmentID, V1.ValDate DESC;
> 
> Mike
> 
> 7/10/15
> 

Thanks Mike, that sort of helps. It runs very quickly

Have modified it slightly thus;-

SELECT Investments.SecurityName, Valuations.InvestmentID, Valuations.ValDate,
Valuations.SharePrice, Investments.InvestmentClosed
FROM Investments INNER JOIN Valuations ON Investments.InvestmentID =
Valuations.InvestmentID WHERE (((Valuations.ValuationID) In (Select top 2
ValuationID from Valuations as V2 where V2.InvestmentID =
Valuations.InvestmentID order by ValDate DESC)) AND
((Investments.InvestmentClosed)=False)) ORDER BY Investments.AccID,
Investments.SecurityName, Valuations.ValDate DESC;

This generally gives the correct answers, but I need to suppress those
SharePrices where there is no change, I need also to surpress thr records
where there is only one date or SharePrice. (Obviously no change) Also I need
the 2 prices in separate columns so that I can see something like

Name     Last Date      Old Price     New Price
Shell       1 Oct 2015     £1.00           £1.02

I am assuming that the corresponding New Date is today.
There are other bits that come into the calculation such as the number of
shares, but that is a trvial calculation.

Many thanks for your help

Phil

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Problem with query "Phil" <phil@stantonfamily.co.uk> - 2015-10-03 12:21 +0100
  Re: Problem with query Luuk <luuk@invalid.lan> - 2015-10-03 14:25 +0200
    Re: Problem with query "Phil" <phil@stantonfamily.co.uk> - 2015-10-03 15:46 +0100
      Re: Problem with query Luuk <luuk@invalid.lan> - 2015-10-03 17:39 +0200
        Re: Problem with query "Phil" <phil@stantonfamily.co.uk> - 2015-10-04 08:45 +0100
          Re: Problem with query Luuk <luuk@invalid.lan> - 2015-10-04 15:38 +0200
            Re: Problem with query "Phil" <phil@stantonfamily.co.uk> - 2015-10-04 22:10 +0100
  Re: Problem with query Mike P <mikewpayne@tiscali.co.uk> - 2015-10-07 15:46 -0700
    Re: Problem with query "Phil" <phil@stantonfamily.co.uk> - 2015-10-08 09:39 +0100
  Re: Problem with query Mike P <mikewpayne@tiscali.co.uk> - 2015-10-16 14:50 -0700
    Closed: Problem with query "Phil" <phil@stantonfamily.co.uk> - 2015-10-17 18:24 +0100

csiph-web