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

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

Problem with query

Back to article view | Back to comp.databases.ms-access

Contents

  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

#11157 — Problem with query

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

[toc] | [next] | [standalone]

#11158

On 03-10-15 13:21, 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());
>
>

> Phil
>

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());


you should have an index on the columns you use in GROYP BY and/or ORDER BY.
but, there's a calculation in that thing....
    (DateDiff("d",[ValDate],Date()))

start creating an index on:
   Investments.AccID,
   Investments.SecurityName,
and see if that helps enough.

These things are hard if there's no info on the complete table structure 
including the number of records.....


BTW:
GROUP BY (and ORDER BY) on ' DateDiff("d",[ValDate],Date())'  is a bad 
idea because you cannot use that in an index.

you could change that to yust  'Valdate', because that's a field you can 
add to an index.

[toc] | [prev] | [next] | [standalone]

#11159

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

[toc] | [prev] | [next] | [standalone]

#11160

On 03-10-15 16:46, Phil wrote:
> On 03/10/2015 13:25:06, Luuk wrote:
>
> Thanks
> The first query (Query3) runs very quickly,

Bt you said:
> I have created 2 queries that give the correct results, but they take ages to
> run.


'they', so BOTH queries take ages to run!

and now you are saying that this query run quickly ?

>
> Thanks
>
> Phil
>


end of problem....

[toc] | [prev] | [next] | [standalone]

#11161

On 03/10/2015 16:39:12, Luuk wrote:
> On 03-10-15 16:46, Phil wrote:
>> On 03/10/2015 13:25:06, Luuk wrote:
>>
>> Thanks
>> The first query (Query3) runs very quickly,
> 
> Bt you said:
>> I have created 2 queries that give the correct results, but they take ages to
>> run.
> 
> 
> 'they', so BOTH queries take ages to run!
> 
> and now you are saying that this query run quickly ?
> 
>>
>> Thanks
>>
>> Phil
>>
> 
> 
> end of problem....
> 

Obviously a misunderstanding.
The first query Query3 is pretty well instantaneous well under a second.
The second query takes about 90 seconds to run and I have a pretty fast
computer.

Phil

[toc] | [prev] | [next] | [standalone]

#11162

On 04-10-15 09:45, Phil wrote:
> On 03/10/2015 16:39:12, Luuk wrote:
>> On 03-10-15 16:46, Phil wrote:
>>> On 03/10/2015 13:25:06, Luuk wrote:
>>>
>>> Thanks
>>> The first query (Query3) runs very quickly,
>>
>> Bt you said:
>>> I have created 2 queries that give the correct results, but they take ages to
>>> run.
>>
>>
>> 'they', so BOTH queries take ages to run!
>>
>> and now you are saying that this query run quickly ?
>>
>>>
>>> Thanks
>>>
>>> Phil
>>>
>>
>>
>> end of problem....
>>
>
> Obviously a misunderstanding.
> The first query Query3 is pretty well instantaneous well under a second.
> The second query takes about 90 seconds to run and I have a pretty fast
> computer.
>
> Phil
>

Ok the Second query was:
> SELECT Query3.SecurityName, Query3.ValDate, Query3.SharePrice, Query3.Expr2
> FROM Query3
> WHERE (((Query3.Expr2)=DMin("Expr2","Query3","InvestmentID = " &
> [Valuations]![InvestmentID])));

Question:
- Do you have an index on '[Valuations]![InvestmentID]'?

What time does this query take:
SELECT
   Query3.SecurityName,
   Query3.ValDate,
   Query3.SharePrice,
   Query3.Expr2
FROM Query3, (SELECT Min(Exp2) AS MinimumValue
        FROM Valutations
        WHERE Query3.InvermentID = Valutations.InvestmentID)
WHERE MinimumValue = Query3.Expr2

[toc] | [prev] | [next] | [standalone]

#11163

On 04/10/2015 14:38:15, Luuk wrote:
> SELECT
> Query3.SecurityName,
> Query3.ValDate,
> Query3.SharePrice,
> Query3.Expr2
> FROM Query3, (SELECT Min(Exp2) AS MinimumValue
> FROM Valutations
> WHERE Query3.InvermentID = Valutations.InvestmentID)
> WHERE MinimumValue = Query3.Expr2

Thanks, that has put me roughly on the right track.
Unfortunatly the SQL you suggested shows in Design View
Query3 and a second table / Query called %$##@_Alias with no relationships.
I can see what you're getting at using a subquery, though.

Query3 remains unalterd.
Query4 is abandone
Query5 is
SELECT Query3.InvestmentID, Min(Query3.Expr2) AS MinOfExpr2
FROM Query3
GROUP BY Query3.InvestmentID;

Query6 is 
SELECT Query3.*
FROM Query3 INNER JOIN Query5 ON (Query3.Expr2 = Query5.MinOfExpr2) 
AND (Query3.InvestmentID = Query5.InvestmentID);

This runs pretty well instantaneously.
However, not that it is important, it would be nice to use only 2 queries.

Thanks again

Phil

[toc] | [prev] | [next] | [standalone]

#11166

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

[toc] | [prev] | [next] | [standalone]

#11168

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

[toc] | [prev] | [next] | [standalone]

#11211

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

[toc] | [prev] | [next] | [standalone]

#11212 — Closed: Problem with query

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web