qry to get the lowest 20% drop from the next day's price

[JRough]

Can you help me, I'm trying to code this by hand in SQL?
I want to compare the open price from qry1 to the open price from
qryII (next day)
I want to filter the largest 20% drops in price

qryI: filters out stocks greater than $5.00
qryII: gets the next day openPrice from those in qryI

thanks,

Select qry1.exchCD, qryI.permNo, qryI.date, qrtI.ticker, qryI.excCD,
qryI.Bidlo, qryI.AskHi, qrtI.Vol, qry1.Ask, qry1.OpenPrc
INNER JOIN qryII
ON qryI.permNo = qryII.permNo
Where (
SELECT TOP 20 PERCENT qry1.OpenPrc - qryII.OPenPrc)
ORDER BY exchCD, ASC)

Did I do qryIII right by choosing the top 20% and making the sort
Ascending?

thanks,
------------------------
I. qry_I
select exchCD, permNo, date, ticker, excCD, Bidlo, AskHi, Vol, Ask,
OpenPrc
WHERE prc > 5;

II. qry_II
select qryI.exchCD, qryI.permNo, qryI.dateadd(d,1,date), qry1.ticker,
qry1.excCD, qry1.Bidlo, qry1.AskHi, qry1.Vol, qry1.Ask, qry1.OpenPrc ;

 

[JRough]

I don't think splitting the dates into two queries is going to work.
I think it needs to be a correlated subquery where it compares the
date with the next day's date. I don't know how to do this.

 

[strive4peace]

firstly, do not use DATE as a fieldname, it is a reserved word

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html

~~

make a query with all the data and a calculated field for the next day

Name --> qOpenPrice_And_DateNextDay
SELECT [Tablename].*, DateValue([Date_fieldname]) + 1 as NextDay
FROM [Tablename]
WHERE <conditions>

Now, make another query on top of that, joining your tablename and query
on permNo, and NextDay from query to [Tablename].[Date_FieldName]

hopefully, this gets you the start that you need <smile>

if you have more questions, post back


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

 

[strive4peace]

little more info:

if joining on the date field to the NextDay field does not work
consistently, do this:

field --> NextDay
criteria --> DateValue([tablename].[Date_FieldName])

also, since your fieldnames will be the same in each list, you will need
to preface calculations with the query/tablename

field --> OpenPrcDiff: [tablename].OpenPrc - [queryname].OpenPrc


the DateValue function strips the Time component from a date if it is
there... and since it can be, it is often a good idea to make sure you
are just comparing the Date portion.


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

firstly, do not use DATE as a fieldname, it is a reserved word

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html

~~

make a query with all the data and a calculated field for the next day

Name --> qOpenPrice_And_DateNextDay
SELECT [Tablename].*, DateValue([Date_fieldname]) + 1 as NextDay
FROM [Tablename]
WHERE <conditions>

Now, make another query on top of that, joining your tablename and query
on permNo, and NextDay from query to [Tablename].[Date_FieldName]

hopefully, this gets you the start that you need <smile>

if you have more questions, post back


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

Can you help me, I'm trying to code this by hand in SQL?
I want to compare the open price from qry1 to the open price from
qryII (next day)
I want to filter the largest 20% drops in price

qryI: filters out stocks greater than $5.00
qryII: gets the next day openPrice from those in qryI

thanks,

Select qry1.exchCD, qryI.permNo, qryI.date, qrtI.ticker, qryI.excCD,
qryI.Bidlo, qryI.AskHi, qrtI.Vol, qry1.Ask, qry1.OpenPrc
INNER JOIN qryII
ON qryI.permNo = qryII.permNo
Where (
SELECT TOP 20 PERCENT qry1.OpenPrc - qryII.OPenPrc)
ORDER BY exchCD, ASC)

Did I do qryIII right by choosing the top 20% and making the sort
Ascending?

thanks,
------------------------
I. qry_I
select exchCD, permNo, date, ticker, excCD, Bidlo, AskHi, Vol, Ask,
OpenPrc
WHERE prc > 5;

II. qry_II
select qryI.exchCD, qryI.permNo, qryI.dateadd(d,1,date), qry1.ticker,
qry1.excCD, qry1.Bidlo, qry1.AskHi, qry1.Vol, qry1.Ask, qry1.OpenPrc ;