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

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

Re: Problem with query

From "Phil" <phil@stantonfamily.co.uk>
Newsgroups comp.databases.ms-access
Subject Re: Problem with query
Date 2015-10-03 15:46 +0100
Organization Aioe.org NNTP Server
Message-ID <muopod$hal$1@speranza.aioe.org> (permalink)
References <muodnr$li4$1@speranza.aioe.org> <560fc911$0$23829$e4fe514c@news.xs4all.nl>

Show all headers | View raw

On 03/10/2015 13:25:06, Luuk wrote:
> 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());


Thanks
The first query (Query3) runs very quickly, 
The problem is obviously with the time taken to evaluate
DMin("Expr2","Query3","InvestmentID = " & [Valuations]![InvestmentID])

Don't think indexing will make any difference

There are about 3880 records returned from Query 3, and 68 records from the
second Query (Query4)

Thanks

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