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

Problem with query

From	"Phil" <phil@stantonfamily.co.uk>
Newsgroups	comp.databases.ms-access
Subject	Problem with query
Date	2015-10-03 12:21 +0100
Organization	Aioe.org NNTP Server
Message-ID	<muodnr$li4$1@speranza.aioe.org> (permalink)

Show all headers | View raw

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

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