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

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

Closed: Problem with query

Show all headers | View raw

On 16/10/2015 22:50:09, Mike P wrote:
> 
> Hi Phil,
> 
> Continuation of previous post.
> 
> I have developed the query further.
> If you copy and paste each of the 4 SQL statements below into a query
> object and give each one the name as indicated then you may have what you
> want.
> 
> qryTop2Valuations - extracts the 2 most recent valuations for each
> investment SELECT Valuations.ValuationID, Valuations.InvestmentID,
> Valuations.ValDate, Valuations.SharePrice FROM Valuations
> WHERE (((Valuations.ValuationID) In (Select top 2
> valuationid from Valuations as V2 where V2.investmentid
> Valuations.investmentid order by ValDate DESC))) ORDER BY
> Valuations.InvestmentID, Valuations.ValDate DESC;
> 
> 
> qryExcludeSinglesAndDuplicates - ignores those investments for which there
> is only one valuation and those investments for which the 2 most recent
> valuations are the same SELECT qryTop2Valuations.InvestmentID
> FROM qryTop2Valuations
> GROUP BY qryTop2Valuations.InvestmentID
> HAVING (((First(qryTop2Valuations.SharePrice))<>Avg([shareprice])) AND
> ((Count(*))>1));
> 
> 
> qryTop2DatesFoeEachInvestment3 - combines the other queries and ignores
> investments that are closed SELECT Investments.SecurityName,
> Investments.InvestmentID, qryTop2Valuations.ValDate,
> qryTop2Valuations.SharePrice FROM (qryExcludeSinglesAndDuplicates INNER
> JOIN qryTop2Valuations ON qryExcludeSinglesAndDuplicates.InvestmentID =
> qryTop2Valuations.InvestmentID) INNER JOIN Investments ON
> qryExcludeSinglesAndDuplicates.InvestmentID = Investments.InvestmentID
> WHERE (((Investments.InvestmentClosed)=False)) ORDER BY Investments.AccID,
> Investments.SecurityName, Investments.InvestmentID,
> qryTop2Valuations.ValDate DESC;
> 
> 
> qryFinalResults - presents the results as specified with dates and
> valuations for each investment appearing in one row. SELECT DISTINCTROW
> qryTop2DatesForEachInvestment3.SecurityName,
> First(qryTop2DatesForEachInvestment3.ValDate) AS FirstOfValDate,
> First(qryTop2DatesForEachInvestment3.SharePrice) AS FirstOfSharePrice,
> Last(qryTop2DatesForEachInvestment3_1.ValDate) AS LastOfValDate,
> Last(qryTop2DatesForEachInvestment3_1.SharePrice) AS LastOfSharePrice FROM
> qryTop2DatesForEachInvestment3 INNER JOIN qryTop2DatesForEachInvestment3
> AS qryTop2DatesForEachInvestment3_1 ON
> (qryTop2DatesForEachInvestment3.ValDate =
> qryTop2DatesForEachInvestment3_1.ValDate) AND
> (qryTop2DatesForEachInvestment3.InvestmentID =
> qryTop2DatesForEachInvestment3_1.InvestmentID) GROUP BY
> qryTop2DatesForEachInvestment3.SecurityName;
> 
> 
> Mik P
> 
> 16/10/15
> 

Thanks, Mike Other than an odd Typo, that worked a treat.

I must confess, I have always struggled a bit with Subqueris which is the key
to your solution

Thanks agian

Phil

Back to comp.databases.ms-access | Previous | Next — Previous 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